explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G8Hc

Settings
# exclusive inclusive rows x rows loops node
1. 6.743 155.426 ↓ 18.1 2,468 1

Merge Left Join (cost=1,050.46..21,353.21 rows=136 width=350) (actual time=15.772..155.426 rows=2,468 loops=1)

  • Merge Cond: (microcredit_request.id = lead_reward_events.contract_request_id)
  • Filter: (((date(microcredit_request.sign_time) <= '2019-09-30'::date) AND ((NOT (alternatives: SubPlan 1 or hashed SubPlan 2)) OR (alternatives: SubPlan 3 or hashed SubPlan 4))) OR ((microcredit_request.sign_time >= '2019-09-01 00:00:00'::timestamp without time zone) AND (alternatives: SubPlan 5 or hashed SubPlan 6)) OR (alternatives: SubPlan 7 or hashed SubPlan 8))
2. 0.926 13.878 ↓ 2.2 443 1

Sort (cost=1,050.18..1,050.69 rows=205 width=350) (actual time=13.696..13.878 rows=443 loops=1)

  • Sort Key: microcredit_request.id
  • Sort Method: quicksort Memory: 253kB
3. 12.952 12.952 ↓ 2.2 443 1

Index Scan using microcredit_request_vendor_client_id_idx on microcredit_request (cost=0.29..1,042.30 rows=205 width=350) (actual time=0.068..12.952 rows=443 loops=1)

  • Index Cond: (vendor_client_id = 4400)
  • Filter: ((is_test IS FALSE) AND ((stage)::text = ANY ('{Confirm,Paidout,Frozen}'::text[])) AND (date(sign_time) >= '2019-01-01'::date))
  • Rows Removed by Filter: 968
4. 6.850 6.850 ↑ 1.2 24,022 1

Index Only Scan using lead_reward_events_contract_request_id_idx on lead_reward_events (cost=0.29..552.79 rows=28,567 width=4) (actual time=0.071..6.850 rows=24,022 loops=1)

  • Heap Fetches: 3728
5.          

SubPlan (for Merge Left Join)

6. 12.340 12.340 ↑ 1.0 1 2,468

Index Scan using lead_reward_events_contract_request_id_idx on lead_reward_events lead_reward_events_1 (cost=0.29..3.15 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=2,468)

  • Index Cond: (lead_reward_events.contract_request_id = contract_request_id)
  • Filter: ((event_type)::text = 'DOCUMENTS_RECEIVED'::text)
  • Rows Removed by Filter: 4
7. 0.000 0.000 ↓ 0.0 0

Index Scan using lead_reward_events_event_type_idx on lead_reward_events lead_reward_events_1_1 (cost=0.41..1,504.43 rows=6,281 width=4) (never executed)

  • Index Cond: ((event_type)::text = 'DOCUMENTS_RECEIVED'::text)
8. 0.000 0.000 ↓ 0.0 0

Nested Loop Semi Join (cost=0.99..82.04 rows=1 width=0) (never executed)

  • Join Filter: (lead_reward_events_3_1.created_at > lead_reward_events_2.created_at)
9. 0.000 0.000 ↓ 0.0 0

Index Scan using lead_reward_events_contract_request_id_idx on lead_reward_events lead_reward_events_2 (cost=0.29..3.15 rows=1 width=8) (never executed)

  • Index Cond: (lead_reward_events.contract_request_id = contract_request_id)
  • Filter: ((event_type)::text = 'DOCUMENTS_RECEIVED'::text)
10. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.70..76.72 rows=145 width=8) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.70..75.99 rows=145 width=8) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Index Scan using lead_reward_events_event_type_idx on lead_reward_events lead_reward_events_3_1 (cost=0.41..25.61 rows=42 width=12) (never executed)

  • Index Cond: ((event_type)::text = 'PROBLEMS_WITH_DOCUMENTS'::text)
13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using lead_reward_events_contract_request_id_idx on lead_reward_events lead_reward_events_3 (cost=0.29..1.18 rows=2 width=4) (never executed)

  • Index Cond: (contract_request_id = lead_reward_events_3_1.contract_request_id)
  • Heap Fetches: 0
14. 74.266 115.615 ↓ 3.0 6,280 1

Nested Loop Semi Join (cost=1.11..10,750.26 rows=2,094 width=4) (actual time=0.597..115.615 rows=6,280 loops=1)

  • Join Filter: (lead_reward_events_3_2.created_at > lead_reward_events_2_1.created_at)
  • Rows Removed by Join Filter: 756294
15. 3.657 3.657 ↓ 1.0 6,282 1

Index Scan using lead_reward_events_event_type_idx on lead_reward_events lead_reward_events_2_1 (cost=0.41..1,504.43 rows=6,281 width=12) (actual time=0.057..3.657 rows=6,282 loops=1)

  • Index Cond: ((event_type)::text = 'DOCUMENTS_RECEIVED'::text)
16. 37.216 37.692 ↑ 1.2 121 6,282

Materialize (cost=0.70..76.72 rows=145 width=8) (actual time=0.000..0.006 rows=121 loops=6,282)

17. 0.053 0.476 ↑ 1.1 129 1

Nested Loop (cost=0.70..75.99 rows=145 width=8) (actual time=0.061..0.476 rows=129 loops=1)

18. 0.129 0.129 ↑ 1.0 42 1

Index Scan using lead_reward_events_event_type_idx on lead_reward_events lead_reward_events_3_2 (cost=0.41..25.61 rows=42 width=12) (actual time=0.034..0.129 rows=42 loops=1)

  • Index Cond: ((event_type)::text = 'PROBLEMS_WITH_DOCUMENTS'::text)
19. 0.294 0.294 ↓ 1.5 3 42

Index Only Scan using lead_reward_events_contract_request_id_idx on lead_reward_events lead_reward_events_4 (cost=0.29..1.18 rows=2 width=4) (actual time=0.007..0.007 rows=3 loops=42)

  • Index Cond: (contract_request_id = lead_reward_events_3_2.contract_request_id)
  • Heap Fetches: 5
20. 0.000 0.000 ↓ 0.0 0

Index Scan using lead_reward_events_contract_request_id_idx on lead_reward_events lead_reward_events_4_1 (cost=0.29..3.15 rows=1 width=0) (never executed)

  • Index Cond: (lead_reward_events.contract_request_id = contract_request_id)
  • Filter: ((event_type)::text = 'REWARD_REVERSAL_ACCRUAL'::text)
21. 0.000 0.000 ↓ 0.0 0

Index Scan using lead_reward_events_event_type_idx on lead_reward_events lead_reward_events_4_2 (cost=0.41..9.80 rows=14 width=4) (never executed)

  • Index Cond: ((event_type)::text = 'REWARD_REVERSAL_ACCRUAL'::text)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using lead_reward_events_contract_request_id_idx on lead_reward_events lead_reward_events_5 (cost=0.29..3.17 rows=1 width=0) (never executed)

  • Index Cond: (lead_reward_events.contract_request_id = contract_request_id)
  • Filter: (((event_type)::text = 'DOCUMENTS_RECEIVED'::text) AND (((event_data ->> 'accounting_period'::text))::date = '2019-09-30'::date))
23. 0.000 0.000 ↓ 0.0 0

Index Scan using lead_reward_events_event_type_idx on lead_reward_events lead_reward_events_5_1 (cost=0.41..1,567.24 rows=31 width=4) (never executed)

  • Index Cond: ((event_type)::text = 'DOCUMENTS_RECEIVED'::text)
  • Filter: (((event_data ->> 'accounting_period'::text))::date = '2019-09-30'::date)
Planning time : 4.492 ms
Execution time : 156.092 ms