Re: [HACKERS] ORDER BY optimisations

Bruce Momjian wrote:
>
> > Hallo Jan,
> >
> > Do I remember right that your pathes to speed up ORDER BYs (by
> > omitting them when not needed) did not make it into 6.4 .
> >
> > If that is the case, are they available anywhere ?
> >
> > I really need them (fast) for my new project.
>
> LIMIT will probably be added to 6.4.1.

Actually I don't need LIMIT that much (for me using CURSOR/MOVE/FETCH
is quite ok). The main benefit from LIMIT seems to be the ability
of giving the (future) optimiser a hint that we actulally need only
a small part of the whole query so it may be better to use an index.

What I am after, is the patch that removed the redundant sort node
when the access is already by an index matching the sort.

> Queries that use '%text%' can not use indexes because they are not
> anchored at the beginning.
> fulltextindex is in contrib for those cases.

It still seems a bit of a cludge, although a useful one as its usage
is quite different from the use of other indexes. It also seems to be
quite liberal with wasting space as it makes both an additional table
_and_ an index for the words it indexes.

In fact I'm currently using my own fulltext indexing scheme outside
the database. I'm planning to work on including it in the pgsql
backend, once I figure out how the extending of access methods works.

--------------
Hannu