Hi Philip,
For what i know a LIMIT instruction should never result in a different
query execution scheme. A limit is always a query result subset and
will never involve the actual query itself. Subsequently analyze won't
be able to optimize anything because a query with limit clause cannot
be more efficient.
I think this is a postgresql scanner bug :)
Best regards,
Eelco
On donderdag, nov 21, 2002, at 15:14 Europe/Amsterdam, Philip Warner
wrote:
At 01:05 AM 22/11/2002 +1100, Philip Warner wrote:
I have confirmed that I get the same strategy as you on an empty DB;
I think you should run an ANALYZE then try the query again. We run an
analyze every night.
I should point out that as of recent versions of PG (7.1?), ANALYZE is
a lot cheaper than it used to be: current implementations take a
random sampling of data from each table file -- they no longer scan
the entire database.
It samples a statistically relevant proportion of the data to build a
profile, which the planner/optimizer then uses. Without running it,
you will definitely see odd behaviour, most likely based on a near
empty database.
The optimizer output from your earlier queries seems to indicate that
it thinks the messageblks table has 1453 rows. If the database is
really 5GB, then I think it is planning based on very old analysis.
----------------------------------------------------------------
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 |/
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
_________________________
E.J.A. van Beek
ICT Manager
IC&S
T: +31 30 2322878
F: +31 30 2322305
PGP-key:
www.ic-s.nl/keys/eelco.txt