Re: [SQL] Optimizing ORDER BY with indexes?

Dirk Lutzebaeck <lutzeb(at)aeccom(dot)com> writes:
> Reading about indexes in the Bowman SQL book I think a
> _clustered index_ is needed for the problem above.
> Does Postgresql support these?

It's there, but (1) the clustering operation itself isn't really
production-grade code (it tends to drop auxiliary data about the table),
and (2) the optimizer doesn't make any allowance in its estimates for
recently clustered indexes, so it might fail to choose an indexscan when
that would actually be the best way to do things.

I've been thinking about fixing (2), but the problem is to know how long
ago the cluster operation was done --- updates to the table will
gradually destroy the cluster order, so you really want to somehow scale
the cost estimate back towards the unordered case as more and more
updates are done following a cluster. But that'd mean keeping an update
counter, which is pretty expensive in itself.

regards, tom lane