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 <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 raduzugrav...@gmail.com radu.zugr...@yahoo.com
