Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 4746C2E2D66 for ; Tue, 12 Feb 2008 11:09:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 36512-05 for ; Tue, 12 Feb 2008 11:08:58 -0400 (AST) Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.231]) by postgresql.org (Postfix) with ESMTP id BD1912E2D65 for ; Tue, 12 Feb 2008 11:09:15 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id 71so5123154wri.5 for ; Tue, 12 Feb 2008 07:09:14 -0800 (PST) Received: by 10.140.82.38 with SMTP id f38mr1013490rvb.27.1202828952520; Tue, 12 Feb 2008 07:09:12 -0800 (PST) Received: by 10.140.163.11 with HTTP; Tue, 12 Feb 2008 07:09:12 -0800 (PST) Message-ID: <3642025c0802120709q51cce630pf74ff63b32b1b154@mail.gmail.com> Date: Tue, 12 Feb 2008 10:09:12 -0500 From: "Chris Kratz" To: "Tom Lane" Subject: Re: mis-estimate in nested query causes slow runtimes Cc: pgsql-performance@postgresql.org In-Reply-To: <11571.1202771257@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11679_5343399.1202828952499" References: <11571.1202771257@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/116 X-Sequence-Number: 28658 ------=_Part_11679_5343399.1202828952499 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 2/11/08, Tom Lane wrote: > > Chris Kratz writes: > > -> Nested Loop (cost=3D42.74..161.76 rows=3D1 width=3D38) (actual > > time=3D2.932..27.772 rows=3D20153 loops=3D1) > > -> Hash Join (cost=3D10.89..22.58 rows=3D1 width=3D24) (actua= l > > time=3D0.065..0.134 rows=3D1 loops=3D1) > > Hash Cond: (mtchsrcprj3.funding_source_id =3D > > mtchsrcprjfs3.nameid) > > -> Seq Scan on project mtchsrcprj3 (cost=3D0.00..11.22 > > rows=3D122 width=3D8) (actual time=3D0.002..0.054 rows=3D122 loops=3D1) > > -> Hash (cost=3D10.83..10.83 rows=3D5 width=3D24) (actu= al > > time=3D0.017..0.017 rows=3D1 loops=3D1) > > -> Index Scan using name_float_lfm_idx on > > namemaster mtchsrcprjfs3 (cost=3D0.00..10.83 rows=3D5 width=3D24) (act= ual > > time=3D0.012..0.013 rows=3D1 loops=3D1) > > Index Cond: (name_float_lfm =3D 'DWS'::text) > > -> Bitmap Heap Scan on transaction_details idatrndtl > > (cost=3D31.85..121.60 rows=3D1407 width=3D22) (actual time=3D2.864..12.= 060 > > rows=3D20153 loops=3D1) > > Recheck Cond: (idatrndtl.ida_trans_match_source_id =3D > > mtchsrcprj3.id) > > -> Bitmap Index Scan on > > transaction_details_ida_trans_match_source_id (cost=3D0.00..31.50 > > rows=3D1407 width=3D0) (actual time=3D2.696..2.696 rows=3D20153 loops= =3D1) > > Index Cond: (idatrndtl.ida_trans_match_source_id = =3D > > 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 I know it's somewhat premature as we haven't had a chance to do the update yet, but here is what I did w/ the statistics with the current version for chuckles and grins just to see if it would make a difference in the plan. # alter table project alter column id set statistics 1000; ALTER TABLE # analyze project; ANALYZE # alter table transaction_details alter column ida_trans_match_source_id se= t statistics 1000; ALTER TABLE # analyze transaction_details; ANALYZE # select * from pg_stats where (tablename=3D'project' and attname=3D'id') o= r (tablename=3D'transaction_details' and attname=3D'ida_trans_match_source_id= '); schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation ------------+---------------------+---------------------------+-----------+= -----------+------------+--------------------------------------------------= ---------------------------------------------------------------------------= -------------------------+-------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------+-------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------+------------- public | project | id | 0 | 4 | -1 | | | {6,7,8,12,13,14,15,17,18,19,24,25,26,27,28,29,30,31,32,33,34,35,36,37,41,42= ,71,72,797,802,803,809,812,813,814,815,816,817,818,822,823,824,825,826,827,= 828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846= ,847,848,849,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,93= 5,936,937,938,939,940,941,942,946,947,948,949,950,951,952,953,954,955,956,9= 57,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,= 977,978} | 0.939317 public | transaction_details | ida_trans_match_source_id | 0.480469 | 4 | 74 | {832,818,930,937,923,812,931,829,837,830,836,14,809,838,936,924,921,922,814= ,816,817,827,815,941,835,967,926,813,968,928,920,939,925,974,833,965,933} | {0.100562,0.100233,0.0412866,0.0245354,0.0223948,0.021277,0.0198998, 0.018817,0.0182431,0.0181583,0.0180236,0.0141714,0.0107633,0.00955071, 0.00917646,0.00639708,0.00562364,0.00491507,0.00453584,0.0037624,0.00332828= , 0.00332828,0.00323846,0.00309874,0.00295403,0.00267959,0.00234526,0.0022704= 1 ,0.00221552,0.00220055,0.00215565,0.00207581,0.00179138,0.00136225, 0.00114269,0.00113271,0.00100796} | {6,6,7,8,15,802,802,802,802,803,803,839,841,844,844,845,845,846,927,927,934= ,934,935,935,938,938,940,942,952,954,955,955,957,972,972,972,978} | 0.218267 (2 rows) This had no appreciable difference in the plan. Here is the part that seem= s to be causing the problem again after the increase in stats. It still thinks there is only one row in the result. -> Nested Loop (cost=3D42.75..161.78 rows=3D1 width=3D38) (actual time= =3D 391.797..425.337 rows=3D20153 loops=3D1) -> Hash Join (cost=3D10.89..22.58 rows=3D1 width=3D24) (actual tim= e=3D 0.069..0.139 rows=3D1 loops=3D1) Hash Cond: (mtchsrcprj3.funding_source_id =3D mtchsrcprjfs3.nameid) -> Seq Scan on project mtchsrcprj3 (cost=3D0.00..11.22 rows= =3D122 width=3D8) (actual time=3D0.002..0.054 rows=3D122 loops=3D1) -> Hash (cost=3D10.83..10.83 rows=3D5 width=3D24) (actual ti= me=3D 0.022..0.022 rows=3D1 loops=3D1) -> Index Scan using name_float_lfm_idx on namemaster mtchsrcprjfs3 (cost=3D0.00..10.83 rows=3D5 width=3D24) (actual time=3D0.013..0.014rows=3D1 loops=3D1) Index Cond: (name_float_lfm =3D 'DWS'::text) -> Bitmap Heap Scan on transaction_details idatrndtl (cost=3D 31.87..121.61 rows=3D1407 width=3D22) (actual time=3D391.722..410.129 rows= =3D20153 loops=3D1) Recheck Cond: (idatrndtl.ida_trans_match_source_id =3D mtchsrcprj3.id) -> Bitmap Index Scan on transaction_details_ida_trans_match_source_id (cost=3D0.00..31.51 rows=3D1= 407 width=3D0) (actual time=3D391.523..391.523 rows=3D20153 loops=3D1) Index Cond: (idatrndtl.ida_trans_match_source_id =3D mtchsrcprj3.id) Here is the relevant snippet from the query <-- snip --> FROM accounts acc left join transactions idatrn on (acc.id =3D idatrn.account_id) left join transaction_details idatrndtl on (idatrn.id =3D idatrndtl.transaction_id) left join project mtchsrcprj3 on (idatrndtl.ida_trans_match_source_id =3D mtchsrcprj3.id) left join namemaster mtchsrcprjfs3 on ( mtchsrcprj3.funding_source_id =3D mtchsrcprjfs3.nameid) <-- snip --> I'll update again once we've had a chance to do the update. -Chris ------=_Part_11679_5343399.1202828952499 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
On 2/11/08, = Tom Lane <tgl@sss.pgh.pa.us= > wrote:
Chris Kratz <chris.kratz@v= istashare.com> writes:
>   ->  Nested L= oop  (cost=3D42.74..161.76 rows=3D1 width=3D38) (actual
> t= ime=3D2.932..27.772 rows=3D20153 loops=3D1)
>         ->  Hash = Join  (cost=3D10.89..22.58 rows=3D1 width=3D24) (actual
> t= ime=3D0.065..0.134 rows=3D1 loops=3D1)
>     = ;          Hash Cond: (mtchsrc= prj3.funding_source_id =3D
> mtchsrcprjfs3.nameid)
>  = ;             -= >  Seq Scan on project mtchsrcprj3  (cost=3D0.00..11= .22
> rows=3D122 width=3D8) (actual time=3D0.002..0.054 rows=3D122 loops=3D1= )
>           =     ->  Hash  (cost=3D10.83..10.83 ro= ws=3D5 width=3D24) (actual
> time=3D0.017..0.017 rows=3D1 loops=3D1)<= br>>           &n= bsp;         ->  Index= Scan using name_float_lfm_idx on
> namemaster mtchsrcprjfs3  (cost=3D0.00..10.83 rows=3D5 width= =3D24) (actual
> time=3D0.012..0.013 rows=3D1 loops=3D1)
> = ;            &n= bsp;            = ; Index Cond: (name_float_lfm =3D 'DWS'::text)
>  &= nbsp;      ->  Bitmap Heap Scan on tr= ansaction_details idatrndtl
> (cost=3D31.85..121.60 rows=3D1407 width=3D22) (actual time=3D2.864..12= .060
> rows=3D20153 loops=3D1)
>     &= nbsp;         Recheck Cond: (idatrn= dtl.ida_trans_match_source_id =3D
> mtchsrcprj3.id)
>            = ;   ->  Bitmap Index Scan on
> transaction_det= ails_ida_trans_match_source_id  (cost=3D0.00..31.50
> rows= =3D1407 width=3D0) (actual time=3D2.696..2.696 rows=3D20153 loops=3D1)
&= gt;            =          Index Cond: (idatrndtl.ida= _trans_match_source_id =3D
> mtchsrcprj3.id)

> The = first frustration is that I can't get the transaction details scan
&= gt; to get any more accurate.  It thinks it will find 1407 record= s,
> instead it finds 20,153.  Then for whatever reason it = thinks that a
> join between 1 record and 1407 records will return 1 record. &nbs= p;This is
> mainly what I can't understand.  Why does i= t think it will only get
> one record in response when it's a lef= t join?

I don't see any left join there ...

> PG 8.2.4 on Linux kernel 2.6.9 x64

The first thing you shou= ld do is update to 8.2.6; we've fixed a fair
number of problems sinc= e 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 f= or
the join columns involved (idatrndtl.ida_trans_match_source_id andmtchsrcprj3.id).  (You do = have up-to-date ANALYZE stats for both
of those tables, right?)

       &= nbsp;           &nbs= p;    regards, tom lane


=
I know it's somewhat premature as we haven't had a chanc= e to do the update yet, but here is what I did w/ the statistics with the c= urrent version for chuckles and grins just to see if it would make a differ= ence in the plan.

# alter table projec= t alter column id set statistics 1000;
ALTER TABLE
# an= alyze project;
ANALYZE
# alter table transaction_detail= s alter column ida_trans_match_source_id set statistics 1000;
ALTER TABLE
# analyze transaction_details;
ANALYZE=
# select * from pg_stats where (tablename=3D'project' an= d attname=3D'id') or (tablename=3D'transaction_details' and= attname=3D'ida_trans_match_source_id');
 schemaname |      tablename      |=          attname         &nbs= p;| null_frac | avg_width | n_distinct |          =                     &nbs= p;                     &n= bsp;             most_common_vals   &nbs= p;                     &n= bsp;                     =                     | &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;             most_common_freqs   &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =              |       &nbs= p;                     &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =                      = ;histogram_bounds                 &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;            | correlation 
------------+---------------------+---------------------------+-------= ----+-----------+------------+---------------------------------------------= ---------------------------------------------------------------------------= ------------------------------+--------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------+--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------------------------------------------+-------------
 public     | project          = ;   | id                  = ;      |         0 |      = ;   4 |         -1 |         &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =                      = ;                     &nb= sp;            |         =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                  | {6,7,= 8,12,13,14,15,17,18,19,24,25,26,27,28,29,30,31,32,33,34,35,36,37,41,42,71,7= 2,797,802,803,809,812,813,814,815,816,817,818,822,823,824,825,826,827,828,8= 29,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,= 848,849,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936= ,937,938,939,940,941,942,946,947,948,949,950,951,952,953,954,955,956,957,95= 8,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,977,9= 78} |    0.939317
 public     | transaction_details | ida_trans_match_sou= rce_id |  0.480469 |         4 |     &nb= sp;   74 | {832,818,930,937,923,812,931,829,837,830,836,14,809,838,936= ,924,921,922,814,816,817,827,815,941,835,967,926,813,968,928,920,939,925,97= 4,833,965,933} | {0.100562,0.100233,0.0412866,0.0245354,0.0223948,0.021277,= 0.0198998,0.018817,0.0182431,0.0181583,0.0180236,0.0141714,0.0107633,0.0095= 5071,0.00917646,0.00639708,0.00562364,0.00491507,0.00453584,0.0037624,0.003= 32828,0.00332828,0.00323846,0.00309874,0.00295403,0.00267959,0.00234526,0.0= 0227041,0.00221552,0.00220055,0.00215565,0.00207581,0.00179138,0.00136225,0= .00114269,0.00113271,0.00100796} | {6,6,7,8,15,802,802,802,802,803,803,839,= 841,844,844,845,845,846,927,927,934,934,935,935,938,938,940,942,952,954,955= ,955,957,972,972,972,978}               =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =     |    0.218267
(2 rows)
 
This had no appreciable difference in t= he plan.  Here is the part that seems to be causing the problem again = after the increase in stats.  It still thinks there is only one row in= the result.

 -> &nb= sp;Nested Loop  (cost=3D42.75..161.78 rows=3D1 width=3D38) (actual tim= e=3D391.797..425.337 rows=3D20153 loops=3D1)
     =   ->  Hash Join  (cost=3D10.89..22.58 rows=3D1 width=3D24= ) (actual time=3D0.069..0.139 rows=3D1 loops=3D1)
             Hash Cond: (mtchsrcprj= 3.funding_source_id =3D mtchsrcprjfs3.nameid)
    =         ->  Seq Scan on project mtchsrcprj3 &n= bsp;(cost=3D0.00..11.22 rows=3D122 width=3D8) (actual time=3D0.002..0.054 r= ows=3D122 loops=3D1)
             ->  Hash  = ;(cost=3D10.83..10.83 rows=3D5 width=3D24) (actual time=3D0.022..0.022 rows= =3D1 loops=3D1)
             &= nbsp;     ->  Index Scan using name_float_lfm_idx on name= master mtchsrcprjfs3  (cost=3D0.00..10.83 rows=3D5 width=3D24) (actual= time=3D0.013..0.014 rows=3D1 loops=3D1)
                   &= nbsp;     Index Cond: (name_float_lfm =3D 'DWS'::text)
       ->  Bitmap Heap Scan on transact= ion_details idatrndtl  (cost=3D31.87..121.61 rows=3D1407 width=3D22) (= actual time=3D391.722..410.129 rows=3D20153 loops=3D1)
             Recheck Cond: (idatrnd= tl.ida_trans_match_source_id =3D mtchsrcp= rj3.id)
             ->=  Bitmap Index Scan on transaction_details_ida_trans_match_source_id &= nbsp;(cost=3D0.00..31.51 rows=3D1407 width=3D0) (actual time=3D391.523..391= .523 rows=3D20153 loops=3D1)
                   I= ndex Cond: (idatrndtl.ida_trans_match_source_id =3D mtchsrcprj3.id)

Here is the relevant snippet from the query

<-- snip -->
FROM 
  accounts acc 
  left join transactions idatrn on (ac= c.id =3D idatrn.account_id) 
left join transaction_details idatrndtl on (idatrn.id =3D idatrndtl.transaction_id) 
left join pr= oject mtchsrcprj3 on (idatrndtl.ida_trans_match_source_id =3D mtchsrcprj3.id) 
left join namemaster mtchsrcprjfs3 on ( mtchsrcprj3.funding_sourc= e_id =3D mtchsrcprjfs3.nameid) 
<-- snip -->

I'll update again on= ce we've had a chance to do the update.

-Chris

------=_Part_11679_5343399.1202828952499--