explain.depesz.com

A tool for finding a real cause for slow queries.

Result: gpy : 32786

options
exclusive inclusive rows x rows loops node
5.090 3813.946 ↓ 42.0 2815 1
→

Group (cost=1660.30..1664.65 rows=67 width=343) (actual time=3808.431..3813.946 rows=2815 loops=1)

57.580 3808.856 ↓ 56.0 3753 1
→

Sort (cost=1660.30..1660.47 rows=67 width=343) (actual time=3808.428..3808.856 rows=3753 loops=1)

  • Sort Key: foo.fullname_last_first_mdl, foo.ida_bank_account_number, foo.system_name_id, foo.owner_id, foo.ida_account_match_source_funding_source_name_float_lfm, foo.ida_account_match_source_project_number, foo.ida_match_rate, foo.ida_max_total_savings_matched, foo.ida_account_closed_date, foo.vs_query_32787_267286, foo.vs_query_32787_267287, foo.vs_query_32787_267288, foo.ida_account_id, (((CASE WHEN ((COALESCE(foo.ida_account_id, 0))::text = '0'::text) THEN ''::text ELSE (COALESCE(foo.ida_account_id, 0))::text END || ' '::text) || CASE WHEN (COALESCE(foo.ida_account_match_source_project_number, '0'::text) = '0'::text) THEN ''::text ELSE COALESCE(foo.ida_account_match_source_project_number, '0'::text) END)), foo.agency_name, foo.vs_query_32788_267224, foo.ida_project_potential_match
  • Sort Method: quicksort Memory: 1093kB
7.285 3751.276 ↓ 56.0 3753 1
→

Subquery Scan on foo (cost=1656.09..1658.27 rows=67 width=343) (actual time=3743.490..3751.276 rows=3753 loops=1)

23.721 3743.991 ↓ 56.0 3753 1
→

Sort (cost=1656.09..1656.26 rows=67 width=226) (actual time=3743.474..3743.991 rows=3753 loops=1)

  • Sort Key: dem.name_float_lfm, acc.ida_bank_account_number
  • Sort Method: quicksort Memory: 1093kB
7.124 3720.270 ↓ 56.0 3753 1
→

Nested Loop Left Join (cost=598.20..1654.06 rows=67 width=226) (actual time=25.997..3720.270 rows=3753 loops=1)

416.908 3709.393 ↓ 3753.0 3753 1
→

Nested Loop Left Join (cost=593.23..1385.47 rows=1 width=224) (actual time=25.988..3709.393 rows=3753 loops=1)

  • Join Filter: (((CASE WHEN ((COALESCE(acc.id, 0))::text = '0'::text) THEN ''::text ELSE (COALESCE(acc.id, 0))::text END || ' '::text) || CASE WHEN (COALESCE(mtchsrcprj2.project_number, '0'::text) = '0'::text) THEN ''::text ELSE COALESCE(mtchsrcprj2.project_number, '0'::text) END) = (((CASE WHEN ((COALESCE(foo.ida_account_id, 0))::text = '0'::text) THEN ''::text ELSE (COALESCE(foo.ida_account_id, 0))::text END || ' '::text) || CASE WHEN (COALESCE(foo.ida_transaction_detail_match_source_project_number, '0'::text) = '0'::text) THEN ''::text ELSE COALESCE(foo.ida_transaction_detail_match_source_project_number, '0'::text) END)))
7.855 136.212 ↓ 3753.0 3753 1
→

Nested Loop Left Join (cost=62.84..854.94 rows=1 width=128) (actual time=0.970..136.212 rows=3753 loops=1)

  • Join Filter: (mtchsrcprj2.project_number = prj.project_number)
0.574 113.345 ↓ 3753.0 3753 1
→

Nested Loop (cost=40.42..832.48 rows=1 width=114) (actual time=0.858..113.345 rows=3753 loops=1)

0.330 101.512 ↓ 3753.0 3753 1
→

Nested Loop (cost=40.42..824.10 rows=1 width=98) (actual time=0.851..101.512 rows=3753 loops=1)

3.193 93.676 ↓ 3753.0 3753 1
→

Nested Loop Left Join (cost=40.42..815.82 rows=1 width=98) (actual time=0.845..93.676 rows=3753 loops=1)

4.259 86.730 ↓ 3753.0 3753 1
→

Nested Loop Left Join (cost=40.42..815.49 rows=1 width=94) (actual time=0.841..86.730 rows=3753 loops=1)

1.967 74.965 ↓ 3753.0 3753 1
→

Nested Loop (cost=40.42..807.11 rows=1 width=82) (actual time=0.833..74.965 rows=3753 loops=1)

3.458 65.492 ↓ 3753.0 3753 1
→

Nested Loop (cost=40.42..806.83 rows=1 width=90) (actual time=0.828..65.492 rows=3753 loops=1)

0.479 47.022 ↓ 83.4 3753 1
→

Nested Loop (cost=40.42..501.39 rows=45 width=66) (actual time=0.815..47.022 rows=3753 loops=1)

0.945 27.778 ↓ 5.5 3753 1
→

Nested Loop (cost=40.42..183.59 rows=686 width=34) (actual time=0.804..27.778 rows=3753 loops=1)

0.552 1.723 ↓ 1.9 62 1
→

Hash Join (cost=40.42..128.66 rows=33 width=26) (actual time=0.792..1.723 rows=62 loops=1)

  • Hash Cond: (mtchsrc2.project_id = mtchsrcprj2.id)
0.497 0.497 ↓ 1.0 4018 1
→

Seq Scan on ida_match_sources mtchsrc2 (cost=0.00..72.93 rows=3993 width=8) (actual time=0.007..0.497 rows=4018 loops=1)

0.009 0.674 ↑ 1.0 9 1
→

Hash (cost=40.31..40.31 rows=9 width=26) (actual time=0.674..0.674 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
0.665 0.665 ↑ 1.0 9 1
→

Seq Scan on project mtchsrcprj2 (cost=0.00..40.31 rows=9 width=26) (actual time=0.130..0.665 rows=9 loops=1)

  • Filter: (project_number = ANY ('{"OR IDA10 (2011)","OR IDA2 (2003)","OR IDA3 (2004)","OR IDA4 (2005)","OR IDA5 (2006)","OR IDA6 (2007)","OR IDA7 (2008)","OR IDA8 (2009)","OR IDA9 (2010)"}'::text[]))
25.110 25.110 ↓ 2.0 61 62
→

Index Scan using ida_account_match_sources_match_group_id_idx on ida_account_match_sources accmtchgrp (cost=0.00..1.28 rows=31 width=8) (actual time=0.151..0.405 rows=61 loops=62)

  • Index Cond: (accmtchgrp.match_group_id = mtchsrc2.match_group_id)
18.765 18.765 ↑ 1.0 1 3753
→

Index Scan using accounts_pkey on accounts acc (cost=0.00..0.45 rows=1 width=36) (actual time=0.004..0.005 rows=1 loops=3753)

  • Index Cond: (acc.id = accmtchgrp.account_id)
  • Filter: (acc.program_id = 297)
15.012 15.012 ↑ 1.0 1 3753
→

Index Scan using nameid_pk on namemaster dem (cost=0.00..6.78 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=3753)

  • Index Cond: (dem.nameid = acc.owner_id)
  • Filter: (dem.programid = 297)
7.506 7.506 ↑ 1.0 1 3753
→

Index Scan using ida_match_groups_pkey on ida_match_groups mtchgrp2 (cost=0.00..0.27 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=3753)

  • Index Cond: (mtchgrp2.id = accmtchgrp.match_group_id)
7.506 7.506 ↑ 1.0 1 3753
→

Index Scan using nameid_pk on namemaster mtchsrcprjfs2 (cost=0.00..8.37 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=3753)

  • Index Cond: (mtchsrcprj2.funding_source_id = mtchsrcprjfs2.nameid)
3.753 3.753 ↓ 0.0 0 3753
→

Index Scan using project_funding_source_id on project prj (cost=0.00..0.31 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=3753)

  • Index Cond: (prj.funding_source_id = dem.nameid)
  • Filter: (prj.program_id = 297)
7.506 7.506 ↑ 1.0 1 3753
→

Index Scan using program_pkey on program ppg (cost=0.00..8.27 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=3753)

  • Index Cond: (ppg.programid = 297)
11.259 11.259 ↑ 1.0 1 3753
→

Index Scan using nameid_pk on namemaster pdm (cost=0.00..8.37 rows=1 width=24) (actual time=0.002..0.003 rows=1 loops=3753)

  • Index Cond: (pdm.nameid = ppg.nameid)
11.259 15.012 ↓ 9.0 9 3753
→

Group (cost=22.42..22.44 rows=1 width=46) (actual time=0.000..0.004 rows=9 loops=3753)

3.693 3.753 ↓ 9.0 9 3753
→

Sort (cost=22.42..22.42 rows=1 width=25) (actual time=0.000..0.001 rows=9 loops=3753)

  • Sort Key: prj.project_number, prj.program_amt_authorized
  • Sort Method: quicksort Memory: 25kB
0.039 0.060 ↓ 9.0 9 1
→

Bitmap Heap Scan on project prj (cost=4.53..22.41 rows=1 width=25) (actual time=0.030..0.060 rows=9 loops=1)

  • Recheck Cond: (program_id = 297)
  • Filter: (project_number = ANY ('{"OR IDA10 (2011)","OR IDA2 (2003)","OR IDA3 (2004)","OR IDA4 (2005)","OR IDA5 (2006)","OR IDA6 (2007)","OR IDA7 (2008)","OR IDA8 (2009)","OR IDA9 (2010)"}'::text[]))
0.021 0.021 ↑ 1.0 37 1
→

Bitmap Index Scan on project_program_id (cost=0.00..4.53 rows=37 width=0) (actual time=0.021..0.021 rows=37 loops=1)

  • Index Cond: (program_id = 297)
3073.707 3156.273 ↓ 99.0 99 3753
→

GroupAggregate (cost=530.39..530.49 rows=1 width=154) (actual time=0.033..0.841 rows=99 loops=3753)

62.297 82.566 ↓ 144.0 144 3753
→

Sort (cost=530.39..530.39 rows=1 width=154) (actual time=0.006..0.022 rows=144 loops=3753)

  • Sort Key: foo.owner_id, foo.ida_bank_account_number, foo.ida_transaction_detail_funding_source_name_float_lfm, foo.ida_transaction_detail_match_source_project_number, foo.ida_account_id, (((CASE WHEN ((COALESCE(foo.ida_account_id, 0))::text = '0'::text) THEN ''::text ELSE (COALESCE(foo.ida_account_id, 0))::text END || ' '::text) || CASE WHEN (COALESCE(foo.ida_transaction_detail_match_source_project_number, '0'::text) = '0'::text) THEN ''::text ELSE COALESCE(foo.ida_transaction_detail_match_source_project_number, '0'::text) END))
  • Sort Method: quicksort Memory: 62kB
0.555 20.269 ↓ 144.0 144 1
→

Subquery Scan on foo (cost=530.34..530.38 rows=1 width=154) (actual time=19.705..20.269 rows=144 loops=1)

0.449 19.714 ↓ 144.0 144 1
→

Sort (cost=530.34..530.35 rows=1 width=79) (actual time=19.684..19.714 rows=144 loops=1)

  • Sort Key: acc.owner_id, acc.ida_bank_account_number
  • Sort Method: quicksort Memory: 45kB
0.093 19.265 ↓ 144.0 144 1
→

Nested Loop (cost=0.00..530.33 rows=1 width=79) (actual time=0.185..19.265 rows=144 loops=1)

0.256 18.308 ↓ 144.0 144 1
→

Nested Loop Left Join (cost=0.00..530.00 rows=1 width=67) (actual time=0.175..18.308 rows=144 loops=1)

0.381 17.332 ↓ 144.0 144 1
→

Nested Loop (cost=0.00..521.62 rows=1 width=55) (actual time=0.169..17.332 rows=144 loops=1)

0.299 15.778 ↓ 65.2 391 1
→

Nested Loop (cost=0.00..519.87 rows=6 width=37) (actual time=0.163..15.778 rows=391 loops=1)

1.828 14.303 ↓ 49.0 147 1
→

Nested Loop (cost=0.00..470.27 rows=3 width=26) (actual time=0.152..14.303 rows=147 loops=1)

3.395 3.395 ↓ 43.2 1816 1
→

Index Scan using transactions_transaction_date on transactions idatrn (cost=0.00..133.99 rows=42 width=12) (actual time=0.017..3.395 rows=1816 loops=1)

  • Index Cond: ((transaction_date >= '2012-01-01'::date) AND (transaction_date <= '2012-02-01'::date))
9.080 9.080 ↓ 0.0 0 1816
→

Index Scan using accounts_pkey on accounts acc (cost=0.00..7.99 rows=1 width=18) (actual time=0.005..0.005 rows=0 loops=1816)

  • Index Cond: (acc.id = idatrn.account_id)
  • Filter: (acc.program_id = 297)
1.176 1.176 ↑ 1.0 3 147
→

Index Scan using transaction_details_transaction_id on transaction_details idatrndtl (cost=0.00..16.50 rows=3 width=19) (actual time=0.006..0.008 rows=3 loops=147)

  • Index Cond: (idatrndtl.transaction_id = idatrn.id)
1.173 1.173 ↓ 0.0 0 391
→

Index Scan using project_pkey on project mtchsrcprj3 (cost=0.00..0.28 rows=1 width=26) (actual time=0.003..0.003 rows=0 loops=391)

  • Index Cond: (mtchsrcprj3.id = idatrndtl.ida_trans_match_source_id)
  • Filter: (mtchsrcprj3.project_number = ANY ('{"OR IDA10 (2011)","OR IDA2 (2003)","OR IDA3 (2004)","OR IDA4 (2005)","OR IDA5 (2006)","OR IDA6 (2007)","OR IDA7 (2008)","OR IDA8 (2009)","OR IDA9 (2010)"}'::text[]))
0.720 0.720 ↑ 1.0 1 144
→

Index Scan using nameid_pk on namemaster mtchsrcprjfs3 (cost=0.00..8.37 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=144)

  • Index Cond: (mtchsrcprj3.funding_source_id = mtchsrcprjfs3.nameid)
0.864 0.864 ↑ 1.0 1 144
→

Index Scan using validanswerid_pk on validanswer trndtlcat (cost=0.00..0.33 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=144)

  • Index Cond: (trndtlcat.validanswerid = idatrndtl.ida_trans_detail_category_id)
  • Filter: (trndtlcat.validvalue = ANY ('{"Match Withdrawal","Match Earned","Match Interest"}'::text[]))
3.753 3.753 ↓ 0.0 0 3753
→

Bitmap Heap Scan on potential_match ppmatch (cost=4.97..267.47 rows=90 width=10) (actual time=0.001..0.001 rows=0 loops=3753)

  • Recheck Cond: (ppmatch.project_id = prj.id)
0.000 0.000 ↓ 0.0 0 3753
→

Bitmap Index Scan on potential_match_project_id_idx (cost=0.00..4.95 rows=90 width=0) (actual time=0.000..0.000 rows=0 loops=3753)

  • Index Cond: (ppmatch.project_id = prj.id)