Will,
Much thanks. Let's keep up the sharing with the community.
On Tue, May 26, 2015 at 11:32 AM, William Dunn wrote:
> The query I previously sent was table level. Here is an index level one:
> SELECT pg_stat_user_indexes.schemaname,
>pg_stat_user_indexes.relname,
>pg_stat_use
The query I previously sent was table level. Here is an index level one:
SELECT pg_stat_user_indexes.schemaname,
pg_stat_user_indexes.relname,
pg_stat_user_indexes.indexrelid,
pg_stat_user_indexes.indexrelname,
pg_stat_user_indexes.idx_scan,
pg_stat_user_tables.se
Melvin - thanks for sharing.
Here is the query I use which lists the percent of queries against the
table which use the index ordered by least used first.
The 'pg_relation_size(relid)>(5*8192)' is used to remove any tables that
would be so small the optimizer would just choose a table scan.
SELE
On 2015-05-25 12:25:01 -0400, Melvin Davidson wrote:
> I'm not sure why you are using "pg_stat_user_indexes".
Because you did. I didn't change that.
> My original query below
> uses "pg_stat_all_indexes" and the schema names are joined and it does work.
I'm not sure what you mean by "original",
I'm not sure why you are using "pg_stat_user_indexes". My original query
below uses "pg_stat_all_indexes" and the schema names are joined and it
does work.
SELECT n.nspname as schema,
i.relname as table,
i.indexrelname as index,
i.idx_scan,
i.idx_tup_read,
i.idx
On 2015-05-22 09:41:57 -0400, Melvin Davidson wrote:
> I'd like to share those queries with the community, as I know there must be
> others out there with the same problem.
>
> /* useless_indexes.sql */
> SELECT
> idstat.schemaname AS schema,
> idstat.relname AS table_name,
>
200 is a completely arbitrary value. At the time, I wanted to find indexes
that were sufficiently less used than most others in a highly queried
system. To find indexes that were never used, just change the value to 0.
On Fri, May 22, 2015 at 9:12 PM, Venkata Balaji N wrote:
> On Fri, May 22, 20
On Fri, May 22, 2015 at 11:41 PM, Melvin Davidson
wrote:
>
> Over the years I've wrote many scripts and queries to track the database
> status. Recently I've had to convince a client who thought it was a good
> idea to create indexes for every column on every table that it is really a
> bad idea.
Over the years I've wrote many scripts and queries to track the database
status. Recently I've had to convince a client who thought it was a good
idea to create indexes for every column on every table that it is really a
bad idea. To do so, I wrote useless_indexes2.sql, which shows every index
that
