Tom Yeh wrote:
> I created an index for a table, say Entity, on a field, say id.
> Then, the follwoing two SQL has different result:
> select * from "Entity" e where e.id = '1000'
> select * from "Entity" e where e.id like '1000'
What is the definition of "Entity"?
How many rows are there in the table?
How many match '1000'?
Are your statistics up to date?
Are you happy that you are gathering enough statistics values for the
Do you understand the issues with using LIKE and non-C locales?
> (The later uses sequential scan. BTW, while 7.4.x uses index why 8.0 behave
You don't say how the EXPLAIN ANALYSE for each is different.
> Once I re-index it, the problem is gone. However, the problem comes back
> randomly if I change some id.
By "randomly" do you mean it switches plans depending on the value you
match against, or that you can repeat the same query twice and it uses
> The above can be replicated by using pgAdmin III only.
Are you saying it doesn't do this from psql?
If you're not convinced this is an actual bug in PostgreSQL's planner,
it might be better to post details to the performance or sql lists where
there are more people to help answer.
In response to
pgsql-bugs by date
|Next:||From: Dave Page||Date: 2005-02-21 16:04:15|
|Subject: Re: BUG #1487: Index problem|
|Previous:||From: Markus Bertheau||Date: 2005-02-21 14:08:02|
|Subject: BUG #1495: RPM: wrong quoting of error message about old database format after upgrading|