I have a query that gets about 80k rows from said table with about 3m. I only need to paginate ten rows at a time, but with
ORDER BY (a+b) DESC (
integer type columns.)
Right now my query's taking about 4 seconds to do this. If I
ORDER BY the primary key instead, it avoids seq scans and takes 200ms, which I would be happy with.
So I created an index on
My thinking there is that the
ORDER BY and primary key both have indexes (as do all the things in the
WHERE clause--and those indexes are being used just fine), so the subquery should be able to accomplish everything it needs to without a seq scan, give up 10 indexed primary keys to the outer query, which, if it needs a seq scan because of the
*, only needs to find ten rows.
It didn't make a difference, anyway.
Unfortunately, I can't share full table details, but I can say that my table has 21 columns, with some
timestamps and even one
ts_vector. Aside from the expression one there are 4 indexes: the PK (text) is btree, I have another btree text, a btree timestamp, and a gin index on the tsvector.
Further: I can also order by the indexed timestamp and the non-primary-key indexed text, and just like the primary key, this reduces query time to 200ms thanks to index usage. It's just my expression index that doesn't seem to work this way in the subquery context.
Other options, none of which seem great:
Add a computed column for (a+b) so the index is just on one column. If it works for
ORDER BYon the pk it should work for that column, right?
Cheat by somehow guesstimating a happy limiter on
(a+b)due to its distribution. I'm not sure how I would always know because the query inputs are dynamic, but in this case, if I add a simple
AND (a+b) > 1000000, I still get ten rows back but it only takes 200ms--and uses my expression index for the WHERE clause where the seq scan would normally be. (About the not knowing, I could start doing some kind of binary-search-esque thing, trying high numbers and reducing by half until I get at least 10 rows back and then calling it a day. If I get it within less than 20 tries, I'm still ahead. But this is beyond kludgy...)
Any ideas? How can I get it to use my expression index and avoid a seq scan?
The reason I don't understand the above is that the docs (going back to 9.4 at least) explicitly put it this way:
Index expressions are relatively expensive to maintain, because the derived expression(s) must be computed for each row upon insertion and whenever it is updated. However, the index expressions are not recomputed during an indexed search, since they are already stored in the index.
So how on earth is
other_column any less expensive to
ORDER BY than
a+b when both are indexed?
Regarding query plans, let's try to get to the heart of it. I've posted a follow-up question.