Skip to content

Instantly share code, notes, and snippets.

@akuzm
Created February 29, 2024 10:49
without sparse minmax index:
test=# explain (analyze, buffers, costs off) select count(*) from ht_metrics_compressed where value < 0.001;
QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Finalize Aggregate (actual time=317.225..317.229 rows=1 loops=1)
Buffers: shared hit=59221
-> Append (actual time=0.046..317.224 rows=5 loops=1)
Buffers: shared hit=59221
-> Partial Aggregate (actual time=0.045..0.046 rows=1 loops=1)
Buffers: shared hit=2
-> Custom Scan (DecompressChunk) on _hyper_2_1_chunk (actual time=0.044..0.045 rows=0 loops=1)
Vectorized Filter: (value < '0.001'::double precision)
Rows Removed by Filter: 36000
Buffers: shared hit=2
-> Seq Scan on compress_hyper_4_328_chunk (actual time=0.003..0.006 rows=40 loops=1)
Buffers: shared hit=2
-> Partial Aggregate (actual time=25.440..25.441 rows=1 loops=1)
Buffers: shared hit=3116
-> Custom Scan (DecompressChunk) on _hyper_2_2_chunk (actual time=0.031..15.741 rows=388359 loops=1)
Vectorized Filter: (value < '0.001'::double precision)
Rows Removed by Filter: 5659641
Buffers: shared hit=3116
-> Seq Scan on compress_hyper_4_329_chunk (actual time=0.002..0.273 rows=6050 loops=1)
Buffers: shared hit=202
-> Partial Aggregate (actual time=160.952..160.953 rows=1 loops=1)
Buffers: shared hit=18937
-> Custom Scan (DecompressChunk) on _hyper_2_7_chunk (actual time=0.093..88.131 rows=2678634 loops=1)
Vectorized Filter: (value < '0.001'::double precision)
Rows Removed by Filter: 3369366
Buffers: shared hit=18937
-> Seq Scan on compress_hyper_4_330_chunk (actual time=0.005..0.274 rows=6050 loops=1)
Buffers: shared hit=75
-> Partial Aggregate (actual time=106.923..106.923 rows=1 loops=1)
Buffers: shared hit=19076
-> Custom Scan (DecompressChunk) on _hyper_2_9_chunk (actual time=0.334..63.265 rows=1557966 loops=1)
Vectorized Filter: (value < '0.001'::double precision)
Rows Removed by Filter: 4489065
Buffers: shared hit=19076
-> Seq Scan on compress_hyper_4_331_chunk (actual time=0.004..0.284 rows=6051 loops=1)
Buffers: shared hit=75
-> Partial Aggregate (actual time=23.854..23.855 rows=1 loops=1)
Buffers: shared hit=18090
-> Custom Scan (DecompressChunk) on _hyper_2_14_chunk (actual time=0.107..23.846 rows=45 loops=1)
Vectorized Filter: (value < '0.001'::double precision)
Rows Removed by Filter: 5148934
Buffers: shared hit=18090
-> Seq Scan on compress_hyper_4_332_chunk (actual time=0.004..0.243 rows=5152 loops=1)
Buffers: shared hit=65
Planning:
Buffers: shared hit=97
Planning Time: 0.274 ms
Execution Time: 317.268 ms
with sparse minmax indes:
test=# explain (analyze, buffers, costs off) select count(*) from ht_metrics_compressed where value < 0.001;
QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Finalize Aggregate (actual time=262.733..262.745 rows=1 loops=1)
Buffers: shared hit=16055
-> Append (actual time=0.014..262.738 rows=5 loops=1)
Buffers: shared hit=16055
-> Partial Aggregate (actual time=0.013..0.015 rows=1 loops=1)
Buffers: shared hit=2
-> Custom Scan (DecompressChunk) on _hyper_2_1_chunk (actual time=0.012..0.014 rows=0 loops=1)
Vectorized Filter: (value < '0.001'::double precision)
Buffers: shared hit=2
-> Seq Scan on compress_hyper_4_323_chunk (actual time=0.012..0.012 rows=0 loops=1)
Filter: (_ts_meta_v2_min_3 < '0.001'::double precision)
Rows Removed by Filter: 40
Buffers: shared hit=2
-> Partial Aggregate (actual time=20.502..20.504 rows=1 loops=1)
Buffers: shared hit=1734
-> Custom Scan (DecompressChunk) on _hyper_2_2_chunk (actual time=0.027..10.886 rows=388359 loops=1)
Vectorized Filter: (value < '0.001'::double precision)
Rows Removed by Filter: 77641
Buffers: shared hit=1734
-> Seq Scan on compress_hyper_4_324_chunk (actual time=0.003..0.394 rows=466 loops=1)
Filter: (_ts_meta_v2_min_3 < '0.001'::double precision)
Rows Removed by Filter: 5584
Buffers: shared hit=215
-> Partial Aggregate (actual time=150.575..150.580 rows=1 loops=1)
Buffers: shared hit=8748
-> Custom Scan (DecompressChunk) on _hyper_2_7_chunk (actual time=0.022..77.125 rows=2678634 loops=1)
Vectorized Filter: (value < '0.001'::double precision)
Rows Removed by Filter: 136966
Buffers: shared hit=8748
-> Seq Scan on compress_hyper_4_325_chunk (actual time=0.006..0.498 rows=2816 loops=1)
Filter: (_ts_meta_v2_min_3 < '0.001'::double precision)
Rows Removed by Filter: 3234
Buffers: shared hit=94
-> Partial Aggregate (actual time=90.823..90.825 rows=1 loops=1)
Buffers: shared hit=5331
-> Custom Scan (DecompressChunk) on _hyper_2_9_chunk (actual time=0.035..46.744 rows=1557966 loops=1)
Vectorized Filter: (value < '0.001'::double precision)
Rows Removed by Filter: 139928
Buffers: shared hit=5331
-> Seq Scan on compress_hyper_4_326_chunk (actual time=0.012..0.429 rows=1699 loops=1)
Filter: (_ts_meta_v2_min_3 < '0.001'::double precision)
Rows Removed by Filter: 4352
Buffers: shared hit=94
-> Partial Aggregate (actual time=0.806..0.807 rows=1 loops=1)
Buffers: shared hit=240
-> Custom Scan (DecompressChunk) on _hyper_2_14_chunk (actual time=0.031..0.803 rows=45 loops=1)
Vectorized Filter: (value < '0.001'::double precision)
Rows Removed by Filter: 44955
Buffers: shared hit=240
-> Seq Scan on compress_hyper_4_327_chunk (actual time=0.008..0.325 rows=45 loops=1)
Filter: (_ts_meta_v2_min_3 < '0.001'::double precision)
Rows Removed by Filter: 5107
Buffers: shared hit=80
Planning:
Buffers: shared hit=103
Planning Time: 0.572 ms
Execution Time: 262.810 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment