At 03:30 PM 21/11/2002 +0100, Eelco van Beek - IC&S wrote:
Fact of the matter is that it will not change the way of which the
information is actually retrieved.
As I said - you are wrong here. Perhaps our emails crossed. Version 7.0 of
PG introduced the ability to use fast-start plans for just this sort of
case. This is a common strategy among relational database implementations.
There is a very simple way to test the hypothesis: run the ANALYZE command.
Alternatively, try the following:
create table t(f bigint, tf text);
insert into t values(1,'sdaasdasd');
-- Simulate a decent sized text chunk to make the records larger,
-- and match messageblks a little better.
update t set tf = tf || tf;
update t set tf = tf || tf;
update t set tf = tf || tf;
update t set tf = tf || tf;
update t set tf = tf || tf;
update t set tf = tf || tf;
update t set tf = tf || tf;
insert into t select f+1,tf from t;
insert into t select f+2,tf from t;
insert into t select f+4,tf from t;
insert into t select f+8,tf from t;
insert into t select f+16,tf from t;
insert into t select f+32,tf from t;
insert into t select f+64,tf from t;
insert into t select f+128,tf from t;
insert into t select f+256,tf from t;
insert into t select f+512,tf from t;
insert into t select f+1024,tf from t;
insert into t select f+2048,tf from t;
insert into t select f+4096,tf from t;
insert into t select f+8192,tf from t;
insert into t select f+16384,tf from t;
insert into t select f+32768,tf from t;
insert into t select f+65536,tf from t;
create unique index tf on t(f);
explain select * from t order by f;
explain select * from t order by f limit 1;
(I get sequential and index strategies respectively, which is exactly what
you would hope for - it uses the fact the index is sorted when it knows
there is a LIMIT)
analyze t;
explain select * from t order by f;
explain select * from t order by f limit 1;
(I get index strategies for both, probably because the index is quite compact)
So: the above shows that (a) LIMIT makes a difference, and (b) analyze
affects index choice.
I would be quite willing to put a non-trivial wager on the fact that an
ANALYZE will result in your DB working as you would hope!
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
