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.
select a from b where a ~ '^11[^3]'
Is that what you want?
I usually find using ~ far better than like.....
Peter Childs
-- 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)
---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly