Re: Finding common hstore key=>value pairs with hstore

On 1/27/2013 3:09 AM, Paul Norman 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?
>
> 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

Seems to me like you should be using a separate field for this 'foo'
defined as a foreign key.

--
john r pierce 37N 122W
somewhere on the middle of the left coast