Improving SQL performance

["Carlos H. Reimer" <carlos.reimer@xxxxxxxxxxxxx>]

Hi,

 

I know that the problem with the following SQL is the "LOG.CODCEP = ENDE.CODCEP||CODLOG" condition, but what can I
do to improve the performance?

 

Is there a type of index that could help or is there another way to build this SQL?

 

Thank you in advance!

 

explain analyze
SELECT ENDE.* , DEND.DESEND, DEND.USOEND, DEND.DUPEND,
               to_char('F') as NOVO,
               LOG.TIPLOG
          FROM TT_END ENDE LEFT OUTER JOIN TD_END DEND ON DEND.CODTAB = ENDE.TIPEND
                           LEFT OUTER JOIN TT_LOG LOG ON LOG.CODCEP = ENDE.CODCEP||CODLOG
         WHERE ENDE.FILCLI = '001'
           AND ENDE.CODCLI = ' 19475';

 

                                                            QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..25366.84 rows=1259 width=417) (actual time=1901.499..1901.529 rows=1 loops=1)
   Join Filter: (("inner".codcep)::text = (("outer".codcep)::text || ("outer".codlog)::text))
   ->  Nested Loop Left Join  (cost=0.00..4.91 rows=1 width=412) (actual time=0.117..0.144 rows=1 loops=1)
         Join Filter: ("inner".codtab = "outer".tipend)
         ->  Index Scan using pk_end on tt_end ende  (cost=0.00..3.87 rows=1 width=388) (actual time=0.066..0.078 rows=1 loops=1)
               Index Cond: ((filcli = '001'::bpchar) AND (codcli = ' 19475'::bpchar))
         ->  Seq Scan on td_end dend  (cost=0.00..1.02 rows=2 width=33) (actual time=0.012..0.018 rows=2 loops=1)
   ->  Seq Scan on tt_log log  (cost=0.00..12254.24 rows=582424 width=17) (actual time=0.013..582.521 rows=582424 loops=1)
 Total runtime: 1901.769 ms
(9 rows)

 

\d tt_log
            Table "TOTALL.tt_log"
 Column |          Type          | Modifiers
--------+------------------------+-----------
 codbai | numeric(5,0)           | not null
 nomlog | character varying(55)  | not null
 codcep | character(8)           | not null

 

\d tt_end
                          Table "TOTALL.tt_end"
 Column |         Type          |                Modifiers
--------+-----------------------+-----------------------------------------
...
...
...
 codlog | character(3)          |
...
...
...
 codcep | character(5)          |
...
...

Reimer