Querying Compressed Chunks Is Inefficient In Disk Usage #8702
Description
Postgres 16, timescale 2.16.0
I am experiencing a performance issue with a simple use-case. I have a hypertable with hundreds of columns. When I query just a few columns from compressed chunks, I expect the operation to be very quick. However, the performance is unexpectedly poor.
In the below example, I am querying across 4 chunks for a total of ~6m rows. My expectation is that this will read the time, a, b, c columns only from the compressed chunks. a, b, and c are double precision floats, so I expect ~32 bytes per row to be read -> 192MB.
I am using an aws gp3 volume with 11k IOPS and 250Mbps provisioned. Thus I expect the query to take 6.144s. Ultimately, I am getting 3x that delay (18s) for the query. The operation does seem to be i/o bound, I have included the output of iostat below as well. I am getting around 19MB (152Mbps) of read throughput and using 1756 reads/sec.
My question is why is each read-op so small? 19MB / 1756 reads = 10.8KB per read op. Couldn't this be much more efficient with larger bulk reads? I would like to scale up the disk throughput to 1Gbps but it seems that the bottleneck is on the application doing too many tiny reads...
trading-g1=# explain (analyze, buffers) SELECT
a,
b,
c
FROM
my_table
WHERE
time BETWEEN '2025-09-01' AND '2025-09-07';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=1.03..36029.58 rows=5972000 width=24) (actual time=5.164..18346.300 rows=5924759 loops=1)
Buffers: shared hit=63005 read=40986
-> Custom Scan (DecompressChunk) on _hyper_19_3892_chunk (cost=1.03..1688.70 rows=1647000 width=24) (actual time=5.163..4531.840 rows=1634804 loops=1)
Vectorized Filter: (("time" >= '2025-09-01 00:00:00'::timestamp without time zone) AND ("time" <= '2025-09-07 00:00:00'::timestamp without time zone))
Buffers: shared hit=17378 read=11259
-> Seq Scan on compress_hyper_22_3934_chunk (cost=0.00..1688.70 rows=1647 width=148) (actual time=0.714..19.757 rows=1647 loops=1)
Filter: ((_ts_meta_max_1 >= '2025-09-01 00:00:00'::timestamp without time zone) AND (_ts_meta_min_1 <= '2025-09-07 00:00:00'::timestamp without time zone))
Buffers: shared read=1664
-> Custom Scan (DecompressChunk) on _hyper_19_3903_chunk (cost=1.04..1688.26 rows=1617000 width=24) (actual time=6.355..4861.852 rows=1605026 loops=1)
Vectorized Filter: (("time" >= '2025-09-01 00:00:00'::timestamp without time zone) AND ("time" <= '2025-09-07 00:00:00'::timestamp without time zone))
Buffers: shared hit=17086 read=11114
-> Seq Scan on compress_hyper_22_3936_chunk (cost=0.00..1688.26 rows=1617 width=148) (actual time=0.639..19.548 rows=1617 loops=1)
Filter: ((_ts_meta_max_1 >= '2025-09-01 00:00:00'::timestamp without time zone) AND (_ts_meta_min_1 <= '2025-09-07 00:00:00'::timestamp without time zone))
Buffers: shared read=1664
-> Custom Scan (DecompressChunk) on _hyper_19_3921_chunk (cost=1.03..1493.67 rows=1445000 width=24) (actual time=7.512..4274.524 rows=1432998 loops=1)
Vectorized Filter: (("time" >= '2025-09-01 00:00:00'::timestamp without time zone) AND ("time" <= '2025-09-07 00:00:00'::timestamp without time zone))
Buffers: shared hit=15234 read=9955
-> Seq Scan on compress_hyper_22_3946_chunk (cost=0.00..1493.67 rows=1445 width=148) (actual time=0.894..17.237 rows=1445 loops=1)
Filter: ((_ts_meta_max_1 >= '2025-09-01 00:00:00'::timestamp without time zone) AND (_ts_meta_min_1 <= '2025-09-07 00:00:00'::timestamp without time zone))
Buffers: shared read=1472
-> Custom Scan (DecompressChunk) on _hyper_19_3928_chunk (cost=1.03..1298.94 rows=1263000 width=24) (actual time=7.529..4075.067 rows=1251931 loops=1)
Vectorized Filter: (("time" >= '2025-09-01 00:00:00'::timestamp without time zone) AND ("time" <= '2025-09-07 00:00:00'::timestamp without time zone))
Buffers: shared hit=13307 read=8658
-> Seq Scan on compress_hyper_22_3963_chunk (cost=0.00..1298.94 rows=1263 width=148) (actual time=0.798..14.305 rows=1263 loops=1)
Filter: ((_ts_meta_max_1 >= '2025-09-01 00:00:00'::timestamp without time zone) AND (_ts_meta_min_1 <= '2025-09-07 00:00:00'::timestamp without time zone))
Buffers: shared read=1280
Planning:
Buffers: shared hit=20256 read=75
Planning Time: 92.184 ms
Execution Time: 18667.762 ms
(30 rows)
avg-cpu: %user %nice %system %iowait %steal %idle
2.04 0.00 0.81 5.23 0.00 91.92
Device r/s rkB/s rrqm/s %rrqm r_await rareq-sz w/s wkB/s wrqm/s %wrqm w_await wareq-sz d/s dkB/s drqm/s %drqm d_await dareq-sz f/s f_await aqu-sz %util
nvme1n1 1756.50 19986.00 0.00 0.00 0.60 11.38 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1.05 99.70