Database Reference
In-Depth Information
Here is a simple example of how to use the index types:
warehouse_db=# CREATE INDEX index_name ON table_name USING
btree(column);
The B-tree index
The B-tree index is effectively used when a query involves the equality operator (
=
)
and range operators (
<
,
<=
,
>
,
>=
,
BETWEEN
, and
IN
).
The hash index
Hash indexes are utilized when a query involves simple equivalent operators only.
Here, we create a hash index on the
item
table. You can see in the following example
that the planner chooses the hash index in the case of an equivalent operator and
does not utilize the hash index in the case of the range operator:
warehouse_db=# CREATE INDEX item_hash_index ON item USING
HASH(item_id);
As discussed, the hash index is the best for queries that have equivalent operators in
the
WHERE
clause. This can be explained with the help of the following example:
warehouse_db=# EXPLAIN SELECT COUNT(*) FROM item WHERE item_id =
100;
QUERY PLAN
------------------------------------------------------------------
Aggregate (cost=8.02..8.03 rows=1 width=0)
-> Index Scan using
item_hash_index
on item (cost=0.00..8.02
rows=1 width=0)
Index Cond: (item_id = 100)
(3 rows)
The hash index method is not suitable for range operators, so the planner will not
select a hash index for range queries:
warehouse_db=# EXPLAIN SELECT COUNT(*) FROM item WHERE item_id >
100;
QUERY PLAN
------------------------------------------------------------------
Aggregate (cost=25258.75..25258.76 rows=1 width=0)
->
Seq Scan
on item (cost=0.00..22759.00 rows=999900 width=0)
Filter: (item_id > 100)
(3 rows)
