Re: performance drop on 8.2.4, reverting to 8.1.4

On 6/5/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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;
';