From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | Paul Norman <penorman(at)mac(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Finding common hstore key=>value pairs with hstore |
Date: | 2013-01-27 18:41:40 |
Message-ID: | CAMkU=1z0eGjKY8F2=D=3CwaJnkXcbPGY4eUw5jX_mc04GGUuiQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Jan 27, 2013 at 3:09 AM, Paul Norman <penorman(at)mac(dot)com> wrote:
> I am in a situation where I have two tables, a and b, each with a hstore
> column called tags. Both tags columns have a GIN index on them. I want to
> find rows of a and b where the both have a particular hstore key and that
> key is the same.
>
> One way to do this would be SELECT * FROM a JOIN b ON a.tags -> 'foo' =
> b.tags -> 'foo'; This would not use the indexes.
>
> I would like some way that makes use of the two indexes. ON a.tags @>
> hstore('foo', b.tags -> 'foo') would be better, making use of the a.tags
> index, but not the b.tags one. Is there any way to use both?
ON a.tags @> hstore('foo', b.tags -> 'foo') does not do what you want,
because if there is an a.tags of hstore('foo',NULL), then it will
match every row of b whose tags does not contain the 'foo' key.
You need to protect that by first checking for existence of the key:
ON b.tags ? 'foo' and a.tags @> hstore('foo', b.tags -> 'foo')
Which will also allow the 2nd index to be used.
Cheers,
Jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2013-01-27 19:41:30 | Re: Optimizing select count query which often takes over 10 seconds |
Previous Message | Alexander Farber | 2013-01-27 17:25:02 | Re: Optimizing select count query which often takes over 10 seconds |