Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to find which tables required indexes in postgresql

how to find which tables required indexes in postgresql

From
Zahid Quadri
Date:
hi,,

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








Re: how to find which tables required indexes in postgresql

From
Atri Sharma
Date:


Sent from my iPad

On 10-Apr-2013, at 17:01, Zahid Quadri <zahid.quadri@cloverinfotech.com> wrote:

hi,,

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







If the table under consideration has lots of data,and queries on it are very slow,you could consider adding an index to column(s) of that table.

Note,however,making excessive indexes may be harmful as well,so please be judicious while making them.

Regards,

Atri



Re: how to find which tables required indexes in postgresql

From
Thomas Kellerer
Date:
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.
>

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

Thomas

Re: how to find which tables required indexes in postgresql

From
JotaComm
Date:
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

Re: how to find which tables required indexes in postgresql

From
Chris Curvey
Date:



On Wed, Apr 10, 2013 at 9:19 AM, JotaComm <jota.comm@gmail.com> wrote:
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).


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.

Re: how to find which tables required indexes in postgresql

From
Michael Paquier
Date:



On Wed, Apr 10, 2013 at 10:19 PM, JotaComm <jota.comm@gmail.com> wrote:
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).
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

Re: how to find which tables required indexes in postgresql

From
Shaun Thomas
Date:
On 04/10/2013 06:31 AM, Zahid Quadri wrote:

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

If you have 8.4 or newer, you can look in pg_stat_statements for queries
that use a lot of time. Check the where clauses for columns or
transforms that can be indexed.

Of course, you have to install it first. Take a look here:

http://www.postgresql.org/docs/8.4/static/pgstatstatements.html

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email