The case for function based indexes

Since a lot of codes are "speaking codes" (...) meaning that they can be split into fixed meaningful substrings I often see queries like this:

select
*
from
t_table
where
c_code like 'DD6%'

which is highly inefficient, look at it's explain plan:

Seq Scan on t_table (cost=100000000.00..100000001.06 rows=1 width=33)
Filter: (((c_code)::text ~~ 'DD6%'::text)

Since the first 3 chars only are meaningful, you can exploit one of the great features of PostgreSQL, function based indexes, create an index like this:

CREATE INDEX t_table_c_code_part_idx
ON t_table (substring(c_code, 1, 3));

and change the query to:

select
*
from
t_table
where
substring(postcode, 1, 3) = 'DD6'

Now look at the explain plan again:

Index Scan using t_table_postcode_part_idx on t_table (cost=0.00..4.69 rows=1 width=33)
Index Cond: ("substring"((postcode)::text, 1, 3) = 'DD6'::text)

WOW, the index is correctly picked up and query efficiency is greately enhanced!!!