Re: Hstore: Query speedups with Gin index

Michael, take a look on http://obartunov.livejournal.com/171959.html
As for the indexing stuff we already thought many times about key&value
mixing, but real solution, probably, could come from spgist and gin
combination. I mean, spgist (suffix array) instead of btree for avoiding
key duplication, which is real stopper for key.value mixing, especially,
for deep nesting structures. We'll research further and probably will
develop a prototype of such hybrid search tree.

Oleg

On Mon, Aug 26, 2013 at 6:11 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> > On Thu, Aug 22, 2013 at 11:55 PM, Blake Smith <blakesmith0(at)gmail(dot)com>
> wrote:
> >> The combined entry is used to support "contains (@>)" queries, and the
> key
> >> only item is used to support "key contains (?)" queries. This change
> seems
> >> to help especially with hstore keys that have high cardinalities.
> Downsides
> >> of this change is that it requires an index rebuild, and the index will
> be
> >> larger in size.
>
> > Index rebuild would be a problem only for minor releases,
>
> That's completely false; people have expected major releases to be
> on-disk-compatible for several years now. While there probably will be
> future releases in which we are willing to break storage compatibility,
> a contrib module doesn't get to dictate that.
>
> What might be a practical solution, especially if this isn't always a
> win (which seems likely given the index-bloat risk), is to make hstore
> offer two different GIN index opclasses, one that works the traditional
> way and one that works this way.
>
> Another thing that needs to be taken into account here is Oleg and
> Teodor's in-progress work on extending hstore:
> https://www.pgcon.org/2013/schedule/events/518.en.html
> I'm not sure if this patch would conflict with that at all, but it
> needs to be considered.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>