From: Keith Worthington <keithw@narrowpathinc.com>
To: PostgreSQL Perform <pgsql-performance@postgresql.org>
Subject: Dynamic query perormance
Date: Wed, 30 Mar 2005 12:22:55 -0500
Message-ID: <20050330170418.M61319@narrowpathinc.com> (raw)
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-ITERA...)
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.
Your comments are appreciated.
Kind Regards,
Keith
CREATE OR REPLACE FUNCTION func_item_list("varchar")
RETURNS SETOF VARCHAR AS
$BODY$
DECLARE
v_status ALIAS FOR $1;
r_item_id RECORD;
BEGIN
-- Build the record set using the appropriate query.
IF lower(v_status) = 'active' THEN
FOR r_item_id IN SELECT tbl_item.id
FROM tbl_item
WHERE NOT tbl_item.inactive
ORDER BY tbl_item.id
LOOP
RETURN NEXT r_item_id;
END LOOP;
ELSIF lower(v_status) = 'inactive' THEN
FOR r_item_id IN SELECT tbl_item.id
FROM tbl_item
WHERE tbl_item.inactive
ORDER BY tbl_item.id
LOOP
RETURN NEXT r_item_id;
END LOOP;
ELSE
FOR r_item_id IN SELECT tbl_item.id
FROM tbl_item
ORDER BY tbl_item.id
LOOP
RETURN NEXT r_item_id;
END LOOP;
END IF;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
SELECT * FROM func_item_list('Active');
CREATE OR REPLACE FUNCTION func_item_list("varchar")
RETURNS SETOF VARCHAR AS
$BODY$
DECLARE
v_status ALIAS FOR $1;
r_item_id RECORD;
BEGIN
-- Build the record set using a dynamically built query.
FOR r_item_id IN EXECUTE 'SELECT tbl_item.id
FROM tbl_item' ||
CASE WHEN lower(v_status) = 'active' THEN
' WHERE NOT tbl_item.inactive '
WHEN lower(v_status) = 'inactive' THEN
' WHERE tbl_item.inactive '
ELSE
' '
END ||
' ORDER BY tbl_item.id'
LOOP
RETURN NEXT r_item_id;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
SELECT * FROM func_item_list('AcTiVe');
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: keithw@narrowpathinc.com
Subject: Re: Dynamic query perormance
In-Reply-To: <20050330170418.M61319@narrowpathinc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox