explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8yGM

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 1,689.727 ↑ 1.0 1 1

Aggregate (cost=192.06..192.10 rows=1 width=32) (actual time=1,689.726..1,689.727 rows=1 loops=1)

2. 0.003 1,689.718 ↑ 1.0 1 1

Limit (cost=192.02..192.02 rows=1 width=134) (actual time=1,689.716..1,689.718 rows=1 loops=1)

3. 0.020 1,689.715 ↑ 1.0 1 1

Sort (cost=192.02..192.02 rows=1 width=134) (actual time=1,689.714..1,689.715 rows=1 loops=1)

  • Sort Key: deposits.priority DESC
  • Sort Method: quicksort Memory: 25kB
4. 0.003 1,689.695 ↑ 1.0 1 1

Subquery Scan on deposits (cost=191.96..192.01 rows=1 width=134) (actual time=1,689.693..1,689.695 rows=1 loops=1)

5. 0.003 1,689.692 ↑ 1.0 1 1

Unique (cost=191.96..191.98 rows=1 width=35) (actual time=1,689.690..1,689.692 rows=1 loops=1)

6. 0.016 1,689.689 ↑ 1.0 1 1

Sort (cost=191.96..191.97 rows=1 width=35) (actual time=1,689.688..1,689.689 rows=1 loops=1)

  • Sort Key: at.id, (CASE WHEN ((btrim((dp.financial_deposits ->> 'urgent_amount_greater_than'::text)) <> ''::text) AND ((at.transaction_amount * '-1'::numeric) >= (((dp.financial_deposits ->> 'urgent_amount_greater_than'::text))::integer)::numeric)) THEN 3 WHEN ((btrim((dp.financial_deposits ->> 'urgent_payment_received_since'::text)) <> ''::text) AND (get_date_difference('day'::character varying, now(), (at.post_date)::timestamp with time zone) >= ((dp.financial_deposits ->> 'urgent_payment_received_since'::text))::integer)) THEN 3 WHEN ((btrim((dp.financial_deposits ->> 'important_amount_greater_than'::text)) <> ''::text) AND ((at.transaction_amount * '-1'::numeric) >= (((dp.financial_deposits ->> 'important_amount_greater_than'::text))::integer)::numeric)) THEN 2 WHEN ((btrim((dp.financial_deposits ->> 'important_payment_received_since'::text)) <> ''::text) AND (get_date_difference('day'::character varying, now(), (at.post_date)::timestamp with time zone) >= ((dp.financial_deposits ->> 'important_payment_received_since'::text))::integer)) THEN 2 ELSE 1 END), cl.property_id, p.property_name
  • Sort Method: quicksort Memory: 25kB
7. 0.802 1,689.673 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.67..191.95 rows=1 width=35) (actual time=1,382.179..1,689.673 rows=1 loops=1)

8. 14.497 1,688.835 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.53..191.15 rows=1 width=44) (actual time=1,381.344..1,688.835 rows=1 loops=1)

  • Filter: (adt.id IS NULL)
  • Rows Removed by Filter: 8301
9. 29.012 1,599.620 ↓ 8,302.0 8,302 1

Nested Loop Semi Join (cost=2.10..190.14 rows=1 width=52) (actual time=874.444..1,599.620 rows=8,302 loops=1)

10. 54.883 1,040.261 ↓ 27,913.0 27,913 1

Nested Loop (cost=1.57..187.75 rows=1 width=52) (actual time=0.315..1,040.261 rows=27,913 loops=1)

11. 39.873 699.807 ↓ 25,961.0 25,961 1

Nested Loop (cost=1.14..185.55 rows=1 width=35) (actual time=0.241..699.807 rows=25,961 loops=1)

12. 19.310 79.120 ↓ 657.0 22,339 1

Nested Loop (cost=0.71..116.70 rows=34 width=35) (actual time=0.174..79.120 rows=22,339 loops=1)

13. 0.130 0.130 ↓ 4.0 32 1

Index Only Scan using idx_properties_cid_id_property_name on properties p (cost=0.28..2.58 rows=8 width=27) (actual time=0.061..0.130 rows=32 loops=1)

  • Index Cond: (cid = 10292)
  • Heap Fetches: 3
14. 59.680 59.680 ↓ 46.5 698 32

Index Scan using idx_cached_leases_cid_property_id on cached_leases cl (cost=0.43..13.82 rows=15 width=12) (actual time=0.018..1.865 rows=698 loops=32)

  • Index Cond: ((cid = 10292) AND (property_id = p.id))
15. 580.814 580.814 ↑ 1.0 1 22,339

Index Scan using idx_ar_payments_cid_property_id_lease_id on ar_payments ap (cost=0.43..1.99 rows=1 width=16) (actual time=0.016..0.026 rows=1 loops=22,339)

  • Index Cond: ((cid = 10292) AND (property_id = cl.property_id) AND (lease_id = cl.id))
  • Filter: (payment_type_id < 4)
  • Rows Removed by Filter: 3
16. 285.571 285.571 ↑ 1.0 1 25,961

Index Scan using idx_ar_transactions_ar_payment_id on ar_transactions at (cost=0.43..2.16 rows=1 width=29) (actual time=0.010..0.011 rows=1 loops=25,961)

  • Index Cond: (ar_payment_id = ap.id)
  • Filter: ((NOT is_temporary) AND (ar_code_type_id = 1) AND (cid = 10292))
17. 267.596 530.347 ↓ 0.0 0 27,913

Nested Loop (cost=0.54..2.36 rows=1 width=8) (actual time=0.019..0.019 rows=0 loops=27,913)

  • Join Filter: (lp.property_id = pba.property_id)
  • Rows Removed by Join Filter: 12
18. 83.739 83.739 ↓ 3.0 3 27,913

Index Only Scan using idx_property_bank_accounts_cid_prop_bank on property_bank_accounts pba (cost=0.29..0.33 rows=1 width=8) (actual time=0.002..0.003 rows=3 loops=27,913)

  • Index Cond: ((cid = 10292) AND (property_id = at.property_id))
  • Heap Fetches: 0
19. 179.012 179.012 ↓ 4.0 4 89,506

Function Scan on load_properties lp (cost=0.25..2.00 rows=1 width=8) (actual time=0.001..0.002 rows=4 loops=89,506)

  • Filter: ((is_disabled = 0) AND (cid = 10292))
20. 74.718 74.718 ↑ 1.0 1 8,302

Index Scan using idx_ar_deposit_transactions_ar_transaction_id on ar_deposit_transactions adt (cost=0.42..0.98 rows=1 width=20) (actual time=0.009..0.009 rows=1 loops=8,302)

  • Index Cond: (ar_transaction_id = at.id)
  • Filter: ((cid = 10292) AND (is_deleted = 0) AND (cid = at.cid) AND (property_id = at.property_id) AND (lease_id = at.lease_id))
  • Rows Removed by Filter: 0
21. 0.036 0.036 ↑ 1.0 1 1

Index Scan using pk_dashboard_priorities on dashboard_priorities dp (cost=0.14..0.18 rows=1 width=104) (actual time=0.035..0.036 rows=1 loops=1)

  • Index Cond: ((cid = cl.cid) AND (cid = 10292))
Planning time : 13.151 ms
Execution time : 1,690.646 ms