Re: How can I make PosgreSQL use an Index ?

Her goes:

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

Citando Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

<> Rhaoni Chiu Pereira <rhaoni(at)sistemica(dot)info> writes:
<> > When I run a explain analyze with this where clause:
<> > ... gsames00.ano_mes = to_char(ftnfco00.data_emissao,'YYYYMM') AND ...
<> > ...
<> > -> Seq Scan on gsames00 (cost=100000000.00..100000006.72 rows=372
<> width=10)
<> > (actual time=0.01..0.96 rows=372 loops=19923)
<> > ...
<>
<> If you're not going to show us the whole query and the whole EXPLAIN
<> output, you're going to get equally incomplete answers. I will say
<> though that forcing an index instead of a seqscan on a 372-row table
<> isn't likely to be a magic bullet. You probably need a better join
<> plan.
<>
<> Please post fuller details on pgsql-performance. You might save some
<> time by reading the posting tips first ...
<> http://techdocs.postgresql.org/guides/SlowQueryPostingGuidelines
<>
<> regards, tom lane
<>
<> ---------------------------(end of broadcast)---------------------------
<> TIP 2: you can get off all lists at once with the unregister command
<> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
<>