Duplicate indexes found in the postgres Database - Mailing list pgsql-admin
| From | Chris White |
|---|---|
| Subject | Duplicate indexes found in the postgres Database |
| Date | |
| Msg-id | NCBBIJCJEKFBDCFKEEEIIEFOHJAA.cjwhite@cisco.com Whole thread Raw |
| Responses |
Re: Duplicate indexes found in the postgres Database
|
| List | pgsql-admin |
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)
pgsql-admin by date: