Avoid applying functions to a database column.
Performing functions on an indexed column in the where clause of a query is a very common practice. But what most developers doesn't know is that the consequence of using it is that the index on the column will not be used.
Let’s see an example:
Consider that you have the EMP table with 1 million rows and the structure below.
CREATE TABLE EMP ( EMPNO serial NOT NULL,d ENAME varchar(1000), JOB varchar(9), MGR numeric(4),ddd HIREDATE DATE, SAL numeric(7,2), COMM numeric(7,2), DEPTNO numeric(2) NOT NULL, CONSTRAINT EMP_EMPNO_PK PRIMARY KEY (EMPNO)); CREATE INDEX ix_emp_hiredate ON emp(hiredate);
Let’s see what is the query plan when a TO_CHAR functions applied on the hiredate column:
dbnazar=# EXPLAIN ANALYZE dbnazar=# SELECT * dbnazar=# FROM emp dbnazar=# WHERE TO_CHAR(hiredate,'dd/mm/yyyy') = '17/10/2014'; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Seq Scan on emp (cost=0.00..83202.49 rows=7708 width=48) (actual time=1.319..2851.346 rows=12051 loops=1) Filter: (to_char((hiredate)::timestamp with time zone, 'dd/mm/yyyy'::text) = '17/10/2014'::text) Rows Removed by Filter: 1181699 Total runtime: 2868.337 ms
As we can see, the database does a Sequential Scan and the query takes almost 3 seconds to execute.
Now, instead of applying the TO_CHAR function to the column, let’s apply a TO_DATE to the variable and see the new query plan:
dbnazar=# EXPLAIN ANALYZE dbnazar=# SELECT * FROM emp dbnazar=# WHERE hiredate = TO_DATE('17-10-2014','DD-MM-YYYY'); QUERY PLAN ------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on emp (cost=526.48..36144.02 rows=16238 width=48) (actual time=39.788..74.872 rows=12051 loops=1) Recheck Cond: (hiredate = to_date('17-10-2014'::text, 'DD-MM-YYYY'::text)) -> Bitmap Index Scan on ix_emp_hiredate (cost=0.00..522.42 rows=16238 width=0) (actual time=36.352..36.352 rows=12051 loops=1) Index Cond: (hiredate = to_date('17-10-2014'::text, 'DD-MM-YYYY'::text)) Total runtime: 58.884 ms
What we can see is that, instead of performing a Sequential Scan, the database uses the index on the hiredate column (ix_emp_hiredate) and now query executes in about 60 millisecond.
So, when writing your queries, don’t apply functions to a database column in the where clause unless you have to. In that case, consider creating a Function Based Index.
One last thing you must know is that Sequential Scans are not always bad, sometimes it’s better than using an index.
