Retrieving the most recent record with skip scan is easy enough, but how do I get the timestamp of the previous record as well?

TimescaleDB and PostgreSQL
, , ,
1

Hello, I am using TSDB and want to get a set of most recent records. I only see good performance if I structure my query like so, and this shows that skip scan is being used when I run EXPLAIN ANALYZE

Planning Time: 0.777 ms
Execution Time: 10.287 ms

WITH updated_widgets AS (
    SELECT w.id
    FROM widgets AS w
    WHERE w.dirty = 1
), widget_data AS (
    SELECT DISTINCT ON (w.id) wd.*
    FROM "widget_data"  AS wd
    ORDER BY wd.mac_address, wd.time DESC
)
SELECT * FROM widget_data
WHERE wd.id IN (SELECT id FROM updated_widgets);

This is very fast. Skip scan would not trigger if I added a WHERE clause or JOIN clause, but it appears to work with a CTE.

Here’s the current issue…I need a window where I can perform some calculations using the most recent record for each widget pulled from widget data AND the record prior to that. Skip scan does not trigger if I use LAG like so:

Planning Time: 0.420 ms
Execution Time: 13079.934 ms

 widget_data AS (
    SELECT DISTINCT ON (w.id)
      wd.time,
     LAG(wd.time, 1) OVER (ORDER BY wd.time DESC) AS prev_time
    FROM "widget_data"  AS wd
    ORDER BY wd.mac_address, wd.time DESC
)

How can I get the current record AND the previous record while using skip scan? Is this even possible?

2

Hey @Anthony_Orona , that’s probably some restrictions in the Query Planner related to the time window that I’m not sure how feasible is the fix, and if we have any track of such case.

You can also open an issue labeled skip-scan.

@sven may share some knowledge about it.

3

Hello @Anthony_Orona , what indexes do you have available on the queried tables. SkipScan has specific index requirements.