explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0im

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 191.329 ↑ 2.0 1 1

Hash Left Join (cost=6,526.17..6,845.76 rows=2 width=349) (actual time=190.253..191.329 rows=1 loops=1)

  • Hash Cond: ((cu.code)::text = postdated.customer_no)
2. 0.005 191.303 ↑ 2.0 1 1

Nested Loop Left Join (cost=6,526.13..6,845.71 rows=2 width=317) (actual time=190.228..191.303 rows=1 loops=1)

3. 0.229 191.274 ↑ 2.0 1 1

Hash Right Join (cost=6,525.85..6,844.69 rows=2 width=347) (actual time=190.199..191.274 rows=1 loops=1)

  • Hash Cond: ((cu.code)::text = (aging_list.code)::text)
4. 2.396 2.396 ↑ 1.0 2,721 1

Seq Scan on customer cu (cost=0.00..308.62 rows=2,721 width=287) (actual time=0.025..2.396 rows=2,721 loops=1)

5. 0.018 188.649 ↑ 2.0 1 1

Hash (cost=6,525.83..6,525.83 rows=2 width=195) (actual time=188.649..188.649 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
6. 0.002 188.631 ↑ 2.0 1 1

Subquery Scan on aging_list (cost=6,525.25..6,525.83 rows=2 width=195) (actual time=188.630..188.631 rows=1 loops=1)

7. 0.001 188.629 ↑ 2.0 1 1

Limit (cost=6,525.25..6,525.81 rows=2 width=232) (actual time=188.629..188.629 rows=1 loops=1)

8. 61.100 188.628 ↑ 2.0 1 1

GroupAggregate (cost=6,525.25..6,525.81 rows=2 width=232) (actual time=188.628..188.628 rows=1 loops=1)

  • Group Key: cu_1.name, cu_1.code, a.customer_id, co.currency
  • Filter: ((abs(sum(CASE WHEN ((a.transaction_type)::text = ANY ('{AR_CREDIT,CB_CREDIT}'::text[])) THEN (('0'::numeric - COALESCE(a.invoice_amount, '0'::numeric)) - COALESCE((sum(COALESCE(c.target_amount, '0'::numeric))), '0'::numeric)) ELSE (COALESCE(a.invoice_amount, '0'::numeric) - COALESCE((sum(COALESCE(c.target_amount, '0'::numeric))), '0'::numeric)) END)) > '0'::numeric) OR (abs(sum(CASE WHEN (((to_date('2020-09-02'::text, 'YYYY-MM-DD'::text) - a.invoice_date) < 30) AND ((to_date('2020-09-02'::text, 'YYYY-MM-DD'::text) - a.invoice_date) >= 0)) THEN CASE WHEN ((a.transaction_type)::text = ANY ('{AR_CREDIT,CB_CREDIT}'::text[])) THEN (('0'::numeric - COALESCE(a.invoice_amount, '0'::numeric)) - COALESCE((sum(COALESCE(c.target_amount, '0'::numeric))), '0'::numeric)) ELSE (COALESCE(a.invoice_amount, '0'::numeric) - COALESCE((sum(COALESCE(c.target_amount, '0'::numeric))), '0'::numeric)) END ELSE '0'::numeric END)) > '0'::numeric) OR (abs(sum(CASE WHEN (((to_date('2020-09-02'::text, 'YYYY-MM-DD'::text) - a.invoice_date) < 60) AND ((to_date('2020-09-02'::text, 'YYYY-MM-DD'::text) - a.invoice_date) >= 30)) THEN CASE WHEN ((a.transaction_type)::text = ANY ('{AR_CREDIT,CB_CREDIT}'::text[])) THEN (('0'::numeric - COALESCE(a.invoice_amount, '0'::numeric)) - COALESCE((sum(COALESCE(c.target_amount, '0'::numeric))), '0'::numeric)) ELSE (COALESCE(a.invoice_amount, '0'::numeric) - COALESCE((sum(COALESCE(c.target_amount, '0'::numeric))), '0'::numeric)) END ELSE '0'::numeric END)) > '0'::numeric) OR (abs(sum(CASE WHEN (((to_date('2020-09-02'::text, 'YYYY-MM-DD'::text) - a.invoice_date) < 90) AND ((to_date('2020-09-02'::text, 'YYYY-MM-DD'::text) - a.invoice_date) >= 60)) THEN CASE WHEN ((a.transaction_type)::text = ANY ('{AR_CREDIT,CB_CREDIT}'::text[])) THEN (('0'::numeric - COALESCE(a.invoice_amount, '0'::numeric)) - COALESCE((sum(COALESCE(c.target_amount, '0'::numeric))), '0'::numeric)) ELSE (COALESCE(a.invoice_amount, '0'::numeric) - COALESCE((sum(COALESCE(c.target_amount, '0'::numeric))), '0'::numeric)) END ELSE '0'::numeric END)) > '0'::numeric) OR (abs(sum(CASE WHEN ((to_date('2020-09-02'::text, 'YYYY-MM-DD'::text) - a.invoice_date) >= 90) THEN CASE WHEN ((a.transaction_type)::text = ANY ('{AR_CREDIT,CB_CREDIT}'::text[])) THEN (('0'::numeric - COALESCE(a.invoice_amount, '0'::numeric)) - COALESCE((sum(COALESCE(c.target_amount, '0'::numeric))), '0'::numeric)) ELSE (COALESCE(a.invoice_amount, '0'::numeric) - COALESCE((sum(COALESCE(c.target_amount, '0'::numeric))), '0'::numeric)) END ELSE '0'::numeric END)) > '0'::numeric))
9. 8.813 127.528 ↓ 7,613.0 15,226 1

Sort (cost=6,525.25..6,525.26 rows=2 width=123) (actual time=126.760..127.528 rows=15,226 loops=1)

  • Sort Key: cu_1.name, cu_1.code, a.customer_id, co.currency
  • Sort Method: quicksort Memory: 2,526kB
10. 10.244 118.715 ↓ 7,613.0 15,226 1

Hash Right Join (cost=4,072.27..6,525.24 rows=2 width=123) (actual time=84.956..118.715 rows=15,226 loops=1)

  • Hash Cond: (c.target_receivable_list_id = a.id)
11. 1.662 66.958 ↓ 1.0 30,146 1

Append (cost=3,565.70..5,905.62 rows=30,145 width=48) (actual time=29.752..66.958 rows=30,146 loops=1)

12. 17.340 49.605 ↓ 1.0 26,445 1

GroupAggregate (cost=3,565.70..4,126.40 rows=26,439 width=48) (actual time=29.752..49.605 rows=26,445 loops=1)

  • Group Key: c.target_receivable_list_id
13. 22.947 32.265 ↑ 1.0 30,696 1

Sort (cost=3,565.70..3,642.44 rows=30,696 width=22) (actual time=29.738..32.265 rows=30,696 loops=1)

  • Sort Key: c.target_receivable_list_id
  • Sort Method: quicksort Memory: 3,037kB
14. 9.318 9.318 ↑ 1.0 30,696 1

Seq Scan on arapply_77da465d144e4a378fb39bb694cb220d c (cost=0.00..1,277.96 rows=30,696 width=22) (actual time=0.024..9.318 rows=30,696 loops=1)

15. 12.265 15.691 ↑ 1.0 3,701 1

HashAggregate (cost=1,431.44..1,477.77 rows=3,706 width=48) (actual time=13.774..15.691 rows=3,701 loops=1)

  • Group Key: c_1.source_receivable_list_id
16. 3.426 3.426 ↑ 1.0 30,696 1

Seq Scan on arapply_77da465d144e4a378fb39bb694cb220d c_1 (cost=0.00..1,277.96 rows=30,696 width=22) (actual time=0.031..3.426 rows=30,696 loops=1)

17. 6.969 41.513 ↓ 7,613.0 15,226 1

Hash (cost=506.54..506.54 rows=2 width=107) (actual time=41.513..41.513 rows=15,226 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,063kB
18. 1.583 34.544 ↓ 7,613.0 15,226 1

Nested Loop (cost=0.70..506.54 rows=2 width=107) (actual time=1.012..34.544 rows=15,226 loops=1)

19. 0.004 2.095 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.28..310.12 rows=1 width=72) (actual time=0.989..2.095 rows=1 loops=1)

20. 2.079 2.079 ↑ 1.0 1 1

Seq Scan on customer cu_1 (cost=0.00..301.82 rows=1 width=240) (actual time=0.973..2.079 rows=1 loops=1)

  • Filter: (((code)::text >= '1RETAIL'::text) AND ((code)::text <= '1RETAIL'::text))
  • Rows Removed by Filter: 2,720
21. 0.012 0.012 ↑ 1.0 1 1

Index Scan using contact_pkey on contact co (cost=0.28..8.30 rows=1 width=41) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: ((cu_1.contact)::text = (id)::text)
22. 30.866 30.866 ↓ 1,903.2 15,226 1

Index Scan using idx_cash_receipt_scheduled_payment_customer_id on cash_receipt_scheduled_payment a (cost=0.41..196.34 rows=8 width=72) (actual time=0.023..30.866 rows=15,226 loops=1)

  • Index Cond: ((customer_id)::text = (cu_1.id)::text)
  • Filter: (active AND ((scheduled_payment_status)::text <> 'CLOSED'::text) AND ((reassign_type IS NULL) OR ((reassign_type)::text <> ALL ('{REASSIGNMENT_REVERSE,REASSIGNMENT}'::text[]))) AND ((to_date('2020-09-02'::text, 'YYYY-MM-DD'::text) - invoice_date) >= 0))
  • Rows Removed by Filter: 17,097
23. 0.024 0.024 ↑ 1.0 1 1

Index Scan using crm_source_entity_pkey on crm_source_entity cuu (cost=0.28..0.51 rows=1 width=44) (actual time=0.024..0.024 rows=1 loops=1)

  • Index Cond: ((cu.invoice_account)::text = (id)::text)
24. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=64) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
25. 0.000 0.001 ↑ 1.0 1 1

Subquery Scan on postdated (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=1)

26. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.001..0.001 rows=1 loops=1)

Planning time : 4.152 ms
Execution time : 193.065 ms