-----Original Message-----
From: Kirill Müller [mailto:kirill.mueller@ivt.baug.ethz.ch]
Sent: Wednesday, January 11, 2012 7:11 PM
To: David Johnston
Cc: pgsql-general@postgresql.org; 'Scott Marlowe'
Subject: Re: [GENERAL] Enumeration of tables is very slow in largish
database
I am impressed. Execution time dropped to less than one second. Thanks a
lot!
On 01/12/2012 12:43 AM, David Johnston wrote:
> I only see one (1) "AND NOT EXISTS" in the provided query.
Sorry, there used to be two "AND NOT EXISTS", but I edited the query without
updating the text.
> Syntax may be a little off but:
>
> ... AND (f_table_schema, f_table_name) NOT IN ( SELECT (nspname,
> relname) FROM geometry_columns )
Just for the record:
...AND (nspname, relname) NOT IN (SELECT f_table_schema, f_table_name FROM
geometry_columns)
> Should work since it is no longer a correlated sub-query; whether the
> size of geometry_columns makes this better or worse performing is
> impossible to tell without testing but it isn't that much different than
using a WITH/CTE.
The execution plan looks much nicer (attached). I'd guess that the altered
query might lose a bit if geometry_columns has only very few entries.?
Apparently it gains a lot if the table is populated.
Thanks again!
Kirill
--------------------------------------------------------------------
Performance for IN should increase as the results from geometry_columns
decrease since the IN target becomes smaller - thus fewer entries to compare
against. EXISTS works better than IN if the IN target is large AS LONG AS
the query that exists is using can use an Index. Since your query was
performing a sequential scan pretty much any size IN target will be better
performing. For small IN targets and index-using EXISTS it likely matters
very little which one you use.
David J.