Christian Schröder wrote:
> Alvaro Herrera wrote:
>> Christian Schröder wrote:
>>
>>
>>> I think it is my job as db admin to make the database work the way my
>>> users need it, and not the user's job to find a solution that fits the
>>> database's needs ...
>>>
>>> Is there really nothing that I can do?
>>>
>>
>> You can improve the selectivity estimator function. One idea is that if
>> you are storing something that's not really a general character string,
>> develop a specific datatype, with a more precise selectivity estimator.
>> If you are you up to coding in C, that is.
>>
>
> Hm, that sounds interesting! I will definitely give it a try.
> Will that also solve the problem of combining more than one of these
> conditions? As far as I can see, the main issue at the moment is that we
> often have "... where test like '11%' and test not like '113%'" in our
> queries. Even if the selectivity estimation of the single condition will be
> improved, it will still be wrong to multiply the selectivities.
Unless you can come up with an operator that expresses better the
"starts with 11 but not with 113" type of condition. For example if
these were telephone number prefixes or something like that, probably
there's some way to do that in a single operation instead of two, and
the selectivity function could produce a much more accurate estimate
saving the need to multiply.
--
Alvaro Herrera http://www.advogato.org/person/alvherre
"I think my standards have lowered enough that now I think 'good design'
is when the page doesn't irritate the living f*ck out of me." (JWZ)