Re: Performance Issues

On 23/09/2007, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>
> 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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>