public inbox for [email protected]
help / color / mirror / Atom feedFrom: Eşref Halıcıoğlu <[email protected]>
To: [email protected] <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: About PostgreSQL Query Plan
Date: Mon, 13 Jan 2025 19:48:09 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<div><div><div><span style="font-size:20px;line-height:28px">Hello,</span></div><div> </div><div><div><span style="font-size:20px;line-height:28px">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.</span></div></div><div> </div><div><span style="font-size:20px;line-height:28px">I am also sharing the EXPLAIN ANALYZE output of the relevant query below:</span></div><div> </div><div><div>Update on "TestTable1" t1 (cost=0.13..159114.84 rows=0 width=0) (actual time=0.007..0.008 rows=0 loops=1)</div><div> Update on "PartitionTable_2020_10" t1</div><div> Update on "PartitionTable_2020_11" t1</div><div> Update on "PartitionTable_2020_12" t1</div><div> Update on "PartitionTable_2021_01" t1</div><div> Update on "PartitionTable_2021_02" t1</div><div> Update on "PartitionTable_2021_03" t1</div><div> Update on "PartitionTable_2021_04" t1</div><div> Update on "PartitionTable_2021_05" t1</div><div> Update on "PartitionTable_2021_06" t1</div><div> Update on "PartitionTable_2021_07" t1</div><div> Update on "PartitionTable_2021_08" t1</div><div> Update on "PartitionTable_2021_09" t1</div><div> Update on "PartitionTable_2021_10" t1</div><div> Update on "PartitionTable_2021_11" t1</div><div> Update on "PartitionTable_2021_12" t1</div><div> Update on "PartitionTable_2022_01" t1</div><div> Update on "PartitionTable_2022_02" t1</div><div> Update on "PartitionTable_2022_03" t1</div><div> Update on "PartitionTable_2022_04" t1</div><div> Update on "PartitionTable_2022_05" t1</div><div> Update on "PartitionTable_2022_06" t1</div><div> Update on "PartitionTable_2022_07" t1</div><div> Update on "PartitionTable_2022_08" t1</div><div> Update on "PartitionTable_2022_09" t1</div><div> Update on "PartitionTable_2022_10" t1</div><div> Update on "PartitionTable_2022_11" t1</div><div> Update on "PartitionTable_2022_12" t1</div><div> Update on "PartitionTable_2023_01" t1</div><div> Update on "PartitionTable_2023_02" t1</div><div> Update on "PartitionTable_2023_03" t1</div><div> Update on "PartitionTable_2023_04" t1</div><div> Update on "PartitionTable_2023_05" t1</div><div> Update on "PartitionTable_2023_06" t1</div><div> Update on "PartitionTable_2023_07" t1</div><div> Update on "PartitionTable_2023_08" t1</div><div> Update on "PartitionTable_2023_09" t1</div><div> Update on "PartitionTable_2023_10" t1</div><div> Update on "PartitionTable_2023_11" t1</div><div> Update on "PartitionTable_2023_12" t1</div><div> Update on "PartitionTable_2024_01" t1</div><div> Update on "PartitionTable_2024_02" t1</div><div> Update on "PartitionTable_2024_03" t1</div><div> Update on "PartitionTable_2024_04" t1</div><div> Update on "PartitionTable_2024_05" t1</div><div> Update on "PartitionTable_2024_06" t1</div><div> Update on "PartitionTable_2024_07" t1</div><div> Update on "PartitionTable_2024_08" t1</div><div> Update on "PartitionTable_2024_09" t1</div><div> Update on "PartitionTable_2024_10" t2</div><div> Update on "PartitionTable_2024_11" t3</div><div> Update on "PartitionTable_2024_12" t4</div><div> Update on "PartitionTable_2025_01" t5</div><div> Update on "PartitionTable_2025_02" t1</div><div> Update on "PartitionTable_2025_03" t1</div><div> Update on "PartitionTable_2025_04" t1</div><div> Update on "PartitionTable_2025_05" t1</div><div> Update on "PartitionTable_2025_06" t1</div><div> Update on "PartitionTable_2025_07" t1</div><div> Update on "PartitionTable_2025_08" t1</div><div> Update on "PartitionTable_2025_09" t1</div><div> Update on "PartitionTable_2025_10" t1</div><div> Update on "PartitionTable_2025_11" t1</div><div> Update on "PartitionTable_2025_12" t1</div><div> Update on "DefaultPartitionTable" t1</div><div> -> Nested Loop (cost=0.13..159114.84 rows=1 width=53) (actual time=0.006..0.006 rows=0 loops=1)</div><div> -> Seq Scan on "TempTable1" tmp (cost=0.00..19.20 rows=920 width=31) (actual time=0.006..0.006 rows=0 loops=1)</div><div> -> Append (cost=0.13..172.29 rows=64 width=38) (never executed)</div><div> Subplans Removed: 60</div><div> -> Index Scan using partitiontable_2024_10_pkey on "PartitionTable_2024_10" t2 (cost=0.43..4.21 rows=1 width=38) (never executed)</div><div> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))</div><div> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))</div><div> -> Index Scan using partitiontable_2024_11_pkey on "PartitionTable_2024_11" t3 (cost=0.43..4.23 rows=1 width=38) (never executed)</div><div> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))</div><div> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))</div><div> -> Index Scan using partitiontable_2024_12_pkey on "PartitionTable_2024_12" t4 (cost=0.43..4.34 rows=1 width=38) (never executed)</div><div> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))</div><div> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))</div><div> -> Index Scan using partitiontable_2025_01_pkey on "PartitionTable_2025_01" t5 (cost=0.43..3.72 rows=1 width=38) (never executed)</div><div> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))</div><div> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))</div><div>Planning Time: 3.860 ms</div><div>Execution Time: 0.066 ms</div></div><div> </div><div><span style="font-size:20px;line-height:28px">Thank you for your interest and support.</span></div><div> </div><div><span style="font-size:20px;line-height:28px">Good work,</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> </div></div><div> </div><div>----------------</div><div>To: Eşref Halıcıoğlu ([email protected]);</div><div>Cc: [email protected];</div><div>Subject: About PostgreSQL Query Plan;</div><div>13.01.2025, 18:42, "hubert depesz lubaczewski" <[email protected]>:</div><blockquote><p>On Mon, Jan 13, 2025 at 05:26:09PM +0300, Eşref Halıcıoğlu wrote:</p><blockquote> Hello,<br /> <br /> 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<br /> plan, I see that all partitions are listed.</blockquote><p><br />Please note that your explain is for update, not select (which<br />"retrieve" in your mail would suggest).<br /> </p><blockquote> This raises a few questions in my mind:<br /> <br /> • Are all partitions really being accessed, or only the partitions of the last 3 months are being accessed while the other<br /> partitions have to be shown in the query plan?</blockquote><p><br />Not really possible to tell without reading explain *analyze*.<br />Potentially all. But perhaps just fewer.<br /><br />Best regards,<br /><br />depesz<br /> </p></blockquote><div> </div><div> </div><div>-- </div><div><img src="https://avatars.mds.yandex.net/get-mail-signature/1640102/65bb545ae0f44457010ad3aaa33d1fca/orig"; /></div><div> </div>
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: About PostgreSQL Query Plan
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox