pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dirk Lutzebäck <lutzeb@aeccom.com>
To: pgsql-performance@postgresql.org
Subject: why does explain analyze differ so much from estimated explain?
Date: Wed, 29 Sep 2004 17:55:45 +0200
Message-ID: <415ADB01.9090001@aeccom.com> (raw)

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

Attachments:

qp (text/plain, 2.4K) download QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=134.58..134.58 rows=1 width=12) (actual time=1218.479..1218.480 rows=1 loops=1) -> Nested Loop (cost=0.00..134.57 rows=1 width=12) (actual time=723.208..1218.167 rows=173 loops=1) -> Nested Loop (cost=0.00..110.49 rows=1 width=42) (actual time=0.687..636.649 rows=4222 loops=1) -> Nested Loop (cost=0.00..86.39 rows=1 width=15) (actual time=0.567..28.520 rows=4222 loops=1) -> Nested Loop (cost=0.00..38.04 rows=1 width=8) (actual time=0.394..6.078 rows=43 loops=1) -> Index Scan using boxinfo_audi_index on boxinfo (cost=0.00..16.89 rows=4 width=8) (actual time=0.190..2.791 rows=165 loops=1) Index Cond: (member = 158096693::oid) Filter: ((community = 169964332::oid) AND (hide = false)) -> Index Scan using envspec_169964482_index on envspec_map (cost=0.00..5.28 rows=1 width=8) (actual time=0.016..0.016 rows=0 loops=165) Index Cond: ((envspec_map.envelope = "outer".envelope) AND (envspec_map.community = 169964332::oid)) Filter: (spec = 169964482) -> Index Scan using docobjflat_169964482_envelope on docobjflat_169964482 ftw (cost=0.00..47.31 rows=83 width=19) (actual time=0.049..0.291 rows=98 loops=43) Index Cond: ("outer".envelope = ftw.envelope) -> Index Scan using docobjflat_169964482_flatid on docobjflat_169964482 fts (cost=0.00..24.07 rows=2 width=27) (actual time=0.010..0.138 rows=1 loops=4222) Index Cond: (fts.flatid = "outer".flatid) Filter: (docstart = 1) -> Index Scan using docobjflat_169964482_flatid on docobjflat_169964482 ftw0 (cost=0.00..24.07 rows=1 width=15) (actual time=0.135..0.135 rows=0 loops=4222) Index Cond: ("outer".flatid = ftw0.flatid) Filter: (val_14 = 'IN-A01'::text) Total runtime: 1219.200 ms (20 rows)

Message-ID: <415ADB01.9090001@aeccom.com>
Permalink:  ../415ADB01.9090001@aeccom.com/
Also on:    postgresql.org/message-id/415ADB01.9090001@aeccom.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: lutzeb@aeccom.com
  Subject: Re: why does explain analyze differ so much from estimated explain?
  In-Reply-To: <415ADB01.9090001@aeccom.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox