Skip site navigation (1) Skip section navigation (2)

Re: mis-estimate in nested query causes slow runtimes

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Chris Kratz <chris(dot)kratz(at)vistashare(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: mis-estimate in nested query causes slow runtimes
Date: 2008-02-11 23:07:37
Message-ID: 11571.1202771257@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Chris Kratz <chris(dot)kratz(at)vistashare(dot)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

In response to

Responses

pgsql-performance by date

Next:From: fabrix peƱuelasDate: 2008-02-11 23:58:35
Subject: Questions about enabling SSL
Previous:From: Chris KratzDate: 2008-02-11 22:24:33
Subject: mis-estimate in nested query causes slow runtimes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group