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. Along with some PostgreSQL specific optimizations, those 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 same index at the same time, which helps to improve throughput dramatically.

However, indexes are not for free:

test=# \di+ 
                       List of relations 
 Schema |  Name  | Type  | Owner | Table  | Size  | Description  
--------+--------+-------+-------+--------+-------+------------- 
 public | idx_id | index | hs    | t_test | 86 MB |  
(1 row) 

As you can see, our index containing 4 million rows will eat up 86 MB of disk space. In addition to that, writes to the table will be slower because the index has to be kept in sync all the time.