pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizing 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>
2000-01-14 09:31 ` Re: [SQL] Optimizing ORDER BY with indexes? 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 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 ` Re: [SQL] Optimizing ORDER BY with indexes? Dirk Lutzebaeck <lutzeb@aeccom.com>
2000-01-14 15:18 ` Re: [SQL] Optimizing ORDER BY with indexes? Tom Lane <tgl@sss.pgh.pa.us>
2000-01-14 16:37 ` Re: [SQL] Optimizing ORDER BY with indexes? Bruce Momjian <pgman@candle.pha.pa.us>
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 08:41 Optimizing ORDER BY with indexes? Dirk Lutzebaeck <lutzeb@aeccom.com>
2000-01-14 09:31 ` Re: [SQL] Optimizing ORDER BY with indexes? Dirk Lutzebaeck <lutzeb@aeccom.com>
@ 2000-01-14 09:34 ` 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 08:41 Optimizing ORDER BY with indexes? Dirk Lutzebaeck <lutzeb@aeccom.com>
2000-01-14 09:31 ` Re: [SQL] Optimizing ORDER BY with indexes? Dirk Lutzebaeck <lutzeb@aeccom.com>
@ 2000-01-14 15:18 ` Tom Lane <tgl@sss.pgh.pa.us>
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 08:41 Optimizing ORDER BY with indexes? Dirk Lutzebaeck <lutzeb@aeccom.com>
2000-01-14 09:31 ` Re: [SQL] Optimizing ORDER BY with indexes? Dirk Lutzebaeck <lutzeb@aeccom.com>
@ 2000-01-14 16:37 ` Bruce Momjian <pgman@candle.pha.pa.us>
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