pattern match, index use and performance - Mailing list pgsql-hackers

This seems an old topic to the list,
but it's one of the confusing area for
postgresql users -

example table:

1. about "like" and "similar to" operators
postgresql can't use index if your database
encoding is anything other than 'C'. After I did
this, I knew this will not take care of
'%pattern%' map, which requires the text search module.

question 1: query uses the index, why the performance is still bad?

emrxdbs=# explain analyze select * from patient where lastname like 'BEC%';
              QUERY PLAN
 
----------------------------------------------------------------------------
--------------------------Index Scan using patient_lname_idx on patient  (cost=0.00..251.65 rows=62
width=176) (actual time=63.48..8860.14 rows=1732 loops=1)  Index Cond: ((lastname >= 'BEC'::character varying) AND
(lastname<
 
'BED'::character varying))  Filter: (lastname ~~ 'BEC%'::text)Total runtime: 8870.27 msec

emrxdbs=#  explain analyze select * from patient where firstname like
'RAN%';                                                            QUERY PLAN
----------------------------------------------------------------------------
--------------------------Index Scan using patient_fname_idx on patient  (cost=0.00..18.25 rows=4
width=176) (actual time=39.84..12515.44 rows=2649 loops=1)  Index Cond: ((firstname >= 'RAN'::character varying) AND
(firstname<
 
'RAO'::character varying))  Filter: (firstname ~~ 'RAN%'::text)Total runtime: 12532.38 msec

question 2: why "not like" is not using index?

emrxdbs=#  explain analyze select * from patient where firstname not like
'MAR%';                                                   QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------Seq Scan on patient  (cost=0.00..20102.05 rows=745897 width=176) (actual
time=0.09..76515.69 rows=717610 loops=1)  Filter: (firstname !~~ 'MAR%'::text)Total runtime: 81983.46 msec
(3 rows)


2. about POSIX Regular Expressions, it doesn't use index at all?!
emrxdbs=# explain analyze select * from patient where firstname ~ 'RAN%';
  QUERY PLAN
 
----------------------------------------------------------------------------
------------------------------------Seq Scan on patient  (cost=0.00..20102.05 rows=5985 width=176) (actual
time=25040.93..25040.93 rows=0 loops=1)  Filter: (firstname ~ 'RAN%'::text)Total runtime: 25041.23 msec
(3 rows)

3. what's the general guidelines in terms of simple pattern
match in postgresql (not the text search module)?

Can someone help to clear the above questions?

thanks.

johnl
============================================================================
========
exmple table - count=750000    Column     |            Type             |
Modifiers
----------------+-----------------------------patseq         | integer                     | not null default
nextval('public.patient_patseq_seq'::text)mpin           | character varying(12)       | not nullmrn            |
charactervarying(18)       | not nullfacilityid     | character varying(12)       | not nulldob            | date
                |sex            | character(1)                |lastname       | character varying(30)       |firstname
   | character varying(30)       |middlename     | character varying(30)       |terminatedcare | boolean
    |iscurrent      | boolean                     | not nullfielda         | character varying(10)       |fieldb
| character varying(10)       |fieldc         | character varying(10)       |fieldd         | character varying(10)
 |fielde         | character varying(10)       |fieldf         | timestamp without time zone | default
 
('now'::text)::timestamp(6) with time zone
Indexes: patient_pkey primary key btree (patseq),        patient_fname_idx btree (firstname),        patient_lname_idx
btree(lastname),        patient_mpin_idx btree (mpin),        patient_mrnfac_idx btree (mrn, facilityid)