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
