Having coveredvirtual columnsrecently,I wanted to cover one additional type of stored value,expression indexes. Unlike virtual columns,which are computed on each access,expression indexes are stored in index files,allowing rapid access. Let's look at some examples,building on thecustomertable andfullnamefunction created in my prevIoUsblog entry:

CREATE INDEX i_customer_lastname ON customer (lastname);
 
CREATE INDEX i_customer_concat ON customer ((firstname || ' ' || lastname));
 
CREATE INDEX i_customer_fullname ON customer (fullname(customer));

The firstcreate indexcommand simply creates a copy of the columnlastnamein a btree-ordered index file. The second example concatenatesfirstnameandlastnamefields and stores theresultin a btree-ordered index file; it requires double-parentheses around the expression. The last example stores the output of the functionfullnamein an index file.

The ability to store the output of expressions and functions allows rapid access for queries that are more complex than simple column comparisons. For example,

SELECT * FROM customer WHERE firstname || ' ' || lastname = 'Mark Pennypincher';
 id | firstname |   lastname
----+-----------+--------------
  1 | Mark      | Pennypincher
 
SELECT * FROM customer WHERE fullname(customer) = 'Mark Pennypincher';
 id | firstname |   lastname
----+-----------+--------------
  1 | Mark      | Pennypincher
 
SELECT * FROM customer WHERE customer.fullname = 'Mark Pennypincher';
 id | firstname |   lastname
----+-----------+--------------
  1 | Mark      | Pennypincher

EXPLAIN SELECT * FROM customer WHERE firstname || ' ' || lastname = 'Mark Pennypincher';
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Index Scan using i_customer_fullname on customer  (cost=0.29..8.31 rows=1 width=12)
   Index Cond: (((firstname || ' '::text) || lastname) = 'Mark Pennypincher'::text)
 
EXPLAIN SELECT * FROM customer WHERE fullname(customer) = 'Mark Pennypincher';
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Index Scan using i_customer_fullname on customer  (cost=0.29..8.31 rows=1 width=12)
   Index Cond: (((firstname || ' '::text) || lastname) = 'Mark Pennypincher'::text)
 
EXPLAIN SELECT * FROM customer WHERE customer.fullname = 'Mark Pennypincher';
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Index Scan using i_customer_fullname on customer  (cost=0.29..8.31 rows=1 width=12)
   Index Cond: (((firstname || ' '::text) || lastname) = 'Mark Pennypincher'::text)

Notice that all three use thei_customer_fullnameindex. Thesqlfullnamefunction was inlined when the index was created,so it was expanded to(((firstname || ' '::text) || lastname); thei_customer_concatindex is simply unnecessary. StoringSoundexvalues in expression indexes is also useful.

Basically,you have three options for creating auto-generated columns:

• virtual columns,via functions,computed on access
• computed table columns,populated by triggers
• computed index columns,populated by expression indexes

Of course,only the last one,expression indexes,allows rapid access to computed values. The example below usessqlto find the input value that yields the suppliedfactorialoutput value,e.g. what value generates a factorial of 120:

-- create a table of 1000 integers
CREATE TABLE factorial_lookup AS SELECT i FROM generate_series(1,1000) x(i);
 
-- compute a factorial for every row and compare
EXPLAIN SELECT i FROM factorial_lookup WHERE factorial(i) = 120;
                            QUERY PLAN
------------------------------------------------------------------
 Seq Scan on factorial_lookup  (cost=0.00..52.00 rows=12 width=4)
   Filter: (factorial((i)::bigint) = 120::numeric)
 
-- create an index of factorial output values
CREATE INDEX i_factorial_lookup ON factorial_lookup (factorial(i));
 
-- generate optimizer statistics
ANALYZE factorial_lookup;
 
-- use the index for rapid factorial output comparison
EXPLAIN SELECT i FROM factorial_lookup WHERE factorial(i) = 120;
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Index Scan using i_factorial_lookup on factorial_lookup  (cost=0.53..8.55 rows=1 width=4)
   Index Cond: (factorial((i)::bigint) = 120::numeric)

SELECT attname FROM pg_stats WHERE tablename = 'i_factorial_lookup';
  attname
-----------
 factorial