I'm using a PostgreSQL database (12.8).

Given events sorted by start_time, I would like to retrieve the next 10 events after the event with id = 10.

My first idea was something like this:

SELECT *
FROM events
ORDER BY start_time
WHERE id > 10
LIMIT 10;

However, this does not work because the WHERE-clause is always applied before the ORDER BY-clause. In other words, first all events are selected which have an id > 10, and only the remaining events are then ordered by start_time.

Next, I came up with a CTE expression. If the record with ID 10 has row_number 3:

WITH ordered_events AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY start_time DESC) AS row_number
  FROM events
)
SELECT *
FROM ordered_events
WHERE row_number > 3
LIMIT 10;

This works as desired, if I would know the row_number beforehand. However, I only know the ID, i.e. id = 10.

a) Ideally, I would do something like this (however, I don't know if there is any way to write such an expression):

WITH ordered_events AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY start_time DESC) AS row_number
  FROM events
)
SELECT *
FROM ordered_events
WHERE row_number > (row_number of row where the ID is 10) # <------
LIMIT 10;

b) The only alternative I came up with is to first retrieve the row_number for all records and then make a second query.

First query:

SELECT id, ROW_NUMBER() OVER (ORDER BY start_time DESC) AS row_number
FROM events;

Now I know that the row_number for the record with ID 10 is 3. Thus, I have enough information to make the query with the CTE expression as described above. However, the performance is bad because I need to retrieve all records from events in the first query.

I would really appreciate it, if you could help me to figure out a (performant) way of doing this.