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

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,

[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<mailto: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>:
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<mailto:raduzugravu90(at)gmail(dot)com>
radu(dot)zugravu(at)yahoo(dot)com<mailto:radu(dot)zugravu(at)yahoo(dot)com>

This mail was received via Mail-SeCure System.