Re: Avoid huge perfomance loss on string concatenation

On Dec 4, 2007 8:02 PM, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
> "Andrus" <kobruleht2(at)hot(dot)ee> wrote:
> > Using string concatenation in where clause causes huge perfomance loss:
> >
> > explain analyze select
> > rid.toode
> > FROM dok JOIN rid USING (dokumnr)
> > JOIN toode USING (toode)
> > LEFT JOIN artliik using(grupp,liik)
> > WHERE rid.toode='NAH S'
> > AND dok.kuupaev BETWEEN '2007-11-01' AND '2007-12-04'
> > and dok.kuupaev||dok.kellaaeg BETWEEN '2007-11-01' AND '2007-12-0423 59'

can you please give us the types of dok.kuupaev and dok.kellaaeg? I
think a simple fix is possible here.

> You provide zero information on the table layout, and the explain output
> has been horribly mangled by your MUA.
>
> I would suspect the problem is that there's no index that can be used
> for that final comparison. Do you have an index along the lines of
> CREATE INDEX dokindex ON dok (kuupaeve||kellaaeg) ?
>
> Overall, the fact that you're concatenating two text fields to generate a
> date field tends to suggest that your database schema has some fairly
> major design problems, but I can only speculate at this point.

just small correction here...expressions like that in the create index
need an extra set of parens (but I agree with your sentiment):
CREATE INDEX dokindex ON dok ((kuupaeve||kellaaeg))

merlin