From: | Radu-Stefan Zugravu <raduzugravu90(at)gmail(dot)com> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: How to properly index hstore tags column to faster search for keys |
Date: | 2013-07-08 09:20:13 |
Message-ID: | CAD4BGM0Z8uBvg4ypD7YaxBkSONRKjreiAbOE7cAw7nGMbKbOmA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Any improvement is welcomed. The overall performance of the application is
not very good. It takes about 200 seconds to compute a path for not so far
star and end points. I want to improve this query as much as I can.
How exactly should I post the explain without the index? Do I have to drop
all created indexes for the tags column? It takes some time to create them
back.
On Mon, Jul 8, 2013 at 11:53 AM, Richard Huxton <dev(at)archonet(dot)com> wrote:
> 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 <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<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
>
--
Radu-Stefan Zugravu
0755 950 145
0760 903 464
raduzugravu90(at)gmail(dot)com
radu(dot)zugravu(at)yahoo(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2013-07-08 10:27:34 | Re: How to properly index hstore tags column to faster search for keys |
Previous Message | Richard Huxton | 2013-07-08 08:53:28 | Re: How to properly index hstore tags column to faster search for keys |