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

On Wed, Dec 10, 2014 at 05:27:16PM -0800, Guyren Howe wrote:
> >> Given the futility of database benchmarking in general, I didn’t
> >> want to go any further with this. What I was interested in was
> >> whether it might be worth switching from BTree to GIST/GIN indexes
> >> with regular sorts of data. It appears to be the case that GIST and
> >> GIN are often better than BTree in general, and given their much
> >> greater flexibility in satisfying queries on different columns, it
> >> might even be the case that one should recommend a single GIST or
> >> GIN index on the frequently-searched columns of a table in most
> >> cases?
> >
> > What GiST and GIN "ops" did you use for the testing?  Was it
> > contrib/btree_gist and contrib/btree_gin?
>
> Sorry; yes. I didn’t realize there was any practical alternative. Is
> there another option I should test?

Well, GIN and GiST are usually used for data that btree can't index,
e.g. JSONB, 2-dimensional points  I  thought the only win for
contrib/btree_gist and contrib/btree_gin would be for indexes with many
duplicates.

> > You might want to look at my presentation on indexing:
> >
> >     http://momjian.us/main/presentations/features.html#indexing
> >
> > It is my understanding that btree is best for single-match indexes
> > like unique indexes, or range queries (not range data types), while
> > GIN is best for indexes with many duplicates.  GiST is more of an
> > indexing framework and I am unclear where it is best except in cases
> > where is the only option, like geometry and perhaps range (shared
> > with SP-GiST).  With the 9.4 GIN improvements I am unclear if GiST
> > is ever better for full text indexing compared to GIN.
>
> Thanks for this. I will look at your presentation.
>
> As I say, if folks can help me work out the definitive answer to all
> this, I’d love to contribute it to the docs.

Great, thanks.

> My starting point was this: given that GIN (and GIST, maybe, the docs
> sort-of say “sort of”) can use arbitrary index fields, rather
> than left to right, if you’re in a situation of wanting to query
> arbitrary subsets of some of the fields on a table, it seems likely
> that a GIN index might be called for. Is that right? The description
> I’ve been able to find (that it’s a BTree with more sophisticated
> handling of duplicates) would surely entail otherwise, but this is
> clearly what the docs say.

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.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +