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.
Thanks again,
On Fri, Jun 5, 2020 at 11:13 PM David Rowley <dgrowleyml@xxxxxxxxx> wrote:
On Sat, 6 Jun 2020 at 14:49, Cedric Leong <cedricleong@xxxxxxxxx> 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
