Re: Slow queries from information schema

Octavio Alvarez <alvarezp(at)alvarezp(dot)ods(dot)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