Does pg_dump ignore indexes? 
Author Message
 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.

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

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

Quote:
> 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
Dgitro Tecnologia Ltda - Brasil



Sat, 10 May 2003 02:54:19 GMT
 Does pg_dump ignore indexes?

Quote:

> 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



Sat, 10 May 2003 04:10:49 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Does pg_dump ignore indexes?

2. pgsql-server/src/bin/pg_dump pg_dump.c pg_dump ...

3. pgsql/src/bin/pg_dump pg_dump.c pg_dump.h

4. pgsql/src/bin/pg_dump pg_dump.c pg_dump.h

5. pgsql/src/bin/pg_dump (common.c pg_backup_archiver.h pg_dump.c pg_dump.h)

6. pgsql/src/bin/pg_dump pg_dump.c pg_dump.h

7. pgsql/src/bin/pg_dump common.c pg_dump.c pg_dump.h

8. pgsql-server/src/bin/pg_dump pg_dump.c pg_dump.h

9. pgsql/src/bin/pg_dump (pg_dump.c pg_dump.h)

10. pgsql/src/bin/pg_dump common.c pg_dump.c pg_dump.h

11. pgsql/src/bin/pg_dump (pg_dump.c pg_dump.h pg_dumpall.sh)

12. pgsql-server/src/bin/pg_dump pg_dump.c pg_dump ...


 
Powered by phpBB® Forum Software