Answer the question
In order to leave comments, you need to log in
How to speed up SELECT with high variance of highly selective WHERE and sort?
There is a table T with fields A, B, C and D.
The type of the fields is not so important, the main thing is that the more / less comparison operators (int, float, timestamp, ...) are applicable to them.
Queries to this table have the following variations:
1) Sorting is always applied (ASC or DESC) by any one of the listed fields.
2) Min/max restrictions are possible (only min, only max, or both) for any of the listed fields in any combination.
At a certain number of lines, everything works slowly. That is the problem.
What has already been done:
1) Built b-tree indexes for all fields, which gave an excellent result in speeding up queries where:
a) only sorting occurs
b) low selective restrictions are applied along with sorting
c) when a highly selective constraint is applied to the same field on which the sort is performed.
In principle, the result is predictable.
Probably already clear when problems arise. Namely, with highly selective restrictions on a field other than the sort field.
The selectivity of such restrictions can be zero. Then the pipe.
The problem does not seem unique to me. How to deal with it?
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question