Re: sequential scan result order vs performance

On 10/30/16 9:12 AM, Tom Lane wrote:
> I think there will be a lot of howls.  People expect that creating
> a table by inserting a bunch of rows, and then reading back those
> rows, will not change the order.  We already futzed with that guarantee
> a bit with syncscans, but that only affects quite large tables --- and
> even there, we were forced to provide a way to turn it off.

Leaving a 30% performance improvement on the floor because some people 
don't grok how sets work seems insane to me.

We could have a GUC to disable this. I suspect ORDER BY ctid would be 
another option.

BTW, I've sometimes wished for a mode where queries would silently have 
result ordering intentionally futzed, to eliminate any possibility of 
dependence on tuple ordering (as well as having sequences start at some 
random value). I guess with the hooks that are in place today it 
wouldn't be hard to stick a ORDER BY random() in if there wasn't already 
a Sort node at the top level...
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461