Thread: FW: Duplicate indexes found in the postgres Database

FW: Duplicate indexes found in the postgres Database

From
"Chris White"
Date:
While interrogating the table information for our database, I notice that
some tables (i.e. gui_config, vm_mailbox, vm_message) have duplicate
entries. Does anybody know why this would occur and is it a problem?
Attached are appropriate outputs. Queries on the tables which are duplicated
in the entries appear to work normally.

We are running version 7.2.1

aesop=# \dt
         List of relations
      Name     | Type  |   Owner
--------------+-------+-----------
  gui_config   | table | aesop_gui
  gui_config   | table | aesop_gui
  gui_prefs    | table | aesop_gui
  vm_config    | table | voicemail
  vm_dbversion | table | voicemail
  vm_emailjob  | table | voicemail
  vm_greeting  | table | voicemail
  vm_mailbox   | table | voicemail
  vm_mailbox   | table | voicemail
  vm_mbxusers  | table | voicemail
  vm_message   | table | voicemail
  vm_message   | table | voicemail
  vm_usermsg   | table | voicemail
(13 rows)


aesop=# select * from pg_tables ;
    tablename    | tableowner | hasindexes | hasrules | hastriggers
----------------+------------+------------+----------+-------------
  pg_type        | postgres   | t          | f        | f
  pg_attribute   | postgres   | t          | f        | f
  pg_class       | postgres   | t          | f        | f
  pg_group       | postgres   | t          | f        | f
  pg_database    | postgres   | t          | f        | f
  pg_xactlock    | postgres   | f          | f        | f
  pg_inherits    | postgres   | t          | f        | f
  pg_index       | postgres   | t          | f        | f
  pg_operator    | postgres   | t          | f        | f
  pg_opclass     | postgres   | t          | f        | f
  pg_am          | postgres   | t          | f        | f
  pg_amop        | postgres   | t          | f        | f
  pg_amproc      | postgres   | t          | f        | f
  pg_language    | postgres   | t          | f        | f
  pg_largeobject | postgres   | t          | f        | f
  pg_aggregate   | postgres   | t          | f        | f
  pg_trigger     | postgres   | t          | f        | f
  pg_listener    | postgres   | f          | f        | f
  pg_shadow      | postgres   | t          | f        | t
  pg_attrdef     | postgres   | t          | f        | f
  pg_description | postgres   | t          | f        | f
  gui_config     | aesop_gui  | t          | f        | f
  pg_proc        | postgres   | t          | f        | f
  pg_relcheck    | postgres   | t          | f        | f
  gui_prefs      | aesop_gui  | t          | f        | f
  gui_config     | aesop_gui  | t          | f        | f
  pg_rewrite     | postgres   | t          | f        | f
  vm_config      | voicemail  | t          | f        | f
  vm_dbversion   | voicemail  | t          | f        | f
  pg_statistic   | postgres   | t          | f        | f
  vm_mbxusers    | voicemail  | t          | f        | t
  vm_greeting    | voicemail  | t          | f        | t
  vm_mailbox     | voicemail  | t          | f        | t
  vm_message     | voicemail  | t          | f        | t
  vm_mailbox     | voicemail  | t          | f        | t
  vm_usermsg     | voicemail  | t          | f        | t
  vm_message     | voicemail  | t          | f        | t
  vm_emailjob    | voicemail  | t          | f        | f
(38 rows)

aesop=# select * from pg_stat_user_tables
aesop-# \g
  relid |   relname    | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch
| n_tup_ins | n_tup_upd | n_tup_del
-------+--------------+----------+--------------+----------+---------------+
-----------+-----------+-----------
  16560 | gui_config   |        0 |            0 |        0 |             0
|         0 |         0 |         0
  16563 | gui_prefs    |        0 |            0
|          |               |         0 |         0 |         0
  16566 | vm_config    |        0 |            0 |        0 |             0
|         0 |         0 |         0
  16569 | vm_dbversion |        0 |            0 |        0 |             0
|         0 |         0 |         0
  16572 | vm_mailbox   |        0 |            0 |        0 |             0
|         0 |         0 |         0
  16575 | vm_mbxusers  |        0 |            0 |        0 |             0
|         0 |         0 |         0
  16584 | vm_greeting  |        0 |            0 |        0 |             0
|         0 |         0 |         0
  16593 | vm_message   |        0 |            0 |        0 |             0
|         0 |         0 |         0
  16602 | vm_usermsg   |        0 |            0 |        0 |             0
|         0 |         0 |         0
  16619 | vm_emailjob  |        0 |            0 |        0 |             0
|         0 |         0 |         0
(10 rows)

aesop=# select * from pg_stat_user_indexes ;
  relid | indexrelid |   relname    |   indexrelname    | idx_scan |
idx_tup_read | idx_tup_fetch
-------+------------+--------------+-------------------+----------+---------
-----+---------------
  16560 |      16562 | gui_config   | gui_config_pkey   |        0
|            0 |             0
  16560 |      16562 | gui_config   | gui_config_pkey   |        0
|            0 |             0
  16566 |      16568 | vm_config    | vm_config_pkey    |        0
|            0 |             0
  16569 |      16571 | vm_dbversion | vm_dbversion_pkey |        0
|            0 |             0
  16572 |      16574 | vm_mailbox   | vm_mailbox_pkey   |        0
|            0 |             0
  16572 |      16574 | vm_mailbox   | vm_mailbox_pkey   |        0
|            0 |             0
  16575 |      16577 | vm_mbxusers  | vm_mbxusers_pkey  |        0
|            0 |             0
  16584 |      16586 | vm_greeting  | vm_greeting_pkey  |        0
|            0 |             0
  16593 |      16595 | vm_message   | vm_message_pkey   |        0
|            0 |             0
  16593 |      16595 | vm_message   | vm_message_pkey   |        0
|            0 |             0
  16602 |      16604 | vm_usermsg   | vm_usermsg_pkey   |        0
|            0 |             0
  16619 |      16624 | vm_emailjob  | vm_emailjob_pkey  |        0
|            0 |             0
(12 rows)