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

Re: Slow queries from information schema

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: alvarezp(at)alvarezp(dot)ods(dot)org
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Slow queries from information schema
Date: 2009-02-14 20:02:02
Message-ID: 5500.1234641722@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Alexander StauboDate: 2009-02-14 20:04:43
Subject: Re: I/O increase after upgrading to 8.3.5
Previous:From: Tom LaneDate: 2009-02-14 19:23:08
Subject: Re: I/O increase after upgrading to 8.3.5

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