Thread: Does pg_dump ignore indexes?
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
"Vilson farias" <vilson.farias@digitro.com.br> writes:
> 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?
Have you done a VACUUM ANALYZE after loading the pg_dump output?
It looks to me like the planner may not have statistics about the table...
regards, tom lane
: "Vilson farias" <vilson.farias@digitro.com.br> writes:
: > 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?
:
: Have you done a VACUUM ANALYZE after loading the pg_dump output?
: It looks to me like the planner may not have statistics about the table...
:
: regards, tom lane
:
I did a vacuum analyse, but my selects were still doing seq scans over
tables. After that, I'd executed VACUUM again, then I discovered that VACUUM
was not reaching the end of analysis, it losts connection with database,
after a lot of error reports. I can't figure out what's happening, could you
give me some light?
Check this out :
[postgres@dgtao backup]$ /usr/bin/vacuumdb --analyze --verbose -d relatorio
NOTICE: --Relation pg_type--
NOTICE: Pages 4: Changed 0, reaped 1, Empty 0, New 0; Tup 274: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 10, MinLen 105, MaxLen 114; Re-using:
Free/Avail. Space 1180/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.03u sec.
NOTICE: Index pg_type_typname_index: Pages 10; Tuples 274: Deleted 0. CPU
0.02s/0.01u sec.
NOTICE: Index pg_type_oid_index: Pages 5; Tuples 274: Deleted 0. CPU
0.00s/0.01u sec.
... lots of...
NOTICE: --Relation cham_chamada--
NOTICE: Rel cham_chamada: TID 4/1: OID IS INVALID. TUPGONE 1.
NOTICE: Rel cham_chamada: TID 4/2: OID IS INVALID. TUPGONE 1.
....3 to 56...
NOTICE: Rel cham_chamada: TID 4/57: OID IS INVALID. TUPGONE 1.
NOTICE: Rel cham_chamada: TID 4/58: OID IS INVALID. TUPGONE 1.
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
connection to server was lost
vacuumdb: vacuum failed
===========================================================
in psql :
relatorio=# \d cham_chamada
Table "cham_chamada"
Attribute | Type | Modifier
---------------+-------------+----------
chave | integer | not null
identificacao | integer | not null
dt_inicial | timestamp | not null
indicadora | integer |
cod_categoria | integer |
identidadea | varchar(20) |
dt_final | timestamp |
juntor | integer |
indicadorb | integer |
identidadeb | varchar(20) |
flg_chamada | char(1) |
flg_liberacao | char(1) |
Index: xpkcham_chamada
My best regards,
José Vilson de Mello de Farias
Dígitro Tecnologia - Brazil
"Vilson farias" <vilson.farias@digitro.com.br> writes:
> Check this out :
> [postgres@dgtao backup]$ /usr/bin/vacuumdb --analyze --verbose -d relatorio
> NOTICE: --Relation pg_type--
> NOTICE: Pages 4: Changed 0, reaped 1, Empty 0, New 0; Tup 274: Vac 0,
> Keep/VTL 0/0, Crash 0, UnUsed 10, MinLen 105, MaxLen 114; Re-using:
> Free/Avail. Space 1180/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.03u sec.
> NOTICE: Index pg_type_typname_index: Pages 10; Tuples 274: Deleted 0. CPU
> 0.02s/0.01u sec.
> NOTICE: Index pg_type_oid_index: Pages 5; Tuples 274: Deleted 0. CPU
> 0.00s/0.01u sec.
> ... lots of...
> NOTICE: --Relation cham_chamada--
> NOTICE: Rel cham_chamada: TID 4/1: OID IS INVALID. TUPGONE 1.
> NOTICE: Rel cham_chamada: TID 4/2: OID IS INVALID. TUPGONE 1.
> ....3 to 56...
> NOTICE: Rel cham_chamada: TID 4/57: OID IS INVALID. TUPGONE 1.
> NOTICE: Rel cham_chamada: TID 4/58: OID IS INVALID. TUPGONE 1.
> pqReadData() -- backend closed the channel unexpectedly.
Looks like you've got some corrupted data in that table :-(.
If you have a backup, you should restore this table from backup. Or you
could try dumping just this table (pg_dump -t cham_chamada relatorio).
If the results look correct, drop the table and reload it from the dump.
If you are running a Postgres version earlier than 7.0.3, I'd strongly
advise updating to 7.0.3 ASAP. There are several critical fixes in
7.0.3, including one for a VACUUM problem that might cause this sort of
corruption, depending on what your update patterns are like.
regards, tom lane