Does pg_dump ignore indexes?

From: "Vilson farias" <vilson(dot)farias(at)digitro(dot)com(dot)br>
To: <pgsql-general(at)postgresql(dot)org>
Cc: "EDUARDO TRIERWEILER Naschenweng" <eduardo(dot)naschenweng(at)digitro(dot)com(dot)br>, "SIMONE Carla MOSENA" <simone(dot)mosena(at)digitro(dot)com(dot)br>
Subject: Does pg_dump ignore indexes?
Date: 2000-11-20 18:54:00
Message-ID: 002d01c05323$3f19f380$98a0a8c0@dti.digitro.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings,

some time ago I sent an email about sequential scans over indexed
fields, like primary keys. My table had a smallinteger primary key, and when
I tried to do a select without a typecast to integer, the planner was
ignoring index fields. All right, I'd changed all my fields from smallint to
integer and select works ok, but sometimes the seq scans were still there.

> sitest=# CREATE TABLE tipo_categoria (
> sitest(# cod_categoria smallint NOT NULL,
> sitest(# descricao varchar(40),
> sitest(# CONSTRAINT XPKtipo_categoria PRIMARY KEY (cod_categoria));

> sitest=# explain select * from tipo_categoria where cod_categoria = 1;
> NOTICE: QUERY PLAN:
> Seq Scan on tipo_categoria (cost=0.00..22.50 rows=10 width=14)

...This table was changed to
> sitest=# CREATE TABLE tipo_categoria (
> sitest(# cod_categoria integer NOT NULL,
> sitest(# descricao varchar(40),
> sitest(# CONSTRAINT XPKtipo_categoria PRIMARY KEY (cod_categoria));

...Then select works fine... but sometimes :
> sitest=# explain select * from tipo_categoria where cod_categoria = 1;
> NOTICE: QUERY PLAN:
> Seq Scan on tipo_categoria (cost=0.00..22.50 rows=10 width=14)

I've been testing hardly and now I found something interesting : these
errors only happen when model/data are imported with pg_dump help, that is,
all indexed fields are sequencially scanned when I do a select from some
pk-indexed table, but if model/data where created/imported without pg_dump,
then everything works fine. Does anyone know why this is happening?

Regards,

José Vilson de Mello de Farias
Dígitro Tecnologia Ltda - Brasil

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Anders R. Sveen 2000-11-20 18:57:11 Logging
Previous Message Martin A. Marques 2000-11-20 18:43:27 Re: [HACKERS] Re: PHPBuilder article -- Postgres vs MySQL