Re: Regexps and Indices.

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)
>