Indexes not working, please help.

Hi there.

The table medidas has the following structure:

Table "medidas"
Column | Type | Modifiers
------------+-----------------------------+-----------------------------------------------------
cod | bigint | not null default
nextval('"medidas_cod_seq"'::text)
cod_ponto | bigint |
data_hora | timestamp without time zone |
valor | smallint |
tipo_ponto | bytea |
unidade | bytea |
Indexes: teste
Primary key: medidas_pkey

I populated it with 8859000 entries using many "100 inserts"
blocks transactions. Inserting data on the table is no problem.
Althought I created the "teste" index ( create index teste on
medidas (cod) ), vacuumed and analyzed the database, pgsql still doesn't
use indexes.

explain select * from medidas where cod = 1231232;
---
NOTICE: QUERY PLAN:

Seq Scan on medidas (cost=0.00..197590.50 rows=1 width=36)
---

I've read all the documentation I found, but I couldn't figure out
what's happening. Setting "enable_seqscan" to false doesn't help either.
This machine I'm working on won't be the production server, but
it's not that slow machine (P3 1GHz, 384MB, ATA 100) and without indexes
working, that select mentioned above takes almost 60 seconds to run...

[]'s
Ricardo.

Ricardo Ryoiti Sugawara Junior <suga(at)netbsd(dot)com(dot)br> writes:
> cod | bigint | not null default

> explain select * from medidas where cod = 1231232;

Try "1231232::bigint".

regards, tom lane

---
Index Scan using medidas_pkey on medidas (cost=0.00..3.01 rows=1
width=36)
---

Indeed, it got a bit faster... :D

Thanks!

[]'s
Ricardo.

On Fri, 5 Jul 2002, Tom Lane wrote:

> Ricardo Ryoiti Sugawara Junior <suga(at)netbsd(dot)com(dot)br> writes:
> > cod | bigint | not null default
>
> > explain select * from medidas where cod = 1231232;
>
> Try "1231232::bigint".
>
> regards, tom lane
>

Ricardo Ryoiti Sugawara Junior wrote:

> Hi there.
>
> The table medidas has the following structure:
>
> Table "medidas"
> Column | Type | Modifiers
> ------------+-----------------------------+-----------------------------------------------------
> cod | bigint | not null default
> nextval('"medidas_cod_seq"'::text)
> cod_ponto | bigint |
> data_hora | timestamp without time zone |
> valor | smallint |
> tipo_ponto | bytea |
> unidade | bytea |
> Indexes: teste
> Primary key: medidas_pkey
>
> I populated it with 8859000 entries using many "100 inserts"
> blocks transactions. Inserting data on the table is no problem.
> Althought I created the "teste" index ( create index teste on
> medidas (cod) ), vacuumed and analyzed the database, pgsql still doesn't
> use indexes.
>
> explain select * from medidas where cod = 1231232;
> ---
> NOTICE: QUERY PLAN:
>
> Seq Scan on medidas (cost=0.00..197590.50 rows=1 width=36)
> ---
>
> I've read all the documentation I found, but I couldn't figure out
> what's happening. Setting "enable_seqscan" to false doesn't help either.
> This machine I'm working on won't be the production server, but
> it's not that slow machine (P3 1GHz, 384MB, ATA 100) and without indexes
> working, that select mentioned above takes almost 60 seconds to run...
>
>

Hi,
Is your index on the attribute cod? If not then try this:
CREATE INDEX teste ON medidas (cod);

Thanks
Antonis

Antonis,

That was not the case, it appears that PostgreSQL have problems
matching the type on the table and on the queries. For instance, cod is a
bigserial primary key, so it does has a index but when you query it
without specifying ::bigint after the value, I don't know why, PostgreSQL
won't use it's indexes.

With the "serial" type it works perfectly.

Thanks anyway,

Ricardo.

On Fri, 5 Jul 2002, Antonis Antoniou wrote:

> Hi,
> Is your index on the attribute cod? If not then try this:
> CREATE INDEX teste ON medidas (cod);
>
> Thanks
> Antonis