As already outlined in this section, there are many optimizations that help speed up queries. One of them is called function inlining. PostgreSQL is able to inline immutable SQL functions. The main idea is to reduce the number of function calls that have to be made, in order to speed things up.

Here is an example of a function that can be inlined by the optimizer:

test=# CREATE OR REPLACE FUNCTION ld(int)
RETURNS numeric AS
$$
   SELECT log(2, $1);
$$
LANGUAGE 'sql' IMMUTABLE;
CREATE FUNCTION

The function will calculate the logarithmus dualis of the input value:

test=# SELECT ld(1024);
        ld
---------------------
 10.0000000000000000
(1 row)

To demonstrate how things work,we will recreate the table with less content to speed up the index creation:

test=# TRUNCATE a;
TRUNCATE TABLE

Then, data can be added again and the index can be applied:

test=# INSERT INTO a SELECT * FROM generate_series(1, 10000);
INSERT 0 10000
test=# CREATE INDEX idx_ld ON a (ld(aid));
CREATE INDEX

As expected, the index created on the function will be used just like any other index. However, let's take a closer look at the indexing condition:

test=# EXPLAIN SELECT * FROM a WHERE ld(aid) = 10;
                          QUERY PLAN 
---------------------------------------------------------------- 
 Index Scan using idx_ld on a (cost=0.29..8.30 rows=1 width=4) 
   Index Cond: (log('2'::numeric, (aid)::numeric) = '10'::numeric) 
(2 rows) 

The important observation here is that the indexing condition actually looks for the log function instead of the ld function. The optimizer has completely gotten rid of the function call.

Logically, this opens the door for the following query:

test=# EXPLAIN SELECT * FROM a WHERE log(2, aid) = 10;
                            QUERY PLAN 
----------------------------------------------------------------- 
 Index Scan using idx_ld on a (cost=0.29..8.30 rows=1 width=4) 
   Index Cond: (log('2'::numeric, (aid)::numeric) = '10'::numeric) 
(2 rows)