From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruce Momjian <maillist@candle.pha.pa.us>
Cc: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
Cc: tjk@tksoft.com, "Roderick A. Anderson" <raanders@altoplanos.net>
Cc: oleg@sai.msu.su
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] trivial problem
Date: Fri, 29 Oct 1999 19:37:49 -0400
Message-ID: <3670.941240269@sss.pgh.pa.us> (raw)
In-Reply-To: <199910292019.QAA07363@candle.pha.pa.us>
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> FAQ says:
> See the <I>fetch</I> manual page, or use SELECT ... LIMIT....<P>
> This only prevents all row results from being transferred to the client.
> The entire query must be evaluated, even if you only want just the first
> few rows. Consider a query that has an <I>order by.</I> There is no way
> to return any rows until the entire query is evaluated and sorted.<P>
That FAQ entry is not right. It might be right for FETCH, but not for
SELECT ... LIMIT. With a LIMIT, the executor will stop once it has
generated the requested number of rows. Of course, how much computation
is needed to reach that point depends greatly on the query and the query
plan.
If an explicit sort step is being used to implement ORDER BY, then the
FAQ is correct. But if the ORDER BY is implemented by an index scan,
rather than an explicit sort step, then presto: we just run the
indexscan for the first N tuples and stop. (6.5.* is not very bright
about avoiding a sort step, but current sources are much better.)
Still to be done: modify the optimizer to be aware of this fact so that
it will be more likely to choose an indexscan when a small LIMIT is
present. Right now it chooses indexscan vs. explicit sort on the basis
of cost to return all the tuples, which is bogus if there's a LIMIT.
Ideally it'd also take LIMIT into account when choosing join types,
etc...
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, maillist@candle.pha.pa.us, moray.mcconnachie@computing-services.oxford.ac.uk, raanders@altoplanos.net, oleg@sai.msu.su
Subject: Re: [SQL] trivial problem
In-Reply-To: <3670.941240269@sss.pgh.pa.us>
References: <199910292019.QAA07363@candle.pha.pa.us> <3670.941240269@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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