explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FhJH

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 147.682 ↑ 2.0 1 1

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

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

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

3. 0.002 147.650 ↑ 2.0 1 1

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

4. 0.004 147.632 ↑ 2.0 1 1

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

5. 0.001 147.595 ↑ 2.0 1 1

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

6. 56.340 147.594 ↑ 2.0 1 1

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

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

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

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

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

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

10. 19.375 29.441 ↓ 1.0 26,445 1

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

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

12. 11.197 14.533 ↑ 1.0 3,701 1

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

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

14. 3.872 33.404 ↓ 7,613.0 15,226 1

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

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

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

16. 1.319 1.319 ↑ 1.0 1 1

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

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

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

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

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

  • Index Cond: ((cu.contact)::text = (id)::text)
20. 0.014 0.014 ↑ 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.014..0.014 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 : 4.260 ms
Execution time : 149.221 ms