Re: BUG #15045: Partitioning not working as intended

On Fri, Feb 2, 2018 at 1:57 AM, PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15045
> Logged by: Tomasz Kamiński
> Email address: mlodikk(at)gmail(dot)com
> PostgreSQL version: 10.1
> Operating system: Centos 6
> Description:
>
> Hi,
> i found something wrong with new partition mechanism in postgresql 10.1
> I have table partitioned by time stored as epoch time. Each partition is
> one
> month (my time is GMT+1).
> I tried to make script, that's count rows from last month. I wrote
> something
> like this:
>
> SELECT count(*) FROM trends where
> clock between (extract(epoch from (date_trunc ('month', CURRENT_DATE)-
> interval '1 month')::date)::integer - 3600 )
> and (extract(epoch from date_trunc ('month', CURRENT_DATE)::date)::integer
> -
> 3601 )
>
> Problem is, that all partitions are scanned.
>

Partition pruning has to happen at planning time, but the time when the
query is executed can differ from the time it was planned, so pruning can't
be done based on expressions like CURRENT_DATE. This might be improved in
the future.

Cheers,

Jeff