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 <yu...@celltick.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-ow...@postgresql.org [mailto: > pgsql-performance-ow...@postgresql.org] *On Behalf Of *Radu-Stefan Zugravu > *Sent:* Monday, July 08, 2013 12:20 PM > *To:* Richard Huxton > *Cc:* pgsql-performance@postgresql.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 <d...@archonet.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 > raduzugrav...@gmail.com > radu.zugr...@yahoo.com **** > > > This mail was received via Mail-SeCure System.**** >
<<image002.jpg>>
