From: | Kirill Müller <kirill(dot)mueller(at)ivt(dot)baug(dot)ethz(dot)ch> |
---|---|
To: | David Johnston <polobo(at)yahoo(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org>, 'Scott Marlowe' <scott(dot)marlowe(at)gmail(dot)com> |
Subject: | Re: Enumeration of tables is very slow in largish database |
Date: | 2012-01-12 00:11:11 |
Message-ID: | 4F0E251F.1090801@ivt.baug.ethz.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
Attachment | Content-Type | Size |
---|---|---|
qgis_list_geometry_columns_improved.txt | text/plain | 3.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-01-12 00:19:00 | Re: Enumeration of tables is very slow in largish database |
Previous Message | David Johnston | 2012-01-11 23:43:51 | Re: Enumeration of tables is very slow in largish database |