Re: [PERFORM] Queries within a function
Thank you all, You were right on the analyze. Insert statement with an aggregated subquery had a problem on an empty table. I had to change the queries to do a simple insert then analyze on the table followed by an update with an aggregated sub query. That goes thru very fast. -mridula From: Віталій Тимчишин [mailto:tiv...@gmail.com] Sent: Wednesday, February 03, 2010 8:11 AM To: Mridula Mahadevan Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Queries within a function 2010/2/2 Mridula Mahadevan mailto:mmahade...@stratify.com>> Hi, I am running a bunch of queries within a function, creating some temp tables and populating them. When the data exceeds say, 100k the queries start getting really slow and timeout (30 min). when these are run outside of a transaction(in auto commit mode), they run in a few seconds. Any ideas on what may be going on and any postgresql.conf parameters etc that might help? Thanks Have you tried to analyze temp tables after you've populated them? Because AFAIK it won't do it automatically for tables created, filled and then used in same transaction.
Re: [PERFORM] Queries within a function
2010/2/2 Mridula Mahadevan > Hi, > > I am running a bunch of queries within a function, creating some temp > tables and populating them. When the data exceeds say, 100k the queries > start getting really slow and timeout (30 min). when these are run outside > of a transaction(in auto commit mode), they run in a few seconds. Any ideas > on what may be going on and any postgresql.conf parameters etc that might > help? > > Thanks > Have you tried to analyze temp tables after you've populated them? Because AFAIK it won't do it automatically for tables created, filled and then used in same transaction.
Re: [PERFORM] Queries within a function
Hi, Try using dynamic sql. Query will be faster in a function regards Ram - Original Message - From: "Mridula Mahadevan" To: "Tom Lane" Cc: Sent: Wednesday, February 03, 2010 2:23 AM Subject: Re: [PERFORM] Queries within a function Tom, I cannot run execute because all these are temp tables with drop on auto commit within a function. This should have something to do with running it in a transaction, when I run them in autocommit mode (without a drop on autocommit) the queries return in a few seconds. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, February 02, 2010 11:28 AM To: Mridula Mahadevan Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Queries within a function Mridula Mahadevan writes: I am running a bunch of queries within a function, creating some temp tables and populating them. When the data exceeds say, 100k the queries start getting really slow and timeout (30 min). when these are run outside of a transaction(in auto commit mode), they run in a few seconds. Any ideas on what may be going on and any postgresql.conf parameters etc that might help? I'll bet the function is caching query plans that stop being appropriate once the table grows in size. You might have to resort to using EXECUTE, although if you're on 8.4 DISCARD PLANS ought to help too. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- 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] Queries within a function
Tom, I cannot run execute because all these are temp tables with drop on auto commit within a function. This should have something to do with running it in a transaction, when I run them in autocommit mode (without a drop on autocommit) the queries return in a few seconds. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, February 02, 2010 11:28 AM To: Mridula Mahadevan Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Queries within a function Mridula Mahadevan writes: > I am running a bunch of queries within a function, creating some temp tables > and populating them. When the data exceeds say, 100k the queries start > getting really slow and timeout (30 min). when these are run outside of a > transaction(in auto commit mode), they run in a few seconds. Any ideas on > what may be going on and any postgresql.conf parameters etc that might help? I'll bet the function is caching query plans that stop being appropriate once the table grows in size. You might have to resort to using EXECUTE, although if you're on 8.4 DISCARD PLANS ought to help too. regards, tom lane -- 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] Queries within a function
_page_cost = 1.0# measured on an arbitrary scale #random_page_cost = 4.0 # same scale as above #cpu_tuple_cost = 0.01 # same scale as above #cpu_index_tuple_cost = 0.005 # same scale as above #cpu_operator_cost = 0.0025 # same scale as above effective_cache_size = 512MB#(Changed from 128 MB to 256 MB) # - Genetic Query Optimizer - #geqo = on #geqo_threshold = 12 #geqo_effort = 5# range 1-10 #geqo_pool_size = 0 # selects default based on effort #geqo_generations = 0 # selects default based on effort #geqo_selection_bias = 2.0 # range 1.5-2.0 # - Other Planner Options - default_statistics_target = 100 # range 1-1000 (changed from 10 to 100) #constraint_exclusion = off #from_collapse_limit = 8 #join_collapse_limit = 8# 1 disables collapsing of explicit # JOIN clauses -Original Message- From: "Ing. Marcos Ortiz Valmaseda" [mailto:mlor...@uci.cu] Sent: Tuesday, February 02, 2010 11:59 AM To: Mridula Mahadevan Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Queries within a function Mridula Mahadevan wrote: > > Hi, > > I am running a bunch of queries within a function, creating some temp > tables and populating them. When the data exceeds say, 100k the > queries start getting really slow and timeout (30 min). when these are > run outside of a transaction(in auto commit mode), they run in a few > seconds. Any ideas on what may be going on and any postgresql.conf > parameters etc that might help? > > Thanks > Do you put here the result of the explain command of the query? Do you put here the postgresql.conf parameters that you have in your box? Regards -- "Para ser realmente grande, hay que estar con la gente, no por encima de ella." Montesquieu Ing. Marcos Luís Ortíz Valmaseda PostgreSQL System DBA && DWH -- BI Apprentice Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD) Universidad de las Ciencias Informáticas Linux User # 418229 -- PostgreSQL -- "TIP 4: No hagas 'kill -9' a postmaster" http://www.postgresql-es.org http://www.postgresql.org http://www.planetpostgresql.org -- DWH + BI -- The Data WareHousing Institute http://www.tdwi.org http://www.tdwi.org/cbip - -- 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] Queries within a function
Mridula Mahadevan wrote: Hi, I am running a bunch of queries within a function, creating some temp tables and populating them. When the data exceeds say, 100k the queries start getting really slow and timeout (30 min). when these are run outside of a transaction(in auto commit mode), they run in a few seconds. Any ideas on what may be going on and any postgresql.conf parameters etc that might help? Thanks Do you put here the result of the explain command of the query? Do you put here the postgresql.conf parameters that you have in your box? Regards -- "Para ser realmente grande, hay que estar con la gente, no por encima de ella." Montesquieu Ing. Marcos Luís Ortíz Valmaseda PostgreSQL System DBA && DWH -- BI Apprentice Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD) Universidad de las Ciencias Informáticas Linux User # 418229 -- PostgreSQL -- "TIP 4: No hagas 'kill -9' a postmaster" http://www.postgresql-es.org http://www.postgresql.org http://www.planetpostgresql.org -- DWH + BI -- The Data WareHousing Institute http://www.tdwi.org http://www.tdwi.org/cbip - <> -- 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] Queries within a function
Mridula Mahadevan writes: > I am running a bunch of queries within a function, creating some temp tables > and populating them. When the data exceeds say, 100k the queries start > getting really slow and timeout (30 min). when these are run outside of a > transaction(in auto commit mode), they run in a few seconds. Any ideas on > what may be going on and any postgresql.conf parameters etc that might help? I'll bet the function is caching query plans that stop being appropriate once the table grows in size. You might have to resort to using EXECUTE, although if you're on 8.4 DISCARD PLANS ought to help too. regards, tom lane -- 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] Queries within a function
Hello look on http://blog.endpoint.com/2008/12/why-is-my-function-slow.html Regards Pavel Stehule 2010/2/2 Mridula Mahadevan : > Hi, > > I am running a bunch of queries within a function, creating some temp > tables and populating them. When the data exceeds say, 100k the queries > start getting really slow and timeout (30 min). when these are run outside > of a transaction(in auto commit mode), they run in a few seconds. Any ideas > on what may be going on and any postgresql.conf parameters etc that might > help? > > Thanks -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Queries within a function
Hi, I am running a bunch of queries within a function, creating some temp tables and populating them. When the data exceeds say, 100k the queries start getting really slow and timeout (30 min). when these are run outside of a transaction(in auto commit mode), they run in a few seconds. Any ideas on what may be going on and any postgresql.conf parameters etc that might help? Thanks
