From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Benjamin Tingle <ben(at)tingle(dot)org> |
Cc: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Query Planner not taking advantage of HASH PARTITION |
Date: | 2022-04-21 11:52:36 |
Message-ID: | 20220421115236.GA10173@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Apr 20, 2022 at 07:11:37PM -0700, Benjamin Tingle wrote:
> @ the first point about write locks
> I think I had/have a misconception about how inserts work in postgres. It's
> my understanding that postgres will never draft a parallel insert plan for
> any query (except maybe CREATE TABLE AS?)
It's correct that DML (INSERT/UPDATE/DELETE) currently is not run in parallel.
https://www.postgresql.org/docs/current/when-can-parallel-query-be-used.html
> because the process needs to acquire an exclusive access write lock to the
> table it is inserting on.
But this is incorrect - DML acquires a relation lock, but not a strong one.
Multiple processes can insert into a table at once (because the row-excl lock
level is not self-conflicting, to be technical).
https://www.postgresql.org/docs/current/explicit-locking.html
In fact, that's a design requirement. It's understood that many people would
be unhappy if only one client were able to run UPDATEs at once, and that only a
toy system would acquire a strong lock for DML.
--
Justin
From | Date | Subject | |
---|---|---|---|
Next Message | Emil Iggland | 2022-04-22 14:53:48 | Performance differential when 0 values present vs when 1 values present. Planner return 52k rows when 0 expected. |
Previous Message | Benjamin Tingle | 2022-04-21 02:11:37 | Re: Query Planner not taking advantage of HASH PARTITION |