Re: Duplicate Index Creation

Поиск
Список
Период
Сортировка
От Raghavendra
Тема Re: Duplicate Index Creation
Дата
Msg-id CA+h6Ahi_BXzgTb8NF0SOjKSE+X5nkCoRSczaMxbNTihp2nzjEA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Duplicate Index Creation  (Samuel Stearns <SStearns@internode.com.au>)
Ответы Re: Duplicate Index Creation  (Samuel Stearns <SStearns@internode.com.au>)
Список pgsql-admin
On Wed, Jul 4, 2012 at 7:09 AM, Samuel Stearns <SStearns@internode.com.au> wrote:

Ok, that returns only the 1 row:

 

SELECT idstat.indexrelid as indexrelid,

       idstat.schemaname AS schema_name,

       idstat.relname AS table_name,

       idstat.indexrelname AS index_name,

       idstat.idx_scan AS times_used,

       idstat.idx_scan AS times_used,

       pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size,

       pg_relation_size(indexrelid) AS index_size,

       n_tup_upd + n_tup_ins + n_tup_del as num_writes

FROM pg_stat_user_indexes AS idstat

JOIN pg_indexes as pi ON indexrelname = indexname and idstat.schemaname =

pi.schemaname

JOIN pg_stat_user_tables AS tabstat ON idstat.relid = tabstat.relid

WHERE idstat.relname = 'input_transaction_snbs'

AND indexdef !~* 'unique'

ORDER BY index_size desc;

 

indexrelid | schema_name |       table_name       | index_name | times_used | times_used | table_size | index_size | num_writes

------------+-------------+------------------------+------------+------------+------------+------------+------------+------------

  727108742 | snbs        | input_transaction_snbs | i1         |         33 |         33 | 2941 MB    |  305160192 |   10381291

(1 row)

 


This is good.. My guess is correct, there is no duplicate indexes. 
 

Out of all the tables in the db why is it that input_transaction_snbs is the only one that returns duplicates from the original query?

 


In your original query, the First join is broken, which won't come out of uniqueness with only comparing on relname=relname, It should also need to use Schemaname=schemaname, and second join is with relid=relid (As Tom Said) its very unique. First join was broken and by adding schemaname its now correct.

Coming *WHY*. if you see the indexrelid's of both queries, they are different. 

schemaname |   relid   | indexrelid |        relname         |           indexrelname

------------+-----------+------------+------------------------+----------------------------------

snbs       | 535026046 |  616672654 | input_transaction_snbs | i1


And 


indexrelid | schema_name |       table_name       | index_name | times_used | times_used | table_size | index_size | num_writes

------------+-------------+------------------------+------------+------------+------------+------------+------------+------------

  727108742 | snbs        | input_transaction_snbs | i1         |         33 |         33 | 2941 MB    |  305160192 |   10381291



Am not sure, how often you do maintenance on database like VACUUM, REINDEX etc., because all these activities will keep update the pg_catalogs. Presently, in mind I can only think reindexing the system catalog would be right option "reinidexdb -s". 
Other's might have good options in fixing this, you should wait for another suggestion.

---
Regards,
Raghavendra
EnterpriseDB Corporation

В списке pgsql-admin по дате отправления:

Предыдущее
От: "Madhu.Lanka"
Дата:
Сообщение: webclient for postgresql
Следующее
От: Raghavendra
Дата:
Сообщение: Re: webclient for postgresql