On Wed, Dec 18, 2019 at 11:19 PM Wells Oliver <wells.oliver@xxxxxxxxx> wrote:
But so are you guys!Thanks, this is helpful.Is it really always true that querying off any column that's not the first column of a multi-column index will NOT use that index?
Are you talking only about btree indexes? Those are certainly the most common, but other index types exist and have other properties.
Anyway, it is not always true for btree indexes. The planner might decide to use the index as a skinny version of the table, and scan the full index to extract some non-leading column from it.
How likely it is to do this will depend mainly on how much smaller the index is than the table, and your relative settings of seq_page_cost and random_page_cost. (Unlike some other products, PostgreSQL will scan the index in logical order, not physical order, even though it doesn't care about the order and this generates a more random IO pattern, because this is the only practical way to protect against concurrent page splits while it scans). Unfortunately, the EXPLAIN output does not make it clear when a index is being used in this way, rather than the more traditional way.
Cheers,
Jeff
