About PostgreSQL Query Plan

Started by Eşref Halıcıoğluover 1 year ago8 messagesgeneral

esref.halicioglu@primeit.com.tr

over 1 year ago

<div><div><span style="font-size:20px;line-height:28px">Hello,</span></div><div> </div><div><span style="font-size:20px;line-height:28px">I have a query in PostgreSQL and I want this query to retrieve only data from the last 3 months. However, when I examine the query plan, I see that all partitions are listed.</span></div><div> </div><div><span style="font-size:20px;line-height:28px">This raises a few questions in my mind:</span></div><ul><li><span style="font-size:20px;line-height:28px">Are all partitions really being accessed, or only the partitions of the last 3 months are being accessed while the other partitions have to be shown in the query plan?</span></li><li><span style="font-size:20px;line-height:28px">If only the relevant partitions are accessed, what is the reason for including other partitions in the query plan?</span></li></ul><div><span style="font-size:20px;line-height:28px">I do not fully understand the logic of this issue. I would be very grateful if you can share information on the subject.</span></div><div> </div><div><div><span style="font-size:20px;line-height:28px">The query plan is as follows.</span></div><div> </div><div><div>Update on "test_table1" tt1 (cost=0.13..159112.84 rows=0 width=0)</div><div> Update on "test_table1_partition_2020_10" tt1</div><div> Update on "test_table1_partition_2020_11" tt1</div><div> Update on "test_table1_partition_2020_12" tt1</div><div> Update on "test_table1_partition_2021_01" tt1</div><div> Update on "test_table1_partition_2021_02" tt1</div><div> Update on "test_table1_partition_2021_03" tt1</div><div> Update on "test_table1_partition_2021_04" tt1</div><div> Update on "test_table1_partition_2021_05" tt1</div><div> Update on "test_table1_partition_2021_06" tt1</div><div> Update on "test_table1_partition_2021_07" tt1</div><div> Update on "test_table1_partition_2021_08" tt1</div><div> Update on "test_table1_partition_2021_09" tt1</div><div> Update on "test_table1_partition_2021_10" tt1</div><div> Update on "test_table1_partition_2021_11" tt1</div><div> Update on "test_table1_partition_2021_12" tt1</div><div> Update on "test_table1_partition_2022_01" tt1</div><div> Update on "test_table1_partition_2022_02" tt1</div><div> Update on "test_table1_partition_2022_03" tt1</div><div> Update on "test_table1_partition_2022_04" tt1</div><div> Update on "test_table1_partition_2022_05" tt1</div><div> Update on "test_table1_partition_2022_06" tt1</div><div> Update on "test_table1_partition_2022_07" tt1</div><div> Update on "test_table1_partition_2022_08" tt1</div><div> Update on "test_table1_partition_2022_09" tt1</div><div> Update on "test_table1_partition_2022_10" tt1</div><div> Update on "test_table1_partition_2022_11" tt1</div><div> Update on "test_table1_partition_2022_12" tt1</div><div> Update on "test_table1_partition_2023_01" tt1</div><div> Update on "test_table1_partition_2023_02" tt1</div><div> Update on "test_table1_partition_2023_03" tt1</div><div> Update on "test_table1_partition_2023_04" tt1</div><div> Update on "test_table1_partition_2023_05" tt1</div><div> Update on "test_table1_partition_2023_06" tt1</div><div> Update on "test_table1_partition_2023_07" tt1</div><div> Update on "test_table1_partition_2023_08" tt1</div><div> Update on "test_table1_partition_2023_09" tt1</div><div> Update on "test_table1_partition_2023_10" tt1</div><div> Update on "test_table1_partition_2023_11" tt1</div><div> Update on "test_table1_partition_2023_12" tt1</div><div> Update on "test_table1_partition_2024_01" tt1</div><div> Update on "test_table1_partition_2024_02" tt1</div><div> Update on "test_table1_partition_2024_03" tt1</div><div> Update on "test_table1_partition_2024_04" tt1</div><div> Update on "test_table1_partition_2024_05" tt1</div><div> Update on "test_table1_partition_2024_06" tt1</div><div> Update on "test_table1_partition_2024_07" tt1</div><div> Update on "test_table1_partition_2024_08" tt1</div><div> Update on "test_table1_partition_2024_09" tt1</div><div> Update on "test_table1_partition_2024_10" tt1_sub1</div><div> Update on "test_table1_partition_2024_11" tt1_sub2</div><div> Update on "test_table1_partition_2024_12" tt1_sub3</div><div> Update on "test_table1_partition_2025_01" tt1_sub4</div><div> Update on "test_table1_partition_2025_02" tt1</div><div> Update on "test_table1_partition_2025_03" tt1</div><div> Update on "test_table1_partition_2025_04" tt1</div><div> Update on "test_table1_partition_2025_05" tt1</div><div> Update on "test_table1_partition_2025_06" tt1</div><div> Update on "test_table1_partition_2025_07" tt1</div><div> Update on "test_table1_partition_2025_08" tt1</div><div> Update on "test_table1_partition_2025_09" tt1</div><div> Update on "test_table1_partition_2025_10" tt1</div><div> Update on "test_table1_partition_2025_11" tt1</div><div> Update on "test_table1_partition_2025_12" tt1</div><div> Update on "test_table1_partition_default" tt1</div><div> -> Nested Loop (cost=0.13..159112.84 rows=1 width=53)</div><div> -> Seq Scan on "temp_test_table1" temp (cost=0.00..19.20 rows=920 width=31)</div><div> -> Append (cost=0.13..172.29 rows=64 width=38)</div><div> Subplans Removed: 60</div><div> -> Index Scan using test_table1_partition_2024_10_pkey on test_table1_partition_2024_10 tt1_sub1 (cost=0.43..4.21 rows=1 width=38)</div><div> Index Cond: (("col1" = temp."col_temp1") AND ("col2" >= (CURRENT_DATE - '3 mons'::interval)) AND ("col2" <= CURRENT_DATE))</div><div> Filter: ((NOT "col_deleted") AND ("col3" = temp."col_temp3"))</div><div> -> Index Scan using test_table1_partition_2024_11_pkey on test_table1_partition_2024_11 tt1_sub2 (cost=0.43..4.23 rows=1 width=38)</div><div> Index Cond: (("col1" = temp."col_temp1") AND ("col2" >= (CURRENT_DATE - '3 mons'::interval)) AND ("col2" <= CURRENT_DATE))</div><div> Filter: ((NOT "col_deleted") AND ("col3" = temp."col_temp3"))</div><div> -> Index Scan using test_table1_partition_2024_12_pkey on test_table1_partition_2024_12 tt1_sub3 (cost=0.43..4.34 rows=1 width=38)</div><div> Index Cond: (("col1" = temp."col_temp1") AND ("col2" >= (CURRENT_DATE - '3 mons'::interval)) AND ("col2" <= CURRENT_DATE))</div><div> Filter: ((NOT "col_deleted") AND ("col3" = temp."col_temp3"))</div><div> -> Index Scan using test_table1_partition_2025_01_pkey on test_table1_partition_2025_01 tt1_sub4 (cost=0.43..3.72 rows=1 width=38)</div><div> Index Cond: (("col1" = temp."col_temp1") AND ("col2" >= (CURRENT_DATE - '3 mons'::interval)) AND ("col2" <= CURRENT_DATE))</div><div> Filter: ((NOT "col_deleted") AND ("col3" = temp."col_temp3"))</div></div></div><div> </div><div> </div><div><span style="font-size:20px;line-height:28px">Thank you,</span></div><div> </div><div><span style="font-size:20px;line-height:28px">Eşref Halıcıoğlu</span></div></div><div> </div><div>-- </div><div><img src="https://avatars.mds.yandex.net/get-mail-signature/1640102/65bb545ae0f44457010ad3aaa33d1fca/orig" /></div><div> </div>

hubert depesz lubaczewski

depesz@depesz.com

over 1 year ago

In reply to: Eşref Halıcıoğlu (#1)

Re: About PostgreSQL Query Plan

On Mon, Jan 13, 2025 at 05:26:09PM +0300, Eşref Halıcıoğlu wrote:

Hello,

I have a query in PostgreSQL and I want this query to retrieve only data from the last 3 months. However, when I examine the query
plan, I see that all partitions are listed.

Please note that your explain is for update, not select (which
"retrieve" in your mail would suggest).

This raises a few questions in my mind:

• Are all partitions really being accessed, or only the partitions of the last 3 months are being accessed while the other
partitions have to be shown in the query plan?

Not really possible to tell without reading explain *analyze*.
Potentially all. But perhaps just fewer.

Best regards,

depesz

Eşref Halıcıoğlu

esref.halicioglu@primeit.com.tr

over 1 year ago

In reply to: hubert depesz lubaczewski (#2)

Re: About PostgreSQL Query Plan

<div><div><div><span style="font-size:20px;line-height:28px">Hello,</span></div><div> </div><div><div><span style="font-size:20px;line-height:28px">Yes, you are right; this query is not a SELECT, it is an UPDATE query, there was a mistake in expressing it here. The columns I want to update here only operate on data from the last 3 months time interval.</span></div></div><div> </div><div><span style="font-size:20px;line-height:28px">I am also sharing the EXPLAIN ANALYZE output of the relevant query below:</span></div><div> </div><div><div>Update on "TestTable1" t1 (cost=0.13..159114.84 rows=0 width=0) (actual time=0.007..0.008 rows=0 loops=1)</div><div> Update on "PartitionTable_2020_10" t1</div><div> Update on "PartitionTable_2020_11" t1</div><div> Update on "PartitionTable_2020_12" t1</div><div> Update on "PartitionTable_2021_01" t1</div><div> Update on "PartitionTable_2021_02" t1</div><div> Update on "PartitionTable_2021_03" t1</div><div> Update on "PartitionTable_2021_04" t1</div><div> Update on "PartitionTable_2021_05" t1</div><div> Update on "PartitionTable_2021_06" t1</div><div> Update on "PartitionTable_2021_07" t1</div><div> Update on "PartitionTable_2021_08" t1</div><div> Update on "PartitionTable_2021_09" t1</div><div> Update on "PartitionTable_2021_10" t1</div><div> Update on "PartitionTable_2021_11" t1</div><div> Update on "PartitionTable_2021_12" t1</div><div> Update on "PartitionTable_2022_01" t1</div><div> Update on "PartitionTable_2022_02" t1</div><div> Update on "PartitionTable_2022_03" t1</div><div> Update on "PartitionTable_2022_04" t1</div><div> Update on "PartitionTable_2022_05" t1</div><div> Update on "PartitionTable_2022_06" t1</div><div> Update on "PartitionTable_2022_07" t1</div><div> Update on "PartitionTable_2022_08" t1</div><div> Update on "PartitionTable_2022_09" t1</div><div> Update on "PartitionTable_2022_10" t1</div><div> Update on "PartitionTable_2022_11" t1</div><div> Update on "PartitionTable_2022_12" t1</div><div> Update on "PartitionTable_2023_01" t1</div><div> Update on "PartitionTable_2023_02" t1</div><div> Update on "PartitionTable_2023_03" t1</div><div> Update on "PartitionTable_2023_04" t1</div><div> Update on "PartitionTable_2023_05" t1</div><div> Update on "PartitionTable_2023_06" t1</div><div> Update on "PartitionTable_2023_07" t1</div><div> Update on "PartitionTable_2023_08" t1</div><div> Update on "PartitionTable_2023_09" t1</div><div> Update on "PartitionTable_2023_10" t1</div><div> Update on "PartitionTable_2023_11" t1</div><div> Update on "PartitionTable_2023_12" t1</div><div> Update on "PartitionTable_2024_01" t1</div><div> Update on "PartitionTable_2024_02" t1</div><div> Update on "PartitionTable_2024_03" t1</div><div> Update on "PartitionTable_2024_04" t1</div><div> Update on "PartitionTable_2024_05" t1</div><div> Update on "PartitionTable_2024_06" t1</div><div> Update on "PartitionTable_2024_07" t1</div><div> Update on "PartitionTable_2024_08" t1</div><div> Update on "PartitionTable_2024_09" t1</div><div> Update on "PartitionTable_2024_10" t2</div><div> Update on "PartitionTable_2024_11" t3</div><div> Update on "PartitionTable_2024_12" t4</div><div> Update on "PartitionTable_2025_01" t5</div><div> Update on "PartitionTable_2025_02" t1</div><div> Update on "PartitionTable_2025_03" t1</div><div> Update on "PartitionTable_2025_04" t1</div><div> Update on "PartitionTable_2025_05" t1</div><div> Update on "PartitionTable_2025_06" t1</div><div> Update on "PartitionTable_2025_07" t1</div><div> Update on "PartitionTable_2025_08" t1</div><div> Update on "PartitionTable_2025_09" t1</div><div> Update on "PartitionTable_2025_10" t1</div><div> Update on "PartitionTable_2025_11" t1</div><div> Update on "PartitionTable_2025_12" t1</div><div> Update on "DefaultPartitionTable" t1</div><div> -> Nested Loop (cost=0.13..159114.84 rows=1 width=53) (actual time=0.006..0.006 rows=0 loops=1)</div><div> -> Seq Scan on "TempTable1" tmp (cost=0.00..19.20 rows=920 width=31) (actual time=0.006..0.006 rows=0 loops=1)</div><div> -> Append (cost=0.13..172.29 rows=64 width=38) (never executed)</div><div> Subplans Removed: 60</div><div> -> Index Scan using partitiontable_2024_10_pkey on "PartitionTable_2024_10" t2 (cost=0.43..4.21 rows=1 width=38) (never executed)</div><div> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))</div><div> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))</div><div> -> Index Scan using partitiontable_2024_11_pkey on "PartitionTable_2024_11" t3 (cost=0.43..4.23 rows=1 width=38) (never executed)</div><div> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))</div><div> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))</div><div> -> Index Scan using partitiontable_2024_12_pkey on "PartitionTable_2024_12" t4 (cost=0.43..4.34 rows=1 width=38) (never executed)</div><div> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))</div><div> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))</div><div> -> Index Scan using partitiontable_2025_01_pkey on "PartitionTable_2025_01" t5 (cost=0.43..3.72 rows=1 width=38) (never executed)</div><div> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))</div><div> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))</div><div>Planning Time: 3.860 ms</div><div>Execution Time: 0.066 ms</div></div><div> </div><div><span style="font-size:20px;line-height:28px">Thank you for your interest and support.</span></div><div> </div><div><span style="font-size:20px;line-height:28px">Good work,</span></div><div> </div><div><span style="font-size:20px;line-height:28px">Eşref Halıcıoğlu</span></div></div><div> </div><div> </div><div> </div></div><div> </div><div>----------------</div><div>To: Eşref Halıcıoğlu (esref.halicioglu@primeit.com.tr);</div><div>Cc: pgsql-general@lists.postgresql.org;</div><div>Subject: About PostgreSQL Query Plan;</div><div>13.01.2025, 18:42, "hubert depesz lubaczewski" <depesz@depesz.com>:</div><blockquote><p>On Mon, Jan 13, 2025 at 05:26:09PM +0300, Eşref Halıcıoğlu wrote:</p><blockquote> Hello,<br /> <br /> I have a query in PostgreSQL and I want this query to retrieve only data from the last 3 months. However, when I examine the query<br /> plan, I see that all partitions are listed.</blockquote><p><br />Please note that your explain is for update, not select (which<br />"retrieve" in your mail would suggest).<br /> </p><blockquote> This raises a few questions in my mind:<br /> <br /> • Are all partitions really being accessed, or only the partitions of the last 3 months are being accessed while the other<br /> partitions have to be shown in the query plan?</blockquote><p><br />Not really possible to tell without reading explain *analyze*.<br />Potentially all. But perhaps just fewer.<br /><br />Best regards,<br /><br />depesz<br /> </p></blockquote><div> </div><div> </div><div>-- </div><div><img src="https://avatars.mds.yandex.net/get-mail-signature/1640102/65bb545ae0f44457010ad3aaa33d1fca/orig" /></div><div> </div>

hubert depesz lubaczewski

depesz@depesz.com

over 1 year ago

In reply to: Eşref Halıcıoğlu (#3)

Re: About PostgreSQL Query Plan

On Mon, Jan 13, 2025 at 07:48:09PM +0300, Eşref Halıcıoğlu wrote:

Hello,

Yes, you are right; this query is not a SELECT, it is an UPDATE query, there was a mistake in expressing it here. The columns I want
to update here only operate on data from the last 3 months time interval.

I am also sharing the EXPLAIN ANALYZE output of the relevant query below:

Update on "TestTable1" t1 (cost=0.13..159114.84 rows=0 width=0) (actual time=0.007..0.008 rows=0 loops=1)
Update on "PartitionTable_2020_10" t1

… 61 lines removed …

Update on "PartitionTable_2025_12" t1
Update on "DefaultPartitionTable" t1
-> Nested Loop (cost=0.13..159114.84 rows=1 width=53) (actual time=0.006..0.006 rows=0 loops=1)
        -> Seq Scan on "TempTable1" tmp (cost=0.00..19.20 rows=920 width=31) (actual time=0.006..0.006 rows=0 loops=1)
        -> Append (cost=0.13..172.29 rows=64 width=38) (never executed)
              Subplans Removed: 60
              -> Index Scan using partitiontable_2024_10_pkey on "PartitionTable_2024_10" t2 (cost=0.43..4.21 rows=1 width=38) (never executed)
                    Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))
                    Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))
              -> Index Scan using partitiontable_2024_11_pkey on "PartitionTable_2024_11" t3 (cost=0.43..4.23 rows=1 width=38) (never executed)
                    Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))
                    Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))
              -> Index Scan using partitiontable_2024_12_pkey on "PartitionTable_2024_12" t4 (cost=0.43..4.34 rows=1 width=38) (never executed)
                    Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))
                    Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))
              -> Index Scan using partitiontable_2025_01_pkey on "PartitionTable_2025_01" t5 (cost=0.43..3.72 rows=1 width=38) (never executed)
                    Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))
                    Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))
Planning Time: 3.860 ms
Execution Time: 0.066 ms

Well, it helped, and I missed some bits of information earlier.
Generally it looks that it was trying to scan only 4 partitions, but
they all got skipped.

Best regards,

depesz

Eşref Halıcıoğlu

esref.halicioglu@primeit.com.tr

over 1 year ago

In reply to: hubert depesz lubaczewski (#4)

Re: About PostgreSQL Query Plan

<div><div>Hello,</div><div> </div><div>Yes, you are right; it seems that only 4 batches had data changes. However, the query also accessed other batches and then removed them again. What could be the reason for this and how can it be solved?</div><div> </div><div>Obviously, I would like to understand this situation better and learn the solution.</div><div> </div><div>I would be very grateful if you could help me.</div><div> </div><div>Thank you,</div><div> </div><div>Eşref Halıcıoğlu</div></div><div> </div><div>----------------</div><div>To: Eşref Halıcıoğlu (esref.halicioglu@primeit.com.tr);</div><div>Cc: pgsql-general@lists.postgresql.org;</div><div>Subject: About PostgreSQL Query Plan;</div><div>13.01.2025, 19:56, "hubert depesz lubaczewski" <depesz@depesz.com>:</div><blockquote><p>On Mon, Jan 13, 2025 at 07:48:09PM +0300, Eşref Halıcıoğlu wrote:</p><blockquote> Hello,<br /> <br /> Yes, you are right; this query is not a SELECT, it is an UPDATE query, there was a mistake in expressing it here. The columns I want<br /> to update here only operate on data from the last 3 months time interval.<br /> <br /> I am also sharing the EXPLAIN ANALYZE output of the relevant query below:<br /> <br /> Update on "TestTable1" t1 (cost=0.13..159114.84 rows=0 width=0) (actual time=0.007..0.008 rows=0 loops=1)<br /> Update on "PartitionTable_2020_10" t1</blockquote><p>… 61 lines removed …</p><blockquote> Update on "PartitionTable_2025_12" t1<br /> Update on "DefaultPartitionTable" t1<br /> -> Nested Loop (cost=0.13..159114.84 rows=1 width=53) (actual time=0.006..0.006 rows=0 loops=1)<br /> -> Seq Scan on "TempTable1" tmp (cost=0.00..19.20 rows=920 width=31) (actual time=0.006..0.006 rows=0 loops=1)<br /> -> Append (cost=0.13..172.29 rows=64 width=38) (never executed)<br /> Subplans Removed: 60<br /> -> Index Scan using partitiontable_2024_10_pkey on "PartitionTable_2024_10" t2 (cost=0.43..4.21 rows=1 width=38) (never executed)<br /> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))<br /> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))<br /> -> Index Scan using partitiontable_2024_11_pkey on "PartitionTable_2024_11" t3 (cost=0.43..4.23 rows=1 width=38) (never executed)<br /> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))<br /> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))<br /> -> Index Scan using partitiontable_2024_12_pkey on "PartitionTable_2024_12" t4 (cost=0.43..4.34 rows=1 width=38) (never executed)<br /> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))<br /> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))<br /> -> Index Scan using partitiontable_2025_01_pkey on "PartitionTable_2025_01" t5 (cost=0.43..3.72 rows=1 width=38) (never executed)<br /> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))<br /> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))<br /> Planning Time: 3.860 ms<br /> Execution Time: 0.066 ms</blockquote><p><br />Well, it helped, and I missed some bits of information earlier.<br />Generally it looks that it was trying to scan only 4 partitions, but<br />they all got skipped.<br /><br />Best regards,<br /><br />depesz<br /> </p></blockquote><div> </div><div> </div><div>-- </div><div><img src="https://avatars.mds.yandex.net/get-mail-signature/1640102/65bb545ae0f44457010ad3aaa33d1fca/orig" /></div><div> </div>

hubert depesz lubaczewski

depesz@depesz.com

over 1 year ago

In reply to: Eşref Halıcıoğlu (#5)

Re: About PostgreSQL Query Plan

On Mon, Jan 13, 2025 at 08:01:56PM +0300, Eşref Halıcıoğlu wrote:

Yes, you are right; it seems that only 4 batches had data changes. However, the query also accessed other batches and then removed
them again. What could be the reason for this and how can it be solved?

Obviously, I would like to understand this situation better and learn the solution.

I would be very grateful if you could help me.

You would need to have explain analyze form some case where the update
actually updates something. And query that you used would be helpful
too.

Best regards,

depesz

David Rowley

dgrowleyml@gmail.com

over 1 year ago

In reply to: Eşref Halıcıoğlu (#1)

Re: About PostgreSQL Query Plan

On Tue, 14 Jan 2025 at 03:45, Eşref Halıcıoğlu
<esref.halicioglu@primeit.com.tr> wrote:

I do not fully understand the logic of this issue. I would be very grateful if you can share information on the subject.

The query plan is as follows.

Update on "test_table1" tt1 (cost=0.13..159112.84 rows=0 width=0)
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=0.13..159112.84 rows=1 width=53)
-> Seq Scan on "temp_test_table1" temp (cost=0.00..19.20 rows=920 width=31)
-> Append (cost=0.13..172.29 rows=64 width=38)
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

Eşref Halıcıoğlu

esref.halicioglu@primeit.com.tr

over 1 year ago

In reply to: David Rowley (#7)

Re: About PostgreSQL Query Plan

<div><div>Hello,</div><div> </div><div>Okay, thank you very much, Mr. David, for your support and the information,</div><div> </div><div>Eşref</div></div><div> </div><div>----------------</div><div>To: Eşref Halıcıoğlu (esref.halicioglu@primeit.com.tr);</div><div>Cc: pgsql-general@lists.postgresql.org;</div><div>Subject: About PostgreSQL Query Plan;</div><div>14.01.2025, 13:09, "David Rowley" <dgrowleyml@gmail.com>:</div><blockquote><p>On Tue, 14 Jan 2025 at 03:45, Eşref Halıcıoğlu<br /><<a href="mailto:esref.halicioglu@primeit.com.tr" rel="noopener noreferrer">esref.halicioglu@primeit.com.tr</a>> wrote:</p><blockquote> I do not fully understand the logic of this issue. I would be very grateful if you can share information on the subject.<br /><br /> The query plan is as follows.<br /><br /> Update on "test_table1" tt1 (cost=0.13..159112.84 rows=0 width=0)<br /> Update on "test_table1_partition_2020_10" tt1<br /> Update on "test_table1_partition_2020_11" tt1</blockquote><p>...<br /> Update on "test_table1_partition_2025_12" tt1</p><blockquote> Update on "test_table1_partition_default" tt1<br /> -> Nested Loop (cost=0.13..159112.84 rows=1 width=53)<br /> -> Seq Scan on "temp_test_table1" temp (cost=0.00..19.20 rows=920 width=31)<br /> -> Append (cost=0.13..172.29 rows=64 width=38)<br /> Subplans Removed: 60</blockquote><p><br />The partitions mentioned in the "Update on" portion of the EXPLAIN<br />aren't being scanned. These are just result relations that potentially<br />could have tuples routed to them. The key part of the EXPLAIN output<br />to knowing that the unrelated partitions are pruned is from which<br />partitions are mentioned below the "Append" node. You can see that 60<br />of your 64 partitions were pruned with the "Subplans Removed: 60"<br />part. The executor is only going to scan the 4 remaining ones that you<br />see below the "Append".<br /><br />I wouldn't worry too much about the additional partitions mentioned in<br />the "Update on". We maybe could do a bit more work to initialise those<br />more lazily as we do for INSERT statements, but I'd be surprised if it<br />was a problem for 64 partitions, especially so for an update statement<br />that might be touching 3 months of data. Nothing about these existing<br />in the "Update on" portion of the EXPLAIN output means that that<br />partition will be scanned by the UPDATE statement, rest assured.<br /><br />David</p></blockquote><div> </div><div> </div><div>-- </div><div><img src="https://avatars.mds.yandex.net/get-mail-signature/1640102/65bb545ae0f44457010ad3aaa33d1fca/orig" /></div><div> </div>