Postgresql - Expression Indexes

2019独角兽企业重金招聘Python工程师标准>>>

Having covered virtual columns recently, 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 the customer table and fullnamefunction created in my previous blog 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 first create index command simply creates a copy of the column lastname in a btree-ordered index file. The second example concatenates firstname andlastname fields and stores the result in a btree-ordered index file; it requires double-parentheses around the expression. The last example stores the output of the function fullname in 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  allows us to see the index being used:

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 the i_customer_fullname index. The sql fullname function was inlined when the index was created, so it was expanded to (((firstname || ' '::text) || lastname); the i_customer_concat index is simply unnecessary. Storing Soundex values 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 uses sql to find the input value that yields the supplied factorial output 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)

Interestingly, Postgres computes optimizer statistics on expression indexes, even though the expressions do not exist in any table:

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

Expressions indexes are just another tool available to Postgres database users — they allow rapid access to rows based on table column expressions and functions.

转载于:https://my.oschina.net/Sebastian/blog/120983