explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ek7H

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 10,234.861 ↑ 1.0 1 1

Aggregate (cost=25,322.50..25,322.51 rows=1 width=8) (actual time=10,234.860..10,234.861 rows=1 loops=1)

2.          

Initplan (forAggregate)

3. 3.785 41.710 ↑ 1.0 1 1

Result (cost=3,079.89..3,079.90 rows=1 width=32) (actual time=41.710..41.710 rows=1 loops=1)

4.          

Initplan (forResult)

5. 10.605 37.925 ↑ 1.4 8,932 1

HashAggregate (cost=2,959.26..3,079.89 rows=12,063 width=4) (actual time=33.482..37.925 rows=8,932 loops=1)

  • Group Key: udr_2.entity_id
6. 7.430 27.320 ↑ 1.4 8,932 1

Append (cost=0.43..2,929.10 rows=12,063 width=4) (actual time=0.627..27.320 rows=8,932 loops=1)

7. 0.021 0.021 ↓ 0.0 0 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_2 (cost=0.43..2.05 rows=26 width=4) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: ((user_id = 27902) AND (entity_type_id = 64))
  • Heap Fetches: 0
8. 0.020 0.550 ↓ 0.0 0 1

Index Scan using idx_contract_service_data_2 on contract_service_data et (cost=12.16..1,198.81 rows=2,642 width=4) (actual time=0.550..0.550 rows=0 loops=1)

  • Index Cond: (contract_id = ANY (($3)::integer[]))
9.          

Initplan (forIndex Scan)

10. 0.008 0.530 ↑ 1.0 1 1

Result (cost=11.84..11.85 rows=1 width=32) (actual time=0.530..0.530 rows=1 loops=1)

11.          

Initplan (forResult)

12. 0.234 0.522 ↑ 1.0 1 1

Aggregate (cost=11.83..11.84 rows=1 width=8) (actual time=0.521..0.522 rows=1 loops=1)

13. 0.288 0.288 ↓ 1.5 542 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_1 (cost=0.43..10.93 rows=360 width=4) (actual time=0.024..0.288 rows=542 loops=1)

  • Index Cond: ((user_id = 27902) AND (entity_type_id = 61))
  • Heap Fetches: 0
14. 19.282 19.319 ↑ 1.1 8,932 1

Seq Scan on contract_service_data et_1 (cost=10.38..1,607.62 rows=9,395 width=4) (actual time=0.053..19.319 rows=8,932 loops=1)

  • Filter: (relation_id = ANY ($1))
  • Rows Removed by Filter: 16346
15.          

Initplan (forSeq Scan)

16. 0.011 0.037 ↑ 1.0 1 1

Result (cost=10.37..10.38 rows=1 width=32) (actual time=0.036..0.037 rows=1 loops=1)

17.          

Initplan (forResult)

18. 0.026 0.026 ↑ 47.4 7 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr (cost=0.43..10.37 rows=332 width=4) (actual time=0.021..0.026 rows=7 loops=1)

  • Index Cond: ((user_id = 27902) AND (entity_type_id = 1))
  • Heap Fetches: 0
19. 0.004 0.101 ↑ 1.0 1 1

Result (cost=4.53..4.55 rows=1 width=4) (actual time=0.100..0.101 rows=1 loops=1)

20.          

Initplan (forResult)

21. 0.076 0.097 ↑ 1.0 1 1

Nested Loop (cost=0.14..4.53 rows=1 width=8) (actual time=0.094..0.097 rows=1 loops=1)

  • Join Filter: (cl.primary_time_zone_id = ctz.id)
  • Rows Removed by Join Filter: 31
22. 0.006 0.006 ↑ 1.0 1 1

Index Scan using pk_client on client cl (cost=0.14..2.35 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=1)

  • Index Cond: (id = 1012)
23. 0.015 0.015 ↑ 1.3 32 1

Seq Scan on time_zone ctz (cost=0.00..1.41 rows=41 width=20) (actual time=0.003..0.015 rows=32 loops=1)

24. 0.005 0.074 ↑ 1.0 1 1

Result (cost=4.53..4.55 rows=1 width=4) (actual time=0.073..0.074 rows=1 loops=1)

25.          

Initplan (forResult)

26. 0.051 0.069 ↑ 1.0 1 1

Nested Loop (cost=0.14..4.53 rows=1 width=8) (actual time=0.067..0.069 rows=1 loops=1)

  • Join Filter: (cl_1.primary_time_zone_id = ctz_1.id)
  • Rows Removed by Join Filter: 31
27. 0.004 0.004 ↑ 1.0 1 1

Index Scan using pk_client on client cl_1 (cost=0.14..2.35 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)

  • Index Cond: (id = 1012)
28. 0.014 0.014 ↑ 1.3 32 1

Seq Scan on time_zone ctz_1 (cost=0.00..1.41 rows=41 width=20) (actual time=0.002..0.014 rows=32 loops=1)

29. 725.376 10,217.823 ↓ 1,668.2 41,706 1

Nested Loop (cost=1.42..22,233.45 rows=25 width=4) (actual time=9,871.179..10,217.823 rows=41,706 loops=1)

  • Join Filter: (c.time_zone_id = tz.id)
  • Rows Removed by Join Filter: 1668240
30. 0.045 0.045 ↑ 1.0 41 1

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

31. 691.607 9,492.402 ↓ 1,668.2 41,706 41

Materialize (cost=1.42..22,216.73 rows=25 width=8) (actual time=1.024..231.522 rows=41,706 loops=41)

32. 69.895 8,800.795 ↓ 1,668.2 41,706 1

Nested Loop (cost=1.42..22,216.60 rows=25 width=8) (actual time=41.971..8,800.795 rows=41,706 loops=1)

33. 62.669 8,647.488 ↓ 1,668.2 41,706 1

Nested Loop (cost=1.14..22,176.13 rows=25 width=8) (actual time=41.961..8,647.488 rows=41,706 loops=1)

34. 275.270 8,501.407 ↓ 1,668.2 41,706 1

Nested Loop (cost=0.85..22,160.66 rows=25 width=12) (actual time=41.951..8,501.407 rows=41,706 loops=1)

35. 6,753.317 6,753.317 ↓ 804.8 245,470 1

Index Scan using consumption_entity_idx on consumption_entity ce (cost=0.42..21,372.20 rows=305 width=12) (actual time=41.751..6,753.317 rows=245,470 loops=1)

  • Index Cond: ((deleted = false) AND (client_id = 1012))
  • Filter: ((NOT deleted) AND (NOT saved_as_draft) AND (service_id = ANY ($5)))
36. 1,160.113 1,472.820 ↓ 0.0 0 245,470

Index Scan using service_data_consumption_idx1 on service_data_consumption sdcd (cost=0.42..2.58 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=245,470)

  • Index Cond: (consumption_entity_id = ce.id)
  • Filter: ((NOT deleted) AND ((SubPlan 7) >= $8) AND ((SubPlan 10) <= $11))
  • Rows Removed by Filter: 1
37.          

SubPlan (forIndex Scan)

38. 245.470 245.470 ↑ 1.0 1 245,470

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=245,470)

39. 67.237 67.237 ↑ 1.0 1 67,237

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=67,237)

40. 83.412 83.412 ↑ 1.0 1 41,706

Index Only Scan using pk_contract_service_data on contract_service_data csd (cost=0.29..0.62 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=41,706)

  • Index Cond: (id = ce.service_id)
  • Heap Fetches: 1930
41. 83.412 83.412 ↑ 1.0 1 41,706

Index Scan using pk_contract on contract c (cost=0.29..1.62 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=41,706)

  • Index Cond: (id = ce.contract_id)