2

I have a modestly big table of about a million rows. With static where clauses it filters down to 200,000 rows. With a typical user search criterion it delivers about 100 rows. This is for a web service hit millions of times per hour. I want to keep the database load as small as possible. So cannot afford an order by. But I need to be able to scroll through the result set, with OFFSET / LIMIT clauses. However, for the first time in my life, I notice that for this query the results come out in a different order every single time I run it (on a system with no other activity). I know that there is no guarantee of any particular ordering in SQL, but I am used to the order being at least stable when the criteria don't change and we only use OFFSET and LIMIT.

First I thought it was the parallel query, but even after I turned that off, single threaded table scan

Limit  (cost=0.00..316982.62 rows=25 width=471)
  ->  Seq Scan on mytable (cost=0.00..316982.62 rows=25 width=471) 
        Filter: ...

I suppose it's a bad idea that I don't use any indexes but only filters. Perhaps that's the anomaly here. But even so, what is the special optimization that PostgreSQL uses that makes it produce a different result all the time? And that seems to be the reason.

But regardless, is there a way I can make sure whatever table scan uses the same whatever random but stable ordering from its table scan?

15
  • 2
    Rows in a table have no implied sort order. The only way to have a guaranteed sort order is to use order by. There is no alternative - especially if you use OFFSET/LIMIT.
    – user1822
    Jul 26, 2022 at 6:00
  • 2
    If you need to rely on a specific sort order, the only way to get that is to use ORDER BY. There is no alternative. There is no such thing as a "usually stable order". If you have observed the same sort order repeatedly, then that was pure coincidence.
    – user1822
    Jul 26, 2022 at 8:49
  • 1
    Well, I repeat myself because you don't accept that the only way to get what you want is to use an ORDER BY. There is absolutely no guarantee whatsoever for the database to return the rows in any deterministic order without that.
    – user1822
    Jul 26, 2022 at 16:40
  • 1
    You wrote "Only that the order doesn't change" which means a consistent (repeatable, deterministic, always the same) sort order.
    – user1822
    Jul 26, 2022 at 20:23
  • 1
    I define "stable order" that the same order comes out when running the same query multiple times when none of the underlying tables have been changed. Jul 27, 2022 at 6:53

2 Answers 2

3

Why it does it is probably due to synchronize_seqscans. Now go have fun shooting yourself in the feet with even more vigor.

2
  • Thanks for the hint about synchronize_seqscans, I will check that out. What the point of all that nastiness is, I don't know. Jul 26, 2022 at 19:23
  • It seems right, with set synchronize_seqscans=off, the order doesn't change anymore. I had this behavior with select a, b from table limit bignum (ex 50000). We cannot see it in dbeaver because dbeaver adds an implicit limit on each query, we must use psql or python to reproduce it
    – Kiruahxh
    Aug 22, 2023 at 14:45
1

No, the answer is a definite NO.

There is only one way to ensure consistent and repeated order. Using ORDER BY.

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.