Snippet content copied to clipboard.
Are you sure to delete this snippet? No, don't delete
  1. WITH ranked_readouts AS (
  2. SELECT
  3. id,
  4. meter_id,
  5. timestamp,
  6. value,
  7. ignored,
  8. RANK() OVER (
  9. PARTITION BY meter_id
  10. ORDER BY
  11. timestamp DESC
  12. ) AS rank
  13. FROM
  14. metrius_readout
  15. WHERE
  16. meter_id IN (
  17. 444404,
  18. 444405,
  19. 444406,
  20. 444407,
  21. 444408,
  22. 227649,
  23. 227676,
  24. 227679,
  25. 227680,
  26. 227690,
  27. 227691,
  28. 227675,
  29. 227712,
  30. 227713,
  31. 227723,
  32. 227724,
  33. 234810,
  34. 234811,
  35. 2024710,
  36. 444394,
  37. 444395,
  38. 1791267,
  39. 1791268,
  40. 1791273,
  41. 1791269,
  42. 1791274,
  43. 1791270,
  44. 1791271,
  45. 1791272,
  46. 227664,
  47. 227665,
  48. 227650,
  49. 227683,
  50. 227684,
  51. 227701,
  52. 227702,
  53. 227651,
  54. 227652,
  55. 227653,
  56. 227654,
  57. 234809,
  58. 234812,
  59. 2024711,
  60. 2024712,
  61. 2024713,
  62. 2024714,
  63. 2024715
  64. )
  65. AND timestamp < '2022-07-19 00:00:00'
  66. AND ignored = 'f'
  67. )
  68. SELECT
  69. id,
  70. meter_id,
  71. timestamp,
  72. value,
  73. ignored
  74. FROM
  75. ranked_readouts
  76. WHERE
  77. rank = 1;
  78. ​
  79. ​
  80. ​
  81. ​
  82. ​
  83. ​
  84. ​
  85. ​
  86. ​
  87. ​
  88. ​
  89. Subquery Scan on ranked_readouts (cost=28529507.44..53157657.71 rows=840744 width=33) (actual time=71912.721..72328.174 rows=36 loops=1)
  90. Filter: (ranked_readouts.rank = 1)
  91. Rows Removed by Filter: 832641
  92. -> WindowAgg (cost=28529507.44..51055798.77 rows=168148715 width=41) (actual time=71912.719..72301.004 rows=832677 loops=1)
  93. -> Gather Merge (cost=28529507.44..48113196.26 rows=168148715 width=33) (actual time=71912.697..72015.758 rows=832677 loops=1)
  94. Workers Planned: 2
  95. Workers Launched: 0
  96. -> Sort (cost=28528507.42..28703662.33 rows=70061965 width=33) (actual time=71912.522..71964.651 rows=832677 loops=1)
  97. Sort Key: metrius_readout.meter_id, metrius_readout."timestamp" DESC
  98. Sort Method: quicksort Memory: 89629kB
  99. -> Parallel Seq Scan on metrius_readout (cost=0.12..17482927.88 rows=70061965 width=33) (actual time=68.466..71579.524 rows=832677 loops=1)
  100. Filter: ((NOT ignored) AND ("timestamp" < '2022-07-19 00:00:00+02'::timestamp with time zone) AND (meter_id = ANY ('{444404,444405,444406,444407,444408,227649,227676,227679,227680,227690,227691,227675,227712,227713,227723,227724,234810,234811,2024710,444394,444395,1791267,1791268,1791273,1791269,1791274,1791270,1791271,1791272,227664,227665,227650,227683,227684,227701,227702,227651,227652,227653,227654,234809,234812,2024711,2024712,2024713,2024714,2024715}'::bigint[])))
  101. Rows Removed by Filter: 1061018541
  102. Planning Time: 0.234 ms
  103. JIT:
  104. Functions: 10
  105. Options: Inlining true, Optimization true, Expressions true, Deforming true
  106. Timing: Generation 0.832 ms, Inlining 3.378 ms, Optimization 31.716 ms, Emission 23.972 ms, Total 59.897 ms
  107. Execution Time: 72332.692 ms
  108. (19 rows)

Edit this Snippet