explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pgkc

Settings
# exclusive inclusive rows x rows loops node
1. 11.583 2,543.926 ↑ 1.0 1 1

Aggregate (cost=236,483.04..236,483.05 rows=1 width=8) (actual time=2,543.926..2,543.926 rows=1 loops=1)

2. 36.972 2,532.343 ↓ 11.3 67,759 1

Sort (cost=236,393.43..236,408.36 rows=5,974 width=568) (actual time=2,521.817..2,532.343 rows=67,759 loops=1)

  • Sort Key: ce.client_entity_seq_id DESC
  • Sort Method: quicksort Memory: 7714kB
3. 27.576 2,495.371 ↓ 11.3 67,759 1

Group (cost=235,958.99..236,018.73 rows=5,974 width=568) (actual time=2,455.636..2,495.371 rows=67,759 loops=1)

  • Group Key: ce.id, sdcd.end_date, csd.id
4. 54.183 2,467.795 ↓ 11.3 67,759 1

Sort (cost=235,958.99..235,973.92 rows=5,974 width=20) (actual time=2,455.631..2,467.795 rows=67,759 loops=1)

  • Sort Key: ce.id, sdcd.end_date, csd.id
  • Sort Method: quicksort Memory: 7714kB
5. 30.576 2,413.612 ↓ 11.3 67,759 1

Nested Loop (cost=1.15..235,584.28 rows=5,974 width=20) (actual time=0.076..2,413.612 rows=67,759 loops=1)

6. 82.708 2,315.277 ↓ 11.3 67,759 1

Nested Loop (cost=1.00..234,644.76 rows=5,974 width=24) (actual time=0.071..2,315.277 rows=67,759 loops=1)

7. 71.417 2,164.810 ↓ 11.3 67,759 1

Nested Loop (cost=0.72..232,834.85 rows=5,974 width=24) (actual time=0.064..2,164.810 rows=67,759 loops=1)

8. 75.196 1,483.562 ↓ 5.7 67,759 1

Nested Loop (cost=0.43..146,773.13 rows=11,948 width=24) (actual time=0.025..1,483.562 rows=67,759 loops=1)

9. 1,202.311 1,202.311 ↓ 5.7 68,685 1

Seq Scan on service_data_consumption sdcd (cost=0.00..122,254.13 rows=12,034 width=12) (actual time=0.010..1,202.311 rows=68,685 loops=1)

  • Filter: ((NOT deleted) AND ((end_date)::date >= ((now() - '11 mons'::interval))::date) AND ((end_date)::date <= ((now() + '3 mons'::interval))::date))
  • Rows Removed by Filter: 2417857
10. 206.055 206.055 ↑ 1.0 1 68,685

Index Scan using pk_consumption_entity_id on consumption_entity ce (cost=0.43..2.04 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=68,685)

  • Index Cond: (id = sdcd.consumption_entity_id)
  • Filter: ((NOT deleted) AND (NOT saved_as_draft) AND (client_id = 1007))
  • Rows Removed by Filter: 0
11. 151.518 609.831 ↑ 1.0 1 67,759

Index Only Scan using pk_contract_service_data on contract_service_data csd (cost=0.29..7.20 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=67,759)

  • 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: 67759
12.          

SubPlan (forIndex Only Scan)

13. 135.518 135.518 ↓ 0.0 0 67,759

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=67,759)

  • Index Cond: ((user_id = 1466) AND (entity_type_id = 64) AND (entity_id = csd.id))
  • Heap Fetches: 0
14. 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
15. 129.118 322.795 ↑ 1.0 1 64,559

Nested Loop (cost=0.86..4.20 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=64,559)

16. 64.559 64.559 ↑ 1.0 1 64,559

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=64,559)

  • Index Cond: (id = csd.id)
17. 129.118 129.118 ↑ 1.0 1 64,559

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=64,559)

  • Index Cond: ((user_id = 1466) AND (entity_type_id = 61) AND (entity_id = et.contract_id))
  • Heap Fetches: 0
18. 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)
19. 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)

20. 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
21. 0.000 0.000 ↓ 0.0 0

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

22. 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 = csd.id)
23. 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
24. 67.759 67.759 ↑ 1.0 1 67,759

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=67,759)

  • Index Cond: (id = ce.contract_id)
25. 67.759 67.759 ↑ 1.0 1 67,759

Index Only Scan using pk_time_zone on time_zone tz (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=67,759)

  • Index Cond: (id = c.time_zone_id)
  • Heap Fetches: 0