I just discussed this with Roel which refreshed some extra information.
The result subset (a LIMIT clause) als always known to the scanner
because it may need to return only one or a few tuples. Fact of the
matter is that it will not change the way of which the information is
actually retrieved.
Blah blah blah blah :)
I'm just curious what the pgsql mailinglist will reply.
Best regards,
Eelco
On donderdag, nov 21, 2002, at 15:20 Europe/Amsterdam, Eelco van Beek -
IC&S wrote:
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
_______________________________________________
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
