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
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=3004.94..3539610.10 rows=15297 width=193) (actual time=177.084..626.521 rows=4821 loops=1)
-> Hash Join (cost=241.56..5383.60 rows=1213 width=133) (actual time=36.977..233.422 rows=9343 loops=1)
Hash Cond: (pg_class.relnamespace = pg_namespace.oid)
Join Filter: ((NOT (hashed SubPlan 4)) AND has_table_privilege((((('"'::text || (pg_namespace.nspname)::text) || '"."'::text) || (pg_class.relname)::text) || '"'::text), 'select'::text))
-> Seq Scan on pg_class (cost=0.00..4802.82 rows=17676 width=73) (actual time=0.074..72.791 rows=17850 loops=1)
Filter: (relkind = ANY ('{v,r}'::"char"[]))
-> Hash (cost=6.85..6.85 rows=63 width=68) (actual time=1.180..1.180 rows=157 loops=1)
-> Seq Scan on pg_namespace (cost=0.00..6.85 rows=63 width=68) (actual time=0.028..0.944 rows=157 loops=1)
Filter: has_schema_privilege((nspname)::text, 'usage'::text)
SubPlan 4
-> Seq Scan on geometry_columns (cost=0.00..211.94 rows=8794 width=43) (actual time=0.024..11.014 rows=8794 loops=1)
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute (cost=2763.38..2913.46 rows=13 width=68) (actual time=0.038..0.040 rows=1 loops=9343)
Index Cond: (pg_attribute.attrelid = pg_class.oid)
Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (hashed SubPlan 3))
SubPlan 1
-> Index Scan using pg_type_oid_index on pg_type (cost=0.00..8.28 rows=1 width=0) (never executed)
Index Cond: (oid = $0)
Filter: (typname = ANY ('{geometry,geography,topogeometry}'::name[]))
SubPlan 2
-> Bitmap Heap Scan on pg_type (cost=12.88..57.58 rows=12 width=4) (actual time=0.152..0.155 rows=2 loops=1)
Recheck Cond: (typname = ANY ('{geometry,geography,topogeometry}'::name[]))
-> Bitmap Index Scan on pg_type_typname_nsp_index (cost=0.00..12.88 rows=12 width=0) (actual time=0.124..0.124 rows=2 loops=1)
Index Cond: (typname = ANY ('{geometry,geography,topogeometry}'::name[]))
SubPlan 3
-> Hash Semi Join (cost=57.73..2629.03 rows=53739 width=4) (actual time=139.039..139.039 rows=0 loops=1)
Hash Cond: (a.typbasetype = b.oid)
-> Seq Scan on pg_type a (cost=0.00..1832.39 rows=53739 width=8) (actual time=0.029..96.628 rows=53878 loops=1)
-> Hash (cost=57.58..57.58 rows=12 width=4) (actual time=0.047..0.047 rows=2 loops=1)
-> Bitmap Heap Scan on pg_type b (cost=12.88..57.58 rows=12 width=4) (actual time=0.038..0.040 rows=2 loops=1)
Recheck Cond: (typname = ANY ('{geometry,geography,topogeometry}'::name[]))
-> Bitmap Index Scan on pg_type_typname_nsp_index (cost=0.00..12.88 rows=12 width=0) (actual time=0.030..0.030 rows=2 loops=1)
Index Cond: (typname = ANY ('{geometry,geography,topogeometry}'::name[]))
Total runtime: 629.619 ms
(33 rows)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
