Mastering PostgreSQL 12 - Third Edition

Deploying simple indexes

Firing up more worker processes to scan ever larger tables is sometimes not the solution. Reading entire tables to find just a single row is usually not a good idea.

Therefore, it makes sense to create indexes:

test=# CREATE INDEX idx_id ON t_test (id);  
CREATE INDEX 
test=# SELECT * FROM t_test WHERE id = 43242;  id    | name -------+------  43242 | hans (1 row) Time: 0.259 ms

PostgreSQL uses Lehman-Yao's high concurrency B-tree for standard indexes (https://www.csd.uoc.gr/~hy460/pdf/p650-lehman.pdf). Along with some PostgreSQL-specific optimizations, these trees provide end users with excellent performance. The most important thing is that Lehman-Yao allows you to run many operations (reading and writing) on the very ...