What is the most efficient way to extract a date from a timestamp in PostgreSQL?

Question

Having queries of the forms

select * from foo
where created_on::date = '2014/1/1'

or

select * from foo
where date_trunc('day', created_on) = '2014/1/1'

or

select * from foo
where date(created_on) = '2014/1/1'

Under what conditions would the different queries perform better/worse? Which is the most efficient of the three options?

Answer 1

To summarize the comments, your first and third solution are identical. Casting to a date simply uses the date function according to @Nick Barnes.

Those options, plus option 2, requires a function to be run against every row of the table, so even if you have an index, it cannot be used.

Assuming there is an index on created_on, this is your best bet:

select * from foo
where created_on >= '2014/1/1 00:00:00'
  and created_on < '2014/1/2 00:00:00';