Why does Postgres give different EXPLAIN ANALYZE results when using now() vs current_timestamp?

Question

At my job we've recently had to optimize a query and kind of stumbled into the answer to make the query faster but can't explain why it worked and it's bothering me.

The situation is something like this. Users on our platform can share widgets. Other users can download those widgets. We have some analytics queries so a user can track how much attention their widgets are getting. The query for all of this is fairly complicated but the simplified and relevant portion is this:

with all_user_widgets as (
    select widget_id, published_result
    from widgets
    where owner_id = :userId
), distinct_widget_downloads_count as (
    select d.widget_id, count(*) as count
    from all_user_widgets taum
             inner join user_widget_first_downloads d on taum.widget_id = d.widget_id
    where current_date - (:startDays + 0) <= d.first_download_at and d.first_download_at < current_date - (:endDays + 0)
    group by d.widget_id
)

Parameter definitions:

• :userId - user we are getting data for
• :startDays - how many days back we want data for (think 30 days ago for a month view)
• :endDays - when to stop looking at data (ex: for comparing the last 2 months of data we would execute this with :startDays=60 and :endDays=30 to get the data from the previous month)

We use this to calculate how many distinct user's have downloaded a widget (and use that in more calculations later). For most sets of parameters this was fast but for some it was very slow (~200ms was fast, 3+ minutes was slow). We found one user that was consistently slow when we would use :startDays=1, :endDays=-1 (this was to get the most recent data when looking at a day view). What we found was that for this set of parameters the query optimizer was ignoring an index on the user_widget_first_downloads table.

So we tried various things to fix this, including making sure the part of the query that defined the date ranges set an upper limit that wasn't in the future. So the query looked like this:

with all_user_widgets as (
    select widget_id, published_result
    from widgets
    where owner_id = :userId
), distinct_widget_downloads_count as (
    select d.widget_id, count(*) as count
    from all_user_widgets taum
             inner join user_widget_first_downloads d on taum.widget_id = d.widget_id
    where current_date - (:startDays + 0) <= d.first_download_at and d.first_download_at < least(current_date - (:endDays + 0), current_timestamp)
    group by d.widget_id
)

This caused the optimizer to use the index but it still wasn't as fast as we wanted (~800ms). By dumb luck we tried replacing current_timestamp with now() and the performance improved to ~300ms. Huh?

Question

Why does using least to set that upper limit change how the query runs? And the one that bothers me more is why does using now() give better performance than current_timestamp? According to everything I can find now() and current_timestamp are equivalent. Though this makes it seem like somewhere they aren't and I'd like to know why (should I just use now() forever and always rather than current_timestamp?).

Table Structure & Data

create table widgets (
    widget_id                 bigint not null
        primary key
        unique,
    published_result         jsonb,
    -- a bunch of other irrelevant data
)

create index widgets_widget_id_idx
    on widgets (widget_id);

create index widgets_owner_id_idx
    on widgets (owner_id);

Table has 1041447 rows.

create table if not exists user_widget_first_downloads
(
    widget_id          bigint                   not null,
    user_id           bigint                   not null,
    first_download_at timestamp with time zone not null
);

-- other indexes that aren't relevant to this

create index user_widget_first_downloads_widget_id_first_download
    on user_widget_first_downloads (widget_id, first_download_at);

Table has 6322562 rows.

Queries, plans and my own research

Query 1 (slow, 3m 42s)

with all_user_widgets as (
    select widget_id, published_result
    from widgets
    where owner_id = :userId
), distinct_widget_downloads_count as (
    select d.widget_id, count(*) as count
    from all_user_widgets taum
             inner join user_widget_first_downloads d on taum.widget_id = d.widget_id
    where current_date - (:startDays + 0) <= d.first_download_at and d.first_download_at < current_date - (:endDays + 0)
    group by d.widget_id
)

(Apologies for cutting off the column headers. They are Operation, Params, Rows, Actual Rows, Total Cost, Actual Total Time, Startup Cost, Actual Startup Time)

Query 2 (better, 792ms)

with all_user_widgets as (
    select widget_id, published_result
    from widgets
    where owner_id = :userId
), distinct_widget_downloads_count as (
    select d.widget_id, count(*) as count
    from all_user_widgets taum
             inner join user_widget_first_downloads d on taum.widget_id = d.widget_id
    where current_date - (:startDays + 0) <= d.first_download_at and d.first_download_at < least(current_date - (:endDays + 0), current_timestamp)
    group by d.widget_id
)

(Apologies for cutting off the column headers. They are Operation, Params, Rows, Actual Rows, Total Cost, Actual Total Time, Startup Cost, Actual Startup Time)

Query 3 (best, 335ms)

with all_user_widgets as (
    select widget_id, published_result
    from widgets
    where owner_id = :userId
), distinct_widget_downloads_count as (
    select d.widget_id, count(*) as count
    from all_user_widgets taum
             inner join user_widget_first_downloads d on taum.widget_id = d.widget_id
    where current_date - (:startDays + 0) <= d.first_download_at and d.first_download_at < least(current_date - (:endDays + 0), now())
    group by d.widget_id
)

(Apologies for cutting off the column headers. They are Operation, Params, Rows, Actual Rows, Total Cost, Actual Total Time, Startup Cost, Actual Startup Time)

And just for giggles and grins I replaced current_date in the original query to see what happened and it got much faster as well.

Query 4 (very good, 369ms)

with all_user_widgets as (
    select widget_id, published_result
    from widgets
    where owner_id = :userId
), distinct_widget_downloads_count as (
    select d.widget_id, count(*) as count
    from all_user_widgets taum
             inner join user_widget_first_downloads d on taum.widget_id = d.widget_id
    where current_date - (:startDays + 0) <= d.first_download_at and d.first_download_at < least(current_date - (:endDays + 0), current_timestamp)
    group by d.widget_id
)

(Apologies for cutting off the column headers. They are Operation, Params, Rows, Actual Rows, Total Cost, Actual Total Time, Startup Cost, Actual Startup Time)

I can see that the row estimates here vary wildly, some being very underestimated, some overestimated. How does now() vs current_timestamp / current_date change that?

We are using Postgres 11.22.

Answer 1

PostgreSQL might cache plans differently based on how the query is written and the functions used. Even slight differences in the query can lead to different plans being used, especially if PostgreSQL estimates the cost and benefits of using indexes or sequential scans differently.

To have more information it would be good to see the EXPLAIN ANALYZE outputs.