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

Re: [pgsql-hackers-win32] select like...not using index

From: pgsql(at)mohawksoft(dot)com
To: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
Cc: "PgSql-Win32" <pgsql-hackers-win32(at)postgresql(dot)org>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: [pgsql-hackers-win32] select like...not using index
Date: 2004-06-01 15:16:24
Message-ID: 16804.24.91.171.78.1086102984.squirrel@mail.mohawksoft.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-hackers-win32
>> > Sometime between yesterday and today queries in the form of
>> > "select * from t where t.f like 'k%'" have been broken so that they
>> > never use the index (on win32, not sure about others).
>> >
>> > On win32, at least, they have been broken for a while but this was
> due
>> > to a known issue based on the locales.  AFAICT, the current cvs has
>> > addressed this issue and (show lc_collate returns C) there seems to
> be
>> > no reason why the queries aren't working properly.
>> >
>> > Merlin
>>
>> Did you do an ANALYZE on the table?
> Yes.  Just for kicks, I also drop/rc the index...no help.  Following
> that, I ran a fresh initdb which reported:
> The database cluster will be initialized with locale English_United
> States.1252.
>
> I then ran I just recently had the same issue (due to locale problems).
> This was recently fixed in cvs and replaced the hack I was using to work
> around the problem.  The index search no longer works and I am very
> suspicious about a locale related issue.  This is all off of a fresh
> copy of 7.5devel from the anonymous cvs server.
>
>> Are there a lot of duplicate keys?
>> How big is the table?
> About 250k with less than 1% duplicatation.
>> What does the "explain" look like?
>
> cpc=# explain select * from hchassis where vin_no = '2FTZX08W8WCA24365';
>                                       QUERY PLAN
> ------------------------------------------------------------------------
> --------------
>  Index Scan using hchassis_vin_no_idx on hchassis  (cost=0.00..8.94
> rows=2 width=437)
>    Index Cond: (vin_no = '2FTZX08W8WCA24365'::bpchar)
> (2 rows)
>
> cpc=# explain select * from hchassis where vin_no like
> '2FTZX08W8WCA24365%';
>                           QUERY PLAN
> --------------------------------------------------------------
>  Seq Scan on hchassis  (cost=0.00..19577.70 rows=1 width=437)
>    Filter: (vin_no ~~ '2FTZX08W8WCA24365%'::text)
> (2 rows)
>
> cpc=#

It looks to me like you have an index of type "bpchar" but are searching
with type "text." I find type conversions very limited with "LIKE."

I would create an index on 'vin_no' using a cast to TEXT. This should work
on both queries.


In response to

pgsql-hackers by date

Next:From: Merlin MoncureDate: 2004-06-01 15:28:00
Subject: Re: [pgsql-hackers-win32] select like...not using index
Previous:From: Merlin MoncureDate: 2004-06-01 14:39:31
Subject: Re: [pgsql-hackers-win32] select like...not using index

pgsql-hackers-win32 by date

Next:From: Merlin MoncureDate: 2004-06-01 15:28:00
Subject: Re: [pgsql-hackers-win32] select like...not using index
Previous:From: Merlin MoncureDate: 2004-06-01 14:39:31
Subject: Re: [pgsql-hackers-win32] select like...not using index

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