Postgresql – How to verify disk hit by a query

What is the exact problem you are trying to solve? pg_dump and pg_restore are official supported PostgreSQL tools, so you should just assume they are correctly implemented. You have a few options on checking databases are identical though, depending on how certain you need to be they are identical.

Your most basic check would be to check that both databases have the same tables. You can find a list of tables via the information schema. For a loose check that the data is identical, you could look at the amount rows in these tables, and the upper and lower bound of primary keys (assuming you have primary keys where ordering makes sense).

If you really need to guarantee the data is identical, then you will need to produce some sort of hash and compare that, and that's essentially what you are doing with sorted dumps. It's worth to remember though that the database is more than just tables, you need to check sequences too.

Let's simply try it!

I've created the table from your question, then did the following:

INSERT INTO mytable 
SELECT json_object(ARRAY['myfield', i::text || 'bla'])::jsonb 
FROM generate_series (1, 100000) t(i);

\di+ trgm_index_1

 Schema │     Name     │ Type  │ Owner  │  Table  │ Size  │ Description 
────────┼──────────────┼───────┼────────┼─────────┼───────┼─────────────
 test   │ trgm_index_1 │ index │ avaczi │ mytable │ 19 MB │ 

So far, we have only rows where myfield is present. Let's now add some other rows, too:

INSERT INTO mytable 
SELECT json_object(ARRAY['other_field', i::text || 'bla'])::jsonb 
FROM generate_series (100001, 200000) t(i);

After this, the reported index size has changed to 22 MB, meaning that the new rows are also accounted for. If you want to exclude them, you have to create a partial index:

CREATE INDEX trgm_index_partial ON mytable
USING gin ((mydata#>>'{myfield}') gin_trgm_ops)
WHERE mydata ? 'myfield';

The size of this one is initially the same (19 MB) as the non-partial index, but after adding the second group of rows, it does not change:

\di+ trgm_index_*

 Schema │        Name        │ Type  │ Owner  │  Table  │ Size  │ Description 
────────┼────────────────────┼───────┼────────┼─────────┼───────┼─────────────
 test   │ trgm_index_1       │ index │ avaczi │ mytable │ 22 MB │ 
 test   │ trgm_index_partial │ index │ avaczi │ mytable │ 19 MB │