public inbox for [email protected]
help / color / mirror / Atom feedRe: About PostgreSQL Query Plan
5+ messages / 2 participants
[nested] [flat]
* Re: About PostgreSQL Query Plan
@ 2025-01-13 15:42 hubert depesz lubaczewski <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: hubert depesz lubaczewski @ 2025-01-13 15:42 UTC (permalink / raw)
To: Eşref Halıcıoğlu <[email protected]>; +Cc: [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: About PostgreSQL Query Plan
@ 2025-01-13 16:48 Eşref Halıcıoğlu <[email protected]>
parent: hubert depesz lubaczewski <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Eşref Halıcıoğlu @ 2025-01-13 16:48 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: [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>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: About PostgreSQL Query Plan
@ 2025-01-13 16:56 hubert depesz lubaczewski <[email protected]>
parent: Eşref Halıcıoğlu <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: hubert depesz lubaczewski @ 2025-01-13 16:56 UTC (permalink / raw)
To: Eşref Halıcıoğlu <[email protected]>; +Cc: [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: About PostgreSQL Query Plan
@ 2025-01-13 17:01 Eşref Halıcıoğlu <[email protected]>
parent: hubert depesz lubaczewski <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Eşref Halıcıoğlu @ 2025-01-13 17:01 UTC (permalink / raw)
To: [email protected] <[email protected]>; +Cc: [email protected] <[email protected]>
<div><div>Hello,</div><div> </div><div>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?</div><div> </div><div>Obviously, I would like to understand this situation better and learn the solution.</div><div> </div><div>I would be very grateful if you could help me.</div><div> </div><div>Thank you,</div><div> </div><div>Eşref Halıcıoğlu</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, 19:56, "hubert depesz lubaczewski" <[email protected]>:</div><blockquote><p>On Mon, Jan 13, 2025 at 07:48:09PM +0300, Eşref Halıcıoğlu wrote:</p><blockquote> Hello,<br /> <br /> 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<br /> to update here only operate on data from the last 3 months time interval.<br /> <br /> I am also sharing the EXPLAIN ANALYZE output of the relevant query below:<br /> <br /> Update on "TestTable1" t1 (cost=0.13..159114.84 rows=0 width=0) (actual time=0.007..0.008 rows=0 loops=1)<br /> Update on "PartitionTable_2020_10" t1</blockquote><p>… 61 lines removed …</p><blockquote> Update on "PartitionTable_2025_12" t1<br /> Update on "DefaultPartitionTable" t1<br /> -> Nested Loop (cost=0.13..159114.84 rows=1 width=53) (actual time=0.006..0.006 rows=0 loops=1)<br /> -> Seq Scan on "TempTable1" tmp (cost=0.00..19.20 rows=920 width=31) (actual time=0.006..0.006 rows=0 loops=1)<br /> -> Append (cost=0.13..172.29 rows=64 width=38) (never executed)<br /> Subplans Removed: 60<br /> -> Index Scan using partitiontable_2024_10_pkey on "PartitionTable_2024_10" t2 (cost=0.43..4.21 rows=1 width=38) (never executed)<br /> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))<br /> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))<br /> -> Index Scan using partitiontable_2024_11_pkey on "PartitionTable_2024_11" t3 (cost=0.43..4.23 rows=1 width=38) (never executed)<br /> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))<br /> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))<br /> -> Index Scan using partitiontable_2024_12_pkey on "PartitionTable_2024_12" t4 (cost=0.43..4.34 rows=1 width=38) (never executed)<br /> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))<br /> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))<br /> -> Index Scan using partitiontable_2025_01_pkey on "PartitionTable_2025_01" t5 (cost=0.43..3.72 rows=1 width=38) (never executed)<br /> Index Cond: (("Col1" = tmp."Col2") AND ("Col3" >= (CURRENT_DATE - '3 mons'::interval)) AND ("Col3" <= CURRENT_DATE))<br /> Filter: ((NOT "IsDeleted") AND ("Col4" = tmp."Col5"))<br /> Planning Time: 3.860 ms<br /> Execution Time: 0.066 ms</blockquote><p><br />Well, it helped, and I missed some bits of information earlier.<br />Generally it looks that it was trying to scan only 4 partitions, but<br />they all got skipped.<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>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: About PostgreSQL Query Plan
@ 2025-01-13 17:04 hubert depesz lubaczewski <[email protected]>
parent: Eşref Halıcıoğlu <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: hubert depesz lubaczewski @ 2025-01-13 17:04 UTC (permalink / raw)
To: Eşref Halıcıoğlu <[email protected]>; +Cc: [email protected] <[email protected]>
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?
>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-01-13 17:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-13 15:42 Re: About PostgreSQL Query Plan hubert depesz lubaczewski <[email protected]>
2025-01-13 16:48 ` Eşref Halıcıoğlu <[email protected]>
2025-01-13 16:56 ` hubert depesz lubaczewski <[email protected]>
2025-01-13 17:01 ` Eşref Halıcıoğlu <[email protected]>
2025-01-13 17:04 ` hubert depesz lubaczewski <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox