BUG #1487: Index problem

The following bug has been logged online:

Bug reference: 1487
Logged by: Tom Yeh
Email address: tom_m_yeh(at)yahoo(dot)com
PostgreSQL version: 8.0.1
Operating system: Windows XP
Description: Index problem
Details:

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'

and

select * from "Entity" e where e.id like '1000'

(The later uses sequential scan. BTW, while 7.4.x uses index why 8.0 behave
worse?)

Once I re-index it, the problem is gone. However, the problem comes back
randomly if I change some id.

The above can be replicated by using pgAdmin III only.

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
"id" column?
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
> worse?)

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
different plans?

> 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.

--
Richard Huxton
Archonet Ltd