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?
