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 <dgrowle...@gmail.com> wrote:
> 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
>
