CODEWITHSUNDEEP
sqlpostgresql
anon
anon

Reputation:

What is the optimal index for this SELECT?

Suppose I have a table with 4 columns: CREATE TABLE table1 (a BIGSERIAL UNIQUE, b INTEGER, c enum1, d enum2);

a | b | c | d|
¯¯|¯¯ |¯¯ |¯¯
2 | 5 |'x'|'r'  
5 | 1 |'y'|'s'
8 | 12|'z'|'r'
10| 8 |'x'|'t'
    ...

The table contains a very large number of rows and I care about the performance of queries of the form:

SELECT SUM(b) WHERE a > A AND c = C and d = D.

The enum types enum1 and enum2 may take one of only several values (is this relevant?).

Is the optimal index simply:

CREATE INDEX on table1 (a, c, d)?

Do I need to specify that the first column is numerical and the later columns in the index are discrete valued?

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

I would recommend an index on (c, d, a).

In particularly, the ordering of c and d doesn't matter in the index, but they should be the first two columns, with a following them.

The index (a, c, d) is not optimal, because the comparison on a is an inequality.

Upvotes: 1

Related Questions