469,580 Members | 1,953 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,580 developers. It's quick & easy.

query results different after vacuum analyze? 7.4.0

query is

select t2.field4, t1.*
from t1
left outer join t2 on t2.field1 = t1.field1 and t2.field2 = t1.field2

There are 55k rows in t1 (103 fields) and 10k in t2 (4 fields, 4 is text).
before vacuum analyze the query gave 10k rows like it was doing an inner
join. after vacuum analyze gave the full 55k.

t2 is a new table which probably has never been vacuum'd before. the
10k rows in t2 were entered via insert statements. there are no
triggers on t2. it has a primary key (fields 1-3). field types for
joined fields are the same. selecting from either table separately
gives the expected number of rows. only returning "t2.field4, t1.field2"
gives the correct number.

7.4.0, rh linux 7.2, p4 (non ht) cpu.

Can anyone think of a situation where vacuum analyze would change the
results of a query? Am I looking at something that's been missed in
setting up the table? Or a bug that's been since fixed? Or do I need to
try and work out a test case?

klint
+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg@kgb.une.edu.au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #1
1 1313
Klint Gore <kg@kgb.une.edu.au> writes:
There are 55k rows in t1 (103 fields) and 10k in t2 (4 fields, 4 is text).
before vacuum analyze the query gave 10k rows like it was doing an inner
join. after vacuum analyze gave the full 55k.
Sounds like a bug ...
7.4.0, rh linux 7.2, p4 (non ht) cpu.


You are of course aware that there are a lot of known bugs in 7.4.0?
Please try it on 7.4.2.

If you can still make it happen on 7.4.2 then I'd be interested in a
test case...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Holger Marzen | last post: by
16 posts views Thread by Dave Weaver | last post: by
5 posts views Thread by Carmen Gloria Sepulveda Dedes | last post: by
6 posts views Thread by Steven D.Arnold | last post: by
3 posts views Thread by Joseph Shraibman | last post: by
2 posts views Thread by lnd | last post: by
6 posts views Thread by Alex | last post: by
reply views Thread by Rajesh Kumar Mallah | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.