explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZW2G

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 155.321 ↑ 2.0 1 1

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

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

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

3. 0.002 155.290 ↑ 2.0 1 1

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

4. 0.004 155.274 ↑ 2.0 1 1

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

5. 0.001 155.238 ↑ 2.0 1 1

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

6. 55.443 155.237 ↑ 2.0 1 1

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

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

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

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

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

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

10. 19.745 35.374 ↓ 1.0 26,445 1

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

  • Group Key: c.target_receivable_list_id
11. 15.629 15.629 ↑ 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.154..15.629 rows=30,696 loops=1)

12. 11.638 14.732 ↑ 1.0 3,701 1

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

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

14. 4.150 34.970 ↓ 7,613.0 15,226 1

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

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

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

16. 1.547 1.547 ↑ 1.0 1 1

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

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

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

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

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

  • Index Cond: ((cu.contact)::text = (id)::text)
20. 0.013 0.013 ↑ 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.013..0.013 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.001..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 : 3.726 ms
Execution time : 156.889 ms