I have a PostgreSQL table constructed as

device_id | point     | dt_edit
100       | geometry  | timestamp
101       | geometry  | timestamp
100       | geometry  | timestamp
102       | geometry  | timestamp
102       | geometry  | timestamp
101       | geometry  | timestamp

I need to select the last 2 records ordered by dt_edit from unique device_id. This query works very slow, on 1 billion records - 500 seconds:

 SELECT dt.device_id,
   FROM ( SELECT gps_data.device_id,
            rank() OVER (PARTITION BY gps_data.device_id
                         ORDER BY gps_data.dt_edit DESC) AS rank
           FROM gps_data) dt
  WHERE dt.rank <= 2

Any ideas?

  • 1
    Can you show \d gps_data? I'm looking for indexes on the table. – Vérace Jul 15 '15 at 5:32
  • 1
  • I assume 1 billion is the number of rows in the table, not the size of the result. What is the cardinality of device_id, i.e. how many unique device_id are there? – Lennart Jul 15 '15 at 6:29
  • can you send an "explain analyze select ..." ... it would be nice to see – Hans-Jürgen Schönig Jul 15 '15 at 10:17
  • Can we assume that a device table holding all devices exists? Also, where you use rank(), do you actually mean rank() or rather row_number() - the latter would return no more then two rows per device. Please provide a table definition and your version of Postgres. – Erwin Brandstetter Jul 15 '15 at 14:45

If we can assume a device table holding all devices of interest. Example:

CREATE TABLE device (device_id int, device text);
INSERT INTO device (device_id, device) VALUES
  (100, 'a')
, (101, 'b')
, (102, 'c');

The query can be very simple:

SELECT d.device_id, g.point, g.dt_edit
FROM   device d
,      LATERAL (
   SELECT point, dt_edit
   FROM   gps_data
   WHERE  device_id = d.device_id
   ORDER  BY dt_edit DESC NULLS LAST -- more items?
   LIMIT  2
   ) g;

Assuming a much smaller cardinality in table device than in table gps_data, i.e. many rows per device in the latter.

If dt_edit is not UNIQUE, add more items to ORDER BY to disambiguate the sort order.

All you need for this to be fast is a multicolumn index;

CREATE INDEX gps_data_foo_idx
ON gps_data (device_id, dt_edit DESC NULLS LAST); -- more items? 

The NULLS LAST modifier is useful if the column can be NULL, but does not hurt in any case. Either way, the query must match the index.

If you don't have a device table, I would suggest to create one (and keep it current).
But there are ways to make this fast even without device table ...


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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