pg_class returns 0 as relpages

All we need is an easy explanation of the problem, so here it is.

I have this table:

                  Table "public.region"
   Column    |   Type   | Collation | Nullable | Default 
-------------+----------+-----------+----------+---------
 r_regionkey | smallint |           | not null | 
 r_name      | text     |           |          | 
 r_comment   | text     |           |          | 
Indexes:
    "region_pkey" PRIMARY KEY, btree (r_regionkey)
Referenced by:
    TABLE "nation" CONSTRAINT "nation_n_regionkey_fkey" FOREIGN KEY (n_regionkey) REFERENCES region(r_regionkey)

weirdly this query:

select oid::regclass as tbl, relpages
from pg_class
where relname='region'

returns relpages = 0.

• Why and how can I fix? If I change smallint to int it works (relpages = 1)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Your table is so small that autoanalyze never got triggered. Since autovacuum_analyze_threshold is 50 by default, tables with less than 50 data modifications are not automatically analyzed.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0