Since this is a large query, attachments for the explains / query.
Configuration:
dev_iqdb=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.4beta1 on i386-portbld-freebsd4.7, compiled by GCC
2.95.4
(1 row)
SET default_statistics_target = 1000;
ANALYZE;
set from_collapse_limit = 100;
set join_collapse_limit = 20;
The query is a segment of a report but shows the issue well enough.
Effectively, the planner has amazingly inaccurate row estimates. With
nestloop on, it estimates 1 row returned. With it off it estimates 12
rows returned. The query actually returns several hundred thousand
entries.
ANALYZE output is in the nestloopoff file. With nestloopon I actually
run out of memory prior to query completion (1GB ram to the single
process).
Any hints? I'm basically stuck. Oh, and I would like to ask for a
pgadmin feature -- visual explain :)
