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!.