PostgreSQL – Using varchar_pattern_ops in a Multi-Column Index

indexoptimizationpostgresql

I'm using postgres 9.5

If I have a table with 2 columns like so:

CREATE TABLE mystuff
(
  somestring character varying(256),
  timestamp_ timestamp without time zone NOT NULL
)

Will this multi-column btree index:

CREATE INDEX mystuff_idx
  ON mystuff
  USING btree
  (timestamp_ , somestring varchar_pattern_ops);

aid in performance of the following query ?

Select count(*)
FROM mystuff
where timestamp_ > '01/01/2012' and somestring like 'foo%'

what about ?

Select count(*)
FROM mystuff
where timestamp_ > '01/01/2012' and somestring like '%foobar'

For extra point please explain how a multi-column btree is used for lookup when there is a like clause in the second (or 3rd etc…) column of the index.

Best Answer

The two-column btree index will help with the like 'foo%' query, but probably not dramatically so. It helps because it can be executed as an index-only scan, and so it can compute the LIKE portion within in the index without ever having to visit the table. The index scan will jump to the first entry > '01/01/2012', and then traverse from there to the logical end of the index. At each entry it will test the LIKE condition. For the ones that pass that condition, it will check the visibility map to see if the table page holding that tuple is all visible. If it is all visible, it will increment the counter and move on. If not, it will have to visit the table page to see if the tuple is visible.

How much of a help this will be is hard to predict, as it depends on the size of the index, the table, your RAM, what mix of queries you are running (which changes which kind of data is likely to be found in the cache), among other things.

It will not help with the `like '%foobar' query. From the outside looking in, there is no reason it couldn't help in the same way. But PostgreSQL's index machinery just hasn't been made clever enough (yet) to recognize and implement that possibility.

Related Question