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)

성능때문에 살펴보던중에 두번째 쿼리처럼 함수리턴 타입을 인덱스 컬럼 타입으로 캐스팅 하니까 full scan 이 index scan 으로 교정되는데요...

결국, string 함수는 대부분 text type으로 리턴하는 함수엔 죄다 캐스팅을 해줘야 되는건지요...???