On 01/12/2012 02:02 AM, Tom Lane wrote:
There were fixes for that in 8.4.9, so I'd be interested to know if you
get a better estimate in a more up-to-date version.
Something weird happened today. The problem vanished into thin air. Plus
we got our server upgrade to 8.4.9, but definitely after the problem
disappeared. Unfortunately, I didn't have the time to capture an
execution plan before the upgrade. The database has changed during the
day, some hundred more tables with geometry columns were added.
Anyway, I have attached the new execution plan, as produced by server
8.4.9 (squeeze/updates/main postgresql-8.4 amd64 8.4.9-0squeeze1+b1).
Cheers
Kirill
EXPLAIN ANALYZE
SELECT
pg_class.relname,pg_namespace.nspname,pg_attribute.attname,pg_class.relkind
FROM pg_attribute,pg_class,pg_namespace
WHERE pg_namespace.oid=pg_class.relnamespace AND pg_attribute.attrelid =
pg_class.oid
AND ( EXISTS (SELECT * FROM pg_type WHERE
pg_type.oid=pg_attribute.atttypid AND pg_type.typname IN
('geometry','geography','topogeometry')) OR pg_attribute.atttypid IN
(SELECT oid FROM pg_type a WHERE EXISTS (SELECT * FROM pg_type b WHERE
a.typbasetype=b.oid AND b.typname IN
('geometry','geography','topogeometry'))))
AND has_schema_privilege( pg_namespace.nspname, 'usage' )
AND has_table_privilege( '"' || pg_namespace.nspname || '"."' ||
pg_class.relname || '"', 'select' )
AND NOT EXISTS (SELECT * FROM geometry_columns WHERE
pg_namespace.nspname=f_table_schema AND pg_class.relname=f_table_name)
AND pg_class.relkind IN ('v','r');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2727.10..3876710.12 rows=7900 width=193) (actual
time=124.931..760.761 rows=5215 loops=1)
-> Hash Anti Join (cost=383.66..8059.84 rows=1551 width=133) (actual
time=34.663..379.947 rows=10020 loops=1)
Hash Cond: ((pg_namespace.nspname =
(geometry_columns.f_table_schema)::name) AND (pg_class.relname =
(geometry_columns.f_table_name)::name))
-> Hash Join (cost=7.84..5234.21 rows=2068 width=133) (actual
time=1.372..307.250 rows=18680 loops=1)
Hash Cond: (pg_class.relnamespace = pg_namespace.oid)
Join Filter: has_table_privilege((((('"'::text ||
(pg_namespace.nspname)::text) || '"."'::text) || (pg_class.relname)::text) ||
'"'::text), 'select'::text)
-> Seq Scan on pg_class (cost=0.00..4847.43 rows=18519
width=73) (actual time=0.044..80.251 rows=18690 loops=1)
Filter: (relkind = ANY ('{v,r}'::"char"[]))
-> Hash (cost=7.00..7.00 rows=67 width=68) (actual
time=1.227..1.227 rows=158 loops=1)
-> Seq Scan on pg_namespace (cost=0.00..7.00 rows=67
width=68) (actual time=0.049..0.957 rows=158 loops=1)
Filter: has_schema_privilege((nspname)::text,
'usage'::text)
-> Hash (cost=235.53..235.53 rows=9353 width=43) (actual
time=33.247..33.247 rows=8998 loops=1)
-> Seq Scan on geometry_columns (cost=0.00..235.53 rows=9353
width=43) (actual time=0.023..11.505 rows=8998 loops=1)
-> Index Scan using pg_attribute_relid_attnum_index on pg_attribute
(cost=2343.43..2494.13 rows=13 width=68) (actual time=0.034..0.036 rows=1
loops=10020)
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..61.28 rows=13 width=4)
(actual time=0.137..0.139 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=13 width=0) (actual time=0.124..0.124 rows=2 loops=1)
Index Cond: (typname = ANY
('{geometry,geography,topogeometry}'::name[]))
SubPlan 3
-> Hash Join (cost=61.60..2205.33 rows=55243 width=4) (actual
time=85.550..85.550 rows=0 loops=1)
Hash Cond: (a.typbasetype = b.oid)
-> Seq Scan on pg_type a (cost=0.00..1936.43 rows=55243
width=8) (actual time=0.028..54.350 rows=56151 loops=1)
-> Hash (cost=61.44..61.44 rows=13 width=4) (actual
time=0.043..0.043 rows=2 loops=1)
-> HashAggregate (cost=61.31..61.44 rows=13 width=4)
(actual time=0.038..0.040 rows=2 loops=1)
-> Bitmap Heap Scan on pg_type b
(cost=12.88..61.28 rows=13 width=4) (actual time=0.025..0.027 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=13 width=0) (actual
time=0.020..0.020 rows=2 loops=1)
Index Cond: (typname = ANY
('{geometry,geography,topogeometry}'::name[]))
Total runtime: 764.002 ms
(36 rows)
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
