Thread

why does explain analyze differ so much from estimated explain?

Dirk Lutzebäck <lutzeb@aeccom.com> — 2004-09-29T15:55:45Z

Hi,

I have a query where I do not understand that the rows number that 
explain analyze finds differs so much from what explain estimates (3rd 
nested loop estimates 1 row but in real it is 4222 rows). I did analyze 
the tables (pgsql 7.4.1).

Here is the query:

explain analyze
SELECT fts.val_1, max(fts.val_2) AS val_2

FROM docobjflat AS fts,
      boxinfo,
      docobjflat AS ftw0,
      docobjflat AS ftw, envspec_map

WHERE boxinfo.member=158096693
AND boxinfo.envelope=ftw.envelope
AND boxinfo.community=169964332
AND boxinfo.hide=FALSE
AND ftw0.flatid=ftw.flatid
AND fts.flatid=ftw.flatid
AND fts.docstart=1
AND envspec_map.spec=169964482
AND envspec_map.community=boxinfo.community
AND envspec_map.envelope=boxinfo.envelope

AND ftw0.val_14='IN-A01'

GROUP BY fts.val_1;

Query plan is attached.

Regards Dirk