|
>> This doesn't do much good. This doesn't tell the planner that the 3 >> customer_ids are actually of same value, and it therefore can't filter them
>> as it sees fit. > You do know you can index on a function, and the planner then keeps > stats on it when you run analyze right? Yes, but I don't think it will make any difference. I don't think I can solve this with
an index lookup. I think my savior is the inference that the 2 columns are of
same value and the planner are free to choose which order to do the filter and join
with this extra information.
I have tried creating a function called
zero_if_null(int) : int that just select COALESCE($1, 0)
and adding a index on (zero_if_null(customer_id)) on table that contains customer_id. The only thing I get from is the planner now only knows how to compare customer_id, but it still doesn't know that they
are of same value, only I know that and I want to declare it for the planner.
I could probably rewrite the whole view in one query, and then fix it with a proper index. But I think I will loose alot of readability.
I could also change the structure to save an explicit state, instead of a calculated state. But then I get some redundancy I need to make sure always stays the same.
In the end one of these will probably be the solution.
I guess the question is more or less,
why doesn't IS NOT DISTINCT FROM behave the same way as = operator, are there any alternatives? And a plausible use case for when it would be useful.
|
