explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SD1

Settings
# exclusive inclusive rows x rows loops node
1. 8.358 2,296.983 ↑ 1.0 1 1

Aggregate (cost=550,031.29..550,031.30 rows=1 width=8) (actual time=2,296.983..2,296.983 rows=1 loops=1)

2. 124.621 2,288.625 ↓ 6.4 39,732 1

Nested Loop (cost=1.00..550,015.87 rows=6,167 width=4) (actual time=0.163..2,288.625 rows=39,732 loops=1)

  • Join Filter: (c.time_zone_id = tz.id)
  • Rows Removed by Join Filter: 732264
3. 36.315 2,044.808 ↓ 6.4 39,732 1

Nested Loop (cost=1.00..546,309.51 rows=6,167 width=8) (actual time=0.152..2,044.808 rows=39,732 loops=1)

4. 43.021 1,968.761 ↓ 6.4 39,732 1

Nested Loop (cost=0.72..544,433.74 rows=6,167 width=8) (actual time=0.144..1,968.761 rows=39,732 loops=1)

5. 23.807 1,607.884 ↓ 3.2 39,732 1

Nested Loop (cost=0.43..207,443.52 rows=12,334 width=12) (actual time=0.103..1,607.884 rows=39,732 loops=1)

6. 1,462.571 1,462.571 ↓ 3.3 40,502 1

Seq Scan on service_data_consumption sdcd (cost=0.00..130,718.12 rows=12,412 width=4) (actual time=0.094..1,462.571 rows=40,502 loops=1)

  • Filter: ((NOT deleted) AND ((end_date)::date >= ((date_trunc('month'::text, now()) - '11 mons'::interval))::date) AND ((end_date)::date <= ((date_trunc('month'::text, now()) + '3 mons'::interval))::date))
  • Rows Removed by Filter: 2446040
7. 121.506 121.506 ↑ 1.0 1 40,502

Index Scan using pk_consumption_entity_id on consumption_entity ce (cost=0.43..6.18 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=40,502)

  • Index Cond: (id = sdcd.consumption_entity_id)
  • Filter: ((NOT deleted) AND (NOT saved_as_draft) AND (client_id = 1007))
  • Rows Removed by Filter: 0
8. 85.864 317.856 ↑ 1.0 1 39,732

Index Only Scan using pk_contract_service_data on contract_service_data csd (cost=0.29..27.32 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=39,732)

  • Index Cond: (id = ce.service_id)
  • Filter: COALESCE(NULLIF((alternatives: SubPlan 1 or hashed SubPlan 2), false), NULLIF((alternatives: SubPlan 3 or hashed SubPlan 4), false), NULLIF((SubPlan 5), false))
  • Heap Fetches: 39732
9.          

SubPlan (forIndex Only Scan)

10. 79.464 79.464 ↓ 0.0 0 39,732

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr (cost=0.57..8.59 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=39,732)

  • Index Cond: ((user_id = 1466) AND (entity_type_id = 64) AND (entity_id = csd.id))
  • Heap Fetches: 1600
11. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on user_data_read_access udr_1 (cost=20.82..3,196.75 rows=805 width=4) (never executed)

  • Recheck Cond: ((user_id = 1466) AND (entity_type_id = 64))
12. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on user_data_read_access_1 (cost=0.00..20.62 rows=805 width=0) (never executed)

  • Index Cond: ((user_id = 1466) AND (entity_type_id = 64))
13. 38.132 152.528 ↑ 1.0 1 38,132

Nested Loop (cost=0.86..16.90 rows=1 width=0) (actual time=0.004..0.004 rows=1 loops=38,132)

14. 38.132 38.132 ↑ 1.0 1 38,132

Index Scan using pk_contract_service_data on contract_service_data et (cost=0.29..8.31 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=38,132)

  • Index Cond: (id = csd.id)
15. 76.264 76.264 ↑ 1.0 1 38,132

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_2 (cost=0.57..8.59 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=38,132)

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

Merge Join (cost=4,650.81..9,111.90 rows=27,066 width=4) (never executed)

  • Merge Cond: (udr_3.entity_id = et_1.contract_id)
17. 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..104,462.53 rows=26,115 width=4) (never executed)

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

Sort (cost=4,630.23..4,726.15 rows=38,371 width=8) (never executed)

  • Sort Key: et_1.contract_id
19. 0.000 0.000 ↓ 0.0 0

Seq Scan on contract_service_data et_1 (cost=0.00..1,708.71 rows=38,371 width=8) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.86..13.10 rows=19 width=0) (never executed)

21. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = csd.id)
22. 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..80.76 rows=19 width=4) (never executed)

  • Index Cond: ((user_id = 1466) AND (entity_type_id = 1) AND (entity_id = et_2.relation_id))
  • Heap Fetches: 0
23. 39.732 39.732 ↑ 1.0 1 39,732

Index Scan using pk_contract on contract c (cost=0.29..0.30 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=39,732)

  • Index Cond: (id = ce.contract_id)
24. 119.184 119.196 ↑ 2.2 19 39,732

Materialize (cost=0.00..1.62 rows=41 width=4) (actual time=0.000..0.003 rows=19 loops=39,732)

25. 0.012 0.012 ↑ 1.3 31 1

Seq Scan on time_zone tz (cost=0.00..1.41 rows=41 width=4) (actual time=0.004..0.012 rows=31 loops=1)

Planning time : 2.017 ms
Execution time : 2,297.146 ms