Re: postgres 11.0 partition table works unexpected in update

On Wed, Oct 24, 2018 at 8:09 PM 张心灵 <zhangsilly(at)gmail(dot)com> wrote:
> I have test postgres 11.0 just now, when I test insert, it runs perfect, but when test update, it's too slow, and explain show it's bad planed.
>
> My table was partioned as this:
>
> CREATE TABLE ysy_test.user_msg
> (
> user_id bigint NOT NULL DEFAULT 0,
> msg_id bigint NOT NULL DEFAULT 0,
> status smallint NOT NULL DEFAULT 0,
> create_time timestamp without time zone NOT NULL DEFAULT now(),
> update_time timestamp without time zone NOT NULL DEFAULT now(),
> CONSTRAINT user_msg_pkey PRIMARY KEY (user_id, msg_id)
> ) PARTITION BY HASH(user_id) WITH (
> OIDS = FALSE
> )

[ ... ]

> Explain select works good as expect:
>
> ysy=> explain (analyze, costs, verbose) select * from ysy_test.user_msg_114 where user_id = 14211;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using user_msg_114_pkey on ysy_test.user_msg_114 (cost=0.43..11.29 rows=8 width=34) (actual time=0.028..0.044 rows=8 loops=1)
> Output: user_id, msg_id, status, create_time, update_time
> Index Cond: (user_msg_114.user_id = 14211)
> Planning Time: 0.089 ms
> Execution Time: 0.065 ms
> (5 rows)
>
> Time: 0.432 ms
>
> But update works bad, and explain like this:
>
> ysy=> explain (analyze, costs, verbose, buffers, timing) update ysy_test.user_msg set status = 2 where user_id = 14211 and msg_id = 1 and (user_id % 128) = (14211 % 128);
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------
> Update on ysy_test.user_msg (cost=0.43..365.12 rows=128 width=40) (actual time=1.114..1.114 rows=0 loops=1)
> Update on ysy_test.user_msg_0

[ ... ]

> Update on ysy_test.user_msg_127

[ ... ]

> Time: 25.532 ms
>
> And when doing update, almost all activities was wait for lock_manager, it's toooo slow!

UPDATE (and DELETE) queries cannot use partition pruning. Also,
constraint exclusion that's used by UPDATE for pruning doesn't work
for hash partitions. So, unlike SELECT, all partitions are included
in the plan. That makes UPDATE slow, not to mention other
inefficiencies involved in UPDATE planning.

> BTW: SELECT 14211 % 128 was 3, why record of HASH column user_id 14211 data in partition of 114?

Hash partitioning doesn't apply modulus 3 directly to the value
'14211'. It first computes its hash using the hashing function for
bigint type and then applies the modulus.

Thanks,
Amit