Thread: Dynamic query perormance

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.

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');

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

>       which is evaluated and replanned on each entry to the
>       FOR loop. This allows the programmer to choose the speed

    On each entry is not the same as on each iteration. It would means "every
time the loop is started"...

    Regards,
    PFC