X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E818D52964 for ; Wed, 30 Mar 2005 19:05:20 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 81000-06 for ; Wed, 30 Mar 2005 18:05:12 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id BDF2F53542 for ; Wed, 30 Mar 2005 19:05:10 +0100 (BST) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j2UI4wW0027008; (envelope-from ) Wed, 30 Mar 2005 12:05:04 -0600 (CST) Received: from [192.168.1.13] (12-215-118-172.client.mchsi.com [12.215.118.172]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j2UHvoHT018045 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Wed, 30 Mar 2005 11:57:52 -0600 (CST) Message-ID: <424AE89D.6050501@arbash-meinel.com> Date: Wed, 30 Mar 2005 11:57:49 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: KeithW@narrowpathinc.com Cc: PostgreSQL Perform Subject: Re: Dynamic query perormance References: <20050330170418.M61319@narrowpathinc.com> In-Reply-To: <20050330170418.M61319@narrowpathinc.com> X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig2DF40541C5E82F4251FD0181" X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/658 X-Sequence-Number: 11363 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig2DF40541C5E82F4251FD0181 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Keith Worthington wrote: >Hi All, > >I am developing a simple set returning function as my first step towards more >complicated processes. I would like to understand the implications of using >the dynamic query capability. > >I have built two forms of an identically performing function. The first uses >a simple IF-THEN-ELSIF-THEN-ELSE structure to choose which query to run. The >second builds the query dynamically using the FOR-IN-EXECUTE structure and a >CASE statement. > >The documentation >(http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING) >indicates that a dynamic query (EXECUTE) is replanned for every LOOP iteration. > > This is like the previous form, except that the source > SELECT statement is specified as a string expression, > which is evaluated and replanned on each entry to the > FOR loop. This allows the programmer to choose the speed > of a preplanned query or the flexibility of a dynamic > query, just as with a plain EXECUTE statement. > >That seems like a potential performance problem. I don't understand why the >query would be planned for every LOOP iteration when the LOOP is over the >record set. > > > Reading the documentation and looking at the example, I don't think you're query will be re-planned for each entry in the loop. I think it will be planned each time the FOR loop is started. If you have the EXECUTE *inside* the LOOP, then it would be re-planned for each entry. At least that is the case for a normal EXECUTE without any for loop. Each time the function is called, the statement is re-planned. Versus without EXECUTE when the planning is done at function declaration time. I would guess that the FOR .. IN EXECUTE .. LOOP runs the EXECUTE one time, and generates the results which it then loops over. Because that is what FOR .. IN SELECT .. LOOP does (you don't re-evaluate the SELECT for each item in the result set). On the other hand, I don't know of any way to test this, unless you have a query that you know takes a long time to plan, and can compare the performance of FOR IN EXECUTE versus FOR IN SELECT. John =:-> >Your comments are appreciated. > >Kind Regards, >Keith > > > --------------enig2DF40541C5E82F4251FD0181 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCSuidJdeBCYSNAAMRAk78AJ4swE0OeNES3oXxX92rsqH3KfhlGACgl+A7 rrm3KaNqmVtGJXd8FiKZIig= =vXOw -----END PGP SIGNATURE----- --------------enig2DF40541C5E82F4251FD0181--