CODEWITHSUNDEEP
databasepostgresql
kunal
kunal

Reputation: 185

problem with limit and offset in sql?

Well I have a sorted table by id and I want to retrieve n rows offset m rows, but when I use it without orderby it is producing unpredictable results and with order by id its taking too much of time, since my table is already ordered, I just want to retrieve n rows leaving first m rows.

Upvotes: 1

Views: 2338

Answers (3)

Peter Eisentraut
Peter Eisentraut

Reputation: 36739

The documentation says:

The query planner takes LIMIT into account when generating a query plan, so you are very likely to get different plans (yielding different row orders) depending on what you use for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

So what you are trying cannot really be done.

I suppose you could play with the planner options or rearrange the query in a clever way to trick the planner into using a plan that suits you, but without actually showing the query, it's hard to say.

Upvotes: 2

Andrew Lazarus
Andrew Lazarus

Reputation: 19340

As pointed out above, SQL does not guarantee anything about order unless you have an ORDER BY clause. LIMIT can still be useful in such a situation, but I can't think of any use for OFFSET. It sounds like you don't have an index on id, because if you do, the query should be extremely fast, clustered or not. Take another look at that. (Also check CLUSTER, which may improve your performance at the margin.)

REPEAT: this is not something about Postgresql. Its behavior here is conforming.

Upvotes: 1

adorablepuppy
adorablepuppy

Reputation: 1077

SELECT * FROM mytable LIMIT 100 OFFSET 0

You really shouldn't rely on implicit ordering though, because you may not be able to predict the exact order of how data goes into the database.

Upvotes: 1

Related Questions