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

[Richard Huxton]

On 07/07/13 08:28, Radu-Stefan Zugravu wrote:

Each node has a geometry column called geom and a hstore column
called tags. I need to extract nodes along a line that have certain
keys in the tags column. To do that I use the following query:



SELECT id, tags
FROM nodes
WHERE ST_DWithin(nodes.geom, ST_MakeLine('{$geom1}', '{$geom2}'), 0.001)
AND tags ? '{$type}';



CREATE  INDEX  nodes_tags_idx ON nodes USING GIN(tags);

After creating the index I searched again for nodes using the same first
query but there is no change in performance.

How can I properly use GIN and GIST to index tags column so I can faster
search for nodes that have a certain key in tags column?


Your index definition looks OK. Try showing the output of EXPLAIN 
ANALYSE for your query - that way we'll see if the index is being used. 
You can always paste explain output to: http://explain.depesz.com/ if 
it's too long for the email.


--
  Richard Huxton
  Archonet Ltd


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

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

[Radu-Stefan Zugravu]

Hi,
Thank you for your answer.
My EXPLAIN ANALYZE output can be found here: http://explain.depesz.com/s/Wbo
.
Also, there is a discution on this subject on dba.stackexchange.com:
http://dba.stackexchange.com/questions/45820/how-to-properly-index-hstore-tags-column-to-faster-search-for-keys


On Mon, Jul 8, 2013 at 10:44 AM, Richard Huxton  wrote:

> On 07/07/13 08:28, Radu-Stefan Zugravu wrote:
>
>> Each node has a geometry column called geom and a hstore column
>> called tags. I need to extract nodes along a line that have certain
>> keys in the tags column. To do that I use the following query:
>>
>
>  SELECT id, tags
>> FROM nodes
>> WHERE ST_DWithin(nodes.geom, ST_MakeLine('{$geom1}', '{$geom2}'), 0.001)
>> AND tags ? '{$type}';
>>
>
>  CREATE  INDEX  nodes_tags_idx ON nodes USING GIN(tags);
>>
>> After creating the index I searched again for nodes using the same first
>> query but there is no change in performance.
>>
>> How can I properly use GIN and GIST to index tags column so I can faster
>> search for nodes that have a certain key in tags column?
>>
>
> Your index definition looks OK. Try showing the output of EXPLAIN ANALYSE
> for your query - that way we'll see if the index is being used. You can
> always paste explain output to: http://explain.depesz.com/ if it's too
> long for the email.
>
> --
>   Richard Huxton
>   Archonet Ltd
>



-- 
Radu-Stefan Zugravu
0755 950 145
0760 903 464
raduzugrav...@gmail.com
radu.zugr...@yahoo.com

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

[Richard Huxton]

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/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


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

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

[Radu-Stefan Zugravu]

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  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/**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

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

[Richard Huxton]

On 08/07/13 10:20, Radu-Stefan Zugravu wrote:

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.


So you have to call this query 1000 times with different start and end 
points?


> I want to improve this query as much as I can.

There's only two ways I can see to get this much below 20ms. This will 
only work if you want a very restricted range of tags.


Drop the tag index and create multiple geometry indexes instead:

CREATE INDEX node_geo_tourist_idx  WHERE tags ? 'tourist';
CREATE INDEX node_geo_tourist_idx  WHERE tags ? 'history';
etc.

This will only work if you have a literal WHERE clause that checks the 
tag. It should be fast though.



The second way would be to delete all the nodes that aren't tagged 
tourist or history. That assumes you are never interested in them of course.



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.


Not important - I was just curious.

--
  Richard Huxton
  Archonet Ltd


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

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

[Radu-Stefan Zugravu]

I do call the query for each neighbour node to find which one is better in
building my path.
I think I will try the first way you mentioned. I also found some
references using BTREE indexes:

CREATE INDEX nodes_tags_btree_historic_idx on nodes USING BTREE ((tags ?
'historic'));
CREATE INDEX nodes_tags_btree_tourist_idx on nodes USING BTREE ((tags ?
'tourist));

Do you think this could make a difference?


On Mon, Jul 8, 2013 at 1:27 PM, Richard Huxton  wrote:

> On 08/07/13 10:20, Radu-Stefan Zugravu wrote:
>
>> 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.
>>
>
> So you have to call this query 1000 times with different start and end
> points?
>
>
> > I want to improve this query as much as I can.
>
> There's only two ways I can see to get this much below 20ms. This will
> only work if you want a very restricted range of tags.
>
> Drop the tag index and create multiple geometry indexes instead:
>
> CREATE INDEX node_geo_tourist_idx  WHERE tags ? 'tourist';
> CREATE INDEX node_geo_tourist_idx  WHERE tags ? 'history';
> etc.
>
> This will only work if you have a literal WHERE clause that checks the
> tag. It should be fast though.
>
>
> The second way would be to delete all the nodes that aren't tagged tourist
> or history. That assumes you are never interested in them of course.
>
>
>  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.
>>
>
> Not important - I was just curious.
>
>
> --
>   Richard Huxton
>   Archonet Ltd
>



-- 
Radu-Stefan Zugravu
0755 950 145
0760 903 464
raduzugrav...@gmail.com
radu.zugr...@yahoo.com

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

[idc danny]

Hi Stefan
1 - If you have a fixed data that does not change a lot, like I assume is your 
fixed 'map' try implementing in your app the hashtrie method. This looks as 
better approach as your query is quite fast. Usually I am starting to query my 
queries (or the query planner) when they start to take more the 2 seconds. The 
fact that you continuously call it for your next node it might not be the best 
approach.
2 - As mentioned by Richard, try either to delete the nodes that does not 
belong to "historic" / "tourist" or simply split the table in 2. One that have 
only them and the rest to the other table. Assuming this will not change a lot 
the other already implemented queries in your app (because you'll have to make 
a 1-to-1 now) it might save your day.
Danny



 From: Radu-Stefan Zugravu 
To: Richard Huxton  
Cc: pgsql-performance@postgresql.org 
Sent: Monday, July 8, 2013 2:01 PM
Subject: Re: [PERFORM] How to properly index hstore tags column to faster 
search for keys
 


I do call the query for each neighbour node to find which one is better in 
building my path.I think I will try the first way you mentioned. I also found 
some references using BTREE indexes:

CREATE INDEX nodes_tags_btree_historic_idx on nodes USING BTREE ((tags ? 
'historic'));
CREATE INDEX nodes_tags_btree_tourist_idx on nodes USING BTREE ((tags ? 
'tourist));


Do you think this could make a difference?



On Mon, Jul 8, 2013 at 1:27 PM, Richard Huxton  wrote:

On 08/07/13 10:20, Radu-Stefan Zugravu wrote:
>
>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.
>>
>
So you have to call this query 1000 times with different start and end points?
>
>
>> I want to improve this query as much as I can.
>
>
There's only two ways I can see to get this much below 20ms. This will only 
work if you want a very restricted range of tags.
>
>Drop the tag index and create multiple geometry indexes instead:
>
>CREATE INDEX node_geo_tourist_idx  WHERE tags ? 'tourist';
>CREATE INDEX node_geo_tourist_idx  WHERE tags ? 'history';
>etc.
>
>This will only work if you have a literal WHERE clause that checks the tag. It 
>should be fast though.
>
>
>The second way would be to delete all the nodes that aren't tagged tourist or 
>history. That assumes you are never interested in them of course.
>
>
>
>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.
>>
>
Not important - I was just curious.
>
>
>-- 
>  Richard Huxton
>  Archonet Ltd
>


-- 

Radu-Stefan Zugravu0755 950 145
0760 903 464
raduzugrav...@gmail.com
radu.zugr...@yahoo.com 

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

[Yuri Levinsky]

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-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 
mailto: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/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.
<>

[PERFORM] Performance autovaccum

[Jeison Bedoya]

Hi, i have a postgresql 9.2.2, but i don´t use autovaccum but i want to 
begin to use it.  some recommendation about the optimal configuration? 
or some link to explain it.


Thanks

--
Atentamente,


JEISON BEDOYA DELGADO
Adm. Servidores y Comunicaciones
AUDIFARMA S.A.



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] 8.4 to 9.2 migration performance

[Tom Harkaway]

I am migrating a Postgres 8.4 installation on a dedicated server to Postgres 
9.2 running on a Virtual Machine. A sample query that run in 10 minutes on the 
8.4 installation take 40 minutes on the 9.2 installation.

Current Server, Postgres 8.4

* 6-core, 3GHz AMD system

* 12GB of RAM

* 4 SATA drive RAID-1 storage

* Mandriva OS

* SQL encoding and 'C' collation

Virtual Machine, Postgres 9.2 ( two different systems)

* 4-core,  3Ghz Intel system

* 12GB or RAM

* SAS storage on one, and 4-SATA drive RAID-10 system on second

* CentOS 6.3 OS

* UTF-8 encoding, and I have tried both 'C' and en_US collation

The first VM is at a local Data Center and the second in on a dedicated server 
in my office. Both give similar results.
The data, indexes and constraints have all been successfully migrated to the 
new system.
I have tuned the VM systems using pgtune with no significant before and after 
difference.
The 'explain' output for the query is very different between the two systems.

It seems like I am missing some simple step for there to be such a huge 
performance difference.

Any suggestions on what else to text/check would be very much appreciated.

Tom

[PERFORM] Process in state BIND, authentication, PARSE

[Jeison Bedoya]

Hi, i want to know why in my database the process stay in BID, PARSE, 
autentication, startup by a couple minuts, generating slow in the 
process, perhaps tunning parameters? or configuration of operating 
system (Linux RHEL 6).


Thanks by your help

--
Atentamente,


JEISON BEDOYA DELGADO
Adm. Servidores y Comunicaciones
AUDIFARMA S.A.



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] 8.4 to 9.2 migration performance

[bricklen]

On Mon, Jul 8, 2013 at 9:21 AM, Tom Harkaway  wrote:

>  The ‘explain’ output for the query is very different between the two
> systems.
>

You ran ANALYZE after loading the data?  Can you post the query and EXPLAIN
ANALYZE output?
Also, some tips on getting answers with (potentially) less ping pong
discussion can be found here:
https://wiki.postgresql.org/wiki/Slow_Query_Questions

Re: [PERFORM] Process in state BIND, authentication, PARSE

[Andrew Dunstan]

On 07/08/2013 12:22 PM, Jeison Bedoya wrote:
Hi, i want to know why in my database the process stay in BID, PARSE, 
autentication, startup by a couple minuts, generating slow in the 
process, perhaps tunning parameters? or configuration of operating 
system (Linux RHEL 6).






You haven't given us nearly enough information about your setup. We'd 
need to see your configuration settings and have some details of the 
machine and where connections are coming from to diagnose it further.


cheers

andrew



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Process in state BIND, authentication, PARSE

[Jeison Bedoya]

Hi, yeah i am sorry, i run the postgresql in a machine with this 
configuration


Ram: 128GB
cpu: 32 cores
Disk: 400GB over SAN

The database run an application web over glassfish, and have 2.000 users

my database configuracion is this:

max_connections = 900
shared_buffers = 4096MB
temp_buffers = 128MB
work_mem = 1024MB
maintenance_work_mem = 1024MB
wal_buffers = 256
checkpoint_segments = 103
effective_cache_size = 4096MB

thanks

Atentamente,


JEISON BEDOYA DELGADO
Adm. Servidores y Comunicaciones
AUDIFARMA S.A.

El 08/07/2013 11:40 a.m., Andrew Dunstan escribió:


On 07/08/2013 12:22 PM, Jeison Bedoya wrote:
Hi, i want to know why in my database the process stay in BID, PARSE, 
autentication, startup by a couple minuts, generating slow in the 
process, perhaps tunning parameters? or configuration of operating 
system (Linux RHEL 6).






You haven't given us nearly enough information about your setup. We'd 
need to see your configuration settings and have some details of the 
machine and where connections are coming from to diagnose it further.


cheers

andrew







--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Performance autovaccum

[Michael Paquier]

On Tue, Jul 9, 2013 at 1:14 AM, Jeison Bedoya  wrote:
> Hi, i have a postgresql 9.2.2,
You should update to 9.2.4. There are major security fixes in this subrelease.

> but i don´t use autovaccum but i want to
> begin to use it.  some recommendation about the optimal configuration? or
> some link to explain it.
Perhaps that?
http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html#AUTOVACUUM

Atentamente,
--
Michael


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Process in state BIND, authentication, PARSE

[Michael Paquier]

On Tue, Jul 9, 2013 at 2:01 AM, Jeison Bedoya  wrote:
> max_connections = 900
> work_mem = 1024MB
> maintenance_work_mem = 1024MB
Aren't work_mem and maintenance_work_mem too high? You need to keep in
mind that those are per-operation settings, so for example if you have
100 clients performing queries, this could grow up to 100G. In your
case you even have a maximum of 900 connections... Do you perform
heavy sort operations with your application that could explain such an
amount of memory needed?
--
Michael


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance