On Sat, 2009-02-14 at 15:02 -0500, Tom Lane wrote: > Octavio Alvarez <alvar...@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';
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). -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
