Обсуждение: 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
examinethe 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
theother
 
>     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

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
columnsI 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=1width=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=1width=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=1width=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=1width=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

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