On Sat, 6 Jun 2020 at 14:49, Cedric Leong <cedricle...@gmail.com> wrote:
> It's less of a complaint rather than just a warning not to do what I did.
My point was really that nobody really knew what you did or what you
did it on. So it didn't seem like a worthwhile warning as it
completely lacked detail.
> These tests are running the exact same query on two different tables with the
> exception that they use their respective partition keys.
Are you sure? It looks like the old one does WHERE date =
((now())::date - '7 days'::interval) and the new version does
(date(created_at) = ((now())::date - '7 days'::interval). I guess you
renamed date to "created_at" and changed the query to use date(). If
that expression is not indexed then I imagine that would be a good
reason for the planner to have moved away from using the index on that
column. Also having date(created_at) will also not allow run-time
pruning to work since your partition key is "created_at".
You might be able to change the query to query a range of value on the
new timestamp column. This will allow you to get rid of the date()
function. For example:
where created_at >= date_trunc('day', now() - '7 days'::interval) and
created_at < date_trunc('day', now() - '6 days'::interval)
David
