CODEWITHSUNDEEP
postgresql
NathanVss
NathanVss

Reputation: 644

PostgreSQL - How to use index for this kind of query

We got this query:

SELECT * FROM table WHERE A AND ( B = X1 OR B = X2 ) AND ( C = X3 OR D = TRUE ) AND E = 0;

I created this index:

CREATE INDEX _my_index ON public.table USING btree (A, B, C, D, E);

But I don't get any better performances ... how to deal with such queries for indexing ? Thank you !

Upvotes: 1

Views: 38

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248235

I'll assume that X1, X2 and X3 are constants and not table columns.

You won't be able to index C = X3 OR D = TRUE — OR is always a performance problem.

The condition B = X1 OR B = X2 should be rewritten to B IN (X1, X2).

Then this is the best index:

CREATE INDEX ON "table" (e, a, b);

If you always want to query for truth of a and e = 0, a partial index would be even better:

CREATE INDEX ON "table" (b) WHERE a AND e = 0;

If you need to index the conditions on c and d as well, and the table has a primary key, you can rewrite the query to:

SELECT * FROM "table"
WHERE a AND b IN (X1, X2) AND c = X3 AND e = 0
UNION
SELECT * FROM "table"
WHERE a AND b IN (X1, X2) AND d AND e = 0;

For this query, the following two indexes are commendable:

CREATE INDEX ON "table" (c, a, e, b);
CREATE INDEX ON "table" (e, a, d, b);

Again, you can move certain index columns into a WHERE condition if you always query for a certain value.

Upvotes: 1

Related Questions