Try and add a multicolumn index that is specifically tuned for that query.
If you have a query that, for example, selects rows from the t1 table on the a column and sorts on the b column, 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);
PostgreSQL 9.2 introduced a new plan type: index-only scans. This allows you to utilize a technique known as covering indexes. If all the columns requested by the SELECT list of a query are available in an index, that particular index is a covering index for that query. This technique allows PostgreSQL to fetch valid rows directly from the index, without accessing the table (heap), so performance improves significantly.
Another often underestimated (or unknown) feature of PostgreSQL is partial indexes. If you use SELECT on a condition, especially if this condition only selects a small number of rows, you can use a conditional index on that expression, like this:
CREATE INDEX t1_proc_ndx ON t1(i1)
WHERE needs_processing = TRUE;
The index will be used by queries that have a WHERE clause that includes the index clause, like the following
SELECT id, ... WHERE needs_processing AND i1 = 5;
There are many types of indexes in Postgres, so you may find that there are multiple types of indexes that can be used for a particular task, and many options to choose from:
- Identifier data: BTREE, HASH
- Text data: GIST, GIN
- JSONB or XML data:GIN
- Time-range data: BRIN
- Geographical data: BRIN, GIST, and SP-GIST
Performance gains in Postgres can also be obtained with another technique: clustering tables on specific indexes. However, 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 multicolumn index, you can use the following command:
CLUSTER t1_a_b_ndx ON t1;
Clustering a table on an index rewrites the whole table in index order. This can lock the table for a long time, so don't do it on a busy system. Also, CLUSTER is a one-time command. New rows do not get inserted in cluster order, and 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 following cluster commands. It is enough to type this:
CLUSTER t1;
It still takes time to rewrite the entire table, though it is probably a little faster once most of the table is in index order.