How to optimize for multiple columns search for PostgreSQL

Question

I have a table in PostgreSQL, it has 20 columns, which are mostly of an enum type. And this table has millions of rows.

I'd like to support and speed up for queries searching for rows with multiple fields, for instance: col2=value1&col3=value2&col5=value3 page=1

I can't use PostgreSQL's compound index, because it only works with a fixed order of the columns. For instance, If I build an index on (col2,col3,col5), then it can't be used for queries searching for col1=value1&col2=value2

And I'd like also to support queries like:

col1=value1&col2=(value3 or value4) orderby=col3 page=1

What would be a solution to this problem? And if I don't need full-text search on any of these columns (since they are all enum types), could the solution be lightweight?

How many distinct combinations of col1...col20 do there exist? — wildplasser
– wildplasser, Commented Mar 31, 2019 at 15:34
I would say potentially hundreds of distinct combinations of any columns — Leo Zhang
– Leo Zhang, Commented Apr 1, 2019 at 16:43
You could collect them into a separate table, addressed by a single (surrogate) key. — wildplasser
– wildplasser, Commented Apr 1, 2019 at 23:43

Laurenz Albe · Accepted Answer · 2019-03-31 17:20:27Z

6

If you want an OR in your search condition, that's pretty mush “game over” for performance (I'm exaggerating a little for effect).

But if you have only ANDs and equality conditions, I want to call your attention to Bloom filters.

You just have to

CREATE EXTENSION bloom;

and then create an index USING bloom on all columns together.

Unlike other indexes, this single index can speed up queries with all possible combinations of columns in the WHERE condition. The index is just a filter that will pass some false positives, so there always has to be a recheck of the condition, but it will significantly speed up the query.

answered Mar 31, 2019 at 17:20

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to optimize for multiple columns search for PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related