Optimizing indexes for large table when ordering and filtering with different columns

Question

I have a table with approximately 20 million rows and ~20 columns, and I'm struggling to index it effectively for a query that filters by one column (updated_at) and orders by a second column (measured_at). What index can I use to make this query efficient?

A typical query:

SELECT * FROM sensor_points 
WHERE updated_at >= '2022-02-11T21:00:15' 
ORDER BY measured_at ASC 
LIMIT 1000;

---

Execution times and EXPLAINs for different indexes I've tried:

1. Just an index for measured_at (required for a different query): ~3.5s.

Limit  (cost=0.44..128.80 rows=1000 width=172)
   ->  Index Scan using index_sensor_points_on_measured_at on sensor_points  (cost=0.44..1243630.81 rows=9688766 width=172)
         Filter: (updated_at >= '2022-02-11 21:00:15'::timestamp without time zone)

2. An index for measured_at and a separate index for updated_at: ~3.5s

 Limit  (cost=0.44..128.80 rows=1000 width=172)
   ->  Index Scan using index_sensor_points_on_measured_at on sensor_points  (cost=0.44..1243630.81 rows=9688766 width=172)
         Filter: (updated_at >= '2022-02-11 21:00:15'::timestamp without time zone)

3. A compound index on updated_at and measured_at (in that order): ~5s (note: I don't understand why this is higher, given that the EXPLAIN is the same)

 Limit  (cost=0.44..128.80 rows=1000 width=172)
   ->  Index Scan using index_sensor_points_on_measured_at on sensor_points  (cost=0.44..1243630.81 rows=9688766 width=172)
         Filter: (updated_at >= '2022-02-11 21:00:15'::timestamp without time zone)

4. A compound index on measured_at and updated_at (in that order): ~3.5s

 Limit  (cost=0.44..128.80 rows=1000 width=172)
   ->  Index Scan using index_sensor_points_on_measured_at on sensor_points  (cost=0.44..1243630.81 rows=9688766 width=172)
         Filter: (updated_at >= '2022-02-11 21:00:15'::timestamp without time zone)

I was surprised by the EXPLAINs being the same regardless of the index.

All of the indexes were btree indexes. For example:

CREATE INDEX index_sensor_points_on_measured_at_and_updated_at ON public.sensor_points USING btree (measured_at, updated_at);

---

Full table schema:

CREATE TABLE public.sensor_points (
    id bigint NOT NULL,
    measured_at timestamp(6) without time zone NOT NULL,
    mission_id uuid NOT NULL,
    transmission_id uuid,
    altitude double precision,
    latitude double precision,
    longitude double precision,
    pressure double precision,
    speed_x double precision,
    speed_y double precision,
    temperature double precision,
    humidity double precision,
    gfs_pressure double precision,
    gfs_wind_u double precision,
    gfs_wind_v double precision,
    gfs_temperature double precision,
    gfs_humidity double precision,
    gps_valid boolean,
    temperature_valid boolean,
    humidity_valid boolean,
    pressure_valid boolean,
    created_at timestamp(6) without time zone DEFAULT clock_timestamp() NOT NULL,
    updated_at timestamp(6) without time zone DEFAULT clock_timestamp() NOT NULL
);

---

Performance of reading matters much, much more that performance of inserting records. Ideally, this query would take on the order of milliseconds, not seconds. As a (perhaps naive) point of reference, SELECT * FROM sensor_points WHERE measured_at >= '2022-02-11T21:00:15' ORDER BY measured_at LIMIT 1000 takes only 4ms to execute.

How can I speed up a query that orders by a different column than it filters on?

Answer 1

Execution was reduced from ~3.5s to ~0.01s by creating a new column that combined both measured_at and updated_at such that it could be both filtered and ordered by the same column:

measured_and_updated_at numeric GENERATED ALWAYS AS (
  (
    (EXTRACT(EPOCH FROM updated_at) - EXTRACT(SECOND FROM updated_at))::numeric(36, 0)*1e6 + 
    (EXTRACT(MICROSECONDS FROM updated_at))::numeric(36, 0)
  ) * 1e18 + 
  (
    (EXTRACT(EPOCH FROM measured_at) - EXTRACT(SECOND FROM measured_at))::numeric(36, 0)*1e6 + 
    (EXTRACT(MICROSECONDS FROM measured_at))::numeric(36, 0)
  )
) STORED

In plain english, we have a generated column of an integer with a precision of 36 decimal places. updated_at is stored in the first 18 digits and measured_at in the second 18 digits. An index on this column was added.

This works extremely well for the use-case in the question; however, it only is useful for this single use-case. Other queries would require other approaches. As such, this likely isn't a best-practice, but it at least works.