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