Subject: Re: About PostgreSQL Query Plan

From: hubert depesz lubaczewski <[email-masked]>
Date: Mon, 13 Jan 2025 16:42:56 +0100

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

---

From: Eşref Halıcıoğlu <[email-masked]>
Date: Mon, 13 Jan 2025 19:48:09 +0300

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

Update on "PartitionTable_2020_11" t1

Update on "PartitionTable_2020_12" t1

Update on "PartitionTable_2021_01" t1

Update on "PartitionTable_2021_02" t1

Update on "PartitionTable_2021_03" t1

Update on "PartitionTable_2021_04" t1

Update on "PartitionTable_2021_05" t1

Update on "PartitionTable_2021_06" t1

Update on "PartitionTable_2021_07" t1

Update on "PartitionTable_2021_08" t1

Update on "PartitionTable_2021_09" t1

Update on "PartitionTable_2021_10" t1

Update on "PartitionTable_2021_11" t1

Update on "PartitionTable_2021_12" t1

Update on "PartitionTable_2022_01" t1

Update on "PartitionTable_2022_02" t1

Update on "PartitionTable_2022_03" t1

Update on "PartitionTable_2022_04" t1

Update on "PartitionTable_2022_05" t1

Update on "PartitionTable_2022_06" t1

Update on "PartitionTable_2022_07" t1

Update on "PartitionTable_2022_08" t1

Update on "PartitionTable_2022_09" t1

Update on "PartitionTable_2022_10" t1

Update on "PartitionTable_2022_11" t1

Update on "PartitionTable_2022_12" t1

Update on "PartitionTable_2023_01" t1

Update on "PartitionTable_2023_02" t1

Update on "PartitionTable_2023_03" t1

Update on "PartitionTable_2023_04" t1

Update on "PartitionTable_2023_05" t1

Update on "PartitionTable_2023_06" t1

Update on "PartitionTable_2023_07" t1

Update on "PartitionTable_2023_08" t1

Update on "PartitionTable_2023_09" t1

Update on "PartitionTable_2023_10" t1

Update on "PartitionTable_2023_11" t1

Update on "PartitionTable_2023_12" t1

Update on "PartitionTable_2024_01" t1

Update on "PartitionTable_2024_02" t1

Update on "PartitionTable_2024_03" t1

Update on "PartitionTable_2024_04" t1

Update on "PartitionTable_2024_05" t1

Update on "PartitionTable_2024_06" t1

Update on "PartitionTable_2024_07" t1

Update on "PartitionTable_2024_08" t1

Update on "PartitionTable_2024_09" t1

Update on "PartitionTable_2024_10" t2

Update on "PartitionTable_2024_11" t3

Update on "PartitionTable_2024_12" t4

Update on "PartitionTable_2025_01" t5

Update on "PartitionTable_2025_02" t1

Update on "PartitionTable_2025_03" t1

Update on "PartitionTable_2025_04" t1

Update on "PartitionTable_2025_05" t1

Update on "PartitionTable_2025_06" t1

Update on "PartitionTable_2025_07" t1

Update on "PartitionTable_2025_08" t1

Update on "PartitionTable_2025_09" t1

Update on "PartitionTable_2025_10" t1

Update on "PartitionTable_2025_11" t1

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

Thank you for your interest and support.

Good work,

Eşref Halıcıoğlu

----------------

To: Eşref Halıcıoğlu ([email-masked]);

Cc: [email-masked];

Subject: About PostgreSQL Query Plan;

13.01.2025, 18:42, "hubert depesz lubaczewski" <[email-masked]>:

Hello,

Best regards,

depesz

--

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Mon, 13 Jan 2025 17:56:55 +0100

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.
>  
… 61 lines removed …
>                     Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))

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

---

From: Eşref Halıcıoğlu <[email-masked]>
Date: Mon, 13 Jan 2025 20:01:56 +0300

Hello,

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.

Thank you,

Eşref Halıcıoğlu

----------------

13.01.2025, 19:56, "hubert depesz lubaczewski" <[email-masked]>:

Hello,

… 61 lines removed …

Best regards,

depesz

--

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Mon, 13 Jan 2025 18:04:21 +0100

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

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