pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: luis.roberto@siscobra.com.br
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Number of Shared Blocks Hit
Date: Thu, 4 Mar 2021 08:23:07 -0300 (BRT)
Message-ID: <295851993.7384397.1614856987194.JavaMail.zimbra@siscobra.com.br> (raw)

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 

view thread (2+ messages)  latest in thread

Message-ID: <295851993.7384397.1614856987194.JavaMail.zimbra@siscobra.com.br>
Permalink:  ../295851993.7384397.1614856987194.JavaMail.zimbra@siscobra.com.br/
Also on:    postgresql.org/message-id/295851993.7384397.1614856987194.JavaMail.zimbra@siscobra.com.br

copy link · copy postgr.es

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: pgsql-bugs@postgresql.org
  Cc: luis.roberto@siscobra.com.br, pgsql-bugs@lists.postgresql.org
  Subject: Re: Number of Shared Blocks Hit
  In-Reply-To: <295851993.7384397.1614856987194.JavaMail.zimbra@siscobra.com.br>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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