From: | Rod Taylor <rbt(at)rbt(dot)ca> |
---|---|
To: | PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Very poor estimates from planner |
Date: | 2003-11-05 19:17:21 |
Message-ID: | 1068059840.51219.13.camel@jester |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 :)
Attachment | Content-Type | Size |
---|---|---|
nestloopoff.sql | text/x-sql | 8.8 KB |
nestloopon.sql | text/x-sql | 5.1 KB |
query.sql | text/x-sql | 2.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Markus Bertheau | 2003-11-05 19:25:27 | Re: \xDD patch for 7.5devel |
Previous Message | Jan Wieck | 2003-11-05 19:06:58 | Performance features the 4th |