Why isn't my index used

Question

I'm running postgresql-9.1.6 on RHEL 5.8 OS. I got a statement implementing seq scan on which column is indexed.

                          Table "public.table"
Column    |         Type          |                Modifiers                
----------+-----------------------+-----------------------------------------
 col1     | character(3)          | not null
 col2     | character varying(20) | not null
 col3     | character varying(20) | 
 col4     | character(1)          | default 0
Indexes:
    "table_pkey" PRIMARY KEY, btree (col1, col2)


postgres=# explain analyze select * from table where col1=right('10000081',3);
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Seq Scan on table  (cost=0.00..31053.24 rows=5650 width=286) (actual time=3.221..429.950 rows=110008 loops=1)
   Filter: ((col1)::text = '081'::text)
 Total runtime: 435.904 ms
(3 rows)

postgres=# explain analyze select * from table where col1=right('10000081',3)::char(3);
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on table  (cost=3097.81..18602.98 rows=112173 width=286) (actual time=18.125..32.707 rows=110008 loops=1)
   Recheck Cond: (col1 = '081'::character(3))
   ->  Bitmap Index Scan on table_pkey  (cost=0.00..3069.77 rows=112173 width=0) (actual time=17.846..17.846 rows=110008 loops=1)
         Index Cond: (col1 = '081'::character(3))
 Total runtime: 38.640 ms
(5 rows)

and I found that [alter column] is one of the solution....

postgres=# alter table table alter column col1 type varchar(3);               
ALTER TABLE
postgres=# explain analyze select * from table where col1=right('10000081',3);
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on table  (cost=160.26..10902.32 rows=5650 width=295) (actual time=20.249..41.658 rows=110008 loops=1)
   Recheck Cond: ((col1)::text = '081'::text)
   ->  Bitmap Index Scan on table_pkey  (cost=0.00..158.85 rows=5650 width=0) (actual time=20.007..20.007 rows=110008 loops=1)
         Index Cond: ((col1)::text = '081'::text)
 Total runtime: 47.408 ms
(5 rows)

What I wonder is WHY???

Answer 1

Avoid the char datatype. It's awful for many reasons, and this is only one of them.

If you stick to text or varchar you'll have fewer issues with implicit casts and confusing behavior.

Answer 2

It looks from the first plan that there is an implicit type cast performed on col1 to match the return type of right().

Filter: ((col1)::text = '081'::text)

Evidently the expression right('10000081',3) returns text.

So I would say that yes, you do have to type cast the expression, although an alternative would be to index on (col1)::text -- not my favourite solution though.