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

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 quer

Re: [PERFORM] Performance autovaccum

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 t

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

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 =

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

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

Re: [PERFORM] 8.4 to 9.2 migration performance

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 pon

[PERFORM] Process in state BIND, authentication, PARSE

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. Se

[PERFORM] 8.4 to 9.2 migration performance

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

[PERFORM] Performance autovaccum

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

Re: [PERFORM] 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 Lev

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

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 ta

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_t

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

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

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 cr

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

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

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

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

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

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.geo