Re: advanced index (descending and table-presorted descending)

On 21/11/06, Vic Cekvenich <v(dot)cekvenich(at)yahoo(dot)com> wrote:
>
> Can pgSQL 8.x do descending indexes like mySQL 5.1?
> (so 1st column is descending and rest are asscending?)
>
>
> Can pgSQL 8.x do "physically" sorted table (like a forced index order) so we
> don't need order by?
>
> tia,
> .V
>
>

No and I don't really believe mySQL can if it can its a bug, I would
not trust it. If you want your results sorted you need the order
by......

However, Cluster might work for you, but you need to re-cluster after
every updates or inserts, so it will probably be fine for static data.
Which I suspect is the same for mySql but I can't be bothered to
check, If mysql really works like this its worse that I originally
thought it was. Even so I would always include the order by clause for
safety.

(Its a bug because the table(file) will always grow and grow and grow
and eventually take up the entire disk with tonnes of gaps which can
be closed without unsorting the data or rewriting the entire file ie
cluster....)

Peter.