Date() Makes Query Not Use Index
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.