Return to Question

Edit:
I must confess, I'm not really familiar with SQL, I tried to write a query which includes also the sorting, order and limit. I cam out with followings:

WITH result_set 
AS (
   SELECT {prefix}.id AS mainId, ROW_NUMBER () OVER (PARTITION BY {prefix}.id), {query} ORDER BY {orderBy} {order}
) 
SELECT mainId 
FROM result_set 
WHERE row_number = 1 
OFFSET {offset} 
LIMIT {paginationOptions.ItemsPerPage}

It works, but the performance on the same table with 1mil rows, dropped to 8sec. I fear ROW_NUMBER is responsible for.
In fact I have a dynamic query, in the result I need just the ordered ids. How can I improve the query?

Edit:
I ended up with a query which loads the paged data in 2,2sec over a table with 1mil rows. Is it an acceptable result?

Edit:
I ended up with a query which loads the paged data in 2,2sec over a table with 1mil rows. Is it an acceptable result?

Edit:
I must confess, I'm not really familiar with SQL, I tried to write a query which includes also the sorting, order and limit. I cam out with followings:

WITH result_set 
AS (
   SELECT {prefix}.id AS mainId, ROW_NUMBER () OVER (PARTITION BY {prefix}.id), {query} ORDER BY {orderBy} {order}
) 
SELECT mainId 
FROM result_set 
WHERE row_number = 1 
OFFSET {offset} 
LIMIT {paginationOptions.ItemsPerPage}

It works, but the performance on the same table with 1mil rows, dropped to 8sec. I fear ROW_NUMBER is responsible for.
In fact I have a dynamic query, in the result I need just the ordered ids. How can I improve the query?

Edit:
I ended up with a query which loads the paged data in 2,2sec over a table with 1mil rows. Is it an acceptable result?