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

On Dec 10, 2014, at 19:38 , Bruce Momjian <bruce@momjian.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
onadditional columns restrict the entries returned by the index, but the condition on the first column is the most
importantone for determining how much of the index needs to be scanned. A GiST index will be relatively ineffective if
itsfirst 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
orGiST, 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
misunderstandingsomething? This is the whole reason I’ve started investigating this — particularly given what it says
aboutGIN.