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@xxxxxxxxxx> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
