Distinct result with row_id for limit query
Would seem like you do not need any CTE, subquery or even JOIN at all. Just an EXISTS semi-join and an additional OFFSET:

SELECT name
     , MIN(score) AS s
     , ROW_NUMBER() OVER (ORDER BY MIN(score)) AS n  -- still needed?
FROM   sample1 a
WHERE  EXISTS (SELECT FROM sample2 s2 WHERE s2.mode = a.mode)
GROUP  BY name
ORDER  BY s
OFFSET $1
LIMIT  $2;


A large OFFSET is always costly, as all the leading, discarded rows have to be considered in the ordering first.

If it's a read-only table, you could add a MATERIALIZED VIEW with an index on the row number and query with SELECT * FROM mv WHERE n &gt; $1 AND n &lt;= $1 + $2.
Would help most for big $1 and small $2.