Retrieving a fixed range of rows from Postgresql

I have a query:

select qid,ansid,ans from table1 where askerid='something'

This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the next 50 rows should be retrieved. I have thought of a query like this :

select qid,ansid,ans 
from table1 
where askerid='something' 
limit 100
minus 
select qid,ansid,ans 
from table1 
where askerid='something' 
limit 50

I want to know if this is a good query performance-wise and alternate queries that perform better than the above one.

The proper way to do this is using LIMIT and OFFSET.

Note that for this to work properly, your query will need an ORDER BY clause so that the rows are fetched in the same order each time.

Your first query would be:

SELECT qid,ansid,ans 
FROM table1 
WHERE askerid='something'
LIMIT 50 OFFSET 0

The second query would have an OFFSET of 50, as you want to skip the first 50 rows:

SELECT qid,ansid,ans
FROM table1 
WHERE askerid='something' 
LIMIT 50 OFFSET 50

The ORDER BY clause wants adding between the WHERE and LIMIT clauses.

The Postgres documentation on this is here, and mentions that this may not be performant for large OFFSET values. It will be ok for small-ish datasets if the ORDER BY column is indexed.

StackExchange Database Administrators Q#30427, answer score: 9