Re: performance drop on 8.2.4, reverting to 8.1.4 - Mailing list pgsql-performance

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

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