Procedures and indexing

In this section, you will learn the basic pitfalls of indexing and procedures. One of the most common issues is that people forget to define their procedures. In PostgreSQL, there are four types of procedures:

• VOLATILE

• STABLE

• IMMUTABLE

• [NOT] LEAKPROOF

A volatile procedure may return different outputs for the same input parameters within the same transaction:

test=# SELECT random(), random();
      random       |      random       
-------------------+-------------------
 0.906597905792296 | 0.368819046299905
(1 row)

The random() function is supposed to return different values all the time. This is the core purpose of a random generator. Logically, this has implications when it comes to indexing:

SELECT * FROM tab WHERE field = random();

Can PostgreSQL use an index here? The answer is no, because what will the engine look up in the B-tree? The value is supposed to change by definition for each row. Therefore, indexes are forbidden here. All existing indexes on the...