Re: Hstore: Query speedups with Gin index

On Thu, Aug 22, 2013 at 11:55 PM, Blake Smith <blakesmith0@gmail.com> wrote:
> We've been experiencing slow "@>" queries involving an hstore column that's
> covered by a Gin index. At the current postgresql git HEAD, the hstore <->
> gin interface produces the following text items to be indexed:
>
> hstore: "'a'=>'1234', 'b'=>'test'"
> Produces indexed text items: "Ka", "V1234", "Kb", "Vtest"
>
> For the size of our production table (10s of millions of rows), I observed
> significant query speedups by changing the index strategy to the following:
What is the order of the speedup?

> hstore: "'a'=>'1234', 'b'=>'test'"
> Produces indexed text items: "Ka", "KaV1234", "Kb", "KbVtest"
I am not a gin expert, but do you see the same speedup for tables with
a lower number of rows, or even a degradation in performance?

> 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, this patch
would be applied only on the current master branch for 9.4 and above.

> Patch attached. Any thoughts on this change?
Please add your patch to the next commit fest that will begin in 3
weeks so as you could get more formal review.
https://commitfest.postgresql.org/action/commitfest_view?id=19

Regards,
-- 
Michael