How does postgres compare dates in WHERE clause?
I have a table with ~200 million rows that has a date column. I want to perform the following query.
select my_date from my_table
where my_date > to_date('2020-10-01', 'YYYY-MM-DD');
As the query is executing, on each my_date in each row, the date value in the database will be compared with my string date cast into a date object. Is postgres smart enough to realise that this transformation from string to date object needs only to be performed once or will it execute with each row?
For Postgres compare dates, postgres will do the string to date conversion just once, because to_date is a STABLE function and the argument is a constant. But if you’re worried about this, use the date literal syntax: date '2020-10-01'. See https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-INPUT