dmigowski@ikoffice.de writes:
> However, when I also want to order by id:
> order by prep_natural_sort(d.number) ASC,
> d.id ASC
> it does a sequential scan.
Sure. That index doesn't satisfy this sort order. (It could have
gotten chosen anyway, if the partial-index predicate were selective
enough, but evidently it isn't.)
> This is a bit stupid, also because the relevant data could be fetched very
> fast by the first order-by expression, and then the results could be ordered
> again, which is then much faster than doing a full sequential scan on the
> data.
That's an unsupported assertion, which we'd have to write a great deal
of code before we could even test. There are a lot of more useful
places to spend hacking time, with greater assurance of the work not
being wasted.
regards, tom lane