explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mkll

Settings
# exclusive inclusive rows x rows loops node
1. 20.839 750.407 ↓ 2,625.0 2,625 1

Sort (cost=81,475.28..81,475.28 rows=1 width=404) (actual time=746.894..750.407 rows=2,625 loops=1)

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

CTE users-library

3. 0.290 0.438 ↑ 1.0 189 1

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

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

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

5. 0.046 0.093 ↑ 1.0 189 1

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

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

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

7.          

CTE _library_comment_apilogs

8. 0.013 0.606 ↓ 3.0 3 1

Sort (cost=15.31..15.31 rows=1 width=106) (actual time=0.605..0.606 rows=3 loops=1)

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

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

10. 0.032 0.566 ↓ 5.0 5 1

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

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

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

12. 0.005 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.039 0.039 ↓ 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.039 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.020 0.020 ↓ 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.004..0.004 rows=0 loops=5)

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

CTE users-librarycopyplate

16. 0.233 0.336 ↑ 1.0 189 1

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

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

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

18. 0.029 0.086 ↑ 1.0 189 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
19. 0.057 0.057 ↑ 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.057 rows=189 loops=1)

20.          

CTE _comment_apilogs

21. 0.257 83.535 ↑ 2.2 650 1

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

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

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

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

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

24. 65.220 81.702 ↑ 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=68.316..81.702 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. 16.482 16.482 ↓ 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=16.482..16.482 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.024 0.406 ↑ 1.0 189 1

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

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

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

29.          

CTE plate_cte

30. 4.664 5.505 ↓ 8.0 2,625 1

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

31. 0.016 0.064 ↑ 1.0 21 1

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

32. 0.010 0.010 ↑ 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.009..0.010 rows=1 loops=1)

  • Index Cond: (library_id = 98)
33. 0.026 0.038 ↑ 1.0 21 1

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

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

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

  • Index Cond: (library_id = 98)
35. 0.588 0.777 ↓ 7.8 125 21

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

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

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

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

CTE copy_well_volumes

38. 4.387 46.998 ↑ 4.9 543 1

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

  • Group Key: copy_well.plate_id
39. 27.748 42.611 ↓ 3.1 8,380 1

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

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

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

41. 0.012 0.026 ↑ 1.0 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.010 0.014 ↑ 1.0 21 1

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

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

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

  • Index Cond: (library_id = 98)
44.          

CTE well_concentrations

45. 26.611 50.609 ↑ 1.0 125 1

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

  • Group Key: well.plate_number
46. 17.186 23.998 ↓ 1.0 48,000 1

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

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

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

  • Index Cond: (library_id = 98)
48.          

CTE plate_statistics

49. 2.343 101.991 ↓ 328.1 2,625 1

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

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

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

51. 0.447 52.531 ↓ 328.1 2,625 1

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

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

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

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

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

54. 0.013 0.024 ↑ 1.0 21 1

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

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

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

  • Index Cond: (library_id = 98)
56. 0.273 0.420 ↓ 7.8 125 21

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

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

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

  • Index Cond: (copy_id = copy_3.copy_id)
58. 0.016 50.662 ↑ 1.0 125 1

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

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

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

60. 7.043 729.568 ↓ 2,625.0 2,625 1

Nested Loop (cost=67.50..1,425.33 rows=1 width=404) (actual time=191.483..729.568 rows=2,625 loops=1)

61. 1.433 126.650 ↓ 2,625.0 2,625 1

Nested Loop Left Join (cost=67.36..75.22 rows=1 width=387) (actual time=106.962..126.650 rows=2,625 loops=1)

62. 1.456 122.592 ↓ 2,625.0 2,625 1

Nested Loop (cost=67.08..74.91 rows=1 width=376) (actual time=106.955..122.592 rows=2,625 loops=1)

63. 2.223 115.886 ↓ 2,625.0 2,625 1

Hash Join (cost=66.80..74.60 rows=1 width=334) (actual time=106.946..115.886 rows=2,625 loops=1)

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

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

65. 0.734 106.862 ↓ 328.1 2,625 1

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

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 412kB
66. 0.786 106.128 ↓ 328.1 2,625 1

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

67. 102.717 102.717 ↓ 328.1 2,625 1

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

68. 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)
69. 5.250 5.250 ↑ 1.0 1 2,625

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

  • Index Cond: (copy_id = plate.copy_id)
  • Filter: (library_id = 98)
70. 2.625 2.625 ↑ 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.001..0.001 rows=1 loops=2,625)

  • Index Cond: (plate.plate_location_id = plate_location_id)
71. 2.625 2.625 ↑ 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.001 rows=1 loops=2,625)

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

SubPlan (forNested Loop)

73. 47.250 294.000 ↑ 1.0 1 2,625

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

74. 196.875 246.750 ↓ 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.030..0.094 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
75. 49.875 49.875 ↓ 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.019..0.019 rows=384 loops=2,625)

  • Index Cond: (plate_number = plate.plate_number)
76. 78.750 81.375 ↑ 1.0 1 2,625

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

77. 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)
78. 2.625 217.875 ↑ 1.0 1 2,625

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

79. 215.250 215.250 ↓ 0.0 0 2,625

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

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