explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ey23

Settings
# exclusive inclusive rows x rows loops node
1. 25.901 950.336 ↓ 2,625.0 2,625 1

Sort (cost=84,383.65..84,383.66 rows=1 width=412) (actual time=946.892..950.336 rows=2,625 loops=1)

  • Sort Key: plate.plate_number, copy.name
  • Sort Method: external merge Disk: 13320kB
2.          

CTE users-library

3. 0.268 0.383 ↑ 1.0 189 1

Hash Join (cost=7.25..20.69 rows=189 width=44) (actual time=0.113..0.383 rows=189 loops=1)

  • Hash Cond: (reports_userprofile.user_id = auth_user.id)
4. 0.043 0.043 ↑ 1.0 189 1

Seq Scan on reports_userprofile (cost=0.00..9.89 rows=189 width=4) (actual time=0.017..0.043 rows=189 loops=1)

5. 0.042 0.072 ↑ 1.0 189 1

Hash (cost=4.89..4.89 rows=189 width=48) (actual time=0.072..0.072 rows=189 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
6. 0.030 0.030 ↑ 1.0 189 1

Seq Scan on auth_user (cost=0.00..4.89 rows=189 width=48) (actual time=0.006..0.030 rows=189 loops=1)

7.          

CTE _library_comment_apilogs

8. 0.011 0.519 ↓ 3.0 3 1

Sort (cost=15.31..15.31 rows=1 width=106) (actual time=0.518..0.519 rows=3 loops=1)

  • Sort Key: reports_apilog.date_time DESC
  • Sort Method: quicksort Memory: 30kB
9. 0.002 0.508 ↓ 3.0 3 1

Nested Loop Anti Join (cost=8.89..15.30 rows=1 width=106) (actual time=0.477..0.508 rows=3 loops=1)

10. 0.021 0.491 ↓ 5.0 5 1

Hash Join (cost=8.46..12.96 rows=1 width=110) (actual time=0.471..0.491 rows=5 loops=1)

  • Hash Cond: (("users-library".username)::text = (reports_apilog.username)::text)
11. 0.426 0.426 ↑ 1.0 189 1

CTE Scan on "users-library" (cost=0.00..3.78 rows=189 width=350) (actual time=0.114..0.426 rows=189 loops=1)

12. 0.008 0.044 ↓ 5.0 5 1

Hash (cost=8.45..8.45 rows=1 width=78) (actual time=0.044..0.044 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
13. 0.036 0.036 ↓ 5.0 5 1

Index Scan Backward using reports_apilog_ref_resource_name_key_date_time_caafef5a_uniq on reports_apilog (cost=0.43..8.45 rows=1 width=78) (actual time=0.034..0.036 rows=5 loops=1)

  • Index Cond: (((ref_resource_name)::text = 'library'::text) AND ((key)::text = 'ChemDiv6'::text))
  • Filter: (comment IS NOT NULL)
  • Rows Removed by Filter: 2
14. 0.015 0.015 ↓ 0.0 0 5

Index Only Scan using reports_logdiff_log_id_4218eabe on reports_logdiff (cost=0.43..4.53 rows=6 width=4) (actual time=0.003..0.003 rows=0 loops=5)

  • Index Cond: (log_id = reports_apilog.id)
  • Heap Fetches: 0
15.          

CTE users-librarycopyplate

16. 0.241 0.304 ↑ 1.0 189 1

Hash Join (cost=7.25..20.69 rows=189 width=44) (actual time=0.059..0.304 rows=189 loops=1)

  • Hash Cond: (reports_userprofile_1.user_id = auth_user_1.id)
17. 0.011 0.011 ↑ 1.0 189 1

Seq Scan on reports_userprofile reports_userprofile_1 (cost=0.00..9.89 rows=189 width=4) (actual time=0.000..0.011 rows=189 loops=1)

18. 0.029 0.052 ↑ 1.0 189 1

Hash (cost=4.89..4.89 rows=189 width=48) (actual time=0.052..0.052 rows=189 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
19. 0.023 0.023 ↑ 1.0 189 1

Seq Scan on auth_user auth_user_1 (cost=0.00..4.89 rows=189 width=48) (actual time=0.002..0.023 rows=189 loops=1)

20.          

CTE _comment_apilogs

21. 0.186 113.169 ↑ 2.2 650 1

Sort (cost=44,133.08..44,136.60 rows=1,408 width=106) (actual time=113.109..113.169 rows=650 loops=1)

  • Sort Key: reports_apilog_1.date_time DESC
  • Sort Method: quicksort Memory: 158kB
22. 0.182 112.983 ↑ 2.2 650 1

Hash Join (cost=5,445.75..44,059.45 rows=1,408 width=106) (actual time=99.754..112.983 rows=650 loops=1)

  • Hash Cond: ((reports_apilog_1.username)::text = ("users-librarycopyplate".username)::text)
23. 0.262 112.428 ↑ 2.1 678 1

Nested Loop Anti Join (cost=5,439.60..44,033.95 rows=1,408 width=74) (actual time=99.373..112.428 rows=678 loops=1)

24. 83.270 111.488 ↑ 3.8 678 1

Bitmap Heap Scan on reports_apilog reports_apilog_1 (cost=5,439.17..40,796.38 rows=2,574 width=78) (actual time=99.360..111.488 rows=678 loops=1)

  • Recheck Cond: ((ref_resource_name)::text = 'librarycopyplate'::text)
  • Filter: ((comment IS NOT NULL) AND ((key)::text ~~* 'ChemDiv6/%%/%%'::text))
  • Rows Removed by Filter: 249013
  • Heap Blocks: exact=15309
25. 28.218 28.218 ↓ 1.0 249,691 1

Bitmap Index Scan on reports_apilog_ref_resource_name_8d7aaa12_like (cost=0.00..5,438.53 rows=249,347 width=0) (actual time=28.218..28.218 rows=249,691 loops=1)

  • Index Cond: ((ref_resource_name)::text = 'librarycopyplate'::text)
26. 0.678 0.678 ↓ 0.0 0 678

Index Only Scan using reports_logdiff_log_id_4218eabe on reports_logdiff reports_logdiff_1 (cost=0.43..4.05 rows=6 width=4) (actual time=0.001..0.001 rows=0 loops=678)

  • Index Cond: (log_id = reports_apilog_1.id)
  • Heap Fetches: 0
27. 0.026 0.373 ↑ 1.0 189 1

Hash (cost=3.78..3.78 rows=189 width=350) (actual time=0.373..0.373 rows=189 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
28. 0.347 0.347 ↑ 1.0 189 1

CTE Scan on "users-librarycopyplate" (cost=0.00..3.78 rows=189 width=350) (actual time=0.060..0.347 rows=189 loops=1)

29.          

CTE plate_cte

30. 3.199 3.833 ↓ 8.0 2,625 1

Nested Loop (cost=9.00..1,303.55 rows=328 width=29) (actual time=0.108..3.833 rows=2,625 loops=1)

31. 0.012 0.067 ↑ 1.0 21 1

Nested Loop (cost=4.59..43.66 rows=21 width=21) (actual time=0.043..0.067 rows=21 loops=1)

32. 0.013 0.013 ↑ 1.0 1 1

Index Scan using library_pkey on library library_1 (cost=0.15..8.17 rows=1 width=15) (actual time=0.011..0.013 rows=1 loops=1)

  • Index Cond: (library_id = 98)
33. 0.023 0.042 ↑ 1.0 21 1

Bitmap Heap Scan on copy copy_1 (cost=4.44..35.28 rows=21 width=10) (actual time=0.026..0.042 rows=21 loops=1)

  • Recheck Cond: (library_id = 98)
  • Heap Blocks: exact=11
34. 0.019 0.019 ↑ 1.0 21 1

Bitmap Index Scan on copy_library_id (cost=0.00..4.44 rows=21 width=0) (actual time=0.019..0.019 rows=21 loops=1)

  • Index Cond: (library_id = 98)
35. 0.336 0.567 ↓ 7.8 125 21

Bitmap Heap Scan on plate plate_1 (cost=4.41..59.72 rows=16 width=12) (actual time=0.014..0.027 rows=125 loops=21)

  • Recheck Cond: (copy_id = copy_1.copy_id)
  • Heap Blocks: exact=90
36. 0.231 0.231 ↓ 7.8 125 21

Bitmap Index Scan on plate_copy_id (cost=0.00..4.41 rows=16 width=0) (actual time=0.011..0.011 rows=125 loops=21)

  • Index Cond: (copy_id = copy_1.copy_id)
37.          

CTE copy_well_volumes

38. 5.338 55.860 ↑ 4.9 543 1

HashAggregate (cost=7,118.65..7,151.92 rows=2,662 width=18) (actual time=55.627..55.860 rows=543 loops=1)

  • Group Key: copy_well.plate_id
39. 31.888 50.522 ↓ 3.1 8,380 1

Hash Join (cost=35.54..7,058.75 rows=2,662 width=18) (actual time=8.420..50.522 rows=8,380 loops=1)

  • Hash Cond: (copy_well.copy_id = copy_2.copy_id)
40. 18.606 18.606 ↑ 1.0 306,477 1

Seq Scan on copy_well (cost=0.00..5,846.61 rows=306,661 width=22) (actual time=0.004..18.606 rows=306,477 loops=1)

41. 0.004 0.028 ↑ 1.0 21 1

Hash (cost=35.28..35.28 rows=21 width=4) (actual time=0.028..0.028 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.017 0.024 ↑ 1.0 21 1

Bitmap Heap Scan on copy copy_2 (cost=4.44..35.28 rows=21 width=4) (actual time=0.010..0.024 rows=21 loops=1)

  • Recheck Cond: (library_id = 98)
  • Heap Blocks: exact=11
43. 0.007 0.007 ↑ 1.0 21 1

Bitmap Index Scan on copy_library_id (cost=0.00..4.44 rows=21 width=0) (actual time=0.007..0.007 rows=21 loops=1)

  • Index Cond: (library_id = 98)
44.          

CTE well_concentrations

45. 29.294 49.706 ↑ 1.0 125 1

HashAggregate (cost=26,038.13..26,039.43 rows=130 width=14) (actual time=49.671..49.706 rows=125 loops=1)

  • Group Key: well.plate_number
46. 12.217 20.412 ↓ 1.0 48,000 1

Bitmap Heap Scan on well (cost=884.72..25,457.01 rows=46,490 width=14) (actual time=8.847..20.412 rows=48,000 loops=1)

  • Recheck Cond: (library_id = 98)
  • Heap Blocks: exact=2762
47. 8.195 8.195 ↓ 1.0 48,000 1

Bitmap Index Scan on well_library_id (cost=0.00..873.10 rows=46,490 width=0) (actual time=8.195..8.195 rows=48,000 loops=1)

  • Index Cond: (library_id = 98)
48.          

CTE plate_statistics

49. 2.846 111.139 ↓ 328.1 2,625 1

Hash Right Join (cost=1,298.35..1,361.74 rows=8 width=367) (actual time=107.933..111.139 rows=2,625 loops=1)

  • Hash Cond: (copy_well_volumes.plate_id = plate_2.plate_id)
50. 56.009 56.009 ↑ 4.9 543 1

CTE Scan on copy_well_volumes (cost=0.00..53.24 rows=2,662 width=204) (actual time=55.630..56.009 rows=543 loops=1)

51. 0.548 52.284 ↓ 328.1 2,625 1

Hash (cost=1,298.25..1,298.25 rows=8 width=167) (actual time=52.284..52.284 rows=2,625 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 236kB
52. 0.686 51.736 ↓ 328.1 2,625 1

Hash Join (cost=13.08..1,298.25 rows=8 width=167) (actual time=49.856..51.736 rows=2,625 loops=1)

  • Hash Cond: (plate_2.plate_number = well_concentrations.plate_number)
53. 0.647 1.269 ↓ 8.0 2,625 1

Nested Loop (cost=8.86..1,292.71 rows=328 width=39) (actual time=0.060..1.269 rows=2,625 loops=1)

54. 0.021 0.034 ↑ 1.0 21 1

Bitmap Heap Scan on copy copy_3 (cost=4.44..35.28 rows=21 width=6) (actual time=0.018..0.034 rows=21 loops=1)

  • Recheck Cond: (library_id = 98)
  • Heap Blocks: exact=11
55. 0.013 0.013 ↑ 1.0 21 1

Bitmap Index Scan on copy_library_id (cost=0.00..4.44 rows=21 width=0) (actual time=0.013..0.013 rows=21 loops=1)

  • Index Cond: (library_id = 98)
56. 0.378 0.588 ↓ 7.8 125 21

Bitmap Heap Scan on plate plate_2 (cost=4.41..59.72 rows=16 width=37) (actual time=0.013..0.028 rows=125 loops=21)

  • Recheck Cond: (copy_id = copy_3.copy_id)
  • Heap Blocks: exact=90
57. 0.210 0.210 ↓ 7.8 125 21

Bitmap Index Scan on plate_copy_id (cost=0.00..4.41 rows=16 width=0) (actual time=0.010..0.010 rows=125 loops=21)

  • Index Cond: (copy_id = copy_3.copy_id)
58. 0.023 49.781 ↑ 1.0 125 1

Hash (cost=2.60..2.60 rows=130 width=132) (actual time=49.781..49.781 rows=125 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
59. 49.758 49.758 ↑ 1.0 125 1

CTE Scan on well_concentrations (cost=0.00..2.60 rows=130 width=132) (actual time=49.676..49.758 rows=125 loops=1)

60.          

CTE plate_screening_statistics

61. 7.584 42.276 ↑ 2.0 1,199 1

HashAggregate (cost=2,828.43..2,852.11 rows=2,368 width=18) (actual time=41.245..42.276 rows=1,199 loops=1)

  • Group Key: assay_plate.plate_id, assay_plate.plate_number, plate_3.copy_id, copy_4.name
62. 5.653 34.692 ↓ 11.4 27,082 1

Hash Join (cost=2,173.75..2,792.91 rows=2,368 width=18) (actual time=29.224..34.692 rows=27,082 loops=1)

  • Hash Cond: (activity.activity_id = library_screening.activity_id)
63. 0.957 0.957 ↓ 1.0 17,709 1

Seq Scan on activity (cost=0.00..529.08 rows=17,708 width=8) (actual time=0.003..0.957 rows=17,709 loops=1)

64. 4.413 28.082 ↓ 11.4 27,082 1

Hash (cost=2,144.15..2,144.15 rows=2,368 width=22) (actual time=28.082..28.082 rows=27,082 loops=1)

  • Buckets: 32768 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1685kB
65. 5.505 23.669 ↓ 11.4 27,082 1

Hash Join (cost=386.64..2,144.15 rows=2,368 width=22) (actual time=3.164..23.669 rows=27,082 loops=1)

  • Hash Cond: (assay_plate.library_screening_id = library_screening.activity_id)
66. 4.176 15.804 ↓ 11.4 27,082 1

Nested Loop (cost=9.28..1,734.22 rows=2,368 width=18) (actual time=0.790..15.804 rows=27,082 loops=1)

67. 0.445 1.128 ↓ 8.0 2,625 1

Nested Loop (cost=8.86..1,292.71 rows=328 width=10) (actual time=0.029..1.128 rows=2,625 loops=1)

68. 0.025 0.032 ↑ 1.0 21 1

Bitmap Heap Scan on copy copy_4 (cost=4.44..35.28 rows=21 width=6) (actual time=0.011..0.032 rows=21 loops=1)

  • Recheck Cond: (library_id = 98)
  • Heap Blocks: exact=11
69. 0.007 0.007 ↑ 1.0 21 1

Bitmap Index Scan on copy_library_id (cost=0.00..4.44 rows=21 width=0) (actual time=0.007..0.007 rows=21 loops=1)

  • Index Cond: (library_id = 98)
70. 0.441 0.651 ↓ 7.8 125 21

Bitmap Heap Scan on plate plate_3 (cost=4.41..59.72 rows=16 width=8) (actual time=0.014..0.031 rows=125 loops=21)

  • Recheck Cond: (copy_id = copy_4.copy_id)
  • Heap Blocks: exact=90
71. 0.210 0.210 ↓ 7.8 125 21

Bitmap Index Scan on plate_copy_id (cost=0.00..4.41 rows=16 width=0) (actual time=0.010..0.010 rows=125 loops=21)

  • Index Cond: (copy_id = copy_4.copy_id)
72. 10.500 10.500 ↑ 2.4 10 2,625

Index Scan using assay_plate_plate_id on assay_plate (cost=0.42..1.11 rows=24 width=12) (actual time=0.001..0.004 rows=10 loops=2,625)

  • Index Cond: (plate_id = plate_3.plate_id)
73. 1.165 2.360 ↑ 1.0 12,594 1

Hash (cost=219.94..219.94 rows=12,594 width=4) (actual time=2.360..2.360 rows=12,594 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 571kB
74. 1.195 1.195 ↑ 1.0 12,594 1

Seq Scan on library_screening (cost=0.00..219.94 rows=12,594 width=4) (actual time=0.003..1.195 rows=12,594 loops=1)

75. 5.531 924.435 ↓ 2,625.0 2,625 1

Nested Loop (cost=75.31..1,481.59 rows=1 width=412) (actual time=278.956..924.435 rows=2,625 loops=1)

76. 1.063 186.529 ↓ 2,625.0 2,625 1

Nested Loop Left Join (cost=75.17..131.48 rows=1 width=395) (actual time=164.945..186.529 rows=2,625 loops=1)

77. 2.828 180.216 ↓ 2,625.0 2,625 1

Nested Loop (cost=74.89..131.18 rows=1 width=384) (actual time=164.942..180.216 rows=2,625 loops=1)

78. 2.759 169.513 ↓ 2,625.0 2,625 1

Hash Right Join (cost=74.61..130.86 rows=1 width=342) (actual time=164.932..169.513 rows=2,625 loops=1)

  • Hash Cond: (plate_screening_statistics.plate_id = plate.plate_id)
79. 43.081 43.081 ↑ 2.0 1,199 1

CTE Scan on plate_screening_statistics (cost=0.00..47.36 rows=2,368 width=12) (actual time=41.248..43.081 rows=1,199 loops=1)

80. 0.900 123.673 ↓ 2,625.0 2,625 1

Hash (cost=74.60..74.60 rows=1 width=338) (actual time=123.673..123.673 rows=2,625 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 439kB
81. 1.237 122.773 ↓ 2,625.0 2,625 1

Hash Join (cost=66.80..74.60 rows=1 width=338) (actual time=117.084..122.773 rows=2,625 loops=1)

  • Hash Cond: (plate_cte.plate_id = plate.plate_id)
82. 4.574 4.574 ↓ 8.0 2,625 1

CTE Scan on plate_cte (cost=0.00..6.56 rows=328 width=36) (actual time=0.112..4.574 rows=2,625 loops=1)

83. 0.914 116.962 ↓ 328.1 2,625 1

Hash (cost=66.70..66.70 rows=8 width=310) (actual time=116.962..116.962 rows=2,625 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 412kB
84. 1.531 116.048 ↓ 328.1 2,625 1

Nested Loop (cost=0.29..66.70 rows=8 width=310) (actual time=107.946..116.048 rows=2,625 loops=1)

85. 111.892 111.892 ↓ 328.1 2,625 1

CTE Scan on plate_statistics (cost=0.00..0.16 rows=8 width=244) (actual time=107.934..111.892 rows=2,625 loops=1)

86. 2.625 2.625 ↑ 1.0 1 2,625

Index Scan using plate_pkey on plate (cost=0.29..8.31 rows=1 width=66) (actual time=0.001..0.001 rows=1 loops=2,625)

  • Index Cond: (plate_id = plate_statistics.plate_id)
87. 7.875 7.875 ↑ 1.0 1 2,625

Index Scan using copy_pkey on copy (cost=0.28..0.30 rows=1 width=50) (actual time=0.003..0.003 rows=1 loops=2,625)

  • Index Cond: (copy_id = plate.copy_id)
  • Filter: (library_id = 98)
88. 5.250 5.250 ↑ 1.0 1 2,625

Index Scan using plate_location_pkey on plate_location (cost=0.28..0.29 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=2,625)

  • Index Cond: (plate.plate_location_id = plate_location_id)
89. 5.250 5.250 ↑ 1.0 1 2,625

Index Scan using library_pkey on library (cost=0.15..8.17 rows=1 width=25) (actual time=0.001..0.002 rows=1 loops=2,625)

  • Index Cond: (library_id = 98)
  • Filter: (short_name = 'ChemDiv6'::text)
90.          

SubPlan (forNested Loop)

91. 55.125 346.500 ↑ 1.0 1 2,625

Aggregate (cost=1,310.11..1,310.12 rows=1 width=0) (actual time=0.132..0.132 rows=1 loops=2,625)

92. 231.000 291.375 ↓ 1.5 352 2,625

Bitmap Heap Scan on well well_1 (cost=7.18..1,309.52 rows=239 width=0) (actual time=0.036..0.111 rows=352 loops=2,625)

  • Recheck Cond: (plate_number = plate.plate_number)
  • Filter: (library_well_type = 'experimental'::text)
  • Rows Removed by Filter: 32
  • Heap Blocks: exact=84462
93. 60.375 60.375 ↓ 1.1 384 2,625

Bitmap Index Scan on well_plate_number_key (cost=0.00..7.12 rows=359 width=0) (actual time=0.023..0.023 rows=384 loops=2,625)

  • Index Cond: (plate_number = plate.plate_number)
94. 97.125 99.750 ↑ 1.0 1 2,625

Aggregate (cost=0.04..0.05 rows=1 width=72) (actual time=0.038..0.038 rows=1 loops=2,625)

95. 2.625 2.625 ↓ 3.0 3 2,625

CTE Scan on _library_comment_apilogs (cost=0.00..0.02 rows=1 width=72) (actual time=0.001..0.001 rows=3 loops=2,625)

  • Filter: ((key)::text = library.short_name)
96. 5.250 280.875 ↑ 1.0 1 2,625

Aggregate (cost=31.75..31.77 rows=1 width=72) (actual time=0.107..0.107 rows=1 loops=2,625)

97. 275.625 275.625 ↓ 0.0 0 2,625

CTE Scan on _comment_apilogs (cost=0.00..31.68 rows=7 width=72) (actual time=0.098..0.105 rows=0 loops=2,625)

  • Filter: ((key)::text = plate_cte.key)
  • Rows Removed by Filter: 650