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

Re: BUG #1487: Index problem

From: Richard Huxton <dev(at)archonet(dot)com>
To: Tom Yeh <tom_m_yeh(at)yahoo(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1487: Index problem
Date: 2005-02-21 15:36:57
Message-ID: 421A0019.8020801@archonet.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Dave PageDate: 2005-02-21 16:04:15
Subject: Re: BUG #1487: Index problem
Previous:From: Markus BertheauDate: 2005-02-21 14:08:02
Subject: BUG #1495: RPM: wrong quoting of error message about old database format after upgrading

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