15

While troubleshooting a server with very high I/O wait, I notice there is a lot of I/O coming from queries which do SELECT max(x) FROM t WHERE y = ?.

My index is btree (x, y).

I notice that the query plan does Index Scan Backward to get the max. Is that bad? Should I worry about that and perhaps add another index (reversed)? Or is there a better way to create an index suitable for this type of queries?

ibz
  • 44,461
  • 24
  • 70
  • 86

2 Answers2

26

No it's not bad, it takes the same amount of time to start with the first index page as it would take to start with the last index page. You can see the "difference" when creating an descending index, using DESC.

An index (y,x) would probably be better for this query.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • 1
    Creating an index on (y, x) reduced query cost from 10k to 300 and hugely reduced query time. Having x DESC doesn't make any difference. Thanks for the tip! – ibz Feb 17 '11 at 07:05
  • Awesome, thank you for this sir! I had a similar issue and comparable performance impact after shifting the index around. – TheDeadSerious Jun 29 '12 at 10:41
  • https://stackoverflow.com/users/271959/frank-heikens Can you elaborate why an index on (y, x) will have a lower query time than an index on (x, y). Thanks. – nisanth074 Jan 05 '18 at 16:16
  • 3
    @nisanth074 I believe it all depends on the index selectivity on the WHERE side. In most cases you would first want to filter out e.g. 300 rows where `y = ?` then choose the `max(x)`. If you have the index on `(x,y)` you can't filter on `y` value. You could filter on just `x` or `x` and `y` together, not the `y` alone. Study the compound index order preference. I think it can explain it better than I. – Tom Raganowicz Jun 03 '19 at 07:45
4

The index is sorted, with the lowest value first. To find the max value, a backward index scan would find the maximum value first :).

I assume a SELECT min(x) would result in a normal index scan, does it?

Daniel
  • 27,718
  • 20
  • 89
  • 133