From: Justin Pryzby <pryzby@telsasoft.com>
To: Benjamin Tingle <ben@tingle.org>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Planner not taking advantage of HASH PARTITION
Date: Thu, 21 Apr 2022 06:52:36 -0500
Message-ID: <20220421115236.GA10173@telsasoft.com> (raw)
In-Reply-To: <CABTcpys3dDV+yFCWs2AJpFxWtoWnvDNxH+mO_gG_ZrKfL-QLSQ@mail.gmail.com>
References: <CABTcpyvoSYrn-2S4p8hRwAsCe3PkOUS4dFxmr6gUy4x9u5PDKA@mail.gmail.com>
<202204171850.iqpjc2mmr47y@alvherre.pgsql>
<CABTcpys3dDV+yFCWs2AJpFxWtoWnvDNxH+mO_gG_ZrKfL-QLSQ@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, ben@tingle.org, alvherre@alvh.no-ip.org
Subject: Re: Query Planner not taking advantage of HASH PARTITION
In-Reply-To: <20220421115236.GA10173@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox