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