From: | Brian Piatkus <Brian(at)fulcrum(dot)plus(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Regexps and Indices. |
Date: | 2003-04-22 18:17:55 |
Message-ID: | 200304221917.55205.Brian@fulcrum.plus.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2003-04-22 18:41:22 | FOUND in plpgsql |
Previous Message | Oliver Elphick | 2003-04-22 18:06:35 | Re: Documentation |