On Dec 4, 2007 8:02 PM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote: > "Andrus" <kobruleht2@xxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
