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
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 |