From: Tom Lane <tgl@sss.pgh.pa.us>
To: Chris Kratz <chris.kratz@vistashare.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: mis-estimate in nested query causes slow runtimes
Date: Mon, 11 Feb 2008 18:07:37 -0500
Message-ID: <11571.1202771257@sss.pgh.pa.us> (raw)
In-Reply-To: <A6EA89F6-B15E-4C1A-AC14-A9FB919E3E52@vistashare.com>
References: <A6EA89F6-B15E-4C1A-AC14-A9FB919E3E52@vistashare.com>
Chris Kratz <chris.kratz@vistashare.com> 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
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: tgl@sss.pgh.pa.us, chris.kratz@vistashare.com
Subject: Re: mis-estimate in nested query causes slow runtimes
In-Reply-To: <11571.1202771257@sss.pgh.pa.us>
* 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