MIME-Version: 1.0
References: <1348181736777057@mail.yandex.com>
In-Reply-To: <1348181736777057@mail.yandex.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Tue, 14 Jan 2025 23:09:19 +1300
Message-ID: <CAApHDvqURruKPqe3hW+pA4eyjXWTk4xvWTB6POn=S2PAaxQGwQ@mail.gmail.com>
Subject: Re: About PostgreSQL Query Plan
To: =?UTF-8?B?RcWfcmVmIEhhbMSxY8Sxb8SfbHU=?= <esref.halicioglu@primeit.com.tr>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAApHDvqURruKPqe3hW%2BpA4eyjXWTk4xvWTB6POn%3DS2PAaxQGwQ%40mail.gmail.com>
Precedence: bulk

On Tue, 14 Jan 2025 at 03:45, E=C5=9Fref Hal=C4=B1c=C4=B1o=C4=9Flu
<esref.halicioglu@primeit.com.tr> wrote:
> I do not fully understand the logic of this issue. I would be very gratef=
ul if you can share information on the subject.
>
> The query plan is as follows.
>
> Update on "test_table1" tt1  (cost=3D0.13..159112.84 rows=3D0 width=3D0)
>   Update on "test_table1_partition_2020_10" tt1
>   Update on "test_table1_partition_2020_11" tt1
...
 Update on "test_table1_partition_2025_12" tt1
>   Update on "test_table1_partition_default" tt1
>   ->  Nested Loop  (cost=3D0.13..159112.84 rows=3D1 width=3D53)
>         ->  Seq Scan on "temp_test_table1" temp  (cost=3D0.00..19.20 rows=
=3D920 width=3D31)
>         ->  Append  (cost=3D0.13..172.29 rows=3D64 width=3D38)
>               Subplans Removed: 60

The partitions mentioned in the "Update on" portion of the EXPLAIN
aren't being scanned. These are just result relations that potentially
could have tuples routed to them.  The key part of the EXPLAIN output
to knowing that the unrelated partitions are pruned is from which
partitions are mentioned below the "Append" node. You can see that 60
of your 64 partitions were pruned with the "Subplans Removed: 60"
part. The executor is only going to scan the 4 remaining ones that you
see below the "Append".

I wouldn't worry too much about the additional partitions mentioned in
the "Update on". We maybe could do a bit more work to initialise those
more lazily as we do for INSERT statements, but I'd be surprised if it
was a problem for 64 partitions, especially so for an update statement
that might be touching 3 months of data. Nothing about these existing
in the "Update on" portion of the EXPLAIN output means that that
partition will be scanned by the UPDATE statement, rest assured.

David