Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3EFFE2E2EB8 for ; Mon, 11 Feb 2008 19:07:42 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 15572-05 for ; Mon, 11 Feb 2008 19:07:39 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id C72E42E032A for ; Mon, 11 Feb 2008 19:07:38 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m1BN7bnl011572; Mon, 11 Feb 2008 18:07:37 -0500 (EST) To: Chris Kratz cc: pgsql-performance@postgresql.org Subject: Re: mis-estimate in nested query causes slow runtimes In-reply-to: References: Comments: In-reply-to Chris Kratz message dated "Mon, 11 Feb 2008 17:24:33 -0500" Date: Mon, 11 Feb 2008 18:07:37 -0500 Message-ID: <11571.1202771257@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/107 X-Sequence-Number: 28649 Chris Kratz writes: > -> Nested Loop (cost=42.74..161.76 rows=1 width=38) (actual > time=2.932..27.772 rows=20153 loops=1) > -> Hash Join (cost=10.89..22.58 rows=1 width=24) (actual > time=0.065..0.134 rows=1 loops=1) > Hash Cond: (mtchsrcprj3.funding_source_id = > mtchsrcprjfs3.nameid) > -> Seq Scan on project mtchsrcprj3 (cost=0.00..11.22 > rows=122 width=8) (actual time=0.002..0.054 rows=122 loops=1) > -> Hash (cost=10.83..10.83 rows=5 width=24) (actual > time=0.017..0.017 rows=1 loops=1) > -> Index Scan using name_float_lfm_idx on > namemaster mtchsrcprjfs3 (cost=0.00..10.83 rows=5 width=24) (actual > time=0.012..0.013 rows=1 loops=1) > Index Cond: (name_float_lfm = 'DWS'::text) > -> Bitmap Heap Scan on transaction_details idatrndtl > (cost=31.85..121.60 rows=1407 width=22) (actual time=2.864..12.060 > rows=20153 loops=1) > Recheck Cond: (idatrndtl.ida_trans_match_source_id = > mtchsrcprj3.id) > -> Bitmap Index Scan on > transaction_details_ida_trans_match_source_id (cost=0.00..31.50 > rows=1407 width=0) (actual time=2.696..2.696 rows=20153 loops=1) > Index Cond: (idatrndtl.ida_trans_match_source_id = > mtchsrcprj3.id) > The first frustration is that I can't get the transaction details scan > to get any more accurate. It thinks it will find 1407 records, > instead it finds 20,153. Then for whatever reason it thinks that a > join between 1 record and 1407 records will return 1 record. This is > mainly what I can't understand. Why does it think it will only get > one record in response when it's a left join? I don't see any left join there ... > PG 8.2.4 on Linux kernel 2.6.9 x64 The first thing you should do is update to 8.2.6; we've fixed a fair number of problems since then that were fallout from the outer-join planning rewrite in 8.2. If it still doesn't work very well, please post the pg_stats rows for the join columns involved (idatrndtl.ida_trans_match_source_id and mtchsrcprj3.id). (You do have up-to-date ANALYZE stats for both of those tables, right?) regards, tom lane