Re: Enumeration of tables is very slow in largish database

=?ISO-8859-1?Q?Kirill_M=FCller?= <kirill(dot)mueller(at)ivt(dot)baug(dot)ethz(dot)ch> writes:
> Thanks for the feedback. I found the relevant parts in the qgis source
> code and have been able to trace the problem. It's just a sub-optimal
> query issued by qgis:

> 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');

> When leaving out the last two "AND NOT EXISTS..." parts, the query
> finishes in no time. I have attached the output of EXPLAIN ANALYZE -- if
> I understand the execution tree correctly, the time is burnt in repeated
> sequential scans of the geometry_columns table (line 38).

Yeah. It wouldn't choose that plan if it weren't for the horrid rowcount
misestimate here:

> -> Hash Anti Join (cost=30586.95..37075.41 rows=1 width=133) (actual time=945.911..1760.307 rows=17836 loops=1)

This is probably an indication of eqjoinsel_semi doing the wrong thing;
we've whacked that estimator around a few times now, so it's hard to
know whether this represents an already-fixed bug or not. What PG
version are you using exactly?

> Rewriting the
> "AND NOT EXISTS" part using WITH solves the performance issues here, but
> works only from Postgres 8.4. Any idea how to speed up this query for
> older versions? (Creating a temporary table or an index should be avoided.)

Maybe use EXCEPT instead of a WHERE condition to get rid of the
already-present entries?

regards, tom lane