Re: Slow queries from information schema

On Sat, 2009-02-14 at 15:02 -0500, Tom Lane wrote:
> 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';

Wow! Just tried it with the UNION (the original) version of
information_schema.table_constraints and it drastically reduced the
total runtime to 309 ms!

I also tested it with UNION ALL and it took 1.6 sec. (and yet, 50% of
the previous time with UNION ALL).