Database Reference
In-Depth Information
If you set the
default_statistics_target
for a database, then it takes effect the next
time anyone connects to the database. So, you should either reconnect, or set it for current
session with direct set
default_statistics_target
=
300
before
ANALYSE
if you want
the new value to be used.
Adding a multi-column index tuned specifically for that query
If you have a query that for example selects rows from table
t1
on column
a
, and sorts on
column
b
, then creating the following index enables PostgreSQL to do it all in one index scan:
CREATE INDEX t1_a_b_ndx ON t1(a,b);
Adding a special conditional index
If you
SELECT
on some condition (and especially if this condition only selects a small number
of rows), you can use a conditional index on that expression like the following:
CREATE INDEX t1_proc_ndx ON t1(i1)
WHERE needs_processing = TRUE;
It is used in a query like the following for finding rows that need some processing to be done:
SELECT id, ... WHERE needs_processing;
Cluster tables on specific indexes
Index access may still not be very efficient if the values accessed by the index are distributed
randomly all over the table. If you know that some fields are likely to be accessed together,
then cluster the table on an index defined on those fields. For a multi-column index shown
above you can use the following command:
CLUSTER t1_a_b_ndx ON t1
Clustering a table on index rewrites the whole table in index order, which can lock the table
for a long time, so don't do it on a busy system. Also,
CLUSTER
is an one-time command, and
new rows do not get inserted in cluster order, so to keep the performance gains, you may need
to cluster the table every now and then.
Once a table is clustered on an index, you don't need to specify the index name in subsequent
cluster commands, and can simply say:
CLUSTER t1;
It still takes time to rewrite the whole table, though it is probably a little faster once most
of the table is in index order.
