pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedNumber of Shared Blocks Hit
2+ messages / 2 participants
[nested] [flat]
* Number of Shared Blocks Hit
@ 2021-03-04 11:23 luis.roberto@siscobra.com.br
2021-04-02 17:35 ` Re: Number of Shared Blocks Hit Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 2+ messages in thread
From: luis.roberto@siscobra.com.br @ 2021-03-04 11:23 UTC (permalink / raw)
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Hi,
While running a update, and checking EXPLAIN ANALYZE output, I found it strange that in the "ModifyTable" node, it shows "Shared Hit Blocks":351938580. If my math is correct, that amounts to more than 2.5TB.
The table itself has only 8GB, and 12 with indices:
pg_table_size|pg_total_relation_size|
-------------|----------------------|
8316043264| 12471787520|
The update statements was: UPDATE inconsistencia SET incondatinc = remincdatcad::timestamp
"incondatinc" was a freshly added column with "now()" as default value.
Is this value correct?
Here's the parsed plan: https://explain.dalibo.com/plan/LTN
[
{
"Plan": {
"Node Type": "ModifyTable",
"Operation": "Update",
"Parallel Aware": false,
"Relation Name": "inconsistencia",
"Schema": "public",
"Alias": "inconsistencia",
"Startup Cost": 0.00,
"Total Cost": 821781.08,
"Plan Rows": 26132966,
"Plan Width": 129,
"Actual Startup Time": 235607.729,
"Actual Total Time": 235607.730,
"Actual Rows": 0,
"Actual Loops": 1,
"Shared Hit Blocks": 351938580,
"Shared Read Blocks": 762606,
"Shared Dirtied Blocks": 1565402,
"Shared Written Blocks": 774916,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 22884.559,
"I/O Write Time": 4.956,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Relation Name": "inconsistencia",
"Schema": "public",
"Alias": "inconsistencia",
"Startup Cost": 0.00,
"Total Cost": 821781.08,
"Plan Rows": 26132966,
"Plan Width": 129,
"Actual Startup Time": 0.008,
"Actual Total Time": 13767.894,
"Actual Rows": 26141470,
"Actual Loops": 1,
"Output": ["remitemseq", "remincdevnom", "remincnumcon", "inccod", "remincobs", "remincrem", "remincdevcod", "carcod", "remincnumpar", "reminclin", "remincreg", "remincdatcad", "(remincdatcad)::timestamp without time zone", "ctid"],
"Shared Hit Blocks": 48185,
"Shared Read Blocks": 446934,
"Shared Dirtied Blocks": 42,
"Shared Written Blocks": 886,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 7726.779,
"I/O Write Time": 4.956
}
]
},
"Planning": {
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000
},
"Planning Time": 0.054,
"Triggers": [
],
"Execution Time": 235607.759
}
]
Luis R. Weck
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Number of Shared Blocks Hit
2021-03-04 11:23 Number of Shared Blocks Hit luis.roberto@siscobra.com.br
@ 2021-04-02 17:35 ` Andres Freund <andres@anarazel.de>
0 siblings, 0 replies; 2+ messages in thread
From: Andres Freund @ 2021-04-02 17:35 UTC (permalink / raw)
To: luis.roberto@siscobra.com.br; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Hi,
On 2021-03-04 08:23:07 -0300, luis.roberto@siscobra.com.br wrote:
> While running a update, and checking EXPLAIN ANALYZE output, I found
> it strange that in the "ModifyTable" node, it shows "Shared Hit
> Blocks":351938580. If my math is correct, that amounts to more than
> 2.5TB.
Note that shared blocks hit can include repeated hits to the same
buffer. Over and over again. E.g. when you insert a new index row for
each of the rows, parts of the index will have to be traversed for each
row. Those pages will all be in the cache, hence no increased "Shared
Read Blocks", but will be counted as separate hits.
Remembering which pages we accessed previously would be quite
expensive...
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-04-02 17:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-04 11:23 Number of Shared Blocks Hit luis.roberto@siscobra.com.br
2021-04-02 17:35 ` Andres Freund <andres@anarazel.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox