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

On Wed, Dec  3, 2014 at 01:15:50AM -0800, Guyren Howe wrote:
> GIN is certainly not the “three times” size suggested in the docs, but perhaps
> that just hasn’t been updated for the 9.4 improvements. Certainly, there isn’t
> sufficient difference here to make the BTree advantage compelling in most
> applications.

I am sure the docs need updating for 9.4 --- any suggestions?

> 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?

> I would absolutely *love* to hear what this community has to say about this
> question: should one consider GIST or GIN indexes on regular old numeric/text
> columns? When, theoretically? When, in practice (ie does anyone have comparable
> benchmarks on 9.4?). Other thoughts?

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.

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

  + Everyone has their own god. +