FW: Duplicate indexes found in the postgres Database

From: "Chris White" <cjwhite(at)cisco(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: FW: Duplicate indexes found in the postgres Database
Date: 2003-01-31 00:09:03
Message-ID: 066a01c2c8bc$f7593260$ff926b80@amer.cisco.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Browse pgsql-general by date

  From Date Subject
Next Message news.verizon.net 2003-01-31 00:20:39 Problem starting a session
Previous Message Arjen van der Meijden 2003-01-31 00:06:32 Weird query plans for my queries, causing terrible performance.