Re: How to properly index hstore tags column to faster search for keys

On 08/07/13 09:31, Radu-Stefan Zugravu wrote:

Hi,
Thank you for your answer.
My EXPLAIN ANALYZE output can be found here:
http://explain.depesz.com/s/Wbo.

Thanks

Also, there is a discution on this subject on dba.stackexchange.com

<http://dba.stackexchange.com>:
http://dba.stackexchange.com/questions/45820/how-to-properly-index-hstore-tags-column-to-faster-search-for-keys

Thanks - also useful to know.

I can't see anything wrong with your query. Reading it from the bottom upwards:
1. Index used for "historic" search - builds a bitmap of blocks
2. Index used for geometry search - builds a bitmap of blocks
3. See where the bitmaps overlap (BitmapAnd)
4. Grab those disk blocks and find the rows (Bitmap Heap Scan)

The whole thing takes under 20ms - what sort of time were you hoping for?

The bulk of it (15ms) is taken up locating the "historic" rows. There are 36351 of those, but presumably most of them are far away on the map.

Could you post the explain without the index? I'm curious as to how slow it is just testing the tags after doing the geometry search.

--
  Richard Huxton
  Archonet Ltd