Postgres Pro
Downloads
Home   >   mailing lists

Thread: Optimizing ORDER BY with indexes?

Optimizing ORDER BY with indexes?

From
Dirk Lutzebaeck
Date:
I'm forced to used ORDER BY on large tables which then takes quite
long. Is there a chance to create/optimize an index for ORDER BY?
Or could I write an index method on my own? How would it look like?

Are there any other possiblities to speed up ORDER BY?

Dirk

Re: [SQL] Optimizing ORDER BY with indexes?

From
Dirk Lutzebaeck
Date:
Dirk Lutzebaeck writes:> > I'm forced to used ORDER BY on large tables which then takes quite> long. Is there a chance
tocreate/optimize an index for ORDER BY?> Or could I write an index method on my own? How would it look like?> > Are
thereany other possiblities to speed up ORDER BY?
 

Reading about indexes in the Bowman SQL book I think a 
_clustered index_ is needed for the problem above.
Does Postgresql support these?

Dirk

Re: [SQL] Optimizing ORDER BY with indexes?

From
Dirk Lutzebaeck
Date:
Dirk Lutzebaeck writes:> Dirk Lutzebaeck writes:>  > >  > I'm forced to used ORDER BY on large tables which then takes
quite> > long. Is there a chance to create/optimize an index for ORDER BY?>  > Or could I write an index method on my
own?How would it look like?>  > >  > Are there any other possiblities to speed up ORDER BY?> > Reading about indexes in
theBowman SQL book I think a > _clustered index_ is needed for the problem above.> Does Postgresql support these?
 

Ok, I'm talking to my self here:

man cluster

RTFM.

Dirk

Re: [SQL] Optimizing ORDER BY with indexes?

From
Tom Lane
Date:
Dirk Lutzebaeck <lutzeb@aeccom.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

Re: [SQL] Optimizing ORDER BY with indexes?

From
Bruce Momjian
Date:
> Dirk Lutzebaeck writes:
>  > 
>  > I'm forced to used ORDER BY on large tables which then takes quite
>  > long. Is there a chance to create/optimize an index for ORDER BY?
>  > Or could I write an index method on my own? How would it look like?
>  > 
>  > Are there any other possiblities to speed up ORDER BY?
> 
> Reading about indexes in the Bowman SQL book I think a 
> _clustered index_ is needed for the problem above.
> Does Postgresql support these?
> 

CLUSTER command.

--  Bruce Momjian                        |  http://www.op.net/~candle pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026