On Dec 4, 2007 8:02 PM, Bill Moran <wmoran@potentialtech.com> wrote:
> "Andrus" <kobruleht2@hot.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