May 2018
Intermediate to advanced
576 pages
30h 25m
English
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. ...