mis-estimate in nested query causes slow runtimes - Mailing list pgsql-performance

Hello,

I've been wrestling w/ a complex query for another developer for
awhile today.  The problem consistently seems to a mis-estimation of
the number of rows resulting from a join.  This causes the query early
on to think it's only going to be processing 1 row and so it chooses
nested loops much of the way up the chain.  I've messed w/ statistics
targets on some of the columns w/ no increase in the accuracy of the
estimates.  I've analyzed the tables in question (autovac is
running).  If I turn off nested loops, the query runs in 1.5 seconds.
Otherwise it takes about 37s.  With other criteria in the where clause
it can take many minutes to return.  Here is a subset of the explain
analyze that I'm wrestling with.  The entire explain is at the end of
the email.

  ->  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?

The thing is that we've had this happen a number of times recently
with complex nested queries.  Most of the time things will run very
quickly, but an early mis-estimation by the planner causes it to use
nested loops exclusively when hash joins would be more appropriate.

Is there anything I can do to improve this short of the set
enable_nestloop=off?

PG 8.2.4 on Linux kernel 2.6.9 x64

-Chris

-------  Full explain analyze -----

Group  (cost=336.76..336.82 rows=1 width=328) (actual
time=36620.831..36621.176 rows=248 loops=1)
    ->  Sort  (cost=336.76..336.76 rows=1 width=328) (actual
time=36620.828..36620.888 rows=248 loops=1)
          Sort Key: county, fullname_last_first_mdl, ((((CASE WHEN
(COALESCE(fullname_last_first_mdl, '0'::text) = '0'::text) THEN
''::text ELSE COALESCE(fullname_last_first_mdl, '0'::text) END || '
'::text) || '-'::text) || ' '::text) || CASE WHEN (COALESCE(ssn,
'0'::text) = '0'::text) THEN ''::text ELSE COALESCE(ssn, '0'::text)
END), system_name_id, ssn, ida_account_id,
ida_account_match_source_funding_source_name_float_lfm,
ida_account_status, vs_query_27453_212267, vs_query_27453_212252,
vs_query_27453_212253, vs_query_27453_212254, vs_query_27453_212255,
(COALESCE(vs_query_27453_212267, 0::numeric) +
COALESCE(vs_query_27453_212255, 0::numeric))
          ->  Subquery Scan foo  (cost=336.72..336.75 rows=1
width=328) (actual time=36614.750..36615.319 rows=248 loops=1)
                ->  Sort  (cost=336.72..336.72 rows=1 width=255)
(actual time=36614.737..36614.798 rows=248 loops=1)
                      Sort Key: cou.validvalue, dem.name_float_lfm
                      ->  Nested Loop Left Join  (cost=194.80..336.71
rows=1 width=255) (actual time=506.599..36611.702 rows=248 loops=1)
                            ->  Nested Loop Left Join
(cost=194.80..332.90 rows=1 width=242) (actual time=506.566..36606.528
rows=248 loops=1)
                                  Join Filter: (acc.id =
qry27453.ida_account_id)
                                  ->  Nested Loop  (cost=30.16..168.13
rows=1 width=82) (actual time=0.461..27.079 rows=248 loops=1)
                                        ->  Nested Loop
(cost=30.16..167.85 rows=1 width=90) (actual time=0.453..25.133
rows=248 loops=1)
                                              ->  Nested Loop
(cost=30.16..165.94 rows=1 width=77) (actual time=0.441..19.687
rows=970 loops=1)
                                                    ->  Nested Loop
(cost=30.16..162.90 rows=1 width=40) (actual time=0.429..11.405
rows=970 loops=1)
                                                          ->  Hash
Join  (cost=30.16..162.48 rows=1 width=32) (actual time=0.417..4.524
rows=970 loops=1)
                                                                Hash
Cond: (accmtchgrp.match_group_id = mtchsrc2.match_group_id)
                                                                ->
Seq Scan on ida_account_match_sources accmtchgrp  (cost=0.00..117.26
rows=3926 width=8) (actual time=0.010..1.597 rows=3933 loops=1)
                                                                ->
Hash  (cost=30.15..30.15 rows=1 width=24) (actual time=0.315..0.315
rows=1 loops=1)
                                                                      -
 >  Hash Join  (cost=22.59..30.15 rows=1 width=24) (actual
time=0.228..0.309 rows=1 loops=1)
                                                                            Hash
  Cond: (mtchsrc2.project_id = mtchsrcprj2.id)
                                                                            ->
   Seq Scan on ida_match_sources mtchsrc2  (cost=0.00..6.85 rows=185
width=8) (actual time=0.004..0.065 rows=185 loops=1)
                                                                            ->
   Hash  (cost=22.58..22.58 rows=1 width=24) (actual time=0.162..0.162
rows=1 loops=1)
                                                                                  ->
   Hash Join  (cost=10.89..22.58 rows=1 width=24) (actual
time=0.091..0.155 rows=1 loops=1)
                                                                                        Hash
  Cond: (mtchsrcprj2.funding_source_id = mtchsrcprjfs2.nameid)
                                                                                        ->
   Seq Scan on project mtchsrcprj2  (cost=0.00..11.22 rows=122
width=8) (actual time=0.005..0.060 rows=122 loops=1)
                                                                                        ->
   Hash  (cost=10.83..10.83 rows=5 width=24) (actual time=0.039..0.039
rows=1 loops=1)
                                                                                              ->
   Index Scan using name_float_lfm_idx on namemaster mtchsrcprjfs2
(cost=0.00..10.83 rows=5 width=24) (actual time=0.028..0.030 rows=1
loops=1)
                                                                                                    Index
  Cond: (name_float_lfm = 'DWS'::text)
                                                          ->  Index
Scan using accounts_pkey on accounts acc  (cost=0.00..0.41 rows=1
width=12) (actual time=0.005..0.005 rows=1 loops=970)
                                                                Index
Cond: (acc.id = accmtchgrp.account_id)

Filter: (program_id = 221)
                                                    ->  Index Scan
using nameid_pk on namemaster dem  (cost=0.00..3.02 rows=1 width=41)
(actual time=0.006..0.007 rows=1 loops=970)
                                                          Index Cond:
(acc.owner_id = dem.nameid)
                                                          Filter:
(programid = 221)
                                              ->  Index Scan using
validanswerid_pk on validanswer accsts  (cost=0.00..1.91 rows=1
width=21) (actual time=0.004..0.005 rows=0 loops=970)
                                                    Index Cond:
(acc.ida_account_status_id = accsts.validanswerid)
                                                    Filter:
(validvalue = 'Open'::text)
                                        ->  Index Scan using
ida_match_groups_pkey on ida_match_groups mtchgrp2  (cost=0.00..0.27
rows=1 width=4) (actual time=0.003..0.006 rows=1 loops=248)
                                              Index Cond:
(accmtchgrp.match_group_id = mtchgrp2.id)
                                  ->  GroupAggregate
(cost=164.63..164.75 rows=1 width=129) (actual time=1.635..147.391
rows=230 loops=248)
                                        ->  Sort  (cost=164.63..164.64
rows=1 width=129) (actual time=1.407..3.160 rows=4715 loops=248)
                                              Sort Key:
foo.ida_account_id, foo.ida_account_status, foo.ida_match_rate
                                              ->  Sort
(cost=164.61..164.61 rows=1 width=82) (actual time=340.444..341.726
rows=4715 loops=1)
                                                    Sort Key: acc.id
                                                    ->  Nested Loop
(cost=42.74..164.60 rows=1 width=82) (actual time=3.069..333.866
rows=4715 loops=1)
                                                          ->  Nested
Loop  (cost=42.74..164.29 rows=1 width=69) (actual time=3.062..310.340
rows=4715 loops=1)
                                                                ->
Nested Loop  (cost=42.74..162.38 rows=1 width=56) (actual
time=2.955..224.985 rows=20048 loops=1)
                                                                      -
 >  Nested Loop  (cost=42.74..162.09 rows=1 width=42) (actual
time=2.947..135.616 rows=20048 loops=1)
                                                                            ->
   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)
                                                                            ->
   Index Scan using transactions_pkey on transactions idatrn
(cost=0.00..0.31 rows=1 width=12) (actual time=0.003..0.004 rows=1
loops=20153)
                                                                                  Index
  Cond: (idatrn.id = idatrndtl.transaction_id)
                                                                                  Filter
: (((transaction_date >= '2007-10-01'::date) OR (transaction_date <=
'2007-10-01'::date)) AND (transaction_date <= '2007-12-31'::date))
                                                                      -
 >  Index Scan using accounts_pkey on accounts acc  (cost=0.00..0.28
rows=1 width=18) (actual time=0.003..0.003 rows=1 loops=20048)
                                                                            Index
  Cond: (acc.id = idatrn.account_id)
                                                                            Filter
: (program_id = 221)
                                                                ->
Index Scan using validanswerid_pk on validanswer accsts
(cost=0.00..1.91 rows=1 width=21) (actual time=0.003..0.003 rows=0
loops=20048)

Index Cond: (acc.ida_account_status_id = accsts.validanswerid)

Filter: (validvalue = 'Open'::text)
                                                          ->  Index
Scan using validanswerid_pk on validanswer trndtlcat  (cost=0.00..0.29
rows=1 width=21) (actual time=0.003..0.004 rows=1 loops=4715)
                                                                Index
Cond: (idatrndtl.ida_trans_detail_category_id = trndtlcat.validanswerid)

Filter: (validvalue = ANY ('{"Match Withdrawal","Match Earned","Match
Interest"}'::text[]))
                            ->  Index Scan using validanswerid_pk on
validanswer cou  (cost=0.00..3.77 rows=1 width=21) (actual
time=0.011..0.012 rows=1 loops=248)
                                  Index Cond: (cou.validanswerid =
dem.county)
  Total runtime: 36622.135 ms
(73 rows)