Robert Berger <[EMAIL PROTECTED]> writes:
> I am running 6.5.3 on a tables with >300,000 rows. I've seen lots of
> cases where queries fail if I haven't done a vacuum analyze; I
> understand this is one of the quirks of PostgreSQl.
You do? It's not a known failure mode to my knowledge --- the query
might be slow, but it should produce a correct answer.
> However, below is an example of a query that fails if I HAVE done a
> vacuum analyze.
That's not a known failure mode either.
I don't know what's going on here, but I'm guessing you've got some
fairly fundamental porting problem. What platform and compiler are
you using? Do the regression tests pass for you?
Another line of thought: the mergejoin you're showing as the culprit
is absolutely dependent on the assumption that both indexes are
sorted in the same order. Once or twice we have seen bizarre trouble
reports that turned out to be due to having started the postmaster
with different LOCALE settings at different times, so that the sort
ordering of a text-column index got completely messed up by inserts
occuring with different ideas of what the ordering should be.
If you aren't careful about starting the postmaster with the same
environment variables every time, this might be your problem.
A recovery method if you suspect this could apply is to drop and
recreate the suspect indexes.
regards, tom lane
