CODEWITHSUNDEEP
sqlpostgresqlindexing
nsavinov
nsavinov

Reputation: 61

Multicolumn index for where and order by clause (postgres)

Consider the query like this:

select ... from table where a = 1 and b > 2 order by c asc

What would be the ideal idex for this query? Should i use one index (a, b, c) or two separate (a, b) and (c)?

thanks in advance.

Upvotes: 2

Views: 1703

Answers (1)

jjanes
jjanes

Reputation: 44423

You probably can't do both well. The equality is not a problem, but you can't cleanly combine inequality with ordering. How many rows do you expect to return? If b>2 is very selective leaving few rows left to sort, then clearly you want want (a,b) as getting the selectivity is useful while sorting the few remaining rows doesn't take very long. On the other hand, if b>2 only rules out a few rows, then (a,c) (perhaps with more columns at the end to allow index-only scans) avoids a big slow sort while removing the few rows that fail b "the hard way" doesn't take very long.

You could build both and let the planner make the call, although it is far from perfect at doing so.

It is possible to use a GiST index on all three columns to achieve both the inequality filtering and the ordering simultaneously, but GiST indexes have a much higher overhead than BTree indexes as well as being slower to build and maintain, so it is quite unlikely to be worth while, although if you used a LIMIT that could make it more attractive. You would also need to write the query in a contorted way, as a KNN query (assisted by the btree_gist extension):

where a = 1 and b > 2 order by c <-> impossibly_low_value

There are other advanced possibilities. If the thing you compare b to is always >2, then you could use a partial index (a,c) where b>2 or an expression index (a,(b>2),c). You might also be able to usefully partition on b. If b has a small number of distinct values, you could just UNION ALL the results of queries on each qualifying distinct value and use the index on (a,b,c), getting an efficient merge append:

(select * from foo where a=1 and b=3 order by c asc) 
    union all 
(select * from foo where a=1 and b=4 order by c asc) 
order by c asc;

Upvotes: 2

Related Questions