public inbox for [email protected]
help / color / mirror / Atom feedReading execution plan - first row time vs last row time
3+ messages / 3 participants
[nested] [flat]
* Reading execution plan - first row time vs last row time
@ 2024-10-01 13:53 =?iso-8859-2?Q?Pecs=F6k_J=E1n?= <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: =?iso-8859-2?Q?Pecs=F6k_J=E1n?= @ 2024-10-01 13:53 UTC (permalink / raw)
To: [email protected] <[email protected]>
Dear community,
We see significant difference in explain analyze Actual time in the first line of execution plan and Execution time in the last line of execution plan. What can be the reason?
For example, first line of execution plan:
Gather (cost=1038.49..257627100.42 rows=7943222226 width=104) (actual time=531.925..3673159.806 rows=7943221053 loops=1)
This is 3673159/1000/60 = 61 minutes
And last 2 lines of execution plan:
Planning Time: 3000.399 ms
Execution Time: 21208130.602 ms
This is : 21208130/1000/60= 353 minutes
What happened in 353 - 61 = 292 minutes? We speculate that disk writes. Query possibly written cca 0,75TB of data, by create table as select query.
S pozdravem
Ján Pecsők
Tychonova 2, 160 00 Praha
Mobil: +420 775 942 871
[email protected]<mailto:[email protected]>
www.profinit.eu<https://www.profinit.eu/;
[cid:[email protected]]<https://www.profinit.eu/;
Attachments:
[image/png] image001.png (5.0K, 3-image001.png)
download | view image
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Reading execution plan - first row time vs last row time
@ 2024-10-01 14:15 Adrian Klaver <[email protected]>
parent: =?iso-8859-2?Q?Pecs=F6k_J=E1n?= <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Adrian Klaver @ 2024-10-01 14:15 UTC (permalink / raw)
To: Pecsök Ján <[email protected]>; [email protected] <[email protected]>
On 10/1/24 06:53, Pecsök Ján wrote:
> Dear community,
>
> We see significant difference in explain analyze Actual time in the
> first line of execution plan and Execution time in the last line of
> execution plan. What can be the reason?
>
> For example, first line of execution plan:
>
> Gather (cost=1038.49..257627100.42 rows=7943222226 width=104) (actual
> time=531.925..3673159.806 rows=7943221053 loops=1)
>
> This is 3673159/1000/60 = 61 minutes
>
> And last 2 lines of execution plan:
>
> Planning Time: 3000.399 ms
>
> Execution Time: 21208130.602 ms
>
> This is : 21208130/1000/60= 353 minutes
>
> What happened in 353 – 61 = 292 minutes? We speculate that disk writes.
> Query possibly written cca 0,75TB of data, by create table as select query.
You will need to show the complete EXPLAIN ANALYZE output as well as query.
>
>
> S pozdravem
>
> *Ján Pecsők*
>
> Tychonova 2, 160 00 Praha
> Mobil: +420 775 942 871
> [email protected] <mailto:[email protected]>
> www.profinit.eu <https://www.profinit.eu/;
>
> <https://www.profinit.eu/;
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Reading execution plan - first row time vs last row time
@ 2024-10-01 14:39 Greg Sabino Mullane <[email protected]>
parent: =?iso-8859-2?Q?Pecs=F6k_J=E1n?= <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Greg Sabino Mullane @ 2024-10-01 14:39 UTC (permalink / raw)
To: Pecsök Ján <[email protected]>; +Cc: [email protected] <[email protected]>
On Tue, Oct 1, 2024 at 9:53 AM Pecsök Ján <[email protected]> wrote:
> We see significant difference in explain analyze Actual time in the first
> line of execution plan and Execution time in the last line of execution
> plan. What can be the reason?
>
>
>
> For example, first line of execution plan:
>
> Gather (cost=1038.49..257627100.42 rows=7943222226 width=104) (actual
> time=531.925..3673159.806 rows=7943221053 loops=1)
>
It takes an hour for Postgres to gather up 7.9 BILLION (!) rows, but then
it is going to take additional time to do something with all of those rows.
That's where the rest of your time is going. Seeing your actual query would
be a help. If this is a CTAS (Create Table As Select), then you have an
hour for the 'Select' part, and the rest of your time is in the 'Create
Table' part.
Cheers,
Greg
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-10-01 14:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-01 13:53 Reading execution plan - first row time vs last row time =?iso-8859-2?Q?Pecs=F6k_J=E1n?= <[email protected]>
2024-10-01 14:15 ` Adrian Klaver <[email protected]>
2024-10-01 14:39 ` Greg Sabino Mullane <[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