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 tu use this index ???
Atenciosamente,
Rhaoni Chiu Pereira
Sistêmica Computadores
Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
