On Wed, Apr 10, 2013 at 10:19 PM, 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). > There is also this tool online that can help you to determine what are the slow parts of a query plan : http://explain.depesz.com/ This is perhaps more simple than visualizing raw ANALYZE output, and it will help you to catch what are the tables needing indexing, or perhaps partial indexing. -- Michael
