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.