Thread: Regexps and Indices.
Hi,
I'm sure that this has come up many times before but :
I have just upgraded from 7.0.2 to 7.2.2 using the latest Mandrake 9.0 RPMS.
The database structure is unchanged but I now find that the db refuses to use
the available index WHERE NAME ~ '^NAME' even with enable_seqscan set off. Am
I missing something ?
Table "t_patient"
Column | Type |
Modifiers
----------------------+-----------------------+---------------------------------------------------
prn | integer | not null default
nextval('patient_prn_seq'::text)
caseno | character(14) | not null
surname | character varying(20) | not null
forename | character varying(16) |
dob | date |
approx_date | boolean |
sex | character(1) |
hospital | character(4) |
ward | character(4) |
cons_type | character(1) |
cons_attr | character(4) |
consultant | text |
maiden_name | character varying(20) |
nhs_no | character varying(16) |
pat_address | text |
cardinal_blood_group | character varying(16) |
displist | character(8) |
Indexes: t_patient_caseno,
t_patient_mn_fn,
t_patient_surname_forename
Unique keys: t_patient_prn
pathology=# explain select * from t_patient where surname ~ '^SMIT';
NOTICE: QUERY PLAN:
Seq Scan on t_patient (cost=100000000.00..100000440.89 rows=64 width=245)
EXPLAIN
pathology=# set enable_seqscan to off;
SET VARIABLE
pathology=# explain select * from t_patient where surname ~ '^SMIT';
NOTICE: QUERY PLAN:
Seq Scan on t_patient (cost=100000000.00..100000440.89 rows=64 width=245)
EXPLAIN
It's probaly because you only have 64 rows.
If you have more, you need to rerun vacuum analyze;
Jon
On Tue, 22 Apr 2003, Brian Piatkus wrote:
> Hi,
> I'm sure that this has come up many times before but :
>
> I have just upgraded from 7.0.2 to 7.2.2 using the latest Mandrake 9.0 RPMS.
> The database structure is unchanged but I now find that the db refuses to use
> the available index WHERE NAME ~ '^NAME' even with enable_seqscan set off. Am
> I missing something ?
>
>
> Table "t_patient"
> Column | Type |
> Modifiers
> ----------------------+-----------------------+---------------------------------------------------
> prn | integer | not null default
> nextval('patient_prn_seq'::text)
> caseno | character(14) | not null
> surname | character varying(20) | not null
> forename | character varying(16) |
> dob | date |
> approx_date | boolean |
> sex | character(1) |
> hospital | character(4) |
> ward | character(4) |
> cons_type | character(1) |
> cons_attr | character(4) |
> consultant | text |
> maiden_name | character varying(20) |
> nhs_no | character varying(16) |
> pat_address | text |
> cardinal_blood_group | character varying(16) |
> displist | character(8) |
>
> Indexes: t_patient_caseno,
> t_patient_mn_fn,
> t_patient_surname_forename
> Unique keys: t_patient_prn
>
> pathology=# explain select * from t_patient where surname ~ '^SMIT';
> NOTICE: QUERY PLAN:
>
> Seq Scan on t_patient (cost=100000000.00..100000440.89 rows=64 width=245)
>
> EXPLAIN
> pathology=# set enable_seqscan to off;
> SET VARIABLE
> pathology=# explain select * from t_patient where surname ~ '^SMIT';
> NOTICE: QUERY PLAN:
>
> Seq Scan on t_patient (cost=100000000.00..100000440.89 rows=64 width=245)
>
> EXPLAIN
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
On Tue, 22 Apr 2003, Brian Piatkus wrote: > Hi, > I'm sure that this has come up many times before but : > > I have just upgraded from 7.0.2 to 7.2.2 using the latest Mandrake 9.0 RPMS. > The database structure is unchanged but I now find that the db refuses to use > the available index WHERE NAME ~ '^NAME' even with enable_seqscan set off. Am > I missing something ? It's possible that you initialized the database in something other than "C" locale which disables the optimization that uses indexes for regexp/like (see past discussions in the archives for details).
Hi It turns out to be more than possible. Locale is set by default to en_GB. Am I right in assuming that I need only to set LANG=C ? and should I also do this for the environment of the running postmaster ? On Tuesday 22 Apr 2003 20:59, you wrote: > On Tue, 22 Apr 2003, Brian Piatkus wrote: > > Hi, > > I'm sure that this has come up many times before but : > > > > I have just upgraded from 7.0.2 to 7.2.2 using the latest Mandrake 9.0 > > RPMS. The database structure is unchanged but I now find that the db > > refuses to use the available index WHERE NAME ~ '^NAME' even with > > enable_seqscan set off. Am I missing something ? > > It's possible that you initialized the database in something other than > "C" locale which disables the optimization that uses indexes for > regexp/like (see past discussions in the archives for details). > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Brian Piatkus <Brian@fulcrum.plus.com> writes:
> It turns out to be more than possible. Locale is set by default to en_GB.
> Am I right in assuming that I need only to set LANG=C ? and should I also do
> this for the environment of the running postmaster ?
You need to re-do initdb with LANG=C in its environment. Pain in the
neck, I know.
regards, tom lane
BTW, What **IS** the the language 'C'? I don't talk to people like:
'if( !happy(honey)){
what_went_wrong_today();
}'
;-)
Brian Piatkus wrote:
> Hi
> It turns out to be more than possible. Locale is set by default to en_GB.
> Am I right in assuming that I need only to set LANG=C ? and should I also do
> this for the environment of the running postmaster ?
>
>
> On Tuesday 22 Apr 2003 20:59, you wrote:
>
>>On Tue, 22 Apr 2003, Brian Piatkus wrote:
>>
>>>Hi,
>>> I'm sure that this has come up many times before but :
>>>
>>>I have just upgraded from 7.0.2 to 7.2.2 using the latest Mandrake 9.0
>>>RPMS. The database structure is unchanged but I now find that the db
>>>refuses to use the available index WHERE NAME ~ '^NAME' even with
>>>enable_seqscan set off. Am I missing something ?
>>
>>It's possible that you initialized the database in something other than
>>"C" locale which disables the optimization that uses indexes for
>>regexp/like (see past discussions in the archives for details).
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Don't 'kill -9' the postmaster
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Tom,
I'm really impressed with the way people chip in with answers & actually help
solve the problem too !
I've done a lot of browsing the archives & a bit of testing with my sample
db. I cant get LANG=C to work but LC_ALL=C does so I'm a bit confused. Still,
if it works and ain't broke .....
Thanks for the helpful tips.
Regards
On Wednesday 23 Apr 2003 15:16, you wrote:
> Brian Piatkus <Brian@fulcrum.plus.com> writes:
> > It turns out to be more than possible. Locale is set by default to en_GB.
> > Am I right in assuming that I need only to set LANG=C ? and should I also
> > do this for the environment of the running postmaster ?
>
> You need to re-do initdb with LANG=C in its environment. Pain in the
> neck, I know.
>
> regards, tom lane