Re: Partition pruning not working on updates

On Tue, 25 Jul 2023 at 20:45, Mr.Bim <bmopat(at)yahoo(dot)com> wrote:
> Partition pruning is not working on the updates query, am I missing something?

In PG13, partition pruning for UPDATE and DELETE only works during
query planning. Because you're using CURRENT_TIMESTAMP, that's not an
immutable expression which can be evaluated during query planning.
This means that the only possible way to prune partitions when using
CURRENT_TIMESTAMP is during query execution. Unfortunately, execution
time pruning does not work for UPDATE/DELETE in PG13. PG14 is the
first version to have that.

There's a note in the documents [1] about this which reads:

"Execution-time partition pruning currently only occurs for the Append
and MergeAppend node types. It is not yet implemented for the
ModifyTable node type, but that is likely to be changed in a future
release of PostgreSQL."

If you were never to imbed that query in a view or use PREPAREd
statements, then you could replace CURRENT_TIMESTAMP with
'now'::timestamp. 'now' will be evaluated at parse time and that
means it'll be a known value to the planner. However, unless you
somehow can be completely certain that this query will never be put
inside a view or used with prepared statements, then I'd highly
recommend not doing this. SQLs tend to get copied and pasted and it
one day might get pasted into the wrong place (e.g. a view) and that
could cause problems.

Example with a view:
postgres=# create view v_test as select 'now'::timestamp;
CREATE VIEW
postgres=# explain verbose select * from v_test;
QUERY PLAN
---------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=8)
Output: '2023-07-26 00:17:32.370399'::timestamp without time zone
(2 rows)

postgres=# explain verbose select * from v_test;
QUERY PLAN
---------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=8)
Output: '2023-07-26 00:17:32.370399'::timestamp without time zone
(2 rows)

note that the view always just returns the time when the view was created.

My recommendation, if this is a show-stopper for you, would be to
consider using a newer version of PostgreSQL.

David

[1] https://www.postgresql.org/docs/13/ddl-partitioning.html