Re: how to find which tables required indexes in postgresql

On Wed, Apr 10, 2013 at 9:19 AM, JotaComm <jota(dot)comm(at)gmail(dot)com> wrote:

> Hello,
>
>
> 2013/4/10 Thomas Kellerer <spam_eater(at)gmx(dot)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).
>
>
I'll also give a shout-out for pgBadger. It parses your slow query logs
and creates a nice summary of queries that could use some attention.