PostgreSQL 11 Administration Cookbook

More ideas with indexes

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_idx 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 of 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. ...