explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u8rQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 24.942 ↑ 1.0 1 1

Result (cost=29,932.19..29,932.20 rows=1 width=41) (actual time=24.941..24.942 rows=1 loops=1)

2.          

CTE __local_0__

3. 0.017 20.364 ↑ 1.0 20 1

Limit (cost=25,226.89..29,385.12 rows=20 width=1,112) (actual time=17.549..20.364 rows=20 loops=1)

4. 0.321 20.347 ↑ 74.5 20 1

Result (cost=25,226.89..334,806.90 rows=1,489 width=1,112) (actual time=17.548..20.347 rows=20 loops=1)

5. 1.455 17.066 ↑ 74.5 20 1

Sort (cost=25,226.89..25,230.62 rows=1,489 width=1,108) (actual time=17.057..17.066 rows=20 loops=1)

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

Hash Join (cost=205.67..25,187.27 rows=1,489 width=1,108) (actual time=2.280..15.611 rows=3,119 loops=1)

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

Seq Scan on "user" __local_18__ (cost=0.00..256.05 rows=3,105 width=16) (actual time=0.012..2.131 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.773 2.207 ↓ 1.0 3,123 1

Hash (cost=166.81..166.81 rows=3,109 width=76) (actual time=2.206..2.207 rows=3,123 loops=1)

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

Seq Scan on employee __local_1__ (cost=0.00..166.81 rows=3,109 width=76) (actual time=0.010..1.434 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.020 2.120 ↑ 1.0 1 20

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

15.          

CTE __local_2__

16. 0.100 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.216 1.900 ↑ 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.033..0.095 rows=2 loops=20)

  • Recheck Cond: (employee_id = __local_1__.id)
  • Heap Blocks: exact=34
18. 0.100 0.100 ↑ 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.005..0.005 rows=2 loops=20)

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

SubPlan (forBitmap Heap Scan)

20. 0.684 1.584 ↑ 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.044 rows=1 loops=36)

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

SubPlan (forSeq Scan)

22. 0.072 0.216 ↑ 1.0 1 36

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

23.          

CTE __local_5__

24. 0.072 0.108 ↓ 0.0 0 36

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

  • Sort Key: __local_6__.group_id
  • Sort Method: quicksort Memory: 25kB
25. 0.000 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.036 0.036 ↓ 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.001..0.001 rows=0 loops=36)

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

CTE __local_7__

28. 0.036 0.144 ↑ 1.0 1 36

Aggregate (cost=0.18..0.19 rows=1 width=32) (actual time=0.004..0.004 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.144 0.144 ↑ 1.0 1 36

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

32. 0.036 0.684 ↑ 1.0 1 36

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

33.          

CTE __local_8__

34. 0.036 0.612 ↓ 0.0 0 36

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

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

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

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

CTE __local_10__

37. 0.036 0.648 ↑ 1.0 1 36

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

38. 0.612 0.612 ↓ 0.0 0 36

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

39.          

Initplan (forResult)

40. 0.648 0.648 ↑ 1.0 1 36

CTE Scan on __local_10__ (cost=0.00..0.02 rows=1 width=32) (actual time=0.018..0.018 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.320 0.780 ↑ 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.039..0.039 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.420 ↑ 1.0 1 20

Result (cost=16.66..16.68 rows=1 width=32) (actual time=0.021..0.021 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.014..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.040 0.360 ↑ 1.0 1 20

Aggregate (cost=0.03..0.04 rows=1 width=32) (actual time=0.018..0.018 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.015..0.016 rows=1 loops=20)

57.          

Initplan (forResult)

58. 0.380 0.380 ↑ 1.0 1 20

CTE Scan on __local_17__ (cost=0.00..0.02 rows=1 width=32) (actual time=0.019..0.019 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.020 0.020 ↑ 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.001..0.001 rows=1 loops=20)

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

CTE __local_19__

62. 0.066 20.476 ↑ 1.0 1 1

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

63. 20.410 20.410 ↑ 1.0 20 1

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

64.          

Initplan (forResult)

65. 20.488 20.488 ↑ 1.0 1 1

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

66. 0.003 0.080 ↑ 744.0 1 1

Nested Loop (cost=1.11..53,429.58 rows=744 width=0) (actual time=0.080..0.080 rows=1 loops=1)

67. 0.055 0.074 ↑ 1,554.0 1 1

Index Scan using employee_active_index on employee __local_1___1 (cost=0.83..52,151.50 rows=1,554 width=16) (actual time=0.074..0.074 rows=1 loops=1)

  • Index Cond: (active = true)
  • Filter: (active AND (is_archived IS FALSE) AND (NOT (hashed SubPlan 30)))
68.          

SubPlan (forIndex Scan)

69. 0.004 0.004 ↑ 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.003..0.004 rows=1 loops=1)

  • Index Cond: (__local_1___1.user_id = id)
70. 0.003 0.003 ↑ 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.003..0.003 rows=1 loops=1)

  • Index Cond: (__local_1___1.user_id = id)
71. 0.012 0.012 ↑ 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.012 rows=20 loops=1)

72. 0.003 0.003 ↑ 1.0 1 1

Index Scan using user_pkey on "user" __local_18___1 (cost=0.28..0.82 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (id = __local_1___1.user_id)
  • Filter: (active AND ((employment_end IS NULL) OR (employment_end > '2019-05-10'::date)))
73. 0.193 4.358 ↑ 1.0 1 1

Aggregate (cost=473.60..473.61 rows=1 width=8) (actual time=4.358..4.358 rows=1 loops=1)

74. 0.678 4.165 ↓ 2.1 3,119 1

Hash Join (cost=205.67..469.87 rows=1,489 width=0) (actual time=1.631..4.165 rows=3,119 loops=1)

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

Seq Scan on "user" __local_18___2 (cost=0.00..256.05 rows=3,105 width=16) (actual time=0.008..1.880 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.390 1.607 ↓ 1.0 3,123 1

Hash (cost=166.81..166.81 rows=3,109 width=16) (actual time=1.606..1.607 rows=3,123 loops=1)

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

Seq Scan on employee __local_1___2 (cost=0.00..166.81 rows=3,109 width=16) (actual time=0.010..1.217 rows=3,123 loops=1)

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