<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>