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

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

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: <pgsql(at)mohawksoft(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 14:39:31
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB34101AE45@Herge.rcsinc.local (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=#

Merlin



Responses

pgsql-hackers by date

Next:From: pgsqlDate: 2004-06-01 15:16:24
Subject: Re: [pgsql-hackers-win32] select like...not using index
Previous:From: Tom LaneDate: 2004-06-01 13:51:11
Subject: Re: CVS tip problems

pgsql-hackers-win32 by date

Next:From: pgsqlDate: 2004-06-01 15:16:24
Subject: Re: [pgsql-hackers-win32] select like...not using index
Previous:From: Thomas KellererDate: 2004-06-01 06:51:37
Subject: Re: registry vs. environment (was re:binary

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