On Wed, Apr 10, 2013 at 9:19 AM, JotaComm <jota.c...@gmail.com> wrote:
> Hello, > > > 2013/4/10 Thomas Kellerer <spam_ea...@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). > > 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.
