Finding common hstore key=>value pairs with hstore

From: Paul Norman <penorman(at)mac(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Finding common hstore key=>value pairs with hstore
Date: 2013-01-27 11:09:36
Message-ID: 108f01cdfc7e$ccbbc8f0$66335ad0$@mac.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

I suppose the other problem is the hstore statistics gathering is
questionable enough that the query plan it comes up with could be absolutely
abysmal. This is of course not unique to this particular problem but is a
common problem with hstore columns

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2013-01-27 11:16:33 Re: Finding common hstore key=>value pairs with hstore
Previous Message Jasen Betts 2013-01-27 11:09:34 Re: Yet Another Timestamp Question: Time Defaults