Menu
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?
567
Backwards scans occur when SQL decides that it's faster to start at the end of an index and scan towards the beginning. This happens frequently with the MAX() function, and when you order a query against the order of the index. If your index is ascending, and your query is ordered descending, for example.
Description: This is very similar to an Index Scan, but the data comes directly from the index and the visibility check is handled specially, so it can avoid looking at the table data entirely. An index-only scan is faster, but it's not always available as an alternative to a regular index scan.
If you need only a single table row, an index scan is much faster than a sequential scan. If you need the whole table, a sequential scan is faster than an index scan.
An index scan has zero or more scan keys, which are implicitly ANDed — the returned tuples are expected to satisfy all the indicated conditions. The access method can report that the index is lossy, or requires rechecks, for a particular query.
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.
150
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
