On 19 May 2002, Andrew McMillan wrote:
> On Sun, 2002-05-19 at 11:18, mike wrote:
> > Andrew McMillan writes:
> > > PostgreSQL will certainly take LIMIT into account when planning queries.
> >
> > I would think most queries using LIMIT would also include an ORDER BY clause
> > which would require PostgreSQL to sort all the results before applying the
> > LIMIT.
>
> Then you would be wrong. ORDER BY can also be handled by an index, if
> there is an appropriate one, and this is where PostgreSQL will switch
> query plans because traversing an index to grab a few hundred records is
> much faster than a sequential scan and sort of many thousands.
It's not so easy. Often a single seq. scan (assuming it turns out into
very few seq. read(2) from a file) provides MUCH more bandwidth than
a set of random reads, given how modern disk performs (I've seen >40MB/s
seq. read rates, paired with <2MB/s for random reads).
Say you have a 40MB table, with 10000 records. Accessing 500 records
in random order may take longer that reading all 10000 sequentially.
This is expecially true for small tables, of course: the various cache
systems (whole tracks by the disk firmware, and OS read ahead and page
cache) may end up in reading the whole table anyway, even if you're
doing an index scan for very few records. It could even be a good idea
to delete indexes at all on small tables.
But I agree that, on big tables, an index may speed up ORDER BY clause a lot.
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it