Creating a partial index on non-default values and searching all rows in that index in PSQL

Question

If I have a table where 99% of rows with column x has a default value and want to search for a specific value in that column, will creating a partial index on x provide any improvements?

For example,

CREATE INDEX non_empty_name ON stores (foo) WHERE foo != '';

Would the following query actually trigger an index-only scan?

SELECT id FROM stores WHERE foo = 'Hello';

OR

SELECT id FROM stores WHERE foo LIKE '%ello';

Answer 1

The second query will never use a B-tree index, but the first query will use your partial index. For such a query, the partial index would be the best one. Its advantages are:

• it is small, hence fast to scan

• inserting a row with an empty foo will not have to modify the index

The partial index can also be used for pattern matching, but only if the wildcard is not in the beginning and you add a second WHERE condition AND foo <> ''.

For a pattern match like in your second query, you'd need a trigram index. That will be quite a bit slower, but it also won't be too large, since most of the rows will only have the single trigram ' '.