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

Hi Yuri and Radu-Stefan

I would'nt give too fast on PostgreSQL!
When looking at your query plan I wonder if one could reformulate the query
to compute the ST_DWithin first (assuming you have an index on the node
geometries!) before it filters the tags.
To investigate that you could formulate a CTE query [1] which computes the
ST_DWithin first.

Yours, Stefan

[1] http://www.postgresql.org/docs/9.2/static/queries-with.html

2013/7/8 Yuri Levinsky <yuril(at)celltick(dot)com>

> Dear Radu-Stefan,****
>
> It seems to me that you trying hard to solve a problem by SQL that
> probably can't be solved. Take a look please on Apache HBase. You can
> access HBase from PostgreSQL as well by utilizing Java or Python for
> example. ****
>
> ** **
>
> *Sincerely yours*,****
>
> ** **
>
> [image: Description: Celltick logo_highres]****
>
> Yuri Levinsky, DBA****
>
> Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel****
>
> Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222****
>
> ** **
>
> *From:* pgsql-performance-owner(at)postgresql(dot)org [mailto:
> pgsql-performance-owner(at)postgresql(dot)org] *On Behalf Of *Radu-Stefan Zugravu
> *Sent:* Monday, July 08, 2013 12:20 PM
> *To:* Richard Huxton
> *Cc:* pgsql-performance(at)postgresql(dot)org
>
> *Subject:* Re: [PERFORM] How to properly index hstore tags column to
> faster search for keys****
>
> ** **
>
> 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.****
>
> ** **
>
> 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****
>
>
>
> ****
>
> ** **
>
> -- ****
>
> Radu-Stefan Zugravu****
>
> 0755 950 145
> 0760 903 464
> raduzugravu90(at)gmail(dot)com
> radu(dot)zugravu(at)yahoo(dot)com ****
>
>
> This mail was received via Mail-SeCure System.****
>