Question 88945 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql 9.5 performance when comparing date to string faster then when comparing date to date

I’m evaluating a query performance and came to a curious finding….

I’m trying to get the count of items created today grouped by a column

Pretty simple SQL query:

SELECT 
  count(id), total_units_sold 
FROM public.items x
WHERE 
  created_at > current_date
GROUP BY total_units_sold ORDER BY 1 DESC;

The interesting finding is that when I run the same query using the current_date as the string 2023-04-18 the result is considerably faster;

To give some context on table size, there’s 32.000 results in that query and the whole table have about 12.000.000 recods

Execution time when using current_date:

enter image description here

Execution time when using 2023-04-18:

enter image description here

So the question here is:
Why does this happens?

Notes:

  • The table is not indexed on created_at and I cannot change that on the moment
  • Result of the EXPLAIN for both queries (Sorry for the hidden parts)

enter image description here

Result of the explain

Tags:

2

Answers


  1. 0

    The problem with current_date existed in older Postgres versions. Here is a discussion from eight years ago on pgsql-performance.

    The best option is to upgrade Postgres to the latest version. Alternatively, use now()::date instead of current_date as a workaround.

    Login or Signup to reply.
  2. 0

    Because created_at is a timestamp type, but currrent_date is a date type, postgres tends to coerce the column type to the constant value in the query before comparing. This means that every row is read from disk and the created_at value cast to a date, which is relatively expensive.

    Instead, use current_timestamp, whose type is the same as the column:

    where created_at > current_timestamp -- no casting required

    If you have an index on created_at, it’s a candidate for use when the types of the comparison match.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search