Skip site navigation (1) Skip section navigation (2)

query results different after vacuum analyze? 7.4.0

From: Klint Gore <kg(at)kgb(dot)une(dot)edu(dot)au>
To: pgsql-general(at)postgresql(dot)org
Subject: query results different after vacuum analyze? 7.4.0
Date: 2004-05-28 04:51:55
Message-ID: 40B6C56B18C.B6E0KG@129.180.47.120 (view raw or flat)
Thread:
Lists: pgsql-general
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(at)kgb(dot)une(dot)edu(dot)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             :                 :
+---------------------------------------+-----------------+

Responses

pgsql-general by date

Next:From: HaleDate: 2004-05-28 05:21:29
Subject: Connecting to an External DB (repost)
Previous:From: Tom LaneDate: 2004-05-28 04:47:39
Subject: Re: Naive schema questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group