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

How can I make PosgreSQL use an Index ?

From: Rhaoni Chiu Pereira <rhaoni(at)sistemica(dot)info>
To: PostgreSQL ADMIN <pgsql-admin(at)postgresql(dot)org>
Subject: How can I make PosgreSQL use an Index ?
Date: 2003-09-17 20:44:42
Message-ID: 1063831482.3f68c7bab4b1a@sistemica.info (view raw or flat)
Thread:
Lists: pgsql-admin
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






Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2003-09-17 21:20:46
Subject: Re: How can I make PosgreSQL use an Index ?
Previous:From: scott.marloweDate: 2003-09-17 19:57:23
Subject: Re: change of table name - any help

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