pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to force an Index ?
6+ messages / 3 participants
[nested] [flat]

* How to force an Index ?
@ 2003-09-17 22:17 Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-09-17 22:38 ` Re: How to force an Index ? Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Rhaoni Chiu Pereira @ 2003-09-17 22:17 UTC (permalink / raw)
  To: 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








^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How to force an Index ?
  2003-09-17 22:17 How to force an Index ? Rhaoni Chiu Pereira <rhaoni@sistemica.info>
@ 2003-09-17 22:38 ` Josh Berkus <josh@agliodbs.com>
  2003-09-18 14:45   ` Re: [PERFORM] How to force an Index ? Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  0 siblings, 1 reply; 6+ messages in thread

From: Josh Berkus @ 2003-09-17 22:38 UTC (permalink / raw)
  To: Rhaoni Chiu Pereira <rhaoni@sistemica.info>; pgsql-performance

Rhaoni,

First off, thanks for posting such complete info.

>    ... 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)
> ...

Your problem is that you're comparing against a calculated expression based on 
ftnfco00, which is being filtered in about 18 other ways.  As a result, the 
planner doesn't know what to estimate (see the cost estimate of 100000000, 
which is a "blind guess" values) and goes for a seq scan.

 Can I ask you to try this workaround, to create an expressional index on 
ftnfco00 (assuming that data_emmisao is of type DATE)

create function date_to_yyyymm( date ) returns text as
'select to_char($1, ''YYYYMM'');
' language sql immutable strict;

create index idx_data_yyyymm on ftnfco00(date_to_yyyymm(data_emmisao));

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [PERFORM] How to force an Index ?
  2003-09-17 22:17 How to force an Index ? Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-09-17 22:38 ` Re: How to force an Index ? Josh Berkus <josh@agliodbs.com>
@ 2003-09-18 14:45   ` Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-09-18 16:16     ` Re: [PERFORM] How to force an Index ? Tom Lane <tgl@sss.pgh.pa.us>
  2003-09-18 17:50     ` Re: [PERFORM] How to force an Index ? Josh Berkus <josh@agliodbs.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Rhaoni Chiu Pereira @ 2003-09-18 14:45 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: PostgreSQL ADMIN <pgsql-admin@postgresql.org>; pgsql-performance

I solve this problem doing this:

 create function date_to_yyyymm( timestamp ) returns gsames00.ano_mes%type as
 'select to_char($1, ''YYYYMM'');
 ' language sql immutable strict;

And changing the SQL where clause:

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

to:

 ... gsames00.ano_mes = date_to_yyyymm(ftnfco00.data_emissao) AND ...

   Then it uses the gsames00 index instead of a SeqScan 'cuz it is camparing
same data type, but .. I don't want to create this function 'cuz this aplication
is used with Oracle too. 
I need to know if there is a way to set the to_char output to varchar instead of
text !
  Any Idea ? So, this way I wont have to change my aplication source.



Atenciosamente,

Rhaoni Chiu Pereira
Sistêmica Computadores

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



Citando Josh Berkus <josh@agliodbs.com>:

<> Rhaoni,
<> 
<> First off, thanks for posting such complete info.
<> 
<> >    ... 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)
<> > ...
<> 
<> Your problem is that you're comparing against a calculated expression based
<> on 
<> ftnfco00, which is being filtered in about 18 other ways.  As a result, the
<> 
<> planner doesn't know what to estimate (see the cost estimate of 100000000, 
<> which is a "blind guess" values) and goes for a seq scan.
<> 
<>  Can I ask you to try this workaround, to create an expressional index on 
<> ftnfco00 (assuming that data_emmisao is of type DATE)
<> 
<> create function date_to_yyyymm( date ) returns text as
<> 'select to_char($1, ''YYYYMM'');
<> ' language sql immutable strict;
<> 
<> create index idx_data_yyyymm on ftnfco00(date_to_yyyymm(data_emmisao));
<> 
<> -- 
<> -Josh Berkus
<>  Aglio Database Solutions
<>  San Francisco
<> 
<> 
<> ---------------------------(end of broadcast)---------------------------
<> TIP 4: Don't 'kill -9' the postmaster
<> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [PERFORM] How to force an Index ?
  2003-09-17 22:17 How to force an Index ? Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-09-17 22:38 ` Re: How to force an Index ? Josh Berkus <josh@agliodbs.com>
  2003-09-18 14:45   ` Re: [PERFORM] How to force an Index ? Rhaoni Chiu Pereira <rhaoni@sistemica.info>
@ 2003-09-18 16:16     ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 6+ messages in thread

From: Tom Lane @ 2003-09-18 16:16 UTC (permalink / raw)
  To: Rhaoni Chiu Pereira <rhaoni@sistemica.info>; +Cc: josh@agliodbs.com, PostgreSQL ADMIN <pgsql-admin@postgresql.org>; pgsql-performance

Rhaoni Chiu Pereira <rhaoni@sistemica.info> writes:
> I need to know if there is a way to set the to_char output to varchar instead of
> text !

Why don't you change the datatype of ano_mes to text, instead?  It's
unlikely your application would notice the difference.  (You could set
a CHECK constraint on the length if you really want to duplicate the
behavior of varchar(6).)

Alternatively, try 7.4 beta. I believe this issue goes away in 7.4,
because varchar no longer has separate comparison operators.

Of course there's also the option of modifying to_char's result type
in pg_proc, but I won't promise that doing so wouldn't break things.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [PERFORM] How to force an Index ?
  2003-09-17 22:17 How to force an Index ? Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-09-17 22:38 ` Re: How to force an Index ? Josh Berkus <josh@agliodbs.com>
  2003-09-18 14:45   ` Re: [PERFORM] How to force an Index ? Rhaoni Chiu Pereira <rhaoni@sistemica.info>
@ 2003-09-18 17:50     ` Josh Berkus <josh@agliodbs.com>
  2003-09-18 18:02       ` Re: How to force an Index ? Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Josh Berkus @ 2003-09-18 17:50 UTC (permalink / raw)
  To: Rhaoni Chiu Pereira <rhaoni@sistemica.info>; +Cc: PostgreSQL ADMIN <pgsql-admin@postgresql.org>; pgsql-performance

Rhaoni,

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

>    Then it uses the gsames00 index instead of a SeqScan 'cuz it is
> camparing same data type, but .. I don't want to create this function 'cuz
> this aplication is used with Oracle too.

You should have said that earlier ....

> I need to know if there is a way to set the to_char output to varchar
> instead of text !

Did you try: 

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


-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: How to force an Index ?
  2003-09-17 22:17 How to force an Index ? Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-09-17 22:38 ` Re: How to force an Index ? Josh Berkus <josh@agliodbs.com>
  2003-09-18 14:45   ` Re: [PERFORM] How to force an Index ? Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-09-18 17:50     ` Re: [PERFORM] How to force an Index ? Josh Berkus <josh@agliodbs.com>
@ 2003-09-18 18:02       ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Josh Berkus @ 2003-09-18 18:02 UTC (permalink / raw)
  To: Rhaoni Chiu Pereira <rhaoni@sistemica.info>; +Cc: pgsql-performance

Rhaoni,

> I could .. but this way I wont be used because Oracle doesn't accept such
> sintax ! I changed gsames00.ano_mes from varchar to text ! But it still not
> fast enough to take Oracle's place !!!
> I still trying to do so ...

Well, your basic problem is that performance tuning for *any* database often 
requires use of database-specific syntax.   You would be having the same 
problem, in the opposite direction, if you were trying to port a PostgreSQL 
app to Oracle without changing any syntax.

Here's syntax Oracle should accept:

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


-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2003-09-18 18:02 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-17 22:17 How to force an Index ? Rhaoni Chiu Pereira <rhaoni@sistemica.info>
2003-09-17 22:38 ` Josh Berkus <josh@agliodbs.com>
2003-09-18 14:45   ` Rhaoni Chiu Pereira <rhaoni@sistemica.info>
2003-09-18 16:16     ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-18 17:50     ` Josh Berkus <josh@agliodbs.com>
2003-09-18 18:02       ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox