explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EH6o

Settings
# exclusive inclusive rows x rows loops node
1. 12.838 1,674.421 ↑ 1.0 1 1

Aggregate (cost=229,932.33..229,932.34 rows=1 width=8) (actual time=1,674.421..1,674.421 rows=1 loops=1)

2. 19.310 1,661.583 ↓ 11.4 68,722 1

Nested Loop (cost=0.43..229,917.26 rows=6,027 width=0) (actual time=0.057..1,661.583 rows=68,722 loops=1)

3. 1,023.757 1,023.757 ↓ 5.7 68,724 1

Seq Scan on service_data_consumption sdcd (cost=0.00..122,254.13 rows=12,054 width=4) (actual time=0.008..1,023.757 rows=68,724 loops=1)

  • Filter: ((id IS NOT NULL) AND ((end_date)::date >= ((now() - '11 mons'::interval))::date) AND ((end_date)::date <= ((now() + '3 mons'::interval))::date))
  • Rows Removed by Filter: 2417818
4. 218.984 618.516 ↑ 1.0 1 68,724

Index Scan using pk_consumption_entity_id on consumption_entity ce (cost=0.43..8.93 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=68,724)

  • Index Cond: (id = sdcd.consumption_entity_id)
  • Filter: ((client_id = 1007) AND COALESCE(NULLIF((alternatives: SubPlan 1 or hashed SubPlan 2), false), NULLIF((alternatives: SubPlan 3 or hashed SubPlan 4), false), NULLIF((SubPlan 5), false)))
  • Rows Removed by Filter: 0
5.          

SubPlan (forIndex Scan)

6. 137.444 137.444 ↓ 0.0 0 68,722

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr (cost=0.57..1.70 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=68,722)

  • Index Cond: ((user_id = 1466) AND (entity_type_id = 64) AND (entity_id = ce.service_id))
  • Heap Fetches: 0
7. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_1 (cost=0.57..18.57 rows=735 width=4) (never executed)

  • Index Cond: ((user_id = 1466) AND (entity_type_id = 64))
  • Heap Fetches: 0
8. 65.522 262.088 ↑ 1.0 1 65,522

Nested Loop (cost=0.86..4.20 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=65,522)

9. 65.522 65.522 ↑ 1.0 1 65,522

Index Scan using pk_contract_service_data on contract_service_data et (cost=0.29..2.51 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=65,522)

  • Index Cond: (id = ce.service_id)
10. 131.044 131.044 ↑ 1.0 1 65,522

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_2 (cost=0.57..1.70 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=65,522)

  • Index Cond: ((user_id = 1466) AND (entity_type_id = 61) AND (entity_id = et.contract_id))
  • Heap Fetches: 0
11. 0.000 0.000 ↓ 0.0 0

Merge Join (cost=1.18..2,297.29 rows=11,528 width=4) (never executed)

  • Merge Cond: (et_1.contract_id = udr_3.entity_id)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_contract_service_data_2 on contract_service_data et_1 (cost=0.29..2,074.28 rows=38,371 width=8) (never executed)

13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_3 (cost=0.57..273.41 rows=11,222 width=4) (never executed)

  • Index Cond: ((user_id = 1466) AND (entity_type_id = 61))
  • Heap Fetches: 0
14. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..3.15 rows=18 width=0) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_contract_service_data on contract_service_data et_2 (cost=0.29..2.51 rows=1 width=4) (never executed)

  • Index Cond: (id = ce.service_id)
16. 0.000 0.000 ↓ 0.0 0

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_4 (cost=0.57..1.86 rows=18 width=4) (never executed)

  • Index Cond: ((user_id = 1466) AND (entity_type_id = 1) AND (entity_id = et_2.relation_id))
  • Heap Fetches: 0