NOW() vs ‘NOW’

The following examples explain how postgresql query planner deals with queries that use the current time as a parameter:

FALSE:
explain SELECT * FROM orders WHERE creation_date > NOW() - interval '30 mins' AND key_status = 5;
-----------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..9370300.50 rows=1964090 width=217)
   Filter: ((creation_date > (now() - '00:30:00'::interval)) AND (key_status = 5))

In the first case the planner sees the timestamp parameter NOW() as a inline function and therefore can’t estimate the number of rows matching the criteria as the result of a function call can never be determined (except for immutable functions). This results in an suboptimal execution plan (sequential scan)

CORRECT:
explain SELECT * FROM orders WHERE creation_date > 'NOW'::timestamptz - interval '30 mins' AND key_status = 5;
--------------------------------------------------------------------------------------------
 Index Scan using idx_order_creation_date on orders  (cost=0.00..98.03 rows=587 width=217)
   Index Cond: (creation_date > '2006-03-03 15:07:22.492173+02'::timestamp with time zone)
   Filter: (key_status = 5)

In the second case the planner handles ‘NOW’ as a constant and therefore is able to estimate how many rows have a greater value resulting in a optimal execution plan (index range scan)

Instead ‘NOW’ you can also use the variable CURRENT_TIMESTAMP and other similar constants which you don’t have to quote. They are somewhere amidst other useful information in the PostgreSQL documentation.