Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create table public.values_with_variable_view( id integer NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,  intvalue bigint);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (1000,'2024-01-04 01:07:10.554192+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (2000,'2024-01-04 01:08:09.267336+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (999,'2024-01-04 01:08:10.554191+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (1999,'2024-01-04 01:09:09.267335+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (998,'2024-01-04 01:09:10.554191+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (1998,'2024-01-04 01:10:09.267335+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (997,'2024-01-04 01:10:10.55419+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (1997,'2024-01-04 01:11:09.267335+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (996,'2024-01-04 01:11:10.55419+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (1996,'2024-01-04 01:12:09.267335+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (995,'2024-01-04 01:12:10.55419+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (1995,'2024-01-04 01:13:09.267334+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (994,'2024-01-04 01:13:10.554189+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (1994,'2024-01-04 01:14:09.267334+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (993,'2024-01-04 01:14:10.554189+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (1993,'2024-01-04 01:15:09.267334+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (992,'2024-01-04 01:15:10.554189+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (1992,'2024-01-04 01:16:09.267334+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (991,'2024-01-04 01:16:10.554188+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (1991,'2024-01-04 01:17:09.267333+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (990,'2024-01-04 01:17:10.554188+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (1990,'2024-01-04 01:18:09.267333+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (989,'2024-01-04 01:18:10.554188+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (1989,'2024-01-04 01:19:09.267333+00',10);
INSERT INTO public."values_with_variable_view"(id, "timestamp",  intvalue) VALUES (988,'2024-01-04 01:19:10.554187+00',10);

CREATE INDEX values_with_variable_view_idx
   ON public.values_with_variable_view(id, timestamp);

-- select * from values_with_variable_view;

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

CREATE INDEX

--Keyset (this works - using postgres tuple comparison)
EXPLAIN (ANALYZE)
SELECT *
FROM public.values_with_variable_view
WHERE ( timestamp, id) > ('2024-01-04 01:12:09.267335+00',1996)
ORDER BY ( timestamp, id) asc
LIMIT 100;
  
SELECT *
FROM public.values_with_variable_view
WHERE ( timestamp, id) > ('2024-01-04 01:12:09.267335+00',1996)
ORDER BY ( timestamp, id) asc
LIMIT 100;

QUERY PLAN

Limit  (cost=1.50..1.52 rows=8 width=52) (actual time=0.080..0.083 rows=15 loops=1)

  ->  Sort  (cost=1.50..1.52 rows=8 width=52) (actual time=0.079..0.080 rows=15 loops=1)

        Sort Key: (ROW("timestamp", id))

        Sort Method: quicksort  Memory: 27kB

        ->  Seq Scan on values_with_variable_view  (cost=0.00..1.38 rows=8 width=52) (actual time=0.013..0.017 rows=15 loops=1)

              Filter: (ROW("timestamp", id) > ROW('2024-01-04 01:12:09.267335+00'::timestamp with time zone, 1996))

              Rows Removed by Filter: 10

Planning Time: 0.530 ms

Execution Time: 0.152 ms

EXPLAIN

id
timestamp
intvalue

995
2024-01-04 01:12:10.55419+00
10

1995
2024-01-04 01:13:09.267334+00
10

994
2024-01-04 01:13:10.554189+00
10

1994
2024-01-04 01:14:09.267334+00
10

993
2024-01-04 01:14:10.554189+00
10

1993
2024-01-04 01:15:09.267334+00
10

992
2024-01-04 01:15:10.554189+00
10

1992
2024-01-04 01:16:09.267334+00
10

991
2024-01-04 01:16:10.554188+00
10

1991
2024-01-04 01:17:09.267333+00
10

990
2024-01-04 01:17:10.554188+00
10

1990
2024-01-04 01:18:09.267333+00
10

989
2024-01-04 01:18:10.554188+00
10

1989
2024-01-04 01:19:09.267333+00
10

988
2024-01-04 01:19:10.554187+00
10

SELECT 15

--alternate to make it work with LINQ (might not be best performance - see below)
EXPLAIN (ANALYZE)
select * from values_with_variable_view
  where (timestamp = '2024-01-04 01:12:09.267335+00' and id > 1996)
  or timestamp > '2024-01-04 01:12:09.267335+00'
  order by timestamp, id
LIMIT 100;

select * from values_with_variable_view
  where (timestamp = '2024-01-04 01:12:09.267335+00' and id > 1996)
  or timestamp > '2024-01-04 01:12:09.267335+00'
  order by timestamp, id
LIMIT 100;

QUERY PLAN

Limit  (cost=1.58..1.60 rows=9 width=20) (actual time=0.021..0.024 rows=15 loops=1)

  ->  Sort  (cost=1.58..1.60 rows=9 width=20) (actual time=0.020..0.022 rows=15 loops=1)

        Sort Key: "timestamp", id

        Sort Method: quicksort  Memory: 26kB

        ->  Seq Scan on values_with_variable_view  (cost=0.00..1.44 rows=9 width=20) (actual time=0.012..0.014 rows=15 loops=1)

              Filter: ((("timestamp" = '2024-01-04 01:12:09.267335+00'::timestamp with time zone) AND (id > 1996)) OR ("timestamp" > '2024-01-04 01:12:09.267335+00'::timestamp with time zone))

              Rows Removed by Filter: 10

Planning Time: 0.137 ms

Execution Time: 0.040 ms

EXPLAIN

id
timestamp
intvalue

995
2024-01-04 01:12:10.55419+00
10

1995
2024-01-04 01:13:09.267334+00
10

994
2024-01-04 01:13:10.554189+00
10

1994
2024-01-04 01:14:09.267334+00
10

993
2024-01-04 01:14:10.554189+00
10

1993
2024-01-04 01:15:09.267334+00
10

992
2024-01-04 01:15:10.554189+00
10

1992
2024-01-04 01:16:09.267334+00
10

991
2024-01-04 01:16:10.554188+00
10

1991
2024-01-04 01:17:09.267333+00
10

990
2024-01-04 01:17:10.554188+00
10

1990
2024-01-04 01:18:09.267333+00
10

989
2024-01-04 01:18:10.554188+00
10

1989
2024-01-04 01:19:09.267333+00
10

988
2024-01-04 01:19:10.554187+00
10

SELECT 15

--alternate to make it work with LINQ (might be better performance)
EXPLAIN (ANALYZE)
select * from values_with_variable_view
  where timestamp >= '2024-01-04 01:12:09.267335+00'              -- index part
  and (timestamp > '2024-01-04 01:12:09.267335+00' or id > 1996)  -- filter part
  order by timestamp, id
LIMIT 100;

select * from values_with_variable_view
  where timestamp >= '2024-01-04 01:12:09.267335+00'              -- index part
  and (timestamp > '2024-01-04 01:12:09.267335+00' or id > 1996)  -- filter part
  order by timestamp, id
LIMIT 100;

QUERY PLAN

Limit  (cost=1.50..1.51 rows=5 width=20) (actual time=0.019..0.022 rows=15 loops=1)

  ->  Sort  (cost=1.50..1.51 rows=5 width=20) (actual time=0.018..0.020 rows=15 loops=1)

        Sort Key: "timestamp", id

        Sort Method: quicksort  Memory: 26kB

        ->  Seq Scan on values_with_variable_view  (cost=0.00..1.44 rows=5 width=20) (actual time=0.010..0.013 rows=15 loops=1)

              Filter: (("timestamp" >= '2024-01-04 01:12:09.267335+00'::timestamp with time zone) AND (("timestamp" > '2024-01-04 01:12:09.267335+00'::timestamp with time zone) OR (id > 1996)))

              Rows Removed by Filter: 10

Planning Time: 0.078 ms

Execution Time: 0.037 ms

EXPLAIN

id
timestamp
intvalue

995
2024-01-04 01:12:10.55419+00
10

1995
2024-01-04 01:13:09.267334+00
10

994
2024-01-04 01:13:10.554189+00
10

1994
2024-01-04 01:14:09.267334+00
10

993
2024-01-04 01:14:10.554189+00
10

1993
2024-01-04 01:15:09.267334+00
10

992
2024-01-04 01:15:10.554189+00
10

1992
2024-01-04 01:16:09.267334+00
10

991
2024-01-04 01:16:10.554188+00
10

1991
2024-01-04 01:17:09.267333+00
10

990
2024-01-04 01:17:10.554188+00
10

1990
2024-01-04 01:18:09.267333+00
10

989
2024-01-04 01:18:10.554188+00
10

1989
2024-01-04 01:19:09.267333+00
10

988
2024-01-04 01:19:10.554187+00
10

SELECT 15
id	timestamp	intvalue
995	2024-01-04 01:12:10.55419+00	10
1995	2024-01-04 01:13:09.267334+00	10
994	2024-01-04 01:13:10.554189+00	10
1994	2024-01-04 01:14:09.267334+00	10
993	2024-01-04 01:14:10.554189+00	10
1993	2024-01-04 01:15:09.267334+00	10
992	2024-01-04 01:15:10.554189+00	10
1992	2024-01-04 01:16:09.267334+00	10
991	2024-01-04 01:16:10.554188+00	10
1991	2024-01-04 01:17:09.267333+00	10
990	2024-01-04 01:17:10.554188+00	10
1990	2024-01-04 01:18:09.267333+00	10
989	2024-01-04 01:18:10.554188+00	10
1989	2024-01-04 01:19:09.267333+00	10
988	2024-01-04 01:19:10.554187+00	10