Why PostgreSQL query give inconsistent results unless enforce a predictable result ordering with ORDER BY

Question

In PostgreSQL doc says:

The query optimizer takes LIMIT into account when generating query plans, so you are very likely to get different plans (yielding different row orders) depending on what you give 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.

But in MySQL InnoDB table, they result will have been delivered in PRIMARY KEY.

Why the query give inconsistent results? What happens in the query?

Answer 1

What the Postgres documentation and your observations are telling you is that records in SQL tables have no internal order. Instead, database tables are modeled after unordered sets of records. Hence, in the following query which could run on either MySQL or Postgres:

SELECT *
FROM yourTable
LIMIT 5

The database is free to return whichever 5 records it wants. In the case of MySQL, if you are seeing an ordering based on primary key, it is only by coincidence, and MySQL does not offer any such contract that this would always happen.

To resolve this problem, you should always be using an ORDER BY clause when using LIMIT. So the following query is well-defined:

SELECT *
FROM yourTable
ORDER BY some_column
LIMIT 5