explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hye

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 9,019.414 ↑ 1.0 1 1

Aggregate (cost=25,279.60..25,279.61 rows=1 width=8) (actual time=9,019.413..9,019.414 rows=1 loops=1)

2.          

Initplan (forAggregate)

3. 3.436 33.147 ↑ 1.0 1 1

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

4.          

Initplan (forResult)

5. 8.648 29.711 ↑ 1.4 8,932 1

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

  • Group Key: udr_2.entity_id
6. 6.489 21.063 ↑ 1.4 8,932 1

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

7. 0.009 0.009 ↓ 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.009..0.009 rows=0 loops=1)

  • Index Cond: ((user_id = 27902) AND (entity_type_id = 64))
  • Heap Fetches: 0
8. 0.009 0.488 ↓ 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.488..0.488 rows=0 loops=1)

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

Initplan (forIndex Scan)

10. 0.003 0.479 ↑ 1.0 1 1

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

11.          

Initplan (forResult)

12. 0.218 0.476 ↑ 1.0 1 1

Aggregate (cost=11.83..11.84 rows=1 width=8) (actual time=0.475..0.476 rows=1 loops=1)

13. 0.258 0.258 ↓ 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.017..0.258 rows=542 loops=1)

  • Index Cond: ((user_id = 27902) AND (entity_type_id = 61))
  • Heap Fetches: 0
14. 14.061 14.077 ↑ 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.028..14.077 rows=8,932 loops=1)

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

Initplan (forSeq Scan)

16. 0.005 0.016 ↑ 1.0 1 1

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

17.          

Initplan (forResult)

18. 0.011 0.011 ↑ 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.007..0.011 rows=7 loops=1)

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

Result (cost=4.53..4.55 rows=1 width=4) (actual time=0.070..0.071 rows=1 loops=1)

20.          

Initplan (forResult)

21. 0.049 0.067 ↑ 1.0 1 1

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

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

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

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

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

24. 0.003 0.050 ↑ 1.0 1 1

Result (cost=4.53..4.55 rows=1 width=4) (actual time=0.049..0.050 rows=1 loops=1)

25.          

Initplan (forResult)

26. 0.029 0.047 ↑ 1.0 1 1

Nested Loop (cost=0.14..4.53 rows=1 width=8) (actual time=0.046..0.047 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. 61.439 8,995.191 ↓ 1,668.2 41,706 1

Nested Loop (cost=1.42..22,190.54 rows=25 width=4) (actual time=33.331..8,995.191 rows=41,706 loops=1)

30. 73.608 8,850.340 ↓ 1,668.2 41,706 1

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

31. 294.900 8,693.320 ↓ 1,668.2 41,706 1

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

32. 6,925.600 6,925.600 ↓ 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=33.176..6,925.600 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)))
33. 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
34.          

SubPlan (forIndex Scan)

35. 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)

36. 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)

37. 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
38. 83.412 83.412 ↑ 1.0 1 41,706

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

  • Index Cond: (id = ce.contract_id)
  • Heap Fetches: 0