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

Re: Very slow INFORMATION_SCHEMA

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mario Weilguni <mweilguni(at)sime(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Very slow INFORMATION_SCHEMA
Date: 2008-05-06 00:56:26
Message-ID: 5792.1210035386@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Mario Weilguni <mweilguni(at)sime(dot)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

In response to

pgsql-performance by date

Next:From: JustinDate: 2008-05-06 02:01:49
Subject: need to speed up query
Previous:From: Craig JamesDate: 2008-05-05 23:33:01
Subject: RAID 10 Benchmark with different I/O schedulers (was: Performance increase with elevator=deadline)

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