The database is range partitioned into 24 partitions based on the ts field, the main table and the partitions are indexed on the timestamp (ts) and status fields.

Hardware spec

AMD Ryzen 7 5800x

128gb Corsair 3600 MT/s RAM

SABRENT M.2 NVMe SSD 1TB Gen 4, Internal Solid State

Debian Linux 12

PostgreSQL 15

With 380m rows in the database, this query is targeted at a specific partitions based on the timestamp field.

ts=# select * from ts where ts between ‘2023-02-09 00:00:00’ and ‘2023-02-09 06:00:00′ and status=’2xx’ order by ts limit 20;

(20 rows)

Time: 0.307 ms

ts=# select count(*) from ts where ts between ‘2023-02-09 00:00:00’ and ‘2023-02-09 06:00:00′ and status=’2xx’;

count

833067
(1 row)

Time: 91.614 ms

ts=# select status, count(*) from ts where ts between ‘2023-02-09 00:00:00’ and ‘2023-02-09 06:00:00’ group by status;
status | count
——–+——–
2xx | 833067
4xx | 356965
5xx | 119060
(3 rows)

Time: 121.393 ms

ts=# select * from ts where ts between ‘2023-01-09 00:00:00’ and ‘2023-01-09 06:00:00′ and status=’2xx’ order by ts limit 20;
id | ts | elapsed | type | key | status | esd
…. results omitted
(20 rows)

Time: 1.365 ms

ts=# explain select * from ts where ts between ‘2023-01-09 00:00:00’ and ‘2023-01-09 06:00:00′ and status=’2xx’ order by ts limit 20;

QUERY PLAN

Limit (cost=0.57..6.53 rows=20 width=165)
-> Index Scan using ts_part_0_ts_idx on ts_part_0 ts (cost=0.57..249850.66 rows=839044 width=165)
Index Cond: ((ts >= ‘2023-01-09 00:00:00’::timestamp without time zone) AND (ts <= ‘2023-01-09 06:00:00’::timestamp without time zone))
Filter: ((status)::text = ‘2xx’::text)
(4 rows)