PostgreSQL: OFFSET 0 and 1 is not working

I am facing a weird issue with PostgreSQL query. If I set OFFSET 0 or 1 it shows Waiting for the query to complete... forever. As soon as I change OFFSET 2 or any other value it works fine.

Here is my query:

SELECT
        m.id,
        l.uuid,
        m.displacement,
        m.total_displacement
FROM
        locations l
        INNER JOIN markers m ON l.id = m.location_id
WHERE
        l.uuid = 42901 AND l.type_name = 'ascending' 
ORDER BY m.id ASC 
LIMIT 100 OFFSET 1

EXPLAIN information for

OFFSET 0:

Limit  (cost=0.85..127003.79 rows=100 width=28)
  ->  Nested Loop  (cost=0.85..215537958.57 rows=169711 width=28)
        Join Filter: (l.id = m.location_id)
        ->  Index Scan using markers_pkey on markers m  (cost=0.57..209234879.46 rows=420204720 width=24)
        ->  Materialize  (cost=0.28..8.30 rows=1 width=16)
              ->  Index Scan using locations_uuid_type_name_key on locations l  (cost=0.28..8.30 rows=1 width=16)
"                    Index Cond: ((uuid = '4294967296001'::bigint) AND ((type_name)::text = 'ascending'::text))"

OFFSET 2:

Limit  (cost=127821.43..127821.68 rows=100 width=28)
  ->  Sort  (cost=127821.43..128245.70 rows=169711 width=28)
        Sort Key: m.id
        ->  Nested Loop  (cost=0.57..121310.95 rows=169711 width=28)
              ->  Seq Scan on locations l  (cost=0.00..124.14 rows=1 width=16)
"                    Filter: ((uuid = '4294967296001'::bigint) AND ((type_name)::text = 'ascending'::text))"
              ->  Index Scan using markers_location_id_idx on markers m  (cost=0.57..118770.45 rows=241636 width=24)
                    Index Cond: (location_id = l.id)

OFFSET 0 has very heavy top-level costs.
Nested Loop (cost=0.85..215537958.57

Does anyone guess the issue?

Postgre version: PostgreSQL 13.7

PostgreSQL believes that scanning markers in id order and joining locations rows until it has found enough results is a viable strategy if few rows are needed. Obviously the optimizer is wrong in this assumption.

I have no idea how to improve the optimizer's estimates here, but you can force PostgreSQL to avoid the slow query plan by changing the ORDER BY condition to

ORDER BY m.id + 0

so that PostgreSQL cannot use an index scan to support it. PostgreSQL can only use an index for <code>ORDER BY some_expression</code> if there is an index on <code>some_expression</code>.