Re: [GENERAL] I did some testing of GIST/GIN vs BTree indexing…

On Dec 10, 2014, at 19:38 , Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> Are you saying when you use a GIN index on a,b,c fields, you can do
> lookups on them independently, like 'c'? I was not aware that works,
> but it might. I know it doesn't work for traditional btree as the index
> is hierarchical. You can look up things like a,c and it will skip over
> 'b', but doing 'c' alone doesn't make any sense for traditional btree.
>
> It would be interesting if that was true, though, and something we
> should more clearly document. Your testing is very useful here.

This page:

http://www.postgresql.org/docs/9.4/static/indexes-multicolumn.html

says:

A multicolumn GiST index can be used with query conditions that involve any subset of the index's columns. Conditions on additional columns restrict the entries returned by the index, but the condition on the first column is the most important one for determining how much of the index needs to be scanned. A GiST index will be relatively ineffective if its first column has only a few distinct values, even if there are many distinct values in additional columns.

A multicolumn GIN index can be used with query conditions that involve any subset of the index's columns. Unlike B-tree or GiST, index search effectiveness is the same regardless of which index column(s) the query conditions use.

This appears to imply greater (complete?) flexibility in using non-leading columns with GIST and GIN indexes, or am I misunderstanding something? This is the whole reason I’ve started investigating this — particularly given what it says about GIN.