explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3qJK

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 958.293 ↑ 1.0 1 1

Hash Right Join (cost=104,039.64..104,323.00 rows=1 width=333) (actual time=958.291..958.293 rows=1 loops=1)

  • Hash Cond: (copy_screening_statistics.copy_id = copy.copy_id)
2.          

CTE plate_location_counts

3. 0.677 19.941 ↑ 11.3 2,254 1

Sort (cost=4,632.29..4,695.93 rows=25,457 width=19) (actual time=19.788..19.941 rows=2,254 loops=1)

  • Sort Key: (count(*)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 354kB
4. 10.317 19.264 ↑ 11.3 2,254 1

HashAggregate (cost=2,451.16..2,769.37 rows=25,457 width=19) (actual time=16.626..19.264 rows=2,254 loops=1)

  • Group Key: plate.copy_id, plate_location.room, plate_location.freezer, plate_location.shelf, plate_location.bin
5. 6.601 8.947 ↑ 1.0 25,449 1

Hash Join (cost=28.33..2,069.31 rows=25,457 width=19) (actual time=0.250..8.947 rows=25,449 loops=1)

  • Hash Cond: (plate.plate_location_id = plate_location.plate_location_id)
6. 2.112 2.112 ↑ 1.0 37,775 1

Seq Scan on plate (cost=0.00..1,644.75 rows=37,775 width=8) (actual time=0.002..2.112 rows=37,775 loops=1)

7. 0.145 0.234 ↑ 1.0 948 1

Hash (cost=16.48..16.48 rows=948 width=19) (actual time=0.234..0.234 rows=948 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 59kB
8. 0.089 0.089 ↑ 1.0 948 1

Seq Scan on plate_location (cost=0.00..16.48 rows=948 width=19) (actual time=0.002..0.089 rows=948 loops=1)

9.          

CTE plate_status_counts

10. 0.672 13.395 ↑ 67.5 56 1

Sort (cost=2,190.32..2,199.77 rows=3,778 width=15) (actual time=13.387..13.395 rows=56 loops=1)

  • Sort Key: (count(*)) DESC NULLS LAST
  • Sort Method: quicksort Memory: 293kB
11. 10.445 12.723 ↑ 1.5 2,516 1

HashAggregate (cost=1,928.06..1,965.84 rows=3,778 width=15) (actual time=12.194..12.723 rows=2,516 loops=1)

  • Group Key: plate_1.copy_id, plate_1.status
12. 2.278 2.278 ↑ 1.0 37,775 1

Seq Scan on plate plate_1 (cost=0.00..1,644.75 rows=37,775 width=15) (actual time=0.002..2.278 rows=37,775 loops=1)

13.          

CTE copy_well_volumes

14. 124.121 144.421 ↓ 1.5 6,817 1

HashAggregate (cost=12,746.48..12,804.53 rows=4,644 width=18) (actual time=141.350..144.421 rows=6,817 loops=1)

  • Group Key: copy_well.plate_id
15. 20.300 20.300 ↑ 1.0 306,477 1

Seq Scan on copy_well (cost=0.00..5,846.61 rows=306,661 width=18) (actual time=0.005..20.300 rows=306,477 loops=1)

16.          

CTE well_concentrations

17. 578.854 725.984 ↓ 1.0 5,959 1

HashAggregate (cost=70,962.24..71,020.54 rows=5,830 width=14) (actual time=724.256..725.984 rows=5,959 loops=1)

  • Group Key: well.plate_number
18. 147.130 147.130 ↑ 1.0 2,094,144 1

Seq Scan on well (cost=0.00..44,785.44 rows=2,094,144 width=14) (actual time=0.005..147.130 rows=2,094,144 loops=1)

19.          

CTE plate_statistics

20. 4.148 888.489 ↓ 1.0 4,201 1

Hash Join (cost=2,106.80..2,391.87 rows=4,158 width=367) (actual time=881.382..888.489 rows=4,201 loops=1)

  • Hash Cond: (well_concentrations.plate_number = plate_2.plate_number)
21. 727.245 727.245 ↓ 1.0 5,959 1

CTE Scan on well_concentrations (cost=0.00..116.60 rows=5,830 width=132) (actual time=724.258..727.245 rows=5,959 loops=1)

22. 0.786 157.096 ↓ 1.1 4,201 1

Hash (cost=2,057.23..2,057.23 rows=3,966 width=239) (actual time=157.096..157.096 rows=4,201 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 382kB
23. 1.411 156.310 ↓ 1.1 4,201 1

Hash Right Join (cost=1,942.05..2,057.23 rows=3,966 width=239) (actual time=150.181..156.310 rows=4,201 loops=1)

  • Hash Cond: (copy_well_volumes.plate_id = plate_2.plate_id)
24. 146.093 146.093 ↓ 1.5 6,817 1

CTE Scan on copy_well_volumes (cost=0.00..92.88 rows=4,644 width=204) (actual time=141.354..146.093 rows=6,817 loops=1)

25. 0.771 8.806 ↓ 1.1 4,201 1

Hash (cost=1,892.48..1,892.48 rows=3,966 width=39) (actual time=8.806..8.806 rows=4,201 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 337kB
26. 3.464 8.035 ↓ 1.1 4,201 1

Hash Join (cost=66.41..1,892.48 rows=3,966 width=39) (actual time=0.300..8.035 rows=4,201 loops=1)

  • Hash Cond: (plate_2.copy_id = copy_1.copy_id)
27. 4.282 4.282 ↑ 1.0 37,775 1

Seq Scan on plate plate_2 (cost=0.00..1,644.75 rows=37,775 width=37) (actual time=0.002..4.282 rows=37,775 loops=1)

28. 0.033 0.289 ↑ 1.0 254 1

Hash (cost=63.24..63.24 rows=254 width=6) (actual time=0.289..0.289 rows=254 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
29. 0.256 0.256 ↑ 1.0 254 1

Seq Scan on copy copy_1 (cost=0.00..63.24 rows=254 width=6) (actual time=0.005..0.256 rows=254 loops=1)

  • Filter: (name = 'A'::text)
  • Rows Removed by Filter: 2165
30.          

CTE copy_statistics

31. 3.224 892.835 ↓ 1.3 254 1

HashAggregate (cost=228.69..232.19 rows=200 width=244) (actual time=892.679..892.835 rows=254 loops=1)

  • Group Key: plate_statistics.copy_id
32. 889.611 889.611 ↓ 1.0 4,201 1

CTE Scan on plate_statistics (cost=0.00..83.16 rows=4,158 width=244) (actual time=881.384..889.611 rows=4,201 loops=1)

33.          

CTE plate_screening_statistics

34. 3.631 29.875 ↑ 18.9 1,519 1

HashAggregate (cost=9,310.74..9,597.20 rows=28,646 width=18) (actual time=29.620..29.875 rows=1,519 loops=1)

  • Group Key: assay_plate.plate_id, assay_plate.plate_number, plate_3.copy_id, copy_2.name
35. 2.365 26.244 ↑ 2.1 13,346 1

Hash Join (cost=1,323.05..8,881.05 rows=28,646 width=18) (actual time=10.158..26.244 rows=13,346 loops=1)

  • Hash Cond: (assay_plate.library_screening_id = library_screening.activity_id)
36. 0.378 14.718 ↑ 2.1 13,346 1

Nested Loop (cost=66.83..7,230.95 rows=28,646 width=18) (actual time=0.949..14.718 rows=13,346 loops=1)

37. 3.553 5.938 ↓ 1.1 4,201 1

Hash Join (cost=66.41..1,892.48 rows=3,966 width=10) (actual time=0.276..5.938 rows=4,201 loops=1)

  • Hash Cond: (plate_3.copy_id = copy_2.copy_id)
38. 2.125 2.125 ↑ 1.0 37,775 1

Seq Scan on plate plate_3 (cost=0.00..1,644.75 rows=37,775 width=8) (actual time=0.006..2.125 rows=37,775 loops=1)

39. 0.024 0.260 ↑ 1.0 254 1

Hash (cost=63.24..63.24 rows=254 width=6) (actual time=0.260..0.260 rows=254 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
40. 0.236 0.236 ↑ 1.0 254 1

Seq Scan on copy copy_2 (cost=0.00..63.24 rows=254 width=6) (actual time=0.004..0.236 rows=254 loops=1)

  • Filter: (name = 'A'::text)
  • Rows Removed by Filter: 2165
41. 8.402 8.402 ↑ 8.0 3 4,201

Index Scan using assay_plate_plate_id on assay_plate (cost=0.42..1.11 rows=24 width=12) (actual time=0.001..0.002 rows=3 loops=4,201)

  • Index Cond: (plate_id = plate_3.plate_id)
42. 1.589 9.161 ↑ 1.0 12,594 1

Hash (cost=1,098.79..1,098.79 rows=12,594 width=12) (actual time=9.161..9.161 rows=12,594 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 670kB
43. 3.473 7.572 ↑ 1.0 12,594 1

Hash Join (cost=377.37..1,098.79 rows=12,594 width=12) (actual time=2.456..7.572 rows=12,594 loops=1)

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

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

45. 1.216 2.406 ↑ 1.0 12,594 1

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

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

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

47.          

CTE copy_screening_statistics

48. 0.267 30.432 ↑ 3.6 56 1

HashAggregate (cost=787.76..789.76 rows=200 width=12) (actual time=30.424..30.432 rows=56 loops=1)

  • Group Key: plate_screening_statistics.copy_id
49. 30.165 30.165 ↑ 18.9 1,519 1

CTE Scan on plate_screening_statistics (cost=0.00..572.92 rows=28,646 width=12) (actual time=29.623..30.165 rows=1,519 loops=1)

50. 30.443 30.443 ↑ 3.6 56 1

CTE Scan on copy_screening_statistics (cost=0.00..4.00 rows=200 width=12) (actual time=30.425..30.443 rows=56 loops=1)

51. 0.001 892.968 ↑ 1.0 1 1

Hash (cost=21.37..21.37 rows=1 width=325) (actual time=892.968..892.968 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.030 892.967 ↑ 1.0 1 1

Hash Right Join (cost=16.61..21.37 rows=1 width=325) (actual time=892.830..892.967 rows=1 loops=1)

  • Hash Cond: (copy_statistics.copy_id = copy.copy_id)
53. 892.910 892.910 ↓ 1.3 254 1

CTE Scan on copy_statistics (cost=0.00..4.00 rows=200 width=196) (actual time=892.681..892.910 rows=254 loops=1)

54. 0.003 0.027 ↑ 1.0 1 1

Hash (cost=16.60..16.60 rows=1 width=133) (actual time=0.027..0.027 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 0.002 0.024 ↑ 1.0 1 1

Nested Loop (cost=0.55..16.60 rows=1 width=133) (actual time=0.024..0.024 rows=1 loops=1)

56. 0.014 0.014 ↑ 1.0 1 1

Index Scan using library_short_name_key on library (cost=0.27..8.29 rows=1 width=79) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: (short_name = 'ChemDiv6'::text)
57. 0.008 0.008 ↑ 1.0 1 1

Index Scan using copy_key on copy (cost=0.28..8.30 rows=1 width=62) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: ((library_id = library.library_id) AND (name = 'A'::text))
58.          

SubPlan (forHash Right Join)

59. 0.000 20.229 ↓ 0.0 0 1

Limit (cost=0.00..4.51 rows=1 width=32) (actual time=20.229..20.229 rows=0 loops=1)

60. 20.229 20.229 ↓ 0.0 0 1

CTE Scan on plate_location_counts (cost=0.00..572.78 rows=127 width=32) (actual time=20.229..20.229 rows=0 loops=1)

  • Filter: (copy_id = copy.copy_id)
  • Rows Removed by Filter: 2254
61. 0.021 0.055 ↑ 1.0 1 1

Aggregate (cost=63.26..63.27 rows=1 width=4) (actual time=0.055..0.055 rows=1 loops=1)

62. 0.018 0.034 ↓ 7.8 125 1

Bitmap Heap Scan on plate p (cost=4.41..63.22 rows=16 width=4) (actual time=0.020..0.034 rows=125 loops=1)

  • Recheck Cond: (copy_id = copy.copy_id)
  • Heap Blocks: exact=6
63. 0.016 0.016 ↓ 7.8 125 1

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

  • Index Cond: (copy_id = copy.copy_id)
64. 0.002 13.409 ↑ 1.0 1 1

Limit (cost=0.00..4.47 rows=1 width=32) (actual time=13.409..13.409 rows=1 loops=1)

65. 13.407 13.407 ↑ 19.0 1 1

CTE Scan on plate_status_counts (cost=0.00..85.00 rows=19 width=32) (actual time=13.407..13.407 rows=1 loops=1)

  • Filter: (copy_id = copy.copy_id)
  • Rows Removed by Filter: 55
66. 0.005 0.063 ↑ 1.0 1 1

Aggregate (cost=63.28..63.29 rows=1 width=156) (actual time=0.063..0.063 rows=1 loops=1)

67. 0.040 0.058 ↓ 0.0 0 1

Bitmap Heap Scan on plate p_1 (cost=4.41..63.26 rows=8 width=156) (actual time=0.058..0.058 rows=0 loops=1)

  • Recheck Cond: (copy_id = copy.copy_id)
  • Filter: (status = 'available'::text)
  • Rows Removed by Filter: 125
  • Heap Blocks: exact=6
68. 0.018 0.018 ↓ 7.8 125 1

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

  • Index Cond: (copy_id = copy.copy_id)
69. 0.031 0.060 ↑ 1.0 1 1

Aggregate (cost=63.26..63.27 rows=1 width=4) (actual time=0.060..0.060 rows=1 loops=1)

70. 0.018 0.029 ↓ 7.8 125 1

Bitmap Heap Scan on plate plate_4 (cost=4.41..63.22 rows=16 width=4) (actual time=0.013..0.029 rows=125 loops=1)

  • Recheck Cond: (copy_id = copy.copy_id)
  • Heap Blocks: exact=6
71. 0.011 0.011 ↓ 7.8 125 1

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=1)

  • Index Cond: (copy_id = copy.copy_id)
72. 0.034 0.060 ↑ 1.0 1 1

Aggregate (cost=63.26..63.27 rows=1 width=4) (actual time=0.060..0.060 rows=1 loops=1)

73. 0.015 0.026 ↓ 7.8 125 1

Bitmap Heap Scan on plate plate_5 (cost=4.41..63.22 rows=16 width=4) (actual time=0.012..0.026 rows=125 loops=1)

  • Recheck Cond: (copy_id = copy.copy_id)
  • Heap Blocks: exact=6
74. 0.011 0.011 ↓ 7.8 125 1

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=1)

  • Index Cond: (copy_id = copy.copy_id)
75. 0.012 0.038 ↑ 1.0 1 1

Aggregate (cost=4.61..4.62 rows=1 width=0) (actual time=0.038..0.038 rows=1 loops=1)

76. 0.026 0.026 ↓ 7.8 125 1

Index Only Scan using plate_copy_id on plate plate_6 (cost=0.29..4.57 rows=16 width=0) (actual time=0.012..0.026 rows=125 loops=1)

  • Index Cond: (copy_id = copy.copy_id)
  • Heap Fetches: 0
77. 0.934 0.934 ↓ 0.0 0 1

Index Scan using copy_well_copy_id_3eceab71 on copy_well copy_well_1 (cost=0.42..1,719.98 rows=213 width=0) (actual time=0.934..0.934 rows=0 loops=1)

  • Index Cond: (copy_id = copy.copy_id)
  • Filter: ((mg_ml_concentration IS NOT NULL) OR (molar_concentration IS NOT NULL))
  • Rows Removed by Filter: 3827
78. 0.000 0.000 ↓ 0.0 0

Seq Scan on copy_well copy_well_2 (cost=0.00..5,846.61 rows=81,444 width=4) (never executed)

  • Filter: ((mg_ml_concentration IS NOT NULL) OR (molar_concentration IS NOT NULL))
79. 0.006 0.006 ↑ 579.0 1 1

Index Scan using copy_well_copy_id_3eceab71 on copy_well copy_well_3 (cost=0.42..1,719.98 rows=579 width=0) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (copy_id = copy.copy_id)
  • Filter: (initial_volume IS NOT NULL)