1

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

Ken White
  • 123,280
  • 14
  • 225
  • 444
3ch01c
  • 2,336
  • 2
  • 17
  • 15
  • 3
    You will need to show the complete query, not just the part you think is the reason. And please **[edit]** your question and add the [execution plan](https://www.postgresql.org/docs/current/static/using-explain.html) generated using `explain (analyze, buffers, format text)` (_not_ just a "simple" explain) as [formatted text](http://stackoverflow.com/help/formatting) and make sure you preserve the indention of the plan. Paste the text, then put `\`\`\`` on the line before the plan and on a line after the plan. –  Feb 13 '23 at 18:56
  • Apparently lasttime is a timestamp, not a timestamptz. This is usually the wrong thing to have. – jjanes Feb 13 '23 at 20:31
  • Your question lacks the information required for an answer. It sounds like the more complicated expression is harder to estimate accurately by the optimizer, which may then choose a bad plan. But without hard data, that remains a conjecture. – Laurenz Albe Feb 14 '23 at 06:41

1 Answers1

0

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.

JGH
  • 15,928
  • 4
  • 31
  • 48