Postgres 8.4 – Replace regular index with Text Based Index

I came across a nice post by Peter Eisentraut that shows how text based index can be used to do direct lookup on the text fields.

Quick test proves that..

postgres=# d employees
  Table "public.employees"
Column |  Type   | Modifiers
--------+---------+-----------
emp_id | integer | not null
name   | text    |
Indexes:
   "employees_pkey" PRIMARY KEY, btree (emp_id)

postgres=# create index idx_employees_name on employees(name);
CREATE INDEX
postgres=# explain select * from employees where name like 'Denish%';
                       QUERY PLAN                        
-----------------------------------------------------------
Seq Scan on employees  (cost=0.00..25.38 rows=6 width=36)
  Filter: (name ~~ 'Denish%'::text)
(2 rows)

postgres=# explain select * from employees where name='Denish';
                                  QUERY PLAN                                   
---------------------------------------------------------------------------------
Bitmap Heap Scan on employees  (cost=4.30..13.76 rows=6 width=36)
  Recheck Cond: (name = 'Denish'::text)
  ->  Bitmap Index Scan on idx_employees_name  (cost=0.00..4.30 rows=6 width=0)
        Index Cond: (name = 'Denish'::text)
(4 rows)

postgres=# create index idx_employees_name_like on employees (name text_pattern_ops);
CREATE INDEX
postgres=# explain select * from employees where name like 'Denish%';
                                     QUERY PLAN                                     
--------------------------------------------------------------------------------------
Bitmap Heap Scan on employees  (cost=4.31..13.78 rows=6 width=36)
  Filter: (name ~~ 'Denish%'::text)
  ->  Bitmap Index Scan on idx_employees_name_like  (cost=0.00..4.31 rows=6 width=0)
        Index Cond: ((name ~>=~ 'Denish'::text) AND (name ~  Bitmap Index Scan on idx_employees_name_like  (cost=0.00..4.30 rows=6 width=0)
        Index Cond: (name = 'Denish'::text)
(4 rows)

 

Above example shows that we don’t need regular index on name field as text based index (idx_employees_name_like) fulfill the gap. However, if we do range query i.e select * from employees where name > ‘denish’ and name<'patel' then text based index will not be used. I don't think we ever run a range query on text based fields!.