How to force an Index ? - Mailing list pgsql-performance

Hi list,

    I have a table like this:

CREATE TABLE "gsames00" (
        "ano_mes" varchar(6) NOT NULL,
        "descricao" varchar(30),
        PRIMARY KEY ("ano_mes")
);

and an index like this:

CREATE INDEX GSAMES01 ON  GSAMES00 (ANO_MES);

  When I run a explain analyze with this where clause:

   ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') AND ...

   ftnfco00.data_emissao is a timestamp. When I run the explain analyze it says:

...
 ->  Seq Scan on gsames00  (cost=100000000.00..100000006.72 rows=372 width=10)
(actual time=0.01..0.96 rows=372 loops=19923)
...

   So it is not using the index, and it makes the query too slow to return the
result. If a run the same query without this clause it gets about 1 minute
faster. You you're wondering : If you can run this query without this clause,
Why don't you take it out ?
   I must use it because this query is created by a BI software  and to
change it, I'll have to make a lot of changes in the BI software source. In the
Oracle DB it works fine 'cuz Oracle use the index and do it instantly.
   Any suggestion on how to force PostgreSQL to use this index ???
   I run  Vaccum Full Analyze many time before posting this ...

Here follow the whole query and the whole explain:

Query:

SELECT /*+  */
ftnfco00.estado_cliente ,
ftcofi00.grupo_faturamento ,
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.qtde_duzias,0)),       '+', NVL(ftnfpr00.qtde_duzias,0),       0) ) ,
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL(ftnfpr00.vlr_liquido,0)), '+',
NVL(ftnfpr00.vlr_liquido,0), 0) ) ,
ftprod00.tipo_cadastro||ftprod00.codigo_produto ,
ftprod00.descricao_produto ,
DIVIDE( SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)
*ftnfpr00.margem_comercial ),
                         SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)) ) ,
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.qtde_duzias,0), 0 ) ) ,
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.vlr_liquido,0), 0 ) )
FROM
ftprod00 ,
ftnfco00 ,
ftcgma00 ,
ftcgca00 ,
ftspro00 ,
ftclcr00 ,
gsames00 ,
ftcofi00 ,
ftrepr00 ,
gsesta00 ,
ftsupv00 ,
ftgrep00 ,
ftclgr00 ,
ftband00 ,
fttcli00 ,
ftredc00 ,
ftnfpr00
WHERE
ftnfco00.emp = 909 AND
ftnfpr00.fil IN ('101') AND
ftnfco00.situacao_nf = 'N' AND
ftnfco00.data_emissao >= CAST('01-JAN-2003' AS DATE) AND
ftnfco00.data_emissao <= CAST('31-MAR-2003' AS DATE) AND
ftcofi00.grupo_faturamento >= '01' AND
(ftcofi00.atual_fatura IN ('+','-') OR ftcofi00.nf_prodgratis = 'S') AND
ftcgma00.emp    = ftprod00.emp AND
ftcgma00.fil    = ftprod00.fil AND
ftcgma00.codigo = ftprod00.cla_marca AND
ftcgca00.emp    = ftprod00.emp AND
ftcgca00.fil    = ftprod00.fil AND
ftcgca00.codigo = ftprod00.cla_categoria AND
ftspro00.emp    = ftprod00.emp AND
ftspro00.fil    = ftprod00.fil AND
ftspro00.codigo = ftprod00.situacao AND
ftclcr00.emp           = ftnfco00.emp AND
ftclcr00.fil           = ftnfco00.empfil AND
ftclcr00.tipo_cadastro = ftnfco00.tipo_cad_clicre AND
ftclcr00.codigo        = ftnfco00.cod_cliente AND
gsames00.ano_mes       = TO_CHAR(ftnfco00.data_emissao,'YYYYMM') AND
ftcofi00.emp           = ftnfco00.emp AND
ftcofi00.fil           = ftnfco00.empfil AND
ftcofi00.codigo_fiscal = ftnfco00.cod_fiscal AND
ftrepr00.emp           = ftnfco00.emp AND
ftrepr00.fil           = ftnfco00.empfil AND
ftrepr00.codigo_repr   = ftnfco00.cod_repres AND
gsesta00.estado_sigla  = ftnfco00.estado_cliente AND
ftsupv00.emp               = ftrepr00.emp AND
ftsupv00.fil               = ftrepr00.fil AND
ftsupv00.codigo_supervisor = ftrepr00.codigo_supervisor AND
ftgrep00.emp               = ftrepr00.emp AND
ftgrep00.fil               = ftrepr00.fil AND
ftgrep00.codigo_grupo_rep  = ftrepr00.codigo_grupo_rep AND
ftclgr00.emp               = ftclcr00.emp AND
ftclgr00.fil               = ftclcr00.fil AND
ftclgr00.codigo            = ftclcr00.codigo_grupo_cliente AND
ftband00.emp               = ftclcr00.emp AND
ftband00.fil               = ftclcr00.fil AND
ftband00.codigo            = ftclcr00.bandeira_cliente AND
fttcli00.emp               = ftclcr00.emp AND
fttcli00.fil               = ftclcr00.fil AND
fttcli00.cod_tipocliente   = ftclcr00.codigo_tipo_cliente AND
ftredc00.emp               = ftclcr00.emp AND
ftredc00.fil               = ftclcr00.fil AND
ftredc00.tipo_contribuinte = ftclcr00.tipo_contribuinte AND
ftredc00.codigo_rede       = ftclcr00.codigo_rede AND
gsesta00.estado_sigla      = ftclcr00.emp_estado AND
ftnfco00.emp           = ftnfpr00.emp AND
ftnfco00.fil           = ftnfpr00.fil AND
ftnfco00.nota_fiscal   = ftnfpr00.nota_fiscal AND
ftnfco00.serie         = ftnfpr00.serie AND
ftnfco00.data_emissao  = ftnfpr00.data_emissao AND
ftprod00.emp           = ftnfpr00.emp AND
ftprod00.fil           = ftnfpr00.empfil AND
ftprod00.tipo_cadastro = ftnfpr00.tipo_cad_promat AND
ftprod00.codigo_produto= ftnfpr00.cod_produto
GROUP BY
ftnfco00.estado_cliente ,
ftcofi00.grupo_faturamento ,
ftprod00.tipo_cadastro||ftprod00.codigo_produto ,
ftprod00.descricao_produto



Explain:



                       QUERY
PLAN


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------------
 Aggregate  (cost=100027780.66..100027780.69 rows=1 width=818) (actual
time=101278.24..105839.69 rows=363 loops=1)
   ->  Group  (cost=100027780.66..100027780.68 rows=1 width=818) (actual
time=101272.08..101761.18 rows=19923 loops=1)
         ->  Sort  (cost=100027780.66..100027780.67 rows=1 width=818) (actual
time=101272.05..101299.09 rows=19923 loops=1)
               Sort Key: ftnfco00.estado_cliente, ftcofi00.grupo_faturamento,
((ftprod00.tipo_cadastro)::text || (ftprod00.codigo_produto)::text),
ftprod00.descricao_produto
               ->  Nested Loop  (cost=100025960.94..100027780.65 rows=1
width=818) (actual time=3476.87..99606.77 rows=19923 loops=1)
                     Join Filter: (("outer".emp = "inner".emp) AND ("outer".fil
= "inner".fil) AND ("outer".codigo_supervisor = "inner".codigo_supervisor) AND
("outer".codigo_grupo_rep = "inner".codigo_grupo_rep))
                     ->  Nested Loop  (cost=100025960.94..100027775.22 rows=1
width=765) (actual time=3476.74..97802.69 rows=19923 loops=1)
                           Join Filter: (("inner".ano_mes)::text = to_char
("outer".data_emissao, 'YYYYMM'::text))
                           ->  Nested Loop  (cost=25960.94..27762.92 rows=1
width=755) (actual time=3475.14..32090.12 rows=19923 loops=1)
                                 Join Filter: (("inner".emp = "outer".emp) AND
("outer".fil = "inner".fil) AND ("outer".codigo = "inner".cla_categoria) AND
("outer".codigo = "inner".cla_marca) AND ("outer".codigo = "inner".situacao))
                                 ->  Nested Loop  (cost=25960.94..27705.22
rows=10 width=665) (actual time=3474.12..17734.21 rows=199230 loops=1)
                                       Join Filter: (("outer".emp
= "inner".emp) AND ("inner".fil = "outer".fil))
                                       ->  Nested Loop
(cost=25960.94..27699.30 rows=1 width=638) (actual time=3474.02..6030.09
rows=19923 loops=1)
                                             Join Filter: (("inner".emp
= "outer".emp) AND ("inner".empfil = "outer".fil))
                                             ->  Merge Join
(cost=25960.94..26128.25 rows=265 width=526) (actual time=3473.78..3841.18
rows=6358 loops=1)
                                                   Merge Cond: (("outer".emp
= "inner".emp) AND ("outer".fil = "inner".fil) AND ("outer".codigo_fiscal
= "inner".cod_fiscal))
                                                   ->  Index Scan using
ftcofi01 on ftcofi00  (cost=0.00..151.73 rows=72 width=52) (actual
time=0.15..6.40 rows=64 loops=1)
                                                         Filter:
((grupo_faturamento >= '01'::character varying) AND ((atual_fatura
= '+'::character varying) OR (atual_fatura = '-'::character varying) OR
(nf_prodgratis = 'S'::character varying)))
                                                   ->  Sort
(cost=25960.94..25965.34 rows=1760 width=474) (actual time=3471.17..3486.98
rows=7666 loops=1)
                                                         Sort Key:
ftnfco00.emp, ftredc00.fil, ftnfco00.cod_fiscal
                                                         ->  Nested Loop
(cost=25687.75..25866.07 rows=1760 width=474) (actual time=2981.05..3241.15
rows=7666 loops=1)
                                                               Join Filter:
(("inner".emp = "outer".emp) AND ("inner".fil = "outer".fil) AND
("outer".codigo = "inner".codigo_grupo_cliente))
                                                               ->  Index Scan
using ftclgr01 on ftclgr00  (cost=0.00..4.68 rows=1 width=32) (actual
time=0.04..0.06 rows=1 loops=1)
                                                               ->  Materialize
(cost=25830.59..25830.59 rows=1760 width=442) (actual time=2980.93..2990.31
rows=7666 loops=1)
                                                                     ->  Hash
Join  (cost=25687.75..25830.59 rows=1760 width=442) (actual
time=2507.55..2945.35 rows=7666 loops=1)
                                                                           Hash
Cond: ("outer".emp_estado = "inner".estado_sigla)
                                                                           ->
Nested Loop  (cost=25683.33..25790.98 rows=1760 width=436) (actual
time=2507.09..2711.66 rows=7666 loops=1)

 Join Filter: (("inner".emp = "outer".emp) AND ("inner".fil = "outer".fil))

 ->  Index Scan using ftgrep01 on ftgrep00  (cost=0.00..4.68 rows=1 width=32)
(actual time=0.05..0.07 rows=1 loops=1)

 ->  Materialize  (cost=25759.91..25759.91 rows=1760 width=404) (actual
time=2506.98..2516.14 rows=7666 loops=1)

       ->  Nested Loop  (cost=25683.33..25759.91 rows=1760 width=404) (actual
time=2288.68..2474.11 rows=7666 loops=1)

             Join Filter: (("inner".emp = "outer".emp) AND ("inner".fil
= "outer".fil))

             ->  Index Scan using ftsupv01 on ftsupv00  (cost=0.00..4.68 rows=1
width=32) (actual time=0.04..0.05 rows=1 loops=1)

             ->  Materialize  (cost=25728.83..25728.83 rows=1760 width=372)
(actual time=2288.58..2297.79 rows=7666 loops=1)

                   ->  Merge Join  (cost=25683.33..25728.83 rows=1760
width=372) (actual time=2086.89..2265.03 rows=7666 loops=1)

                         Merge Cond: (("outer".emp = "inner".emp) AND
("outer".fil = "inner".fil) AND ("outer".cod_tipocliente
= "inner".codigo_tipo_cliente))

                         ->  Index Scan using fttcli01 on fttcli00
(cost=0.00..5.85 rows=17 width=33) (actual time=0.03..0.25 rows=17 loops=1)

                         ->  Sort  (cost=25683.33..25687.73 rows=1760
width=339) (actual time=2086.71..2095.86 rows=7666 loops=1)

                               Sort Key: ftnfco00.emp, ftredc00.fil,
ftclcr00.codigo_tipo_cliente

                               ->  Nested Loop  (cost=25389.10..25588.46
rows=1760 width=339) (actual time=1729.53..1897.73 rows=7666 loops=1)

                                     Join Filter: (("inner".emp = "outer".emp)
AND ("inner".fil = "outer".fil) AND ("outer".codigo = "inner".bandeira_cliente))

                                     ->  Index Scan using ftband01 on ftband00
(cost=0.00..4.68 rows=1 width=32) (actual time=0.04..0.06 rows=1 loops=1)

                                     ->  Materialize  (cost=25552.99..25552.99
rows=1760 width=307) (actual time=1729.44..1738.69 rows=7666 loops=1)

                                           ->  Nested Loop
(cost=25389.10..25552.99 rows=1760 width=307) (actual time=1566.24..1705.51
rows=7666 loops=1)

                                                 Join Filter: (("inner".emp
= "outer".emp) AND ("inner".fil = "outer".fil))

                                                 ->  Index Scan using ftcgma01
on ftcgma00  (cost=0.00..4.68 rows=1 width=32) (actual time=0.03..0.05 rows=1
loops=1)

                                                 ->  Materialize
(cost=25521.91..25521.91 rows=1760 width=275) (actual time=1566.16..1575.29
rows=7666 loops=1)

                                                       ->  Merge Join
(cost=25389.10..25521.91 rows=1760 width=275) (actual time=1320.59..1542.54
rows=7666 loops=1)

                                                             Merge Cond:
(("outer".codigo = "inner".cod_cliente) AND ("outer".emp_estado
= "inner".estado_cliente) AND ("outer".tipo_cadastro = "inner".tipo_cad_clicre)
AND ("outer".fil = "inner".empfil) AND ("outer".emp = "inner".emp))

                                                             ->  Sort
(cost=6241.05..6269.31 rows=11304 width=166) (actual time=1093.04..1105.44
rows=10478 loops=1)

                                                                   Sort Key:
ftclcr00.codigo, ftclcr00.emp_estado, ftclcr00.tipo_cadastro, ftredc00.fil,
ftredc00.emp

                                                                   ->  Merge
Join  (cost=3920.20..5480.05 rows=11304 width=166) (actual time=516.40..951.73
rows=10956 loops=1)

                                                                         Merge
Cond: (("outer".emp = "inner".emp) AND ("outer".fil = "inner".fil) AND
("outer".tipo_contribuinte = "inner".tipo_contribuinte) AND
("outer".codigo_rede = "inner".codigo_rede))

                                                                         ->
Merge Join  (cost=0.00..1256.74 rows=8906 width=72) (actual time=0.13..180.25
rows=8906 loops=1)


Merge Cond: ("outer".emp = "inner".emp)

                                                                               -
>  Index Scan using ftredc01 on ftredc00  (cost=0.00..1118.47 rows=8906
width=40) (actual time=0.05..72.02 rows=8906 loops=1)

                                                                               -
>  Index Scan using ftcgca01 on ftcgca00  (cost=0.00..4.68 rows=1 width=32)
(actual time=0.04..19.14 rows=1 loops=1)

                                                                         ->
Sort  (cost=3920.20..3947.59 rows=10956 width=94) (actual time=516.19..529.77
rows=10956 loops=1)


Sort Key: ftclcr00.emp, ftclcr00.fil, ftclcr00.tipo_contribuinte,
ftclcr00.codigo_rede

                                                                               -
>  Index Scan using ftclcr07 on ftclcr00  (cost=0.00..3185.08 rows=10956
width=94) (actual time=0.09..146.20 rows=10956 loops=1)

                                                             ->  Sort
(cost=19148.05..19167.27 rows=7688 width=109) (actual time=227.46..237.00
rows=7668 loops=1)

                                                                   Sort Key:
ftnfco00.cod_cliente, ftnfco00.estado_cliente, ftnfco00.tipo_cad_clicre,
ftnfco00.empfil, ftnfco00.emp

                                                                   ->  Index
Scan using ftnfco06 on ftnfco00  (cost=0.00..18651.88 rows=7688 width=109)
(actual time=0.16..116.43 rows=7668 loops=1)

                                                                         Index
Cond: ((emp = 909::numeric) AND (situacao_nf = 'N'::character varying) AND
(data_emissao >= '2002-10-01 00:00:00'::timestamp without time zone) AND
(data_emissao <= '2003-03-31 00:00:00'::timestamp without time zone))
                                                                           ->
Hash  (cost=4.33..4.33 rows=33 width=6) (actual time=0.23..0.23 rows=0 loops=1)

 ->  Index Scan using gsesta01 on gsesta00  (cost=0.00..4.33 rows=33 width=6)
(actual time=0.04..0.15 rows=33 loops=1)
                                             ->  Index Scan using ftnfpr05 on
ftnfpr00  (cost=0.00..5.91 rows=1 width=112) (actual time=0.06..0.15 rows=3
loops=6358)
                                                   Index Cond: (("outer".emp =
ftnfpr00.emp) AND ("outer".fil = ftnfpr00.fil) AND (ftnfpr00.fil =
101::numeric) AND ("outer".data_emissao = ftnfpr00.data_emissao) AND
("outer".nota_fiscal = ftnfpr00.nota_fiscal) AND ("outer".serie =
ftnfpr00.serie))
                                       ->  Index Scan using ftspro01 on
ftspro00  (cost=0.00..5.78 rows=10 width=27) (actual time=0.01..0.07 rows=10
loops=19923)
                                 ->  Index Scan using ftprod01 on ftprod00
(cost=0.00..5.74 rows=1 width=90) (actual time=0.04..0.05 rows=1 loops=199230)
                                       Index Cond: ((ftprod00.emp
= "outer".emp) AND (ftprod00.fil = "outer".empfil) AND (ftprod00.tipo_cadastro
= "outer".tipo_cad_promat) AND (ftprod00.codigo_produto = "outer".cod_produto))
                           ->  Seq Scan on gsames00
(cost=100000000.00..100000006.72 rows=372 width=10) (actual time=0.01..0.96
rows=372 loops=19923)
                     ->  Index Scan using ftrepr01 on ftrepr00
(cost=0.00..5.41 rows=1 width=53) (actual time=0.04..0.05 rows=1 loops=19923)
                           Index Cond: ((ftrepr00.emp = "outer".emp) AND
(ftrepr00.fil = "outer".empfil) AND (ftrepr00.codigo_repr = "outer".cod_repres))
 Total runtime: 105885.43 msec
(75 rows)



The Oracle functions like NVL, DECODE, and others had been created in
PostgreSQL.


Atenciosamente,

Rhaoni Chiu Pereira
Sistêmica Computadores

Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122