Re: How to force an Index ?

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