Re: postgres slower on nested queries

Martijn van Oosterhout <kleptog@svana.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.

            regards, tom lane