0

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';
1
  • 1
    The execution plan will tell you
    – user1822
    Commented Apr 6, 2023 at 6:27

1 Answer 1

0

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 ' '.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.