Using SkipScan to retrieve the most recent value with multiple dimensions

Using SkipScan to retrieve the most recent value with multiple dimensions

A question was recently asked on Twitter about finding the most recent set of values based on two columns. While one of the fastest ways to do this in TimescaleDB can often be to use SkipScan, facilitated by DISTINCT ON(), the current implementation only allows skipping over one indexed column, not two.

As an example, consider a simple table that tracks (at least) these columns for financial data.

ts timestamptz NOT NULL,
supply_rate float4 NULL,
borrow_rate float4 NULL,
manual float4 null,
exchange TEXT NOT NULL,

With data populated, it might be tempting to create a corresponding index and then query the DISTINCT of both columns for the timestamp like this:

-- Create supporting index
CREATE INDEX test_ss ON ticks (exchange, symbol, ts DESC);

SELECT DISTINCT ON (exchange, symbol) * FROM ticks
ORDER BY exchange, symbol, ts DESC;

Unfortunately, this DISTINCT query will chose a regular Index Scan instead of the TimescaleDB SkipScan implementation. Even though there is an index that appears to support a distinct query on multiple columns, SkipScan will only consider the first column of the index (as of TimescaleDB 2.7).

Depending on the cardinality of your data, there are a few options for querying this data using SkipScan and both columns.

First, let’s create the indexes that we want TimescaleDB to use regardless of which approach we take to query the data.

CREATE INDEX exchange_ss ON ticks (exchange, ts DESC);
CREATE INDEX symbol_ss ON ticks (symbol, exchange, ts desc);

With the indexes created, let’s look at each option.

In both examples below, we’re pulling the values for exchange and symbol out of the time-series hypertable rather than a supporting dimension table. This could be simplified if there was a small, already “distinct” table that tracks exchanges for instance, so YMMV depending on your schema design.

Two DISTINCT queries using LATERAL

In this example, we can use a DISTINCT ON query to get the list of exchanges that exist in the ticks table and then “loop” (LATERAL) over the inner DISTINCT ON query using the value of the exchange. This SQL is just a little bit longer to write, but works well as long as the proper indexes exist.

  (SELECT DISTINCT ON (exchange) exchange FROM ticks ORDER BY exchange, ts DESC) a,
   LATERAL (SELECT DISTINCT ON (symbol) symbol, ts, supply_rate, borrow_rate, manual FROM ticks 
         WHERE exchange = ORDER BY symbol, ts DESC) b;

Function to query DISTINCT rows per exchange

This approach does hide some SQL in a function to clean up the day-to-day querying. Notice that we don’t have to use a LATERAL query here because the function is run once per exchange and returns multiple rows, which creates an implicit CROSS JOIN so that many rows are produced for each exchange.

-- first create the function that will return all rows for a specific
-- exchange/symbol combination.
TABLE (ts timestamptz, supply_rate float4, borrow_rate float4, manual float4, symbol text)
AS $$
   select distinct on (symbol) ts, supply_rate, borrow_rate, manual, symbol from ticks
   where exchange=$1
   ORDER BY symbol, ts DESC;

-- Now query the function with a DISTINCT on exchange
WITH s1 AS (
   SELECT DISTINCT (exchange) exchange FROM ticks ORDER BY exchange, ts DESC
SELECT exchange, (symbol_array(exchange)).* FROM s1;

In this example, we first get a list of exchanges from the data table using SkipScan (in case there are many of them!) and then use that value to query the function we created which uses skip scan and filters the data to a specific exchange using the second index symbol_ss using SkipScan further.

The key is that for SkipScan to be selected, DISTINCT ON() can only contain one column and the ORDER BY columns must batch an existing index and index order.


I assume with a small enough hypertable, it’s more efficient to allow Postgres to do a typical index scan rather than add the lateral join to the query. So far with about 1.1 million records in my test database (I realize this is a small number of records, but the table is growing), the index scan is faster than skip scan with the lateral join. The normal query with index scan takes around 1 minute. The skip scan with a single lateral join is taking around 3.3 seconds. I’d like to add an additional lateral to perform a distinct on 3 columns, but it slows the query down to 5.8 seconds.

Another solution I’m considering is creating an additional column that’s a hash of my 3 columns on which I need to perform the distinct and using that hash column for my query. It would increase the size of the data in my table, but I would think the performance increase would make up for the increased storage space used.

Welcome to the forum @rwinte,

It would be really helpful to see an example schema and query (with the indexes you’ve created). On 1.1 million rows, it’s hard to imagine any of these queries taking more than a second unless you have really high cardinality.

Is it possible for you to provide some examples to see if we can provide some better input?


Thank you for the welcome, @ryanbooz.

I made a copy of my table and changed a few column names to provide an example that’s essentially the same table as what I’m working with.

CREATE TABLE IF NOT EXISTS public.skipscan_test
    "time" timestamp with time zone,
    unique_id uuid,
    src_ip character varying COLLATE pg_catalog."default",
    dest_ip character varying COLLATE pg_catalog."default",
    src_port integer,
    dest_port integer,
    item_index integer,
    item_value double precision,
    item_type character varying COLLATE pg_catalog."default",
    item_secondary_time timestamp with time zone,
    size integer

CREATE INDEX skipscan_test_time_idx
    ON public.skipscan_test USING btree
    ("time" DESC NULLS LAST)

CREATE INDEX skipscan_src_ip_idx
    ON public.skipscan_test USING btree
    (src_ip ASC NULLS LAST, "time" DESC NULLS LAST)

CREATE INDEX IF NOT EXISTS skipscan_src_port_idx
    ON public.skipscan_test USING btree
    (src_port ASC NULLS LAST, src_ip ASC NULLS LAST, "time" DESC NULLS LAST)

CREATE INDEX IF NOT EXISTS skipscan_recent_values_idx
    ON public.skipscan_test USING btree
    (src_ip ASC NULLS LAST, src_port ASC NULLS LAST, item_index ASC NULLS LAST, "time" DESC NULLS LAST)

This is the same structure as the table I’m using in my application. I’ve tried some variations of the indexes depending on whether I’m performing one or two laterals in my query.

With my original table, Timescale is performing a custom (skip) scan of the data, but for some reason I can’t even get it to perform the custom scan with the setup above, only the normal Postgres index scan.

I have about 2.2 million rows in this table.

The following are the two queries I’ve tried. They did perform a skip scan on my original table, although slow.

  (SELECT DISTINCT ON (src_ip) src_ip FROM skipscan_test ORDER BY src_ip, time DESC) a,
     LATERAL (SELECT DISTINCT ON (item_index) item_index, time, item_value, src_ip, src_port FROM skipscan_test
			  WHERE src_ip = a.src_ip
			  ORDER BY item_index, time DESC) c;

  (SELECT DISTINCT ON (src_ip) src_ip FROM skipscan_test ORDER BY src_ip, time DESC) a,
     LATERAL (SELECT DISTINCT ON (src_port) src_port FROM skipscan_test
			  WHERE src_ip = a.src_ip ORDER BY src_port, time DESC) b,
     LATERAL (SELECT DISTINCT ON (item_index) item_index, time, item_value, src_ip, src_port FROM skipscan_test
			  WHERE src_ip = a.src_ip AND src_port = b.src_port
			  ORDER BY item_index, time DESC) c;

Hi @ryanbooz,

UPDATE: Resolved. The indexes were sorting on “time DESC NULLS LAST” but the queries were sorting on “NULLS FIRST” by default. I was creating most of my indexes using the GUI in pgadmin, which has “NULLS LAST” selected by default in a drop down. The indexes that worked had “NULLS FIRST” by default because I was creating them by hand in the query window. I assume it’s by design that Timescale won’t perform skipscan if the index is “resorted” during the query.

I’ve determined my issue has something to do with the sorting of the “time” column. I created a new copy of my application table and create the indexes from scratch and was able to successfully perform skipscan querying using two LATERAL statements.

However, with the table structure from my post above, I notice that Postgres is insisting on performing a sort after both index scans. If I remove the “time DESC” from the ORDER BY clauses, the sort is removed and the custom scan is performed. I don’t understand why this would be since the indexes already sort on “time DESC”?

I guess I need to do some more reading on what Postgres is doing, but maybe you have a better understanding of what might be happening here?

Howdy @rwinte,

So yes, by default B-tree indexes sort with NULLs first. I haven’t tested it but this sounds a bit like what would happen with an implicit cast on a data type that then impacts the ability of Postgres to not use the index. It almost seems like you “literally” asked for the index one way, the query was asking for another, and our code is using the default (NULLs FIRST) which means the order doesn’t “match” and so SkipScan isn’t chosen. I’d have to play more to make sure. I’d be curious if you put ‘NULLS LAST’ in your ORDER BY clause if SkipScan was then chosen. Essentially the order of the columns has to match exactly or be exactly opposite for SkipScan to use the index, but it seems like it’s not working if you don’t specify the order of NULLs to match too.

I also wanted to mention the queries you showed. Unless I’m misunderstanding something, I think there’s a slight misunderstanding of how SkipScan works with the index (and how DISTINCT gets involved). Looking at the first example query, I don’t believe you need any lateral here, which would require TimescaleDB to iterate the total number of distinct items and do a second index scan rather than just let the SkipScan node work the index directly. Minor difference, but it probably has a moderate impact on overall performance.

  (SELECT DISTINCT ON (src_ip) src_ip FROM skipscan_test ORDER BY src_ip, time DESC) a,
     LATERAL (SELECT DISTINCT ON (item_index) item_index, time, item_value, src_ip, src_port FROM skipscan_test
			  WHERE src_ip = a.src_ip
			  ORDER BY item_index, time DESC) c;

Should just become the following. This (should) iterate every src_ip by the most recent time, pull out all values from that row, and then move on. It should use the skipscan_src_ip_idx index for this.

SELECT DISTINCT ON (src_ip) item_index, time, item_value, src_ip, src_port FROM skipscan_test
ORDER BY src_ip, time DESC

For the second query, it looks like you want distinct “last point” values for each src_ip and src_port. Since SkipScan doesn’t yet work with multiple front columns (before the time column), I think you have the right idea, just one too many laterals…

  (SELECT DISTINCT ON (src_ip) src_ip FROM skipscan_test ORDER BY src_ip, time DESC) a,
     (SELECT DISTINCT ON (src_port) item_index, time, item_value, src_ip, src_port FROM 
      WHERE src_ip = a.src_ip ORDER BY src_port, time DESC) b;


Thank you for the response!

I tried this and your thinking is correct. Changing the index and query to “DESC NULLS LAST” causes the SkipScan to be chosen.

I’m not sure if I follow your suggestion. I wonder if you’re assuming that my item_index column is a unique id for the row? If that’s your reasoning, the item_index in my example is not unique, but is an identifier for the value specified in the item_value column, so each time an IP provides data, you would see the same item_index’s again.

For my query, I want to know the most recent value from the source IP and port for each item_index that comes from that source IP. Hopefully that makes sense.

If I’m wrong and I don’t need the additional lateral, I’m definitely curious what I’m not understanding from your post.

Thanks for the additional info. I misunderstood a bit, partially because of the index setup in your example. (not your fault, just threw me off a bit)

The primary piece regarding SkipScan is that the leading column must match the DISTINCT ON for an index to be chosen. This means that the ultimate goal of getting the latest item_index per src_ip and src_port don’t benefit once you get to the last query.

So, yes, because there wasn’t an index with item_index as a leading column, I assumed you really just wanted the most recent row for ip/port.

Without knowing your data and overall cardinality of ips, ports, and item_index, my first reaction is that I’m not 100% sure this is the right approach for what you want. It probably will be faster than some other alternatives you could come up with for some time, but I wonder if this isn’t an opportunity to have a composite (alternative) key in this table instead. Meaning, you could combine all three columns (ip/port/item_index) into one column using something like a generated column that is then indexed by time descending. Then you’d just have one DISTINCT ON query which would use the index, you wouldn’t have to use any laterals, and you’d get the most recent value for everything.

I don’t know if there’s a way to do that with the incoming data (and I’ll be honest that I haven’t tried this with SkipScan yet… just never thought about it), but reducing the number of index scans you have to loop through would be ideal.

I’m not sure what your response time is right now with these queries (even when SkipScan is involved), and I don’t want to dictate a design. I’m just trying to think about the long-term as rows and cardinality go up.