2

I know that since version 8.1 Postgres can use multiple indexes for queries with or at query time.

I wonder if it is possible to combine multiple columns into one index column at index time. That is if I have a query:

select *
from contacts
where firstname = 'Cassidy'
or lastname = 'Cassidy'
or nickname = 'Cassidy'

Can I create an index that unions all the values from firstname, lastname and nickname so that this search (possibly with an adapted query) becomes a single index lookup?

AndreKR
  • 607
  • 9
  • 19

2 Answers2

2

Query

Note this equivalent sorter syntax for your query:

SELECT *
FROM   contacts
WHERE  'Cassidy' IN (firstname, lastname, nickname);

Resolves to the same expressions in the query tree:

  (('Cassidy'::text = contacts.firstname)
OR ('Cassidy'::text = contacts.lastname)
OR ('Cassidy'::text = contacts.nickname))

The three columns can even have different data types. The single given string literal 'Cassidy' is cast to different data types for this. Exactly like in your original query.

Index

You could use a single bloom index (after installing the additional module, once per database):

CREATE INDEX contacts_bloom_idx ON contacts USING bloom (firstname, lastname, nickname);

Especially if you have more candidate columns like this and query random combinations. Also if you are short on disk / cache memory or the index is rarely needed (so not in cache). Then size matters more and a bloom index is typically small.

But it's better for AND-ed conditions, less so for OR-ed conditions. And the manual advises:

This type of index is most useful when a table has many attributes and queries test arbitrary combinations of them. A traditional btree index is faster than a bloom index, but it can require many btree indexes to support all possible queries where one needs only a single bloom index. Note however that bloom indexes only support equality queries, whereas btree indexes can also perform inequality and range searches.

And:

Only operator classes for int4 and text are included with the module.

For just three columns, three simple btree indexes should be faster and more versatile:

CREATE INDEX ON tbl (firstname);
CREATE INDEX ON tbl (lastname);
CREATE INDEX ON tbl (nickname);

Postgres can rather efficiently combine multiple indexes in a bitmap index scan. Related:

A multicolumn index like:

CREATE INDEX ON tbl (firstname, lastname, nickname);

is not very usefull for this. It happens to cover firstname as first column moderately well (not as well as a smaller index on just (firstname)). Additional columns are covered poorly. You are not looking for combinations of columns, so it's the wrong tool.

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
1

Can I create an index that unions all the values from firstname, lastname and nickname so that this search (possibly with an adapted query) becomes a single index lookup?

You can do that a few different ways. All of these work with your query provided, first on a compound index it will be a single lookup/scan

CREATE INDEX f ON t (firstname,lastname,nickname);

Or if you don't want to store all of them on the index, you can use a partial index.

CREATE INDEX f ON t (whatever) WHERE
  firstname = 'Cassidy'
  OR lastname = 'Cassidy'
  OR nickname = 'Cassidy';

Can I create an index that unions all the values from firstname, lastname and nickname so that this search (possibly with an adapted query) becomes a single index lookup?

A "lookup" isn't a precise term, if you need a seek you'll need different trees for each column that you're indexing. If you need a scan, you can use an inverted index (GIN) on the three columns or force it to crawl the btree (or convince it that it's worth it). There is no way to store three distinct cases on a single tree.

See also bloom indexing

Alternatively, if you're looking for were a=b=c, you can create a hash index of the three.

CREATE INDEX ON g USING hash( concat(a,b,c) );
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507