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