explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fJLH

Settings
# exclusive inclusive rows x rows loops node
1. 0.214 939.386 ↓ 267.0 267 1

Sort (cost=90,509.60..90,509.61 rows=1 width=227) (actual time=939.365..939.386 rows=267 loops=1)

  • Sort Key: plate.plate_number, copy.name
  • Sort Method: quicksort Memory: 95kB
2.          

CTE plate_cte

3. 0.270 1.210 ↑ 1.0 267 1

Hash Join (cost=909.53..1,661.37 rows=267 width=29) (actual time=0.809..1.210 rows=267 loops=1)

  • Hash Cond: (copy_1.library_id = library_1.library_id)
4. 0.056 0.841 ↑ 1.0 267 1

Hash Join (cost=882.93..1,629.09 rows=267 width=18) (actual time=0.698..0.841 rows=267 loops=1)

  • Hash Cond: (plate_1.copy_id = copy_1.copy_id)
5. 0.101 0.291 ↑ 1.0 267 1

Bitmap Heap Scan on plate plate_1 (cost=795.50..1,537.99 rows=267 width=12) (actual time=0.199..0.291 rows=267 loops=1)

  • Recheck Cond: (plate_id = ANY ('{1293,1296,1297,1298,1310,1311,1314,1316,1317,1318,1319,1320,1323,1328,1329,1331,1340,1505,1506,1507,1513,1515,1517,1524,1532,1541,1562,1573,1575,1601,1620,1653,1654,1656,1657,1661,1662,1663,1664,1667,1668,1669,1671,1672,1674,1676,1677,1678,1679,1680,1682,1685,1689,1694,1696,1697,1776,1777,1780,1781,1784,1790,1791,1801,1817,1818,1819,1822,1825,1826,1827,1828,1830,1831,1832,1833,1834,1836,1837,1838,1839,1840,1841,1846,1878,1880,1889,1890,1893,1898,1900,1902,1903,1908,1909,1910,1911,1912,1926,1930,1932,1936,1937,1975,1977,1981,1985,1998,2002,2015,2035,2036,2041,2042,2048,2052,2064,2067,2068,2073,2261,2266,2338,2340,2687,2688,2693,2696,2699,2700,2701,2705,2707,2709,2711,2713,2807,2814,2815,3205,4057,4065,4069,8674,8694,13233,13256,13311,13315,13317,13318,14103,14298,14299,14301,14304,14305,17719,17724,17725,17730,17732,17733,17734,17736,17740,17741,17743,17744,17747,17749,19232,19236,19240,19241,19246,19250,19253,19254,19255,19256,19259,19263,19269,19270,19273,19280,19281,19284,19285,19288,19294,19299,19300,19301,19586,19587,19588,19589,19590,19591,20416,20418,20422,20428,20433,20434,20441,20456,20961,20962,20963,20964,20965,20966,20967,20972,20973,20974,20976,20977,20984,22206,22852,22854,22856,22858,22859,22860,22861,24157,24158,24159,24163,24519,24520,24521,24523,24524,28246,28247,28248,28249,28254,28330,28348,28350,28355,28357,28371,28373,28377,28381,28384,28391,28396,28398,28399,28402,28408,28419,28440,28443,28444,28445,28448,28454}'::integer[]))
  • Heap Blocks: exact=81
6. 0.190 0.190 ↑ 1.0 267 1

Bitmap Index Scan on plate_pkey (cost=0.00..795.43 rows=267 width=0) (actual time=0.190..0.190 rows=267 loops=1)

  • Index Cond: (plate_id = ANY ('{1293,1296,1297,1298,1310,1311,1314,1316,1317,1318,1319,1320,1323,1328,1329,1331,1340,1505,1506,1507,1513,1515,1517,1524,1532,1541,1562,1573,1575,1601,1620,1653,1654,1656,1657,1661,1662,1663,1664,1667,1668,1669,1671,1672,1674,1676,1677,1678,1679,1680,1682,1685,1689,1694,1696,1697,1776,1777,1780,1781,1784,1790,1791,1801,1817,1818,1819,1822,1825,1826,1827,1828,1830,1831,1832,1833,1834,1836,1837,1838,1839,1840,1841,1846,1878,1880,1889,1890,1893,1898,1900,1902,1903,1908,1909,1910,1911,1912,1926,1930,1932,1936,1937,1975,1977,1981,1985,1998,2002,2015,2035,2036,2041,2042,2048,2052,2064,2067,2068,2073,2261,2266,2338,2340,2687,2688,2693,2696,2699,2700,2701,2705,2707,2709,2711,2713,2807,2814,2815,3205,4057,4065,4069,8674,8694,13233,13256,13311,13315,13317,13318,14103,14298,14299,14301,14304,14305,17719,17724,17725,17730,17732,17733,17734,17736,17740,17741,17743,17744,17747,17749,19232,19236,19240,19241,19246,19250,19253,19254,19255,19256,19259,19263,19269,19270,19273,19280,19281,19284,19285,19288,19294,19299,19300,19301,19586,19587,19588,19589,19590,19591,20416,20418,20422,20428,20433,20434,20441,20456,20961,20962,20963,20964,20965,20966,20967,20972,20973,20974,20976,20977,20984,22206,22852,22854,22856,22858,22859,22860,22861,24157,24158,24159,24163,24519,24520,24521,24523,24524,28246,28247,28248,28249,28254,28330,28348,28350,28355,28357,28371,28373,28377,28381,28384,28391,28396,28398,28399,28402,28408,28419,28440,28443,28444,28445,28448,28454}'::integer[]))
7. 0.233 0.494 ↑ 1.0 2,419 1

Hash (cost=57.19..57.19 rows=2,419 width=10) (actual time=0.494..0.494 rows=2,419 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 138kB
8. 0.261 0.261 ↑ 1.0 2,419 1

Seq Scan on copy copy_1 (cost=0.00..57.19 rows=2,419 width=10) (actual time=0.002..0.261 rows=2,419 loops=1)

9. 0.036 0.099 ↑ 1.0 338 1

Hash (cost=22.38..22.38 rows=338 width=15) (actual time=0.099..0.099 rows=338 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
10. 0.063 0.063 ↑ 1.0 338 1

Seq Scan on library library_1 (cost=0.00..22.38 rows=338 width=15) (actual time=0.004..0.063 rows=338 loops=1)

11.          

CTE copy_well_volumes

12. 126.580 140.923 ↓ 1.5 6,817 1

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

  • Group Key: copy_well.plate_id
13. 14.343 14.343 ↑ 1.0 306,477 1

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

14.          

CTE well_concentrations

15. 603.427 749.918 ↓ 1.0 5,959 1

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

  • Group Key: well.plate_number
16. 146.491 146.491 ↑ 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.014..146.491 rows=2,094,144 loops=1)

17.          

CTE plate_statistics

18. 0.891 897.217 ↑ 1.0 267 1

Hash Join (cost=1,746.39..2,141.03 rows=280 width=367) (actual time=893.505..897.217 rows=267 loops=1)

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

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

20. 0.533 145.391 ↑ 1.0 267 1

Hash (cost=1,743.05..1,743.05 rows=267 width=239) (actual time=145.391..145.391 rows=267 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
21. 0.123 144.858 ↑ 1.0 267 1

Hash Join (cost=1,628.76..1,743.05 rows=267 width=239) (actual time=137.355..144.858 rows=267 loops=1)

  • Hash Cond: (plate_2.copy_id = copy_2.copy_id)
22. 0.819 144.275 ↑ 1.0 267 1

Hash Right Join (cost=1,541.33..1,651.95 rows=267 width=237) (actual time=136.891..144.275 rows=267 loops=1)

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

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

24. 0.036 0.344 ↑ 1.0 267 1

Hash (cost=1,537.99..1,537.99 rows=267 width=37) (actual time=0.344..0.344 rows=267 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
25. 0.116 0.308 ↑ 1.0 267 1

Bitmap Heap Scan on plate plate_2 (cost=795.50..1,537.99 rows=267 width=37) (actual time=0.200..0.308 rows=267 loops=1)

  • Recheck Cond: (plate_id = ANY ('{1293,1296,1297,1298,1310,1311,1314,1316,1317,1318,1319,1320,1323,1328,1329,1331,1340,1505,1506,1507,1513,1515,1517,1524,1532,1541,1562,1573,1575,1601,1620,1653,1654,1656,1657,1661,1662,1663,1664,1667,1668,1669,1671,1672,1674,1676,1677,1678,1679,1680,1682,1685,1689,1694,1696,1697,1776,1777,1780,1781,1784,1790,1791,1801,1817,1818,1819,1822,1825,1826,1827,1828,1830,1831,1832,1833,1834,1836,1837,1838,1839,1840,1841,1846,1878,1880,1889,1890,1893,1898,1900,1902,1903,1908,1909,1910,1911,1912,1926,1930,1932,1936,1937,1975,1977,1981,1985,1998,2002,2015,2035,2036,2041,2042,2048,2052,2064,2067,2068,2073,2261,2266,2338,2340,2687,2688,2693,2696,2699,2700,2701,2705,2707,2709,2711,2713,2807,2814,2815,3205,4057,4065,4069,8674,8694,13233,13256,13311,13315,13317,13318,14103,14298,14299,14301,14304,14305,17719,17724,17725,17730,17732,17733,17734,17736,17740,17741,17743,17744,17747,17749,19232,19236,19240,19241,19246,19250,19253,19254,19255,19256,19259,19263,19269,19270,19273,19280,19281,19284,19285,19288,19294,19299,19300,19301,19586,19587,19588,19589,19590,19591,20416,20418,20422,20428,20433,20434,20441,20456,20961,20962,20963,20964,20965,20966,20967,20972,20973,20974,20976,20977,20984,22206,22852,22854,22856,22858,22859,22860,22861,24157,24158,24159,24163,24519,24520,24521,24523,24524,28246,28247,28248,28249,28254,28330,28348,28350,28355,28357,28371,28373,28377,28381,28384,28391,28396,28398,28399,28402,28408,28419,28440,28443,28444,28445,28448,28454}'::integer[]))
  • Heap Blocks: exact=81
26. 0.192 0.192 ↑ 1.0 267 1

Bitmap Index Scan on plate_pkey (cost=0.00..795.43 rows=267 width=0) (actual time=0.192..0.192 rows=267 loops=1)

  • Index Cond: (plate_id = ANY ('{1293,1296,1297,1298,1310,1311,1314,1316,1317,1318,1319,1320,1323,1328,1329,1331,1340,1505,1506,1507,1513,1515,1517,1524,1532,1541,1562,1573,1575,1601,1620,1653,1654,1656,1657,1661,1662,1663,1664,1667,1668,1669,1671,1672,1674,1676,1677,1678,1679,1680,1682,1685,1689,1694,1696,1697,1776,1777,1780,1781,1784,1790,1791,1801,1817,1818,1819,1822,1825,1826,1827,1828,1830,1831,1832,1833,1834,1836,1837,1838,1839,1840,1841,1846,1878,1880,1889,1890,1893,1898,1900,1902,1903,1908,1909,1910,1911,1912,1926,1930,1932,1936,1937,1975,1977,1981,1985,1998,2002,2015,2035,2036,2041,2042,2048,2052,2064,2067,2068,2073,2261,2266,2338,2340,2687,2688,2693,2696,2699,2700,2701,2705,2707,2709,2711,2713,2807,2814,2815,3205,4057,4065,4069,8674,8694,13233,13256,13311,13315,13317,13318,14103,14298,14299,14301,14304,14305,17719,17724,17725,17730,17732,17733,17734,17736,17740,17741,17743,17744,17747,17749,19232,19236,19240,19241,19246,19250,19253,19254,19255,19256,19259,19263,19269,19270,19273,19280,19281,19284,19285,19288,19294,19299,19300,19301,19586,19587,19588,19589,19590,19591,20416,20418,20422,20428,20433,20434,20441,20456,20961,20962,20963,20964,20965,20966,20967,20972,20973,20974,20976,20977,20984,22206,22852,22854,22856,22858,22859,22860,22861,24157,24158,24159,24163,24519,24520,24521,24523,24524,28246,28247,28248,28249,28254,28330,28348,28350,28355,28357,28371,28373,28377,28381,28384,28391,28396,28398,28399,28402,28408,28419,28440,28443,28444,28445,28448,28454}'::integer[]))
27. 0.201 0.460 ↑ 1.0 2,419 1

Hash (cost=57.19..57.19 rows=2,419 width=6) (actual time=0.460..0.460 rows=2,419 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 128kB
28. 0.259 0.259 ↑ 1.0 2,419 1

Seq Scan on copy copy_2 (cost=0.00..57.19 rows=2,419 width=6) (actual time=0.002..0.259 rows=2,419 loops=1)

29. 0.316 939.172 ↓ 267.0 267 1

Nested Loop (cost=804.88..2,882.12 rows=1 width=227) (actual time=895.259..939.172 rows=267 loops=1)

30. 0.000 909.219 ↓ 267.0 267 1

Nested Loop Left Join (cost=804.73..1,571.79 rows=1 width=210) (actual time=895.076..909.219 rows=267 loops=1)

31. 0.215 908.966 ↓ 267.0 267 1

Nested Loop (cost=804.46..1,571.31 rows=1 width=199) (actual time=895.071..908.966 rows=267 loops=1)

32. 7.145 908.484 ↓ 267.0 267 1

Nested Loop (cost=804.18..1,570.37 rows=1 width=174) (actual time=895.064..908.484 rows=267 loops=1)

  • Join Filter: (plate.plate_id = plate_statistics.plate_id)
  • Rows Removed by Join Filter: 71022
33. 897.334 897.334 ↑ 1.0 267 1

CTE Scan on plate_statistics (cost=0.00..5.60 rows=280 width=116) (actual time=893.509..897.334 rows=267 loops=1)

34. 2.389 4.005 ↓ 133.5 267 267

Materialize (cost=804.18..1,556.38 rows=2 width=70) (actual time=0.006..0.015 rows=267 loops=267)

35. 0.088 1.616 ↓ 133.5 267 1

Hash Join (cost=804.18..1,556.37 rows=2 width=70) (actual time=1.489..1.616 rows=267 loops=1)

  • Hash Cond: (plate.plate_id = plate_cte.plate_id)
36. 0.053 0.218 ↑ 1.0 267 1

Bitmap Heap Scan on plate (cost=795.50..1,537.99 rows=267 width=66) (actual time=0.171..0.218 rows=267 loops=1)

  • Recheck Cond: (plate_id = ANY ('{1293,1296,1297,1298,1310,1311,1314,1316,1317,1318,1319,1320,1323,1328,1329,1331,1340,1505,1506,1507,1513,1515,1517,1524,1532,1541,1562,1573,1575,1601,1620,1653,1654,1656,1657,1661,1662,1663,1664,1667,1668,1669,1671,1672,1674,1676,1677,1678,1679,1680,1682,1685,1689,1694,1696,1697,1776,1777,1780,1781,1784,1790,1791,1801,1817,1818,1819,1822,1825,1826,1827,1828,1830,1831,1832,1833,1834,1836,1837,1838,1839,1840,1841,1846,1878,1880,1889,1890,1893,1898,1900,1902,1903,1908,1909,1910,1911,1912,1926,1930,1932,1936,1937,1975,1977,1981,1985,1998,2002,2015,2035,2036,2041,2042,2048,2052,2064,2067,2068,2073,2261,2266,2338,2340,2687,2688,2693,2696,2699,2700,2701,2705,2707,2709,2711,2713,2807,2814,2815,3205,4057,4065,4069,8674,8694,13233,13256,13311,13315,13317,13318,14103,14298,14299,14301,14304,14305,17719,17724,17725,17730,17732,17733,17734,17736,17740,17741,17743,17744,17747,17749,19232,19236,19240,19241,19246,19250,19253,19254,19255,19256,19259,19263,19269,19270,19273,19280,19281,19284,19285,19288,19294,19299,19300,19301,19586,19587,19588,19589,19590,19591,20416,20418,20422,20428,20433,20434,20441,20456,20961,20962,20963,20964,20965,20966,20967,20972,20973,20974,20976,20977,20984,22206,22852,22854,22856,22858,22859,22860,22861,24157,24158,24159,24163,24519,24520,24521,24523,24524,28246,28247,28248,28249,28254,28330,28348,28350,28355,28357,28371,28373,28377,28381,28384,28391,28396,28398,28399,28402,28408,28419,28440,28443,28444,28445,28448,28454}'::integer[]))
  • Heap Blocks: exact=81
37. 0.165 0.165 ↑ 1.0 267 1

Bitmap Index Scan on plate_pkey (cost=0.00..795.43 rows=267 width=0) (actual time=0.165..0.165 rows=267 loops=1)

  • Index Cond: (plate_id = ANY ('{1293,1296,1297,1298,1310,1311,1314,1316,1317,1318,1319,1320,1323,1328,1329,1331,1340,1505,1506,1507,1513,1515,1517,1524,1532,1541,1562,1573,1575,1601,1620,1653,1654,1656,1657,1661,1662,1663,1664,1667,1668,1669,1671,1672,1674,1676,1677,1678,1679,1680,1682,1685,1689,1694,1696,1697,1776,1777,1780,1781,1784,1790,1791,1801,1817,1818,1819,1822,1825,1826,1827,1828,1830,1831,1832,1833,1834,1836,1837,1838,1839,1840,1841,1846,1878,1880,1889,1890,1893,1898,1900,1902,1903,1908,1909,1910,1911,1912,1926,1930,1932,1936,1937,1975,1977,1981,1985,1998,2002,2015,2035,2036,2041,2042,2048,2052,2064,2067,2068,2073,2261,2266,2338,2340,2687,2688,2693,2696,2699,2700,2701,2705,2707,2709,2711,2713,2807,2814,2815,3205,4057,4065,4069,8674,8694,13233,13256,13311,13315,13317,13318,14103,14298,14299,14301,14304,14305,17719,17724,17725,17730,17732,17733,17734,17736,17740,17741,17743,17744,17747,17749,19232,19236,19240,19241,19246,19250,19253,19254,19255,19256,19259,19263,19269,19270,19273,19280,19281,19284,19285,19288,19294,19299,19300,19301,19586,19587,19588,19589,19590,19591,20416,20418,20422,20428,20433,20434,20441,20456,20961,20962,20963,20964,20965,20966,20967,20972,20973,20974,20976,20977,20984,22206,22852,22854,22856,22858,22859,22860,22861,24157,24158,24159,24163,24519,24520,24521,24523,24524,28246,28247,28248,28249,28254,28330,28348,28350,28355,28357,28371,28373,28377,28381,28384,28391,28396,28398,28399,28402,28408,28419,28440,28443,28444,28445,28448,28454}'::integer[]))
38. 0.024 1.310 ↑ 1.0 267 1

Hash (cost=5.34..5.34 rows=267 width=4) (actual time=1.310..1.310 rows=267 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
39. 1.286 1.286 ↑ 1.0 267 1

CTE Scan on plate_cte (cost=0.00..5.34 rows=267 width=4) (actual time=0.810..1.286 rows=267 loops=1)

40. 0.267 0.267 ↑ 1.0 1 267

Index Scan using copy_pkey on copy (cost=0.28..0.93 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=267)

  • Index Cond: (copy_id = plate.copy_id)
41. 0.267 0.267 ↓ 0.0 0 267

Index Scan using plate_location_pkey on plate_location (cost=0.28..0.47 rows=1 width=19) (actual time=0.000..0.001 rows=0 loops=267)

  • Index Cond: (plate.plate_location_id = plate_location_id)
42. 0.267 0.267 ↑ 1.0 1 267

Index Scan using library_pkey on library (cost=0.15..0.20 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=267)

  • Index Cond: (library_id = copy.library_id)
43.          

SubPlan (forNested Loop)

44. 4.806 29.370 ↑ 1.0 1 267

Aggregate (cost=1,310.11..1,310.12 rows=1 width=0) (actual time=0.110..0.110 rows=1 loops=267)

45. 19.224 24.564 ↓ 1.5 350 267

Bitmap Heap Scan on well well_1 (cost=7.18..1,309.52 rows=239 width=0) (actual time=0.031..0.092 rows=350 loops=267)

  • Recheck Cond: (plate_number = plate.plate_number)
  • Filter: (library_well_type = 'experimental'::text)
  • Rows Removed by Filter: 34
  • Heap Blocks: exact=6832
46. 5.340 5.340 ↓ 1.1 384 267

Bitmap Index Scan on well_plate_number_key (cost=0.00..7.12 rows=359 width=0) (actual time=0.020..0.020 rows=384 loops=267)

  • Index Cond: (plate_number = plate.plate_number)