Search code examples
sqlpostgresqlindexinghstore

PostgreSQL: sequential table scan on Hstore column despite having a GiN index

I have a table with a hstore column and roughly 22 mio records (the ways table from a partial osm-database).

Despite having a GIN index on the hstore column, queries for a specific tag result in a sequential table scan that takes > 60 sec to return a single column.

What i have been doing so far.

  1. I created the GIN index using pgAdminIII.
  2. Executing vacuum analayze
  3. Executing a query of the kind: select id from table where tags->'name'='foo'
  4. Deleting index and starting from 1. again ...
  5. [Edit] As suggested by the user a_horse_with_no_name I updated the table statistics by executing analyze on the table. But that had no effect.

enter image description here

You can see the query plan here. For some reason the explain analyze takes only ~20 sec to complete.

How can I properly index a hstore column on a large table like this, to reduce query execution cost significantly?

Thank you for your help!

Solution

  • I see two possible solutions:

    If you always query that key value for equality you can use an a B-Tree index on the expression (`tags -> 'name')

    create index idx_name on ways ( (tags -> 'name') );

    A quick test has shown that Postgres does use the index to find if a key value is present in the hstore column, but apparently not for finding the associated value.

    So you could try to add a condition to test for that key value as well: 

    select id
from ways
where tags ? 'name' 
  and tags -> 'name' = 'Wiehbergpark';

    If all rows contain that key, it might not help though.