Skip site navigation (1) Skip section navigation (2)

How to force an Index ?

From: Rhaoni Chiu Pereira <rhaoni(at)sistemica(dot)info>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: How to force an Index ?
Date: 2003-09-17 22:17:47
Message-ID: 1063837067.3f68dd8b5a62b@sistemica.info (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-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






Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2003-09-17 22:38:33
Subject: Re: How to force an Index ?
Previous:From: Josh BerkusDate: 2003-09-17 22:17:26
Subject: Re: Is there a reason _not_ to vacuum continuously?

pgsql-admin by date

Next:From: Josh BerkusDate: 2003-09-17 22:38:33
Subject: Re: How to force an Index ?
Previous:From: Rhaoni Chiu PereiraDate: 2003-09-17 21:54:38
Subject: Re: How can I make PosgreSQL use an Index ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group