Re: Query planning around one key of a multi-column index

On Wed, Dec 18, 2019 at 11:19 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
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

>