From: Richard Huxton <dev@archonet.com>
To: Radu-Stefan Zugravu <raduzugravu90@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to properly index hstore tags column to faster search for keys
Date: Mon, 08 Jul 2013 09:53:28 +0100
Message-ID: <51DA7E08.6090007@archonet.com> (raw)
In-Reply-To: <CAD4BGM0GYXZp0Vr9m=+MKssE0=M-6kVBTYUrbTyz3m0cM+HmAg@mail.gmail.com>
References: <CAD4BGM1JbMiT8jYXSjg+zP+fN0kwgyZOKq46xzR=h9=GDpVAiA@mail.gmail.com>
<51DA6DE9.4030309@archonet.com>
<CAD4BGM0GYXZp0Vr9m=+MKssE0=M-6kVBTYUrbTyz3m0cM+HmAg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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-sear...
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, raduzugravu90@gmail.com
Subject: Re: How to properly index hstore tags column to faster search for keys
In-Reply-To: <51DA7E08.6090007@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox