On 6/5/07, Tom Lane <[EMAIL PROTECTED]> wrote:

If you're feeling desperate you could revert this patch in your local
copy:
http://archives.postgresql.org/pgsql-committers/2006-11/msg00066.php

                       regards, tom lane


Reverting that patch has not appeared to solve our problem.  Perhaps I
didn't provide enough information, because I feel like there's more going
on here.

One instance of our problem goes like this, and I have included a
self-contained example with which you can reproduce the problem.  We make
heavy use of partitioned tables, so during our schema install, we create a
lot of inherited tables (on the order of 2000) to which we also want to add
the FK constraints that exist on the parent table.  The PLpgSQL function
below does this.  It queries for all FK constraints that are on the parent
table but not on the child, then generates the sql to add them to
the child.  (The function has been modified from the original but the main
query is the same.)

Note the "this is slow" section and the "replace with this which is fast"
section.  Both queries are fast on 8.1.4 (entire function completes in 2
minutes), but not on 8.2.4.  If you notice the "ELAPSED TIME"s written to
the console, the query times start equally fast but grows painfully slow
rather quickly with the "slow" version on 8.2.4.

Sorry for not providing explain analyze output, but I found it hard to tie
the output into the execution of the function.  When I did stand-alone
explain analyzes, the actual times reported were similar on 8.1.4 and 8.2.4.
I think the degradation has more to do with doing many such queries in a
single transaction or something like that.

Plus, correct me if I'm wrong, but the degrading query is executed against
pg_catalog tables only, which are in general smallish, so I have a hard time
believing that even a sub-optimal query plan results in this level of
degradation.

Any help is much appreciated, thanks.
Steve


CREATE OR REPLACE FUNCTION inherit_fks_test()
   RETURNS interval
   VOLATILE
   LANGUAGE PLpgSQL
   AS '
       DECLARE
           childtbl varchar;
           childoid oid;
           rec      record;
           start    timestamptz;
           finish   timestamptz;
           time1    timestamptz;
           time2    timestamptz;
           elapsed  interval;
       BEGIN
           start := timeofday();

           EXECUTE ''SET LOCAL log_min_messages TO NOTICE'';
           EXECUTE ''CREATE TABLE foo(a INT UNIQUE)'';
           EXECUTE ''CREATE TABLE bar(b INT REFERENCES foo(a))'';

           FOR count IN 1 .. 2000
           LOOP
               childtbl := ''bar_'' || count;
               EXECUTE ''CREATE TABLE '' || childtbl || ''() INHERITS
(bar)'';

               childoid := childtbl::regclass::oid;

               time1 := timeofday();
               FOR rec IN
                   SELECT ''ALTER TABLE ''
                          || quote_ident(n.nspname) || ''.''
                          || quote_ident(cl.relname)
                          || '' ADD CONSTRAINT ''
                          || quote_ident(parent_const.conname) || '' ''
                          || parent_const.def AS cmd
                       FROM pg_catalog.pg_class cl
                       JOIN pg_catalog.pg_namespace n
                           ON (n.oid = cl.relnamespace)
                       JOIN pg_catalog.pg_inherits i
                           ON (i.inhrelid = cl.oid)
                       JOIN (
                           SELECT c.conname,
                                  c.conrelid,
                                  c.confrelid,
                                  pg_get_constraintdef(c.oid) AS def
                               FROM pg_catalog.pg_constraint c
                               WHERE c.confrelid <> 0
                       ) AS parent_const
                           ON (parent_const.conrelid = i.inhparent)

-- This is slow
-------------------------------------------------------------------------------
                       LEFT OUTER JOIN (
                           SELECT c2.conname,
                                  c2.conrelid,
                                  c2.confrelid,
                                  pg_get_constraintdef(c2.oid) AS def
                               FROM pg_catalog.pg_constraint c2
                               WHERE c2.confrelid <> 0
                       ) AS child_const
                           ON (child_const.conrelid = cl.oid
                               AND child_const.conname =
                                   parent_const.conname
                               AND child_const.confrelid =
                                   parent_const.confrelid
                               AND child_const.def = parent_const.def)
                       WHERE child_const.conname IS NULL
-------------------------------------------------------------------------------

-- Replace with this which is fast
-------------------------------------------------------------------------------
--                        WHERE conname NOT IN (
--                            SELECT c2.conname
--                                FROM pg_catalog.pg_constraint c2
--                                WHERE c2.confrelid <> 0
--                                    AND c2.conrelid = cl.oid
--                                    AND c2.conname = parent_const.conname
--                                    AND c2.confrelid =
parent_const.confrelid
--                                    AND pg_get_constraintdef(c2.oid) =
--                                        parent_const.def
--                        )
-------------------------------------------------------------------------------

                           AND cl.oid = childoid
               LOOP
                   time2 := timeofday();
                   EXECUTE rec.cmd;
               END LOOP;

               elapsed := time2 - time1;
               RAISE NOTICE ''%: ELAPSED TIME: %'',count,elapsed;

           END LOOP;

           finish := timeofday();
           RETURN finish - start;
       END;
   ';

Reply via email to