pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Optimizing ORDER BY with indexes?
5+ messages / 3 participants
[nested] [flat]

* Optimizing ORDER BY with indexes?
@ 2000-01-14 08:41  Dirk Lutzebaeck <lutzeb@aeccom.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Dirk Lutzebaeck @ 2000-01-14 08:41 UTC (permalink / raw)
  To: pgsql-sql


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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Optimizing ORDER BY with indexes?
@ 2000-01-14 09:31  Dirk Lutzebaeck <lutzeb@aeccom.com>
  parent: Dirk Lutzebaeck <lutzeb@aeccom.com>
  0 siblings, 3 replies; 5+ messages in thread

From: Dirk Lutzebaeck @ 2000-01-14 09:31 UTC (permalink / raw)
  To: pgsql-sql

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?

Dirk




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Optimizing ORDER BY with indexes?
@ 2000-01-14 09:34  Dirk Lutzebaeck <lutzeb@aeccom.com>
  parent: Dirk Lutzebaeck <lutzeb@aeccom.com>
  2 siblings, 0 replies; 5+ messages in thread

From: Dirk Lutzebaeck @ 2000-01-14 09:34 UTC (permalink / raw)
  To: pgsql-sql

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 the Bowman 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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Optimizing ORDER BY with indexes?
@ 2000-01-14 15:18  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Dirk Lutzebaeck <lutzeb@aeccom.com>
  2 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 2000-01-14 15:18 UTC (permalink / raw)
  To: Dirk Lutzebaeck <lutzeb@aeccom.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Optimizing ORDER BY with indexes?
@ 2000-01-14 16:37  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Dirk Lutzebaeck <lutzeb@aeccom.com>
  2 siblings, 0 replies; 5+ messages in thread

From: Bruce Momjian @ 2000-01-14 16:37 UTC (permalink / raw)
  To: Dirk Lutzebaeck <lutzeb@aeccom.com>; +Cc: pgsql-sql

> 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, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2000-01-14 16:37 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-14 08:41 Optimizing ORDER BY with indexes? Dirk Lutzebaeck <lutzeb@aeccom.com>
2000-01-14 09:31 ` Dirk Lutzebaeck <lutzeb@aeccom.com>
2000-01-14 09:34   ` Dirk Lutzebaeck <lutzeb@aeccom.com>
2000-01-14 15:18   ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-14 16:37   ` Bruce Momjian <pgman@candle.pha.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox