Hi List,
I've a strange problem with missing indices.
\d import.dvinfo_import
gives me:
Table "import.dvinfo_import"
Column | Type | Modifiers
-------------------+------------------------+-----------
s_part_number | character varying(20) |
s_pda | character varying(4) |
dv_id | character varying(30) |
s_revision | integer |
s_strl1_typ | character varying(255) |
s_strl1_nr | integer |
s_strl2_typ | character varying(255) |
s_strl2_nr | integer |
[...]
format | character varying(25) |
file_id_mimetype | character varying(255) |
without any indices. But:
\di import.dvinfo_import*
lists:
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+--------------+---------------
import | dvinfo_import_1 | index | usr_nefa_dev | dvinfo_import
import | dvinfo_import_2 | index | usr_nefa_dev | dvinfo_import
import | dvinfo_import_3 | index | usr_nefa_dev | dvinfo_import
Unfortunately the planner doesn't use them.
I didn't get any error while creating the indices.
Looking at the logs I suspect a problem with autovacuum?
-------------------------------------------------------------------
2009-05-13 03:14:04.149 CEST 4582 usr_nefa_dev enkaw1062.wob.vw.vwg
db_nefa_dev LOG: statement: CREATE INDEX dvinfo_import_1 ON
import.dvinfo_import
( s_part_number,
s_pda,
s_revision,
s_strl1_typ,
s_strl1_nr,
s_strl2_typ,
s_strl2_nr );
CREATE INDEX dvinfo_import_2 ON
import.dvinfo_import ( dv_id );
CREATE INDEX dvinfo_import_3 ON
import.dvinfo_import ( file_id );
CREATE INDEX kstand_import_1 ON
import.kstand_import ( dv_id );
CREATE INDEX kstand_import_2 ON
import.kstand_import (s_part_number);
2009-05-13 03:14:42.692 CEST 4958 LOG: autovacuum: processing database
"postgres"
2009-05-13 03:15:42.326 CEST 5104 LOG: autovacuum: processing database
"db_trac_dev"
2009-05-13 03:16:43.989 CEST 5275 LOG: autovacuum: processing database
"db_nefa_dev"
2009-05-13 03:16:45.582 CEST 4582 usr_nefa_dev enkaw1062.wob.vw.vwg
db_nefa_dev LOG: duration: 161433.451 ms statement: CREATE INDEX
dvinfo_import_1 ON import.dvin
fo_import
( s_part_number,
s_pda,
s_revision,
s_strl1_typ,
s_strl1_nr,
s_strl2_typ,
s_strl2_nr );
CREATE INDEX dvinfo_import_2 ON
import.dvinfo_import ( dv_id );
CREATE INDEX dvinfo_import_3 ON
import.dvinfo_import ( file_id );
CREATE INDEX kstand_import_1 ON
import.kstand_import ( dv_id );
CREATE INDEX kstand_import_2 ON
import.kstand_import (s_part_number);
-----------------------------------------------------------------------
Using Red Hat Enterprise Linux Client release 5.3 (Tikanga)
with:
postgresql-server-8.1.11.1.el5_1.1 (x86_64)
postgresql-libs-8.1.11.1.el5_1.1 (i386)
postgresql-libs-8.1.11.1.el5_1.1 (x86_64)
postgresql-8.1.11.1.el5_1.1 (x86_64)
Regards,
Lars