Snippet content copied to clipboard.
Are you sure to delete this snippet? No, don't delete
  1. explain analyze 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. Subquery Scan on ranked_readouts (cost=2927062.80..2957590.34 rows=4697 width=33) (actual time=480.563..789.980 rows=36 loops=1)
  83. Filter: (ranked_readouts.rank = 1)
  84. Rows Removed by Filter: 832641
  85. -> WindowAgg (cost=2927062.80..2945848.98 rows=939309 width=41) (actual time=480.562..767.270 rows=832677 loops=1)
  86. -> Sort (cost=2927062.80..2929411.07 rows=939309 width=33) (actual time=480.549..525.889 rows=832677 loops=1)
  87. Sort Key: metrius_readout.meter_id, metrius_readout."timestamp" DESC
  88. Sort Method: quicksort Memory: 89629kB
  89. -> Bitmap Heap Scan on metrius_readout (cost=24414.56..2833877.52 rows=939309 width=33) (actual time=86.977..195.763 rows=832677 loops=1)
  90. Recheck Cond: ((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[])) AND ("timestamp" < '2022-07-19 00:00:00+02'::timestamp with time zone))
  91. Filter: (NOT ignored)
  92. Rows Removed by Filter: 40
  93. Heap Blocks: exact=48446
  94. -> Bitmap Index Scan on metrius_readout_meter_id_timestamp_desc (cost=0.00..24179.62 rows=949276 width=0) (actual time=29.529..29.529 rows=832717 loops=1)
  95. Index Cond: ((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[])) AND ("timestamp" < '2022-07-19 00:00:00+02'::timestamp with time zone))
  96. Planning Time: 0.180 ms
  97. JIT:
  98. Functions: 12
  99. Options: Inlining true, Optimization true, Expressions true, Deforming true
  100. Timing: Generation 0.800 ms, Inlining 2.734 ms, Optimization 28.138 ms, Emission 20.926 ms, Total 52.599 ms
  101. Execution Time: 795.707 ms
  102. (20 rows)

Edit this Snippet