Обсуждение: BUG #1487: Index problem
The following bug has been logged online: Bug reference: 1487 Logged by: Tom Yeh Email address: tom_m_yeh@yahoo.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
=20 > -----Original Message----- > From: pgsql-bugs-owner@postgresql.org=20 > [mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Richard Huxton > Sent: 21 February 2005 15:37 > To: Tom Yeh > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #1487: Index problem >=20 > > The above can be replicated by using pgAdmin III only. >=20 > Are you saying it doesn't do this from psql? 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.
Dave Page wrote: > >>-----Original Message----- >>From: pgsql-bugs-owner@postgresql.org >>[mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Richard Huxton >>Sent: 21 February 2005 15:37 >>To: Tom Yeh >>Cc: pgsql-bugs@postgresql.org >>Subject: Re: [BUGS] BUG #1487: Index problem >> >> >>>The above can be replicated by using pgAdmin III only. >> >>Are you saying it doesn't do this from psql? > > > 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. Could it set the encoding/locale differently to psql (on the same machine)? -- Richard Huxton Archonet Ltd
=20 > -----Original Message----- > From: Richard Huxton [mailto:dev@archonet.com]=20 > Sent: 21 February 2005 16:13 > To: Dave Page > Cc: Tom Yeh; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #1487: Index problem >=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=20 > to affect > > query plans in any way. >=20 > Could it set the encoding/locale differently to psql (on the=20 > same machine)? Good point, yes - it will try to set the encoding to unicode if it can. /D
Tom M. Yeh wrote: > 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'. 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
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@vale-housing.co.uk] Sent: Tuesday, February 22, 2005 12:19 AM To: Richard Huxton Cc: Tom Yeh; pgsql-bugs@postgresql.org Subject: RE: [BUGS] BUG #1487: Index problem > -----Original Message----- > From: Richard Huxton [mailto:dev@archonet.com] > Sent: 21 February 2005 16:13 > To: Dave Page > Cc: Tom Yeh; pgsql-bugs@postgresql.org > Subject: Re: [BUGS] BUG #1487: Index problem > > > 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. > > Could it set the encoding/locale differently to psql (on the > same machine)? 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