Using SkipScan to retrieve the most recent value with multiple dimensions

[rwinte]

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 * FROM
  (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 * FROM
  (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;