PostgreSQL 10 High Performance - Third Edition

Index creation

To try and improve things, let's create an index on the value column and look at how large it is:

CREATE INDEX i ON t(v);
SELECT relname,reltuples,relpages FROM pg_class WHERE relname='i';
relname | reltuples | relpages 
---------+----------+---------
i       |    100000 |      221
    
      
    
SELECT relname,round(reltuples / relpages) AS rows_per_page FROM pg_class WHERE relname='i';
relname | rows_per_page 
---------+--------------
i       |           452
  

One thing you should notice here is that the index is certainly not tiny compared to the table, which is the case surprisingly often. It's about half the size of the table, at 221 pages. You should always note how large any index created is relative to the table when determining whether it's a worthwhile addition. ...