Re: how to find which tables required indexes in postgresql

Поиск
Список
Период
Сортировка
От JotaComm
Тема Re: how to find which tables required indexes in postgresql
Дата
Msg-id CAA8OQ6_Sk7MFDNt5O4QpfYSovqhD8bbZikQmLv8L0Avaa=OkLQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: how to find which tables required indexes in postgresql  (Thomas Kellerer <spam_eater@gmx.net>)
Ответы Re: how to find which tables required indexes in postgresql
Re: how to find which tables required indexes in postgresql
Список pgsql-general
Дерево обсуждения
Hello,


2013/4/10 Thomas Kellerer <spam_eater@gmx.net>
Zahid Quadri, 10.04.2013 13:31:

hi,,

please suggest if there is any way which i can find which tables need indexes in postgresql.

You have some possibilities:

- the log file (slow queries)

- statistics with old information (see the ANALYZE command)

- statistics tables, for example: pg_stat_user_tables

You can run this SQL:

SELECT pg_stat_user_tables.schemaname,
pg_stat_user_tables.relname,
pg_stat_user_tables.seq_scan,
pg_stat_user_tables.seq_tup_read,
pg_stat_user_tables.idx_scan,
pg_stat_user_tables.idx_tup_fetch
FROM pg_stat_user_tables;

If you have a big value in seq_scan column compared to the idx_scan column (small value), this indicate that you probably need to create an index in some column, but you need to discover what column needs the index. (the log file is a good indication).







Tables don't need indexes. Queries do. You will need to show us the queries in question (e.g. those that are slow) in order to decide which index is helpful.

Thomas





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Regards

--
JotaComm
http://jotacomm.wordpress.com

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

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: How to convert US date format to European date format ?
Следующее
От: JotaComm
Дата:
Сообщение: Re: Characters