X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A301632A132 for ; Wed, 29 Sep 2004 16:56:01 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 95889-10 for ; Wed, 29 Sep 2004 15:55:55 +0000 (GMT) Received: from mail2.aeccom.com (port-212-202-101-158.static.qsc.de [212.202.101.158]) by svr1.postgresql.org (Postfix) with ESMTP id 22002329CDD for ; Wed, 29 Sep 2004 16:55:48 +0100 (BST) Received: from [192.168.2.12] (cayambe.core.aeccom.com [192.168.2.12]) by mail2.aeccom.com (Postfix) with ESMTP id 7AB643F for ; Wed, 29 Sep 2004 17:55:45 +0200 (CEST) Message-ID: <415ADB01.9090001@aeccom.com> Date: Wed, 29 Sep 2004 17:55:45 +0200 From: =?ISO-8859-1?Q?Dirk_Lutzeb=E4ck?= User-Agent: Mozilla Thunderbird 0.8 (X11/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: why does explain analyze differ so much from estimated explain? Content-Type: multipart/mixed; boundary="------------060000050407090400010500" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/326 X-Sequence-Number: 8454 This is a multi-part message in MIME format. --------------060000050407090400010500 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 --------------060000050407090400010500 Content-Type: text/plain; name="qp" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="qp" 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) --------------060000050407090400010500--