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, "Magnus Hagander" <mha(at)sollentuna(dot)net>
Subject: Re: [pgsql-hackers-win32] select like...not using index
Date: 2004-06-02 00:34:44
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-hackers-win32
>> 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.
> Not in this case.  Just to be sure, I created a new column as text type,
> created index, analyzed, and searched and got the same behavior.

Hmmm, snipped from your reply was the explain plan from the query where it
was clear you were using two different character data types: bpchat and
text. That, alone, may have been a problem.

Looking at your defaults, did you do:

initdb --locale=C somepath

I found, at some point, 'C' used to be the default, now it seems initdb
wants to fish out what locale your system is using.

Personally, I think, if I do not specify a locale, I don't want a specific
locale. Period. I haven't been paying too close attention to the hackers
list to say when this happened, but it bit me a couple times.

> Furthermore, I did this:
> cpc=# show lc_collate;
>  lc_collate
> ------------
>  C
> (1 row)
> cpc=# show lc_ctype;
>  lc_ctype
> ----------
>  C
> (1 row)
> followed by this:
> C:\postgres\pgsql\src\test\locale>pg_controldata
> [...]
> LC_COLLATE:                           English_United States.1252
> LC_CTYPE:                             English_United States.1252
> At this point I'm about 90% sure I've turned up a locale related
> bug...initdb warned me wrt the locale but psql is still reporting 'C'.
> Plus, my queries don't work where they used to about a week ago.  My
> next step is to initdb --locale=C to confirm this.  I've informed Magnus
> about this and he is looking into it.
> Merlin

In response to

pgsql-hackers by date

Next:From: Lamar OwenDate: 2004-06-02 01:31:36
Subject: Re: Official Freeze Date for 7.5: July 1st, 2004
Previous:From: Neil ConwayDate: 2004-06-01 23:49:54
Subject: Re: Converting postgresql.conf parameters to kilobytes

pgsql-hackers-win32 by date

Next:From: Bruce MomjianDate: 2004-06-02 03:19:26
Subject: Re: postgres snapshot compile woes...!
Previous:From: Bruce MomjianDate: 2004-06-01 20:16:32
Subject: Re: nested transaction patch build?

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