explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l7Zh3

Settings
# exclusive inclusive rows x rows loops node
1. 0.093 183.803 ↑ 2.0 1 1

Hash Left Join (cost=6,527.71..6,546.12 rows=2 width=349) (actual time=183.801..183.803 rows=1 loops=1)

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

Nested Loop Left Join (cost=6,527.68..6,546.07 rows=2 width=286) (actual time=183.707..183.708 rows=1 loops=1)

3. 0.003 183.694 ↑ 2.0 1 1

Nested Loop Left Join (cost=6,527.39..6,545.05 rows=2 width=316) (actual time=183.693..183.694 rows=1 loops=1)

4. 0.004 183.652 ↑ 2.0 1 1

Nested Loop Left Join (cost=6,527.11..6,544.01 rows=2 width=349) (actual time=183.651..183.652 rows=1 loops=1)

5. 0.001 183.592 ↑ 2.0 1 1

Limit (cost=6,526.83..6,527.37 rows=2 width=197) (actual time=183.592..183.592 rows=1 loops=1)

6. 78.895 183.591 ↑ 2.0 1 1

GroupAggregate (cost=6,526.83..6,527.37 rows=2 width=197) (actual time=183.590..183.591 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. 5.510 104.696 ↓ 7,613.0 15,226 1

Sort (cost=6,526.83..6,526.84 rows=2 width=88) (actual time=103.830..104.696 rows=15,226 loops=1)

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

Hash Right Join (cost=498.55..6,526.82 rows=2 width=88) (actual time=58.452..99.186 rows=15,226 loops=1)

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

Append (cost=0.29..5,915.50 rows=30,145 width=48) (actual time=0.021..53.668 rows=30,146 loops=1)

10. 24.572 37.435 ↓ 1.0 26,445 1

GroupAggregate (cost=0.29..4,136.29 rows=26,439 width=48) (actual time=0.020..37.435 rows=26,445 loops=1)

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

Index Scan using idx_tmparapply_code on arapply_77da465d144e4a378fb39bb694cb220d c (cost=0.29..3,652.32 rows=30,696 width=22) (actual time=0.011..12.863 rows=30,696 loops=1)

12. 11.292 14.253 ↑ 1.0 3,701 1

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

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

14. 4.469 36.556 ↓ 7,613.0 15,226 1

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

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

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

16. 1.432 1.432 ↑ 1.0 1 1

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

  • Filter: (((code)::text >= '1RETAIL'::text) AND ((code)::text <= '1RETAIL'::text))
  • Rows Removed by Filter: 2,720
17. 29.183 29.183 ↓ 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.017..29.183 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
18. 0.056 0.056 ↑ 1.0 1 1

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

  • Index Cond: ((a.customer_id)::text = (id)::text)
19. 0.039 0.039 ↑ 1.0 1 1

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

  • Index Cond: ((cu.contact)::text = (id)::text)
20. 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)
21. 0.000 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
22. 0.001 0.002 ↑ 1.0 1 1

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

23. 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.056 ms
Execution time : 185.258 ms