Any ways to speed up like 'foo%' queries in PostgreSQL?-CodePudding

I have many queries like

select * from table where (upper (column1) like 'FOO%') 
                    and (upper (column2) like 'BAR%')
                    and (upper (column3) like 'XYZ%')

And suach an index:

create index on table (upper(column1::text), upper(column2::text), upper(column3::text));

But for some reason queries are pretty slow and explain operator shows that it doesn't use any index scan, just simple sec scan. I've read that B-tree index type is the best for queries like mine with anchor in the end of the constant. Any ideas why this happen? May be something wrong with my index creation command?

CodePudding user response：

For that, you need three indexes:

/* "text_pattern_ops" makes the index usable for LIKE */
CREATE INDEX ON "table" (column1 text_pattern_ops);
CREATE INDEX ON "table" (column2 text_pattern_ops);
CREATE INDEX ON "table" (column3 text_pattern_ops);

PostgreSQL will scan the index or the indexes for the WHERE conditions that promise to significantly reduce the number of rows. If it scans several indexes, it can combine the result. If one of these WHERE conditions is never selective, you can omit the corresponding index, since it won't be used.

You won't be able to cover that query with a single index.

CodePudding user response：

Well the use of the UPPER function on the three columns basically precludes any chance of an index being used. However, if you could ensure that you only store uppercase values in the three columns, then you could add an index:

CREATE INDEX idx ON yourTable (column1, column2, column3);

You would then use this version of your query:

SELECT *
FROM yourTable
WHERE column1 LIKE 'FOO%' AND column2 LIKE 'BAR%' AND column3 LIKE 'XYZ%';

The reason this index would work is that your LIKE expressions are substrings starting from the very beginning of the column values. As a result, a B-tree index can be used to search for these starting substrings.