From: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
---|---|
To: | Blake Smith <blakesmith0(at)gmail(dot)com> |
Cc: | PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Hstore: Query speedups with Gin index |
Date: | 2013-08-26 01:36:54 |
Message-ID: | CAB7nPqTZdPCY-hh9kr8L2MzW1cEeJukEbutiP3dMhhrkKXyF+A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Thu, Aug 22, 2013 at 11:55 PM, Blake Smith <blakesmith0(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-08-26 02:11:50 | Re: Hstore: Query speedups with Gin index |
Previous Message | Jim Nasby | 2013-08-25 22:47:33 | Re: pg_system_identifier() |