Avoid huge perfomance loss on string concatenation

["Andrus" <kobruleht2@xxxxxx>]

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'

"Nested Loop Left Join  (cost=68.75..5064.86 rows=1 width=24) (actual 
time=8.081..26995.552 rows=567 loops=1)"
"  Join Filter: ((toode.grupp = artliik.grupp) AND (toode.liik = 
artliik.liik))"
"  ->  Nested Loop  (cost=68.75..5062.19 rows=1 width=43) (actual 
time=8.045..26965.731 rows=567 loops=1)"
"        ->  Index Scan using toode_pkey on toode  (cost=0.00..8.27 rows=1 
width=43) (actual time=0.023..0.026 rows=1 loops=1)"
"              Index Cond: ('NAH S'::bpchar = toode)"
"        ->  Nested Loop  (cost=68.75..5053.91 rows=1 width=24) (actual 
time=8.016..26964.698 rows=567 loops=1)"
"              ->  Index Scan using dok_kuupaev_idx on dok 
(cost=0.00..4326.16 rows=10 width=4) (actual time=0.059..67.985 rows=3543 
loops=1)"
"                    Index Cond: ((kuupaev >= '2007-11-01'::date) AND 
(kuupaev <= '2007-12-04'::date))"
"                    Filter: ((((kuupaev)::text || (kellaaeg)::text) >= 
'2007-11-01'::text) AND (((kuupaev)::text || (kellaaeg)::text) <= 
'2007-12-0423 59'::text))"
"              ->  Bitmap Heap Scan on rid  (cost=68.75..72.76 rows=1 
width=28) (actual time=7.577..7.577 rows=0 loops=3543)"
"                    Recheck Cond: ((dok.dokumnr = rid.dokumnr) AND 
(rid.toode = 'NAH S'::bpchar))"
"                    ->  BitmapAnd  (cost=68.75..68.75 rows=1 width=0) 
(actual time=7.574..7.574 rows=0 loops=3543)"
"                          ->  Bitmap Index Scan on rid_dokumnr_idx 
(cost=0.00..5.13 rows=83 width=0) (actual time=0.037..0.037 rows=14 
loops=3543)"
"                                Index Cond: (dok.dokumnr = rid.dokumnr)"
"                          ->  Bitmap Index Scan on rid_toode_idx 
(cost=0.00..63.03 rows=1354 width=0) (actual time=7.528..7.528 rows=21144 
loops=3543)"
"                                Index Cond: (toode = 'NAH S'::bpchar)"
"  ->  Seq Scan on artliik  (cost=0.00..2.27 rows=27 width=19) (actual 
time=0.007..0.020 rows=27 loops=567)"
"Total runtime: 26996.399 ms"

takes 26 seconds !

If I remove last line it takes only 0 seconds:

SET SEARCH_PATH TO FIRMA1,public;
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'

"Hash Left Join  (cost=4313.85..7702.10 rows=24 width=24) (actual 
time=10.138..48.884 rows=567 loops=1)"
"  Hash Cond: ((toode.grupp = artliik.grupp) AND (toode.liik = 
artliik.liik))"
"  ->  Nested Loop  (cost=4311.17..7699.14 rows=24 width=43) (actual 
time=10.049..47.877 rows=567 loops=1)"
"        ->  Index Scan using toode_pkey on toode  (cost=0.00..8.27 rows=1 
width=43) (actual time=0.043..0.046 rows=1 loops=1)"
"              Index Cond: ('NAH S'::bpchar = toode)"
"        ->  Hash Join  (cost=4311.17..7690.63 rows=24 width=24) (actual 
time=9.998..47.341 rows=567 loops=1)"
"              Hash Cond: (rid.dokumnr = dok.dokumnr)"
"              ->  Index Scan using rid_toode_idx on rid 
(cost=0.00..3372.45 rows=1354 width=28) (actual time=0.089..24.265 
rows=21144 loops=1)"
"                    Index Cond: (toode = 'NAH S'::bpchar)"
"              ->  Hash  (cost=4286.20..4286.20 rows=1998 width=4) (actual 
time=9.871..9.871 rows=3543 loops=1)"
"                    ->  Index Scan using dok_kuupaev_idx on dok 
(cost=0.00..4286.20 rows=1998 width=4) (actual time=0.057..6.779 rows=3543 
loops=1)"
"                          Index Cond: ((kuupaev >= '2007-11-01'::date) AND 
(kuupaev <= '2007-12-04'::date))"
"  ->  Hash  (cost=2.27..2.27 rows=27 width=19) (actual time=0.060..0.060 
rows=27 loops=1)"
"        ->  Seq Scan on artliik  (cost=0.00..2.27 rows=27 width=19) (actual 
time=0.009..0.027 rows=27 loops=1)"
"Total runtime: 49.409 ms"


How to rewrite the query

select
       rid.toode,
      artliik.*
   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'

so it runs fast ?

Andrus.


"PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 
(mingw-special)" 



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings