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.

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?
If only the relevant partitions are accessed, what is the reason for including other partitions in the query plan?

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_2020_12" tt1

Update on "test_table1_partition_2021_01" tt1

Update on "test_table1_partition_2021_02" tt1

Update on "test_table1_partition_2021_03" tt1

Update on "test_table1_partition_2021_04" tt1

Update on "test_table1_partition_2021_05" tt1

Update on "test_table1_partition_2021_06" tt1

Update on "test_table1_partition_2021_07" tt1

Update on "test_table1_partition_2021_08" tt1

Update on "test_table1_partition_2021_09" tt1

Update on "test_table1_partition_2021_10" tt1

Update on "test_table1_partition_2021_11" tt1

Update on "test_table1_partition_2021_12" tt1

Update on "test_table1_partition_2022_01" tt1

Update on "test_table1_partition_2022_02" tt1

Update on "test_table1_partition_2022_03" tt1

Update on "test_table1_partition_2022_04" tt1

Update on "test_table1_partition_2022_05" tt1

Update on "test_table1_partition_2022_06" tt1

Update on "test_table1_partition_2022_07" tt1

Update on "test_table1_partition_2022_08" tt1

Update on "test_table1_partition_2022_09" tt1

Update on "test_table1_partition_2022_10" tt1

Update on "test_table1_partition_2022_11" tt1

Update on "test_table1_partition_2022_12" tt1

Update on "test_table1_partition_2023_01" tt1

Update on "test_table1_partition_2023_02" tt1

Update on "test_table1_partition_2023_03" tt1

Update on "test_table1_partition_2023_04" tt1

Update on "test_table1_partition_2023_05" tt1

Update on "test_table1_partition_2023_06" tt1

Update on "test_table1_partition_2023_07" tt1

Update on "test_table1_partition_2023_08" tt1

Update on "test_table1_partition_2023_09" tt1

Update on "test_table1_partition_2023_10" tt1

Update on "test_table1_partition_2023_11" tt1

Update on "test_table1_partition_2023_12" tt1

Update on "test_table1_partition_2024_01" tt1

Update on "test_table1_partition_2024_02" tt1

Update on "test_table1_partition_2024_03" tt1

Update on "test_table1_partition_2024_04" tt1

Update on "test_table1_partition_2024_05" tt1

Update on "test_table1_partition_2024_06" tt1

Update on "test_table1_partition_2024_07" tt1

Update on "test_table1_partition_2024_08" tt1

Update on "test_table1_partition_2024_09" tt1

Update on "test_table1_partition_2024_10" tt1_sub1

Update on "test_table1_partition_2024_11" tt1_sub2

Update on "test_table1_partition_2024_12" tt1_sub3

Update on "test_table1_partition_2025_01" tt1_sub4

Update on "test_table1_partition_2025_02" tt1

Update on "test_table1_partition_2025_03" tt1

Update on "test_table1_partition_2025_04" tt1

Update on "test_table1_partition_2025_05" tt1

Update on "test_table1_partition_2025_06" tt1

Update on "test_table1_partition_2025_07" tt1

Update on "test_table1_partition_2025_08" tt1

Update on "test_table1_partition_2025_09" tt1

Update on "test_table1_partition_2025_10" tt1

Update on "test_table1_partition_2025_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

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

Index Cond: (("col1" = temp."col_temp1") AND ("col2" >= (CURRENT_DATE - '3 mons'::interval)) AND ("col2" <= CURRENT_DATE))

Filter: ((NOT "col_deleted") AND ("col3" = temp."col_temp3"))

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

Index Cond: (("col1" = temp."col_temp1") AND ("col2" >= (CURRENT_DATE - '3 mons'::interval)) AND ("col2" <= CURRENT_DATE))

Filter: ((NOT "col_deleted") AND ("col3" = temp."col_temp3"))

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

Index Cond: (("col1" = temp."col_temp1") AND ("col2" >= (CURRENT_DATE - '3 mons'::interval)) AND ("col2" <= CURRENT_DATE))

Filter: ((NOT "col_deleted") AND ("col3" = temp."col_temp3"))

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

Index Cond: (("col1" = temp."col_temp1") AND ("col2" >= (CURRENT_DATE - '3 mons'::interval)) AND ("col2" <= CURRENT_DATE))

Filter: ((NOT "col_deleted") AND ("col3" = temp."col_temp3"))

Thank you,

Eşref Halıcıoğlu