explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pLi7

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 24.026 ↑ 1.0 1 1

Result (cost=30,069.69..30,069.70 rows=1 width=41) (actual time=24.025..24.026 rows=1 loops=1)

2.          

CTE __local_0__

3. 0.016 19.625 ↑ 1.0 20 1

Limit (cost=25,361.16..29,519.39 rows=20 width=1,112) (actual time=16.764..19.625 rows=20 loops=1)

4. 0.301 19.609 ↑ 74.8 20 1

Result (cost=25,361.16..336,604.46 rows=1,497 width=1,112) (actual time=16.763..19.609 rows=20 loops=1)

5. 1.414 16.368 ↑ 74.8 20 1

Sort (cost=25,361.16..25,364.91 rows=1,497 width=1,108) (actual time=16.364..16.368 rows=20 loops=1)

  • Sort Key: ((SubPlan 23)), ((SubPlan 24)), __local_1__.id
  • Sort Method: top-N heapsort Memory: 29kB
6. 4.732 14.954 ↓ 2.1 3,119 1

Hash Join (cost=206.27..25,321.33 rows=1,497 width=1,108) (actual time=2.080..14.954 rows=3,119 loops=1)

  • Hash Cond: (__local_18__.id = __local_1__.user_id)
7. 1.948 1.948 ↑ 1.0 3,122 1

Seq Scan on "user" __local_18__ (cost=0.00..256.65 rows=3,128 width=16) (actual time=0.010..1.948 rows=3,122 loops=1)

  • Filter: (active AND ((employment_end IS NULL) OR (employment_end > '2019-05-10'::date)))
  • Rows Removed by Filter: 3362
8. 0.745 2.036 ↑ 1.0 3,123 1

Hash (cost=167.18..167.18 rows=3,127 width=76) (actual time=2.035..2.036 rows=3,123 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 323kB
9. 1.291 1.291 ↑ 1.0 3,123 1

Seq Scan on employee __local_1__ (cost=0.00..167.18 rows=3,127 width=76) (actual time=0.008..1.291 rows=3,123 loops=1)

  • Filter: (active AND (is_archived IS FALSE))
  • Rows Removed by Filter: 3358
10.          

SubPlan (forHash Join)

11. 3.119 3.119 ↑ 1.0 1 3,119

Index Scan using user_pkey on "user" user_2 (cost=0.28..8.30 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=3,119)

  • Index Cond: (__local_1__.user_id = id)
12. 3.119 3.119 ↑ 1.0 1 3,119

Index Scan using user_pkey on "user" user_3 (cost=0.28..8.30 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=3,119)

  • Index Cond: (__local_1__.user_id = id)
13.          

SubPlan (forResult)

14. 0.040 2.140 ↑ 1.0 1 20

Result (cost=149.67..149.68 rows=1 width=32) (actual time=0.107..0.107 rows=1 loops=20)

15.          

CTE __local_2__

16. 0.080 2.000 ↑ 2.5 2 20

Sort (cost=149.50..149.51 rows=5 width=48) (actual time=0.100..0.100 rows=2 loops=20)

  • Sort Key: __local_3__.id
  • Sort Method: quicksort Memory: 25kB
17. 0.220 1.920 ↑ 2.5 2 20

Bitmap Heap Scan on form_field_entry __local_3__ (cost=4.32..149.44 rows=5 width=48) (actual time=0.031..0.096 rows=2 loops=20)

  • Recheck Cond: (employee_id = __local_1__.id)
  • Heap Blocks: exact=34
18. 0.080 0.080 ↑ 2.5 2 20

Bitmap Index Scan on form_field_entry_employee_id_index (cost=0.00..4.32 rows=5 width=0) (actual time=0.004..0.004 rows=2 loops=20)

  • Index Cond: (employee_id = __local_1__.id)
19.          

SubPlan (forBitmap Heap Scan)

20. 0.720 1.620 ↑ 1.0 1 36

Seq Scan on form_field __local_4__ (cost=0.00..25.75 rows=1 width=32) (actual time=0.036..0.045 rows=1 loops=36)

  • Filter: (__local_3__.form_field_id = id)
  • Rows Removed by Filter: 187
21.          

SubPlan (forSeq Scan)

22. 0.072 0.180 ↑ 1.0 1 36

Result (cost=14.71..14.72 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=36)

23.          

CTE __local_5__

24. 0.036 0.072 ↓ 0.0 0 36

Sort (cost=14.48..14.50 rows=7 width=64) (actual time=0.002..0.002 rows=0 loops=36)

  • Sort Key: __local_6__.group_id
  • Sort Method: quicksort Memory: 25kB
25. 0.036 0.036 ↓ 0.0 0 36

Bitmap Heap Scan on form_field_group_read __local_6__ (cost=4.21..14.39 rows=7 width=64) (actual time=0.001..0.001 rows=0 loops=36)

  • Recheck Cond: (form_field_id = __local_4__.id)
26. 0.000 0.000 ↓ 0.0 0 36

Bitmap Index Scan on form_field_group_read_form_field_id_index (cost=0.00..4.21 rows=7 width=0) (actual time=0.000..0.000 rows=0 loops=36)

  • Index Cond: (form_field_id = __local_4__.id)
27.          

CTE __local_7__

28. 0.000 0.108 ↑ 1.0 1 36

Aggregate (cost=0.18..0.19 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=36)

29. 0.108 0.108 ↓ 0.0 0 36

CTE Scan on __local_5__ (cost=0.00..0.14 rows=7 width=24) (actual time=0.003..0.003 rows=0 loops=36)

30.          

Initplan (forResult)

31. 0.108 0.108 ↑ 1.0 1 36

CTE Scan on __local_7__ (cost=0.00..0.02 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=36)

32. 0.036 0.720 ↑ 1.0 1 36

Result (cost=5.78..5.79 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=36)

33.          

CTE __local_8__

34. 0.036 0.648 ↓ 0.0 0 36

Sort (cost=5.63..5.64 rows=4 width=48) (actual time=0.018..0.018 rows=0 loops=36)

  • Sort Key: __local_9__.id
  • Sort Method: quicksort Memory: 25kB
35. 0.612 0.612 ↓ 0.0 0 36

Seq Scan on form_field_option __local_9__ (cost=0.00..5.59 rows=4 width=48) (actual time=0.017..0.017 rows=0 loops=36)

  • Filter: (form_field_id = __local_4__.id)
  • Rows Removed by Filter: 217
36.          

CTE __local_10__

37. 0.036 0.684 ↑ 1.0 1 36

Aggregate (cost=0.10..0.11 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=36)

38. 0.648 0.648 ↓ 0.0 0 36

CTE Scan on __local_8__ (cost=0.00..0.08 rows=4 width=24) (actual time=0.018..0.018 rows=0 loops=36)

39.          

Initplan (forResult)

40. 0.684 0.684 ↑ 1.0 1 36

CTE Scan on __local_10__ (cost=0.00..0.02 rows=1 width=32) (actual time=0.019..0.019 rows=1 loops=36)

41.          

CTE __local_11__

42. 0.040 2.080 ↑ 1.0 1 20

Aggregate (cost=0.13..0.14 rows=1 width=32) (actual time=0.104..0.104 rows=1 loops=20)

43. 2.040 2.040 ↑ 2.5 2 20

CTE Scan on __local_2__ (cost=0.00..0.10 rows=5 width=24) (actual time=0.101..0.102 rows=2 loops=20)

44.          

Initplan (forResult)

45. 2.100 2.100 ↑ 1.0 1 20

CTE Scan on __local_11__ (cost=0.00..0.02 rows=1 width=32) (actual time=0.105..0.105 rows=1 loops=20)

46. 0.280 0.720 ↑ 1.0 1 20

Index Scan using user_pkey on "user" __local_13__ (cost=0.28..25.00 rows=1 width=32) (actual time=0.036..0.036 rows=1 loops=20)

  • Index Cond: (__local_1__.user_id = id)
47.          

SubPlan (forIndex Scan)

48. 0.040 0.040 ↑ 1.0 1 20

Function Scan on __local_12__ (cost=0.01..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=20)

49. 0.040 0.400 ↑ 1.0 1 20

Result (cost=16.66..16.68 rows=1 width=32) (actual time=0.019..0.020 rows=1 loops=20)

50.          

CTE __local_14__

51. 0.185 0.300 ↑ 1.0 1 20

Index Only Scan using user_department_pkey on user_department __local_16__ (cost=0.28..16.61 rows=1 width=64) (actual time=0.013..0.015 rows=1 loops=20)

  • Index Cond: (user_id = __local_13__.id)
  • Heap Fetches: 23
52.          

SubPlan (forIndex Only Scan)

53. 0.115 0.115 ↑ 1.0 1 23

Index Scan using department_pkey on department __local_15__ (cost=0.28..8.30 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=23)

  • Index Cond: (__local_16__.department_id = id)
54.          

CTE __local_17__

55. 0.020 0.340 ↑ 1.0 1 20

Aggregate (cost=0.03..0.04 rows=1 width=32) (actual time=0.017..0.017 rows=1 loops=20)

56. 0.320 0.320 ↑ 1.0 1 20

CTE Scan on __local_14__ (cost=0.00..0.02 rows=1 width=24) (actual time=0.014..0.016 rows=1 loops=20)

57.          

Initplan (forResult)

58. 0.360 0.360 ↑ 1.0 1 20

CTE Scan on __local_17__ (cost=0.00..0.02 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=20)

59. 0.040 0.040 ↑ 1.0 1 20

Index Scan using user_pkey on "user" (cost=0.28..8.30 rows=1 width=7) (actual time=0.001..0.002 rows=1 loops=20)

  • Index Cond: (__local_1__.user_id = id)
60. 0.040 0.040 ↑ 1.0 1 20

Index Scan using user_pkey on "user" user_1 (cost=0.28..8.30 rows=1 width=7) (actual time=0.002..0.002 rows=1 loops=20)

  • Index Cond: (__local_1__.user_id = id)
61.          

CTE __local_19__

62. 0.066 19.741 ↑ 1.0 1 1

Aggregate (cost=0.50..0.51 rows=1 width=32) (actual time=19.741..19.741 rows=1 loops=1)

63. 19.675 19.675 ↑ 1.0 20 1

CTE Scan on __local_0__ (cost=0.00..0.40 rows=20 width=24) (actual time=16.770..19.675 rows=20 loops=1)

64.          

Initplan (forResult)

65. 19.752 19.752 ↑ 1.0 1 1

CTE Scan on __local_19__ (cost=0.00..0.02 rows=1 width=32) (actual time=19.752..19.752 rows=1 loops=1)

66. 0.006 0.086 ↑ 748.0 1 1

Nested Loop (cost=0.83..55,395.37 rows=748 width=0) (actual time=0.086..0.086 rows=1 loops=1)

67. 0.008 0.008 ↑ 3,128.0 1 1

Seq Scan on "user" __local_18___1 (cost=0.00..256.65 rows=3,128 width=16) (actual time=0.008..0.008 rows=1 loops=1)

  • Filter: (active AND ((employment_end IS NULL) OR (employment_end > '2019-05-10'::date)))
68. 0.052 0.072 ↑ 1.0 1 1

Index Scan using employee_user_id_unique on employee __local_1___1 (cost=0.83..17.63 rows=1 width=16) (actual time=0.072..0.072 rows=1 loops=1)

  • Index Cond: (user_id = __local_18___1.id)
  • Filter: (active AND (is_archived IS FALSE) AND (NOT (hashed SubPlan 30)))
69.          

SubPlan (forIndex Scan)

70. 0.005 0.005 ↑ 1.0 1 1

Index Scan using user_pkey on "user" user_4 (cost=0.28..8.30 rows=1 width=7) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (__local_1___1.user_id = id)
71. 0.002 0.002 ↑ 1.0 1 1

Index Scan using user_pkey on "user" user_5 (cost=0.28..8.30 rows=1 width=7) (actual time=0.002..0.002 rows=1 loops=1)

  • Index Cond: (__local_1___1.user_id = id)
72. 0.013 0.013 ↑ 1.0 20 1

CTE Scan on __local_0__ __local_0___1 (cost=0.00..0.50 rows=20 width=32) (actual time=0.002..0.013 rows=20 loops=1)

73. 0.187 4.174 ↑ 1.0 1 1

Aggregate (cost=474.87..474.88 rows=1 width=8) (actual time=4.174..4.174 rows=1 loops=1)

74. 0.705 3.987 ↓ 2.1 3,119 1

Hash Join (cost=206.27..471.13 rows=1,497 width=0) (actual time=1.613..3.987 rows=3,119 loops=1)

  • Hash Cond: (__local_18___2.id = __local_1___2.user_id)
75. 1.685 1.685 ↑ 1.0 3,122 1

Seq Scan on "user" __local_18___2 (cost=0.00..256.65 rows=3,128 width=16) (actual time=0.004..1.685 rows=3,122 loops=1)

  • Filter: (active AND ((employment_end IS NULL) OR (employment_end > '2019-05-10'::date)))
  • Rows Removed by Filter: 3362
76. 0.374 1.597 ↑ 1.0 3,123 1

Hash (cost=167.18..167.18 rows=3,127 width=16) (actual time=1.597..1.597 rows=3,123 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 179kB
77. 1.223 1.223 ↑ 1.0 3,123 1

Seq Scan on employee __local_1___2 (cost=0.00..167.18 rows=3,127 width=16) (actual time=0.008..1.223 rows=3,123 loops=1)

  • Filter: (active AND (is_archived IS FALSE))
  • Rows Removed by Filter: 3358
Planning time : 1.431 ms
Execution time : 24.404 ms