Database Reference
In-Depth Information
The preceding query will scan each row or table and convert
item_name
to uppercase
and compare it with
COFFEE
; this is really expensive. The following is the command to
create an expression index on the
item_name
column:
warehouse_db=# CREATE INDEX item_expression_index ON item
(UPPER(item_name));
The result can be seen using the following statement:
warehouse_db=# \d item;
Table "item"
Column | Type | Modifiers
------------+--------------------+----------
item_id | integer | not null
item_name | text |
item_price | numeric |
item_data | text |
Indexes:
"item_expression_index" btree (upper(item_name::text))
An expression index is only used when the exact expression is used in a query
as in the deinition. In this example, we query the
item
table and did not use the
expression, so the planner does not use the expression index. This can be seen
as follows:
warehouse_db=# EXPLAIN SELECT item_name FROM item WHERE item_name
= 'item-10';
QUERY PLAN
---------------------------------------------------------
Seq Scan on item (cost=0.00..22759.00 rows=1 width=11)
Filter: (item_name = 'item-10'::text)
(2 rows)
However, in this example, we used the same expression as in the index deinition,
so the planner selects that index in the following manner:
warehouse_db=# EXPLAIN SELECT item_name FROM item WHERE
UPPER(item_name) = 'ITEM-10';
QUERY PLAN
-------------------------------------------------------------
Bitmap Heap Scan on item (cost=107.18..8714.04 rows=5000
width=11)
Recheck Cond: (upper(item_name) = 'ITEM-10'::text)
-> Bitmap
Index Scan
on
item_expression_index
(cost=0.00..105.93 rows=5000 width=0)
Index Cond: (upper(item_name) = 'ITEM-10'::text)
(4 rows)
