Re: 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