Re: Slow queries from information schema - Mailing list pgsql-performance

From Tom Lane
Subject Re: Slow queries from information schema
Date
Msg-id 5500.1234641722@sss.pgh.pa.us
Whole thread Raw
In response to Slow queries from information schema  (Octavio Alvarez <alvarezp@alvarezp.ods.org>)
Responses Re: Slow queries from information schema  (Octavio Alvarez <alvarezp@alvarezp.ods.org>)
List pgsql-performance
Octavio Alvarez <alvarezp@alvarezp.ods.org> writes:
> The result, on the above view: ~80ms. Fair enough. But if I apply a
> condition:
> SELECT * FROM ___pgnui_relation_tree.raw_relation_tree WHERE
> parent_schema <> child_schema;
> it takes ~2 seconds (!) to complete.

I'm not sure I'm seeing the exact same case as you, but what I see here
is that 8.3 puts the join condition involving _pg_keysequal() at the
top of the tree where it will be executed quite a lot of times (way
more than the planner expects, because of bad rowcount estimates below)
... and _pg_keysequal() is implemented in a depressingly inefficient way.

CVS HEAD seems to avoid this trap in the same case, but I'm not entirely
convinced whether it's getting better rowcount estimates or just got
lucky.

Anyway it seems to help a great deal if you use a less sucky definition
of the function, such as

create or replace function information_schema._pg_keysequal(smallint[], smallint[]) RETURNS boolean
LANGUAGE sql STRICT IMMUTABLE AS
'select $1 <@ $2 and $2 <@ $1';

            regards, tom lane

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: I/O increase after upgrading to 8.3.5
Next
From: Alexander Staubo
Date:
Subject: Re: I/O increase after upgrading to 8.3.5