explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7SRD

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 18,580.204 ↓ 0.0 0 1

GroupAggregate (cost=52,002.78..52,002.87 rows=2 width=330) (actual time=18,580.204..18,580.204 rows=0 loops=1)

  • Group Key: voo.name1, voo.cash, voo.card, voo.total, voo.lastupdated
2. 0.032 18,580.202 ↓ 0.0 0 1

Sort (cost=52,002.78..52,002.78 rows=2 width=298) (actual time=18,580.202..18,580.202 rows=0 loops=1)

  • Sort Key: voo.name1, voo.cash, voo.card, voo.total, voo.lastupdated
  • Sort Method: quicksort Memory: 25kB
3. 0.002 18,580.170 ↓ 0.0 0 1

Subquery Scan on voo (cost=52,002.64..52,002.77 rows=2 width=298) (actual time=18,580.170..18,580.170 rows=0 loops=1)

4. 0.002 18,580.168 ↓ 0.0 0 1

GroupAggregate (cost=52,002.64..52,002.75 rows=2 width=309) (actual time=18,580.168..18,580.168 rows=0 loops=1)

  • Group Key: o.name, mcd.ad_client_id, mcd.ad_org_id, (CASE WHEN (d_1.docbasetype = 'MMS'::bpchar) THEN sum(mcd.amt) WHEN (d_1.docbasetype = 'MMR'::bpchar) THEN (sum(mcd.amt) * '-1'::numeric) ELSE '0'::numeric END)
5. 0.015 18,580.166 ↓ 0.0 0 1

Sort (cost=52,002.64..52,002.64 rows=2 width=196) (actual time=18,580.166..18,580.166 rows=0 loops=1)

  • Sort Key: o.name, mcd.ad_client_id, mcd.ad_org_id, (CASE WHEN (d_1.docbasetype = 'MMS'::bpchar) THEN sum(mcd.amt) WHEN (d_1.docbasetype = 'MMR'::bpchar) THEN (sum(mcd.amt) * '-1'::numeric) ELSE '0'::numeric END)
  • Sort Method: quicksort Memory: 25kB
6. 0.001 18,580.151 ↓ 0.0 0 1

Nested Loop Left Join (cost=25,200.97..52,002.63 rows=2 width=196) (actual time=18,580.151..18,580.151 rows=0 loops=1)

  • Join Filter: ((mino.dateacct = p.dateacct) AND (mcd.ad_org_id = o.ad_org_id))
7. 0.000 18,580.150 ↓ 0.0 0 1

Nested Loop (cost=1,004.66..27,753.99 rows=1 width=177) (actual time=18,580.150..18,580.150 rows=0 loops=1)

  • Join Filter: (p.ad_org_id = o.ad_org_id)
8. 0.393 0.393 ↓ 22.0 22 1

Index Scan using ad_org_pkey on ad_org o (cost=0.14..12.70 rows=1 width=154) (actual time=0.096..0.393 rows=22 loops=1)

  • Filter: (ad_client_id = '1000000'::numeric)
  • Rows Removed by Filter: 10
9. 2,544.608 18,599.658 ↓ 0.0 0 22

Gather (cost=1,004.52..27,741.28 rows=1 width=29) (actual time=844.530..845.439 rows=0 loops=22)

  • Workers Planned: 2
  • Workers Launched: 2
10. 0.066 16,055.050 ↓ 0.0 0 66 / 3

Hash Join (cost=4.53..26,741.18 rows=1 width=29) (actual time=729.775..729.775 rows=0 loops=66)

  • Hash Cond: (p.c_doctype_id = d.c_doctype_id)
11. 16,054.984 16,054.984 ↓ 0.0 0 66 / 3

Parallel Seq Scan on c_payment p (cost=0.00..26,736.65 rows=2 width=36) (actual time=729.772..729.772 rows=0 loops=66)

  • Filter: ((COALESCE(c_charge_id, '0'::numeric) = '0'::numeric) AND (dateacct = CURRENT_DATE))
  • Rows Removed by Filter: 203093
12. 0.000 0.000 ↓ 0.0 0 / 3

Hash (cost=4.50..4.50 rows=2 width=6) (never executed)

13. 0.000 0.000 ↓ 0.0 0 / 3

Seq Scan on c_doctype d (cost=0.00..4.50 rows=2 width=6) (never executed)

  • Filter: (docbasetype = 'ARR'::bpchar)
14. 0.000 0.000 ↓ 0.0 0

Finalize GroupAggregate (cost=24,196.31..24,241.04 rows=304 width=87) (never executed)

  • Group Key: mcd.ad_client_id, mcd.ad_org_id, mino.dateacct, d_1.docbasetype
15. 0.000 0.000 ↓ 0.0 0

Gather Merge (cost=24,196.31..24,229.12 rows=254 width=55) (never executed)

  • Workers Planned: 2
  • Workers Launched: 0
16. 0.000 0.000 ↓ 0.0 0

Partial GroupAggregate (cost=23,196.28..23,199.77 rows=127 width=55) (never executed)

  • Group Key: mcd.ad_client_id, mcd.ad_org_id, mino.dateacct, d_1.docbasetype
17. 0.000 0.000 ↓ 0.0 0

Sort (cost=23,196.28..23,196.60 rows=127 width=28) (never executed)

  • Sort Key: mcd.ad_client_id, mcd.ad_org_id, d_1.docbasetype
18. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.50..23,191.84 rows=127 width=28) (never executed)

19. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=5.08..23,150.85 rows=65 width=20) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=4.65..22,577.24 rows=38 width=20) (never executed)

  • Hash Cond: (mino.c_doctype_id = d_1.c_doctype_id)
21. 0.000 0.000 ↓ 0.0 0

Parallel Seq Scan on m_inout mino (cost=0.00..22,571.54 rows=385 width=23) (never executed)

  • Filter: ((docstatus = ANY ('{CO,CL}'::bpchar[])) AND (dateacct = CURRENT_DATE))
22. 0.000 0.000 ↓ 0.0 0

Hash (cost=4.50..4.50 rows=12 width=10) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Seq Scan on c_doctype d_1 (cost=0.00..4.50 rows=12 width=10) (never executed)

  • Filter: (docbasetype = ANY ('{MMR,MMS}'::bpchar[]))
24. 0.000 0.000 ↓ 0.0 0

Index Scan using m_inoutline_inout on m_inoutline mil (cost=0.42..15.07 rows=3 width=16) (never executed)

  • Index Cond: (m_inout_id = mino.m_inout_id)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using m_inoutline_idx on m_costdetail mcd (cost=0.43..0.62 rows=1 width=24) (never executed)

  • Index Cond: (m_inoutline_id = mil.m_inoutline_id)