Date: Mon, 13 Jan 2025 17:56:55 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: =?utf-8?B?RcWfcmVmIEhhbMSxY8Sxb8SfbHU=?= <esref.halicioglu@primeit.com.tr>
Cc: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: About PostgreSQL Query Plan
Message-ID: <Z4VF16YkM-tsJmIP@depesz.com>
Reply-To: depesz@depesz.com
References: <1348181736777057@mail.yandex.com>
 <Z4U0gAcTLQ8sX29J@depesz.com>
 <260121736786553@mail.yandex.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <260121736786553@mail.yandex.com>
Archived-At: <https://www.postgresql.org/message-id/Z4VF16YkM-tsJmIP%40depesz.com>
Precedence: bulk

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