Обсуждение: Indices MIA

Поиск
Список
Период
Сортировка

Indices MIA

От
Lars Hamann
Дата:
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

Re: Indices MIA

От
Tom Lane
Дата:
Lars Hamann <extern.lars.hamann@volkswagen.de> writes:
> I've a strange problem with missing indices.

Curious.  Is pg_class.relhasindex true for this table?
If not, try manually UPDATE'ing that pg_class row to make it true.

            regards, tom lane