Hello all, how to build an multicolumn index with one column order ASCENDING and another column order DESCENDING?
The use case that I have is that I use 2 column index where the first column is kind of flag and the second column is an actual ordering column. The flag should be always ordered DESCENDING, but the second column is ordered DESCENDING when it is a numeric column, and ASCENDING when it is a text column. CREATE TABLE storage (id int, flag int, numeric_data int, text_data text); SELECT * FROM storage ORDER BY flag DESC, numeric_column DESC LIMIT 20 OFFSET 0; SELECT * FROM storage ORDER BY flag DESC, text_column ASC LIMIT 20 OFFSET 0; Definitely the multicolumn index on (flag, numeric_column) is being used. But how to create an index on (flag, text_column DESC)? I will try to index by ((-flag), text_column) and sort by (-flag) ASC, but it, to say the truth, does not really look like a nice solution. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend