Re: Date vs Timestamp without timezone Partition Key

On Sat, 6 Jun 2020 at 14:49, Cedric Leong <cedricleong(at)gmail(dot)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