PostgreSQL: Index-Only Scans with Partial Indexes

Partial sun lurking out of the water.

Another posts of my PostgreSQL 9.6 series. This time, I am talking about commit 9/299.  The complete discussion can be found here.

Title index-only scans with partial indexes
Topic Performance
Created 2015-07-10 18:32:48
Last modified 2016-03-31 22:00:55 (2 months, 4 weeks ago)
Latest email 2016-04-01 02:39:49 (2 months, 4 weeks ago)
Status
2016-03: Committed
2016-01: Moved to next CF
2015-11: Moved to next CF
2015-09: Moved to next CF
Authors Tomas Vondra, Kyotaro Horiguchi
Reviewers Kyotaro Horiguchi, Kevin Grittner, Konstantin Knizhnik
Committer Tom Lane (tgl)

This commit basically allows to use partial indexes to participate in index-only scans. Index-only scans, as the name suggests, use the corresponding index only to sift through the data thus being much faster than going back and forth to the related table.

However, some indexes, namely partial indexes, that cannot be used that easily for this kind of optimization. Partial indexes are indexes which include a WHERE clause in order to index a slice of the data only.

Quoting Tomas Vondra:

In other words, unless you include columns from the index predicate to the index, the planner will decide index only scans are not possible. Which is a bit unfortunate, because those columns are not needed at runtime, and will only increase the index size (and the main benefit of partial indexes is size reduction).

Initial discussion on this topic concerns the increase of complexity and runtime of the planning phase which is usually the case when the planner needs to act more and more intelligent. Properly discussed and being delayed three times, the patch was merged into the development branch of PostgreSQL by Tom Lane on 31th of March 2016.

Thanks again for another improvement on the performance of the PostgreSQL-Server.


Sven