Why does `date_trunc('day', current_date) + interval '1 day' - interval '1 second'` cause query to hang?

Question

When I set up a date range using max(lasttime) for the upper bound, the query works.

range_values as (
    select date_trunc('month', current_date) as minval,
        max(lasttime) as maxval
    from people
)

When I use date_trunc('day', current_date) + interval '1 day' - interval '1 second' for the upper bound, the query hangs seemingly forever.

range_values as (
    select date_trunc('month', current_date) as minval,
        (
            date_trunc('day', current_date) + interval '1 day' - interval '1 second'
        ) as maxval
    from people
)

Here's how those values differ.

select max(lasttime) as max_lasttime, (date_trunc('day', current_date) + interval '1 day' - interval '1 second') as end_of_day from people;

{
  "max_lasttime": "2023-02-13 07:30:01",
  "end_of_day": "2023-02-13 23:59:59-07"
}

I expected this would not make a difference. Why does it?

PostgreSQL 10.18 (Ubuntu 10.18-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit

Answer 1

In the 1st query, you are using an aggregate (max) so the output is a single row, regardless of the size of the people table.

In the 2nd query, you are fetching 2 constant values for every row in the people, so chances are you are building a massive cross-join with another (or the same!) table.