Tom Lane wrote:

> I've still got a problem with these checks; I think they are a net
> waste of cycles on average. They would only be a win if you expected a
> nontrivial percentage of perfectly-in-order or perfectly-reverse-order
> inputs. What I think is much more probable in the Postgres environment
> is almost-but-not-quite-ordered inputs --- eg, a table that was
> perfectly ordered by key when filled, but some of the tuples have since
> been moved by UPDATEs. This is the worst possible case for the in-order
> checks, because they can then grovel over large percentages of the file
> before failing ... and when they fail, those cycles are entirely wasted;
> you have not advanced the state of the sort at all.

It seems to me like people who reimport their data ordered should simply
also CLUSTER their tables (maybe CLUSTER should first check if things
are already ordered). PG should then just check of the table is
CLUSTER'ed or not. Obviously the "i am clustered flag" needs to be
removed the second you get UPDATE's (maybe with a check if the clustered
key was updated) or INSERT's (maybe with a check if the row did not end
up in the proper order). This would then cover cases where people
intentionally make sure their input is sorted, and so this would reduce
the number if situations where this check would improve performance.

regards,
Lukas

PS: Hope you guys do not object for me jumping into the discussion like
that, not being a PG hacker and all.