Re: literal vs dynamic partition constraint in plan execution - Mailing list pgsql-general

On Fri, Aug 30, 2019 at 8:29 AM Luca Ferrari <fluca1978@gmail.com> wrote:
> testdb=># \d+ respi.y2018
> ...
> Partition of: respi.root FOR VALUES IN ('2018')
> Partition constraint: ((date_part('year'::text, mis_ora) IS NOT NULL)
> AND (date_part('year'::text, mis_ora) = '2018'::double precision))
> Partition key: LIST (date_part('month'::text, mis_ora))
> Check constraints:
>     "y2018_mis_ora_check" CHECK (date_part('year'::text, mis_ora) =
> 2018::double precision)
>     "y2018_mis_ora_check1" CHECK (mis_ora >= make_timestamp(2018, 1,
> 1, 0, 0, 0::double precision) AND mis_ora <= make_timestamp(2018, 12,
> 31, 23, 59, 59::double precision))
> Partitions: respi.y2018m01 FOR VALUES IN ('1'),
>             respi.y2018m02 FOR VALUES IN ('2'),
>             respi.y2018m03 FOR VALUES IN ('3'),
>             respi.y2018m04 FOR VALUES IN ('4'),
>             respi.y2018m05 FOR VALUES IN ('5'),
>             respi.y2018m06 FOR VALUES IN ('6'),
>             respi.y2018m07 FOR VALUES IN ('7'),
>             respi.y2018m08 FOR VALUES IN ('8'),
>             respi.y2018m09 FOR VALUES IN ('9'),
> ...
>


While the condition
mis_ora >= current_timestamp
does not cut off the 2018 branch, the following does

=# explain select * from respi.root where ts >= '2019-08-28 23:35:00.007245'
and  extract( year from mis_ora ) = extract( year from current_timestamp )
and extract( month from mis_ora ) >= extract( month from
current_timestamp )    order by ts;

 Sort  (cost=7246692.21..7246692.28 rows=26 width=36)
   Sort Key: r.ts
   ->  Nested Loop  (cost=0.00..7246691.60 rows=26 width=36)
         Join Filter: (r.sen_id = s.sen_id)
         ->  Seq Scan on sensori s  (cost=0.00..13.57 rows=329 width=16)
               Filter: interesting
         ->  Materialize  (cost=0.00..7246465.93 rows=43 width=32)
               ->  Append  (cost=0.00..7246465.72 rows=43 width=32)
                     Subplans Removed: 31
                     ->  Seq Scan on y2019m08 r  (cost=0.00..623008.30
rows=2 width=32)
                           Filter: ((ts >= '2019-08-28
23:35:00.007245'::timestamp without time zone) AND
(date_part('month'::text, mis_ora) >= date_part('month'::text,
CURRENT_TIMESTAMP)) AND (date_part('year'::text, mis_ora) =
date_part('year'::text, CURRENT_TIMESTAMP)))
                     ->  Seq Scan on y2019m09 r_1  (cost=0.00..49.00
rows=1 width=32)
                           Filter: ((ts >= '2019-08-28
23:35:00.007245'::timestamp without time zone) AND
(date_part('month'::text, mis_ora) >= date_part('month'::text,
CURRENT_TIMESTAMP)) AND (date_part('year'::text, mis_ora) =
date_part('year'::text, CURRENT_TIMESTAMP)))
                     ->  Seq Scan on y2019m10 r_2  (cost=0.00..49.00
rows=1 width=32)
                           Filter: ((ts >= '2019-08-28
23:35:00.007245'::timestamp without time zone) AND
(date_part('month'::text, mis_ora) >= date_part('month'::text,
CURRENT_TIMESTAMP)) AND (date_part('year'::text, mis_ora) =
date_part('year'::text, CURRENT_TIMESTAMP)))
                     ->  Seq Scan on y2019m11 r_3  (cost=0.00..49.00
rows=1 width=32)
                           Filter: ((ts >= '2019-08-28
23:35:00.007245'::timestamp without time zone) AND
(date_part('month'::text, mis_ora) >= date_part('month'::text,
CURRENT_TIMESTAMP)) AND (date_part('year'::text, mis_ora) =
date_part('year'::text, CURRENT_TIMESTAMP)))

The fact that making explicit the condition against the year and the
month, which are the top level partition constraint, makes me think
that the executor will try to go down all the branches to the leaf if
the condition is not filtered at the top level. Even if I don't
understand why.

Luca