4 ms·
I think the point is they don't know in advance what the query is and they didn't think they had a good solution to optimize all user entered variants across th
by TimPC 8y ago
I think the point is they don't know in advance what the query is and they didn't think they had a good solution to optimize all user entered variants across the range of possible groupings so they wanted a solution that was easier to optimize globally.
The general form of this is:
Select loyaltyMemberID
from table
WHERE V1_1= x_1 OR ... OR V1_n=x_n)
AND (V2_1 = x_2_1 OR V2_2=x_2_2 OR ... V2_n=x_2_n)
AND ...
AND (Vn_1 = x_n_1 OR ... OR Vn_n= x_n_n)
(some of these n's should actually be m_i's but I was lazy)
There may be some ability to optimize this in a number of ways but optimizing one example is not optimizing the general form. I can easily see how technology change could be a cleaner solution.
- orf 8y ago> There may be some ability to optimize this in a number of ways but optimizing one example is not optimizing the general form. I totally get that, but isn't that the point of the query optimizer within the database itself? Why are you trying to outwit it? It should select the right indexes, provided the columns are indexed, and "do the right thing(tm)". It might take a bit of cajoling but they seem pretty good at this. Postgres collects statistics about the distribution of values themselves within the table to guide its choice of index, so in theory it could rewrite the boolean logic to use a specific index if it's sure that it will eliminate a higher % of the rows than another plan. In any case, it seems the SQL they posted is a bit off. Why nest each individual filter as a UNION? If you wanted to go down the UNION route couldn't you do each individual group as a UNION, with standard WHERE filters?