dpaste/GegB1 (SQL)

Comparison with previous snippet

--- Previous Snippet
+++ Current Snippet
@@ -1 +1,29 @@
+-- without seq_scan enabled
+Subquery Scan on ranked_readouts (cost=29951050.70..54579200.96 rows=840744 width=33) (actual time=689.271..1075.429 rows=36 loops=1)
+ Filter: (ranked_readouts.rank = 1)
+ Rows Removed by Filter: 832641
+ -> WindowAgg (cost=29951050.70..52477342.03 rows=168148715 width=41) (actual time=689.270..1050.556 rows=832677 loops=1)
+ -> Gather Merge (cost=29951050.70..49534739.51 rows=168148715 width=33) (actual time=689.254..789.413 rows=832677 loops=1)
+ Workers Planned: 2
+ Workers Launched: 0
+ -> Sort (cost=29950050.67..30125205.59 rows=70061965 width=33) (actual time=688.973..742.501 rows=832677 loops=1)
+ Sort Key: metrius_readout.meter_id, metrius_readout."timestamp" DESC
+ Sort Method: quicksort Memory: 89629kB
+ -> Parallel Bitmap Heap Scan on metrius_readout (cost=2696913.34..18904471.14 rows=70061965 width=33) (actual time=145.713..376.225 rows=832677 loops=1)
+ 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[]))
+ Filter: ((NOT ignored) AND ("timestamp" < '2022-07-19 00:00:00+02'::timestamp with time zone))
+ Rows Removed by Filter: 1502214
+ Heap Blocks: exact=86065
+ -> Bitmap Index Scan on metrius_readout_meter_id (cost=0.00..2654876.04 rows=249543049 width=0) (actual time=71.511..71.512 rows=2334891 loops=1)
+ 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[]))
+ Planning Time: 0.271 ms
+ JIT:
+ Functions: 12
+ Options: Inlining true, Optimization true, Expressions true, Deforming true
+ Timing: Generation 1.200 ms, Inlining 3.082 ms, Optimization 32.731 ms, Emission 24.207 ms, Total 61.219 ms
+ Execution Time: 1079.415 ms
+
+
+---------------
+
WITH ranked_readouts AS (
@@ -108 +136,4 @@
(19 rows)
+
+
+

-- without seq_scan enabled
Subquery Scan on ranked_readouts (cost=29951050.70..54579200.96 rows=840744 width=33) (actual time=689.271..1075.429 rows=36 loops=1)
Filter: (ranked_readouts.rank = 1)
Rows Removed by Filter: 832641
-> WindowAgg (cost=29951050.70..52477342.03 rows=168148715 width=41) (actual time=689.270..1050.556 rows=832677 loops=1)
-> Gather Merge (cost=29951050.70..49534739.51 rows=168148715 width=33) (actual time=689.254..789.413 rows=832677 loops=1)
Workers Planned: 2
Workers Launched: 0
-> Sort (cost=29950050.67..30125205.59 rows=70061965 width=33) (actual time=688.973..742.501 rows=832677 loops=1)
Sort Key: metrius_readout.meter_id, metrius_readout."timestamp" DESC
Sort Method: quicksort Memory: 89629kB
-> Parallel Bitmap Heap Scan on metrius_readout (cost=2696913.34..18904471.14 rows=70061965 width=33) (actual time=145.713..376.225 rows=832677 loops=1)
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[]))
Filter: ((NOT ignored) AND ("timestamp" < '2022-07-19 00:00:00+02'::timestamp with time zone))
Rows Removed by Filter: 1502214
Heap Blocks: exact=86065
-> Bitmap Index Scan on metrius_readout_meter_id (cost=0.00..2654876.04 rows=249543049 width=0) (actual time=71.511..71.512 rows=2334891 loops=1)
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[]))
Planning Time: 0.271 ms
JIT:
Functions: 12
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 1.200 ms, Inlining 3.082 ms, Optimization 32.731 ms, Emission 24.207 ms, Total 61.219 ms
Execution Time: 1079.415 ms
---------------
WITH ranked_readouts AS (
SELECT
id,
meter_id,
timestamp,
value,
ignored,
RANK() OVER (
PARTITION BY meter_id
ORDER BY
timestamp DESC
) AS rank
FROM
metrius_readout
WHERE
meter_id IN (
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
)
AND timestamp < '2022-07-19 00:00:00'
AND ignored = 'f'
)
SELECT
id,
meter_id,
timestamp,
value,
ignored
FROM
ranked_readouts
WHERE
rank = 1;
Subquery Scan on ranked_readouts (cost=28529507.44..53157657.71 rows=840744 width=33) (actual time=71912.721..72328.174 rows=36 loops=1)
Filter: (ranked_readouts.rank = 1)
Rows Removed by Filter: 832641
-> WindowAgg (cost=28529507.44..51055798.77 rows=168148715 width=41) (actual time=71912.719..72301.004 rows=832677 loops=1)
-> Gather Merge (cost=28529507.44..48113196.26 rows=168148715 width=33) (actual time=71912.697..72015.758 rows=832677 loops=1)
Workers Planned: 2
Workers Launched: 0
-> Sort (cost=28528507.42..28703662.33 rows=70061965 width=33) (actual time=71912.522..71964.651 rows=832677 loops=1)
Sort Key: metrius_readout.meter_id, metrius_readout."timestamp" DESC
Sort Method: quicksort Memory: 89629kB
-> 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)
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[])))
Rows Removed by Filter: 1061018541
Planning Time: 0.234 ms
JIT:
Functions: 10
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.832 ms, Inlining 3.378 ms, Optimization 31.716 ms, Emission 23.972 ms, Total 59.897 ms
Execution Time: 72332.692 ms
(19 rows)

Edit this Snippet

Syntax

Expires

Right-to-Left

Content -- without seq_scan enabled Subquery Scan on ranked_readouts (cost=29951050.70..54579200.96 rows=840744 width=33) (actual time=689.271..1075.429 rows=36 loops=1) Filter: (ranked_readouts.rank = 1) Rows Removed by Filter: 832641 -> WindowAgg (cost=29951050.70..52477342.03 rows=168148715 width=41) (actual time=689.270..1050.556 rows=832677 loops=1) -> Gather Merge (cost=29951050.70..49534739.51 rows=168148715 width=33) (actual time=689.254..789.413 rows=832677 loops=1) Workers Planned: 2 Workers Launched: 0 -> Sort (cost=29950050.67..30125205.59 rows=70061965 width=33) (actual time=688.973..742.501 rows=832677 loops=1) Sort Key: metrius_readout.meter_id, metrius_readout."timestamp" DESC Sort Method: quicksort Memory: 89629kB -> Parallel Bitmap Heap Scan on metrius_readout (cost=2696913.34..18904471.14 rows=70061965 width=33) (actual time=145.713..376.225 rows=832677 loops=1) 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[])) Filter: ((NOT ignored) AND ("timestamp" < '2022-07-19 00:00:00+02'::timestamp with time zone)) Rows Removed by Filter: 1502214 Heap Blocks: exact=86065 -> Bitmap Index Scan on metrius_readout_meter_id (cost=0.00..2654876.04 rows=249543049 width=0) (actual time=71.511..71.512 rows=2334891 loops=1) 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[])) Planning Time: 0.271 ms JIT: Functions: 12 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 1.200 ms, Inlining 3.082 ms, Optimization 32.731 ms, Emission 24.207 ms, Total 61.219 ms Execution Time: 1079.415 ms --------------- WITH ranked_readouts AS ( SELECT id, meter_id, timestamp, value, ignored, RANK() OVER ( PARTITION BY meter_id ORDER BY timestamp DESC ) AS rank FROM metrius_readout WHERE meter_id IN ( 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 ) AND timestamp < '2022-07-19 00:00:00' AND ignored = 'f' ) SELECT id, meter_id, timestamp, value, ignored FROM ranked_readouts WHERE rank = 1; Subquery Scan on ranked_readouts (cost=28529507.44..53157657.71 rows=840744 width=33) (actual time=71912.721..72328.174 rows=36 loops=1) Filter: (ranked_readouts.rank = 1) Rows Removed by Filter: 832641 -> WindowAgg (cost=28529507.44..51055798.77 rows=168148715 width=41) (actual time=71912.719..72301.004 rows=832677 loops=1) -> Gather Merge (cost=28529507.44..48113196.26 rows=168148715 width=33) (actual time=71912.697..72015.758 rows=832677 loops=1) Workers Planned: 2 Workers Launched: 0 -> Sort (cost=28528507.42..28703662.33 rows=70061965 width=33) (actual time=71912.522..71964.651 rows=832677 loops=1) Sort Key: metrius_readout.meter_id, metrius_readout."timestamp" DESC Sort Method: quicksort Memory: 89629kB -> 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) 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[]))) Rows Removed by Filter: 1061018541 Planning Time: 0.234 ms JIT: Functions: 10 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 0.832 ms, Inlining 3.378 ms, Optimization 31.716 ms, Emission 23.972 ms, Total 59.897 ms Execution Time: 72332.692 ms (19 rows)