explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0wt

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 170.147 ↑ 2.0 1 1

Hash Left Join (cost=6,517.82..6,536.24 rows=2 width=349) (actual time=170.146..170.147 rows=1 loops=1)

  • Hash Cond: ((cu.id)::text = postdated.customer_no)
2. 0.002 170.135 ↑ 2.0 1 1

Nested Loop Left Join (cost=6,517.79..6,536.19 rows=2 width=286) (actual time=170.134..170.135 rows=1 loops=1)

3. 0.006 170.121 ↑ 2.0 1 1

Nested Loop Left Join (cost=6,517.51..6,535.17 rows=2 width=316) (actual time=170.121..170.121 rows=1 loops=1)

4. 0.007 170.100 ↑ 2.0 1 1

Nested Loop Left Join (cost=6,517.23..6,534.12 rows=2 width=349) (actual time=170.100..170.100 rows=1 loops=1)

5. 0.001 169.847 ↑ 2.0 1 1

Limit (cost=6,516.95..6,517.49 rows=2 width=197) (actual time=169.847..169.847 rows=1 loops=1)

6. 66.077 169.846 ↑ 2.0 1 1

GroupAggregate (cost=6,516.95..6,517.49 rows=2 width=197) (actual time=169.846..169.846 rows=1 loops=1)

  • Group Key: a.customer_id
  • 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))
7. 4.681 103.769 ↓ 7,613.0 15,226 1

Sort (cost=6,516.95..6,516.95 rows=2 width=88) (actual time=103.089..103.769 rows=15,226 loops=1)

  • Sort Key: a.customer_id
  • Sort Method: quicksort Memory: 2,526kB
8. 7.684 99.088 ↓ 7,613.0 15,226 1

Hash Right Join (cost=4,063.96..6,516.94 rows=2 width=88) (actual time=70.657..99.088 rows=15,226 loops=1)

  • Hash Cond: (c.target_receivable_list_id = a.id)
9. 1.494 54.677 ↓ 1.0 30,146 1

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

10. 13.818 39.405 ↓ 1.0 26,445 1

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

  • Group Key: c.target_receivable_list_id
11. 17.187 25.587 ↑ 1.0 30,696 1

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

  • Sort Key: c.target_receivable_list_id
  • Sort Method: quicksort Memory: 3,037kB
12. 8.400 8.400 ↑ 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.018..8.400 rows=30,696 loops=1)

13. 10.921 13.778 ↑ 1.0 3,701 1

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

  • Group Key: c_1.source_receivable_list_id
14. 2.857 2.857 ↑ 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.022..2.857 rows=30,696 loops=1)

15. 4.339 36.727 ↓ 7,613.0 15,226 1

Hash (cost=498.24..498.24 rows=2 width=72) (actual time=36.727..36.727 rows=15,226 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,676kB
16. 1.325 32.388 ↓ 7,613.0 15,226 1

Nested Loop (cost=0.41..498.24 rows=2 width=72) (actual time=1.063..32.388 rows=15,226 loops=1)

17. 1.641 1.641 ↑ 1.0 1 1

Seq Scan on customer cu_1 (cost=0.00..301.82 rows=1 width=209) (actual time=1.039..1.641 rows=1 loops=1)

  • Filter: (((code)::text >= '1RETAIL'::text) AND ((code)::text <= '1RETAIL'::text))
  • Rows Removed by Filter: 2,720
18. 29.422 29.422 ↓ 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.022..29.422 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
19. 0.246 0.246 ↑ 1.0 1 1

Index Scan using customer_pkey on customer cu (cost=0.28..8.30 rows=1 width=287) (actual time=0.246..0.246 rows=1 loops=1)

  • Index Cond: ((a.customer_id)::text = (id)::text)
20. 0.015 0.015 ↑ 1.0 1 1

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

  • Index Cond: ((cu.contact)::text = (id)::text)
21. 0.012 0.012 ↑ 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.012..0.012 rows=1 loops=1)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
23. 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)

24. 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 : 3.366 ms
Execution time : 171.672 ms