I can confirm that was the issue, after removing the expression and using only what was indexed it definitely fixed the query plan. I appreciate all the help you've given me, I didn't really think to look there but it makes a ton of sense that a filter on the database would only work well if it's indexed.
On Sat, 6 Jun 2020 at 14:49, Cedric Leong <cedricleong@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)