1

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)
  1. 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)
  1. 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)
  1. 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?

2
  • 2
    The execution plans generated using explain (analyze, buffers) would be more helpful
    – user1822
    Dec 16, 2022 at 6:44
  • It just keeps using its favorite index. over and over. If you want to force it to use a different index, you could drop the one is likes to use so it can't use it anymore.
    – jjanes
    Dec 17, 2022 at 1:06

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.