Database Reference
In-Depth Information
Finally, remember that each index you create works against only a single opclass. If you
would like an index on a column to cover multiple opclasses, you must create separate
indexes. To add the default index
text_ops
to a table, run:
CREATE
INDEX
idx2
ON
census
.
lu_tracts
USING
btree
(
tract_name
);
Now you have two indexes against the same column. (There's no limit to the number
of indexes you can build against a single column.) The planner will choose
idx2
for
basic equality queries and
idx1
for comparisons using
like
.
You'll find operator classes detailed in
Operator Classes
. We also strongly recommend
that you read our article for tips on troubleshooting index issues,
Why is My Index Not
Functional Indexes
PostgreSQL lets you add indexes to functions of columns. Functional indexes prove
their usefulness in mixed-case textual data. PostgreSQL is a case-sensitive database. To
perform a case-insensitive search you could create a functional index:
CREATE
INDEX
fidx
ON
featnames_short
USING
btree
(
upper
(
fullname
)
varchar_pattern_ops
);
Creating such an index ensures that queries such as
SELECT fullname FROM feat
names_short WHERE upper(fullname) LIKE 'S%';
can utilize an index.
Always use the same function when querying to ensure usage of the index.
Both PostgreSQL and Oracle provide functional indexes. MySQL and SQL Server pro‐
vide computed columns, which you can index. As of version 9.3, PostgreSQL supports
indexes on materialized views as well as tables.
Partial Indexes
Partial indexes (sometimes called filtered indexes) are indexes that cover only rows
fitting a predefined
WHERE
condition. For instance, if you have a table of 1,000,000 rows,
but you care about a fixed set of 10,000, you're better off creating partial indexes. The
resulting indexes can be faster because more of them can fit into RAM, plus you'll save
a bit of disk space on the index itself.
Partial indexes let you place uniqueness constraints only on some rows of the data.
Pretend that you manage newspaper subscribers who signed up in the past 10 years and
want to ensure that nobody is getting more than one paper delivered per day. With
dwindling interest in print media, only about 5% of your subscribers have a current
subscription. You don't care about subscribers who have stopped getting newspapers
being duplicated, because they're not on the carriers' list anyway. Your table looks like
this:
