Date() Makes Query Not Use Index

by bitznbitez

This is a common problem that comes up from time to time when a developer has a slow query.   In this case its a java programmer and Postgres 8.1.    We have a table with an index on create_ts which is a timestamp not null with default current_timestamp column.

In the SQL the developer says :

where date(create_ts) between ? and ?

They do this because the application only asks the user for a date.   The problem is that by doing this the index on create_ts is not used.   Instead a full table scan is initiated.

If instead the developer will take the date value and convert it to a timestamp in the code and then us a sql as follows

where create_ts between ? and ?

The access path will be an indexed path and a 1 minute long query will magically become subsecond.

If you wish to validate the access plan changes in postgres, download a copy of PGAdmin III and in the window where you enter the sql code hit the button to display the explain output.