From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mario Weilguni <mweilguni@sime.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very slow INFORMATION_SCHEMA
Date: Mon, 05 May 2008 20:56:26 -0400
Message-ID: <5792.1210035386@sss.pgh.pa.us> (raw)
In-Reply-To: <481EEFD4.3060203@sime.com>
References: <481B82AE.8050607@gmail.com>
<26118.1209766858@sss.pgh.pa.us>
<481EEFD4.3060203@sime.com>
Mario Weilguni <mweilguni@sime.com> writes:
> I can confirm this for a quite larger result set (4020 rows) for a DB
> with 410 tables and a lot of foreign key constraints.
> ...
> This is Postgresql 8.2.4, on a Dual-Core XEON 3.6GHz. With nested_loops
> off, I get a very fast response (330ms).
FWIW, it looks like 8.3 is significantly smarter about this example
--- it's able to push the toplevel conditions on CONSTRAINT_SCHEMA
and CONSTRAINT_TYPE down inside the UNION, where 8.2 fails to do so.
Which is not to say that there's not more left to do on optimizing
the information_schema views. In this particular case, for example,
I wonder why the UNION in INFORMATION_SCHEMA.TABLE_CONSTRAINTS isn't a
UNION ALL. There's probably a lot more such micro-optimizations that
could be done if anyone was motivated to look at it.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mweilguni@sime.com
Subject: Re: Very slow INFORMATION_SCHEMA
In-Reply-To: <5792.1210035386@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox