Postgresql - Expression Indexes

概述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 st

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 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 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 integersCREATE table factorial_lookup AS SELECT i FROM generate_serIEs(1,1000) x(i); -- compute a factorial for every row and compareEXPLAIN 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 valuesCREATE INDEX i_factorial_lookup ON factorial_lookup (factorial(i)); -- generate optimizer statisticsANALYZE factorial_lookup; -- use the index for rAPId factorial output comparisonEXPLAIN 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. 总结

以上是内存溢出为你收集整理的Postgresql - Expression Indexes全部内容，希望文章能够帮你解决Postgresql - Expression Indexes所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。