Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id E58E363FFF0 for ; Tue, 2 Feb 2010 16:30:42 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 31536-01 for ; Tue, 2 Feb 2010 20:30:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mx3.uci.cu (ns3.uci.cu [200.55.140.180]) by mail.postgresql.org (Postfix) with SMTP id EB01565A278 for ; Tue, 2 Feb 2010 15:57:34 -0400 (AST) Received: (qmail 25202 invoked by uid 507); 2 Feb 2010 19:57:23 -0000 Received: from 10.0.0.183 by ns3.uci.cu (envelope-from , uid 501) with qmail-scanner-2.01st (avp: 5.0.2.0. spamassassin: 3.0.6. perlscan: 2.01st. Clear:RC:1(10.0.0.183):. Processed in 0.664753 secs); 02 Feb 2010 19:57:23 -0000 Received: from unknown (HELO ucimail2.uci.cu) (10.0.0.183) by 0 with SMTP; 2 Feb 2010 19:57:22 -0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by ucimail2.uci.cu (Postfix) with ESMTP id B23A83DC26C; Tue, 2 Feb 2010 14:59:04 -0500 (CST) X-Virus-Scanned: amavisd-new at uci.cu Received: from ucimail2.uci.cu ([127.0.0.1]) by localhost (ucimail2.uci.cu [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Mswmr3imOZaE; Tue, 2 Feb 2010 14:59:03 -0500 (CST) Received: from [10.36.17.6] (unknown [10.36.17.6]) by ucimail2.uci.cu (Postfix) with ESMTP id AB0B23DC268; Tue, 2 Feb 2010 14:59:03 -0500 (CST) Message-ID: <4B68840B.3020402@uci.cu> Date: Tue, 02 Feb 2010 14:59:07 -0500 From: =?ISO-8859-1?Q?=22Ing=2E_Marcos_Orti=ADz_Valmaseda=22?= User-Agent: Thunderbird 2.0.0.23 (X11/20090817) MIME-Version: 1.0 To: Mridula Mahadevan CC: "pgsql-performance@postgresql.org" Subject: Re: Queries within a function References: <0A59BA5B590B7E4A8D441196A9F17E904C68825D26@corpmail11.calpurnia.com> In-Reply-To: <0A59BA5B590B7E4A8D441196A9F17E904C68825D26@corpmail11.calpurnia.com> Content-Type: multipart/mixed; boundary="------------020403030404080602040100" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=-10 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 201002/29 X-Sequence-Number: 37450 This is a multi-part message in MIME format. --------------020403030404080602040100 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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 --------------------------------------------------------------------------------- --------------020403030404080602040100 Content-Type: text/x-vcard; charset=utf-8; name="mlortiz.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="mlortiz.vcf" begin:vcard fn;quoted-printable:Marcos Lu=C3=ADs Ort=C3=ADz Valmaseda n;quoted-printable;quoted-printable:Ort=C3=ADz Valmaseda;Marcos Lu=C3=ADs org:CENTALAD;PostgreSQL DB Architect and Sys Admin adr;quoted-printable:;;Universidad de las Ciencias Inform=C3=A1ticas;La Habana;CH;10400;Cuba email;internet:marcosluis2186@gmail.com title;quoted-printable:Centro de Tecnolog=C3=ADas de Almacenamiento y An=C3=A1lisis de Datos tel;quoted-printable;work:Centro de Tecnolog=C3=ADas de Almacenamiento y An=C3=A1lisis de Datos tel;home:Edificio # 18. Apto 9 .5to Piso. Rpto: A.Mestre x-mozilla-html:TRUE url:http://www.uci.cu version:2.1 end:vcard --------------020403030404080602040100--