5

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.

1 Answer 1

9

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.

1
  • Yeah it says that large offsets can be inefficient. Then suppose I have 10000 rows and I am retrieving 100 rows at a time. Then for the last 100 rows offset - 9900 and limit - 100. So then what is the efficient option for such situations.
    – Ashwin
    Dec 14, 2012 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.