From: | Jonathan Bartlett <johnnyb(at)eskimo(dot)com> |
---|---|
To: | Brian Piatkus <Brian(at)fulcrum(dot)plus(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Regexps and Indices. |
Date: | 2003-04-22 19:29:55 |
Message-ID: | Pine.GSU.4.44.0304221229180.4701-100000@eskimo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)postgresql(dot)org)
>
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-04-22 19:59:00 | Re: Regexps and Indices. |
Previous Message | Jim C. Nasby | 2003-04-22 18:41:22 | FOUND in plpgsql |