We have append-only
events table where events from different devices are being collected.
We use PostgreSQL 9.6 and pg_partman to split the table into monthly partitions.
Data is partitioned using
Each monthly table has about 100M rows.
There's a query from application side that is used to show latest events from a device. It looks something like this (simplified):
SELECT event_time, event_data FROM events WHERE device_id = 'zzz' ORDER BY event_time DESC LIMIT 10
Every search criteria is covered by index, so PostgreSQL does only index scans.
The problem is that it scans all child tables and only then does the
Indexes for all these tables are quite big and don't fit into memory, so it takes up to 20 seconds to complete this query.
But in most cases the 10 most recent events are available from one child table (partition) that is named
Is there any way to implement "progressive" scan for child tables inside PostgreSQL without changing application code?
events events_p2019_02 <- and so on... events_p2019_03 <- scan this if less than 10 rows found in _04 and _05 events_p2019_04 <- scan this if less than 10 rows found in events_p2019_05 events_p2019_05 <- scan this first
We don't know in advance the possible date range for that 10 events. Some device may have them during the last day, while others may have them spanned over several months (so in many child tables).