Re: postgres slower on nested queries

Tom Lane schrieb:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
>
>> ... Your real problem is that
>> you're using the information_schema which has to do all sorts of extra
>> work for standards compliance. If you don't need that you're going to
>> be much better off just using the system catalogs.
>>
>
> To put that in some perspective:
>
> regression=> create table fooey(f1 int);
> CREATE TABLE
> regression=> \timing
> Timing is on.
> regression=> select column_name from information_schema.columns where table_nam
> e = 'fooey';
> column_name
> -------------
> f1
> (1 row)
>
> Time: 84.388 ms
> regression=> select attname from pg_attribute where attrelid = 'fooey'::regclas
> s and attnum > 0 and not attisdropped;
> attname
> ---------
> f1
> (1 row)
>
> Time: 7.834 ms
> regression=>
>
> When you look at the plans involved (information_schema.columns is an
> 8-way join) the surprising thing is not that the first query is so slow,
> it's that it's so fast.
>
AAAH! That is the reason.

So creating a temporary table from the information schema would speedup
the whole thing by factor 10 (at least), I guess.
Thanks a lot!
;)

Greetings,
Alexander Elgert