I've started to use postgresql 11 recently and I encountered with one interesting behavior.
I created table "conversions" with partitions (logdate is a key).
CREATE TABLE conversions(
.....
logdate timestamp without time zone,
....
) PARTITION BY RANGE (logdate);
CREATE INDEX ON conversions(logdate);
--
CREATE unique INDEX conversions_log_id_idx ON conversions(logdate, id);
CREATE INDEX conversions_log_is_created_idx ON conversions(logdate, is_created);
The partition pruning works for SELECT statements (as describes in documentation):
SELECT *
FROM conversions
WHERE logdate BETWEEN to_date('2017-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND to_date('2017-09-08 23:59:59','YYYY-MM-DD HH24:MI:SS');
"Append (cost=0.42..11134.74 rows=13051 width=1715)"
" Subplans Removed: 12"
" -> Index Scan using conversions_y2017q03_logdate_idx on conversions_y2017q03 (cost=0.42..10962.37 rows=13038 width=1715)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_default_logdate_is_created_idx on conversions_default (cost=0.14..8.16 rows=1 width=2030)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
It works well, you can see it before.
But it doesn't work for update statements.
As example:
UPDATE conversions
SET is_created = 'N'
WHERE logdate BETWEEN to_date('2017-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS') AND to_date('2017-09-08 23:59:59','YYYY-MM-DD HH24:MI:SS')
"Update on conversions (cost=0.42..11069.48 rows=13051 width=1727)"
" Update on conversions_y2016q04"
" Update on conversions_y2017q01"
" Update on conversions_y2017q02"
" Update on conversions_y2017q03"
" Update on conversions_y2017q04"
" Update on conversions_y2018q01"
" Update on conversions_y2018q02"
" Update on conversions_y2018q03"
" Update on conversions_y2018q04"
" Update on conversions_y2019q01"
" Update on conversions_y2019q02"
" Update on conversions_y2019q03"
" Update on conversions_y2019q04"
" Update on conversions_default"
" -> Index Scan using conversions_y2016q04_logdate_is_created_idx on conversions_y2016q04 (cost=0.42..8.44 rows=1 width=1661)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_y2017q01_logdate_is_created_idx on conversions_y2017q01 (cost=0.42..8.45 rows=1 width=1804)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_y2017q02_logdate_is_created_idx on conversions_y2017q02 (cost=0.42..8.45 rows=1 width=1805)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_y2017q03_logdate_idx on conversions_y2017q03 (cost=0.42..10962.37 rows=13038 width=1727)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_y2017q04_logdate_id_idx on conversions_y2017q04 (cost=0.29..8.31 rows=1 width=1699)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_y2018q01_logdate_is_created_idx on conversions_y2018q01 (cost=0.14..8.16 rows=1 width=2036)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_y2018q02_logdate_is_created_idx on conversions_y2018q02 (cost=0.14..8.16 rows=1 width=2036)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_y2018q03_logdate_is_created_idx on conversions_y2018q03 (cost=0.14..8.16 rows=1 width=2036)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_y2018q04_logdate_is_created_idx on conversions_y2018q04 (cost=0.14..8.16 rows=1 width=2036)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_y2019q01_logdate_is_created_idx on conversions_y2019q01 (cost=0.14..8.16 rows=1 width=2036)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_y2019q02_logdate_is_created_idx on conversions_y2019q02 (cost=0.14..8.16 rows=1 width=2036)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_y2019q03_logdate_is_created_idx on conversions_y2019q03 (cost=0.14..8.16 rows=1 width=2036)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_y2019q04_logdate_is_created_idx on conversions_y2019q04 (cost=0.14..8.16 rows=1 width=2036)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
" -> Index Scan using conversions_default_logdate_is_created_idx on conversions_default (cost=0.14..8.16 rows=1 width=2036)"
" Index Cond: ((logdate >= to_date('2017-09-01 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (logdate <= to_date('2017-09-08 23:59:59'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))"
It looks like Postgresql are using index for seeking neccesary partitions.
Is it good behavior? You thoughts?
Are you using prepared statements? If so, a generic plan may be used, which means the planner won't be able to perform pruning because the values needed to do it would only be made available when executing the generic plan. SELECT supports execution-time pruning as of PostgreSQL 11, so it's okay even if planner can't do the pruning in its case. However, for UPDATE, only the planner can perform pruning, so there is none if a generic plan is used. I suspected that you may be using prepared statement, because looking at the SELECT's explain output shared in the question:
"Append (cost=0.42..11134.74 rows=13051 width=1715)"
" Subplans Removed: 12"
it's clear that run-time pruning is being used. Read more about partition pruning here:
https://www.postgresql.org/docs/devel/ddl-partitioning.html#DDL-PARTITION-PRUNING
EDIT: I would like to also mention that UPDATE can use pruning if the planner gets a change to do so (non-generic plan), provided you're using LIST and RANGE partitioning (doesn't work with HASH that's new in PostgreSQL 11).
EDIT 2: As mentioned in the comment, this seems to have to do with the fact that OP is using to_date function in the where clause which makes the expression being compared to partition key an immutable expression, as far as PostgreSQL is concerned. That means planner cannot perform pruning with it, although executor can because it's still a stable expression. As I said above, executor pruning is not supported for UPDATEs.