Date: Thu, 4 Mar 2021 08:23:07 -0300 (BRT)
From: luis.roberto@siscobra.com.br
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Message-ID: <295851993.7384397.1614856987194.JavaMail.zimbra@siscobra.com.br>
Subject: Number of Shared Blocks Hit
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Thread-Index: S9A9mv4WtUkXyNl+hAj5/5H4i0iiag==
Thread-Topic: Number of Shared Blocks Hit
Archived-At: 
 <https://www.postgresql.org/message-id/295851993.7384397.1614856987194.JavaMail.zimbra%40siscobra.com.br>
Precedence: bulk

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