Skip site navigation (1) Skip section navigation (2)

Re: performance drop on 8.2.4, reverting to 8.1.4

From: "Steven Flatt" <steven(dot)flatt(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Steinar H(dot) Gunderson" <sgunderson(at)bigfoot(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: performance drop on 8.2.4, reverting to 8.1.4
Date: 2007-06-07 17:11:44
Message-ID: 357fa7590706071011g4e337937rc622ffed39856425@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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;
    ';

In response to

Responses

pgsql-performance by date

Next:From: Joe LesterDate: 2007-06-07 17:48:43
Subject: Getting Slow
Previous:From: Y SidhuDate: 2007-06-07 16:47:52
Subject: How Are The Variables Related?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group