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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom Yeh wrote:
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?

You don't say how the EXPLAIN ANALYSE for each is different.

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?

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

=20

pgAdmin uses libpq, just as psql does, so I cannot imagine why this
would be the case. pgAdmin also does not do anything to try to affect
query plans in any way.

Regards, Dave.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Dave Page wrote:
Could it set the encoding/locale differently to psql (on the same machine)?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

=20

Good point, yes - it will try to set the encoding to unicode if it can.

/D

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom M. Yeh wrote:
Search the list archives for LIKE and locale - different locales have
different sorting rules. Setting locale=C makes everything simple.

With 8.0 you can define your own operator classes to tell PG it can use
an index (see ch 11.6 of the manuals). I think there are examples in the
mailing-list archives.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Yes, it really depends on Locale. If I created a
database with Locale = C, the problem won't happen (at
least so far). BTW, I forgot to mention I tested with
some Chinese and Japanese characters.

However, it raises another issue why a wrong Locale
would damage index? It shall only affect records with
wrong conversion, not something like '1000'.

The real environment in my program is J2EE+JDBC. I
simply used pgAdmin to demonstrate the problem (not
caused by JDBC or so).

Though Locale C solved the problem, LIKE 'prefix%'
still uses Seq Scan. It is a job to change hundreds
(if not thousands SQL) to use BETWEEN/AND instead. Any
suggestion?

-----Original Message-----
From: Dave Page [mailto:dpage (AT) vale-housing (DOT) co.uk]
Sent: Tuesday, February 22, 2005 12:19 AM
To: Richard Huxton
Cc: Tom Yeh; pgsql-bugs (AT) postgresql (DOT) org
Subject: RE: [BUGS] BUG #1487: Index problem




Good point, yes - it will try to set the encoding to
unicode if it can.

/D




__________________________________
Do you Yahoo!?
The all-new My Yahoo! - Get yours free!
http://my.yahoo.com



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly