Unused indexes - PostgreSQL 9.2

On Tue, May 10, 2016 at 4:40 PM, Lucas Possamai <drum.lucas@gmail.com>
wrote:

Hi all,

I ran a query to search for unused indexes, and get some free space in my
DB:

SELECT

--*,
relid::regclass AS table,
indexrelid::regclass AS index,
--pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS
index_size,
pg_relation_size(indexrelid::regclass) AS index_size,
idx_tup_read,
idx_tup_fetch,
idx_scan
FROM
pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE
idx_scan = 0
AND indisunique IS FALSE;

The query returns the columns:
idx_tup_read,
idx_tup_fetch,
idx_scan

*What I did was:*

1 - Run the query above
2 - select one index and drop it
3 - Found some slow queries... When I saw it, the query was using one of
the index I've dropped.
4 - Re-created the index
5 - Ran the query with explain analyze (The query was indeed hitting the
index)
6 - re-ran the first query above, and still.. the index wasn't being used
from those statistics
7 - ?

So, my problem is: the statistics are not running? What happened to the
statistics?

Do you guys know how can I update the stats?

My crystal ball is not working, you have a PostgreSQL version?

in postgresql.conf are track_activities and track_counts both on?

Did you ANALYZE the table after you re-added the index?

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

My crystal ball is not working, you have a PostgreSQL version?

Maybe you should have a look on the subject of this email...

in postgresql.conf are track_activities and track_counts both on?

yes

Did you ANALYZE the table after you re-added the index?

Yes

On 11 May 2016 at 09:06, Lucas Possamai <drum.lucas@gmail.com> wrote:

My crystal ball is not working, you have a PostgreSQL version?

Maybe you should have a look on the subject of this email...

in postgresql.conf are track_activities and track_counts both on?

yes

Did you ANALYZE the table after you re-added the index?

Yes

Also.. just to let you guys know:

Some time ago I changed the pg_stat_temp directory from
/var/lib/pgsq/whatever to /tmp

postgresql.conf:

stats_temp_directory = '/tmp/pg_stat_tmp'

I tested it and it's "working":

#su - postgres
#cd /tmp/pg_stat_tmp
#touch test.html

ls -la /tmp/pg_stat_tmp:

On Tue, May 10, 2016 at 5:06 PM, Lucas Possamai <drum.lucas@gmail.com>
wrote:

My crystal ball is not working, you have a PostgreSQL version?

Maybe you should have a look on the subject of this email...

in postgresql.conf are track_activities and track_counts both on?

yes

Did you ANALYZE the table after you re-added the index?

Yes

Maybe you should have a look on the subject of this email...

Sorry, I was too busy looking at the content.

Has the size / # rows changed recently? If the planner thinks it can load
all the rows faster, it will use a seqscan regardless if you have an index.

If that is the case, you can force index use by doing a

SET enable_seqscan = off

before executing the query.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Sorry, I was too busy looking at the content.

Has the size / # rows changed recently? If the planner thinks it can load
all the rows faster, it will use a seqscan regardless if you have an index.

If that is the case, you can force index use by doing a

SET enable_seqscan = off

before executing the query.

Hmm... ok... but the situation is:

1 - I dropped the index
2 - Found a very slow query
3 - The "WHERE" clause was using the index that I've just dropped
4 - I ran the query in my test environment (Same DB as prod) with explain
analyze to see if the query was indeed using the index I've dropped
5 - Yes, the query was using the index
6 - re-created the index

7 - The total time went from 2000ms to 200ms

So, I don't think the index was indeed not being used.
I believe the stats are not working, just don't know how to confirm that,
as I have nothing on my logs

On Tue, May 10, 2016 at 5:17 PM, Lucas Possamai <drum.lucas@gmail.com>
wrote:

Sorry, I was too busy looking at the content.

Has the size / # rows changed recently? If the planner thinks it can load
all the rows faster, it will use a seqscan regardless if you have an index.

If that is the case, you can force index use by doing a

SET enable_seqscan = off

before executing the query.

Hmm... ok... but the situation is:

1 - I dropped the index
2 - Found a very slow query
3 - The "WHERE" clause was using the index that I've just dropped
4 - I ran the query in my test environment (Same DB as prod) with explain
analyze to see if the query was indeed using the index I've dropped
5 - Yes, the query was using the index
6 - re-created the index

7 - The total time went from 2000ms to 200ms

So, I don't think the index was indeed not being used.
I believe the stats are not working, just don't know how to confirm that,
as I have nothing on my logs

Some time ago I changed the pg_stat_temp directory from

/var/lib/pgsq/whatever to /tmp
Have you checked the postgres log to see if there are any errors about it
not being able to write to the pg_stat_temp dir?

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Some time ago I changed the pg_stat_temp directory from

/var/lib/pgsq/whatever to /tmp
Have you checked the postgres log to see if there are any errors about it
not being able to write to the pg_stat_temp dir?

Yep.... no errors =\

On Tue, May 10, 2016 at 5:23 PM, Lucas Possamai <drum.lucas@gmail.com>
wrote:

Some time ago I changed the pg_stat_temp directory from

/var/lib/pgsq/whatever to /tmp
Have you checked the postgres log to see if there are any errors about it
not being able to write to the pg_stat_temp dir?

Yep.... no errors =\

And what happens if you run this query?

SELECT idstat.schemaname AS schema,
idstat.relname AS table_name,
indexrelname AS index_name,
idstat.idx_scan AS times_used,
pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) ||
'.' || quote_ident(idstat.relname))) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) ||
'.' || quote_ident(indexrelname))) AS index_size,
n_tup_upd + n_tup_ins + n_tup_del as num_writes,
indexdef AS definition
FROM pg_stat_user_indexes AS idstat
JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE indexrelname = ' {YOUR QUERY NAME } ';

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

And what happens if you run this query?

SELECT idstat.schemaname AS schema,
idstat.relname AS table_name,
indexrelname AS index_name,
idstat.idx_scan AS times_used,
pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) ||
'.' || quote_ident(idstat.relname))) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) ||
'.' || quote_ident(indexrelname))) AS index_size,
n_tup_upd + n_tup_ins + n_tup_del as num_writes,
indexdef AS definition
FROM pg_stat_user_indexes AS idstat
JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE indexrelname = ' {YOUR QUERY NAME } ';

Sorry.. not sure what I should put into the WHERE clause ..

But, taking off the WHERE it returns me 600 rows

My bad, WHERE indexrelname = ' {YOUR INDEX NAME } ';

If you put the name of your index, you should get back stats for it. What
are those stats?

On Tue, May 10, 2016 at 5:47 PM, Lucas Possamai <drum.lucas@gmail.com>
wrote:

And what happens if you run this query?

SELECT idstat.schemaname AS schema,
idstat.relname AS table_name,
indexrelname AS index_name,
idstat.idx_scan AS times_used,
pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) ||
'.' || quote_ident(idstat.relname))) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) ||
'.' || quote_ident(indexrelname))) AS index_size,
n_tup_upd + n_tup_ins + n_tup_del as num_writes,
indexdef AS definition
FROM pg_stat_user_indexes AS idstat
JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE indexrelname = ' {YOUR QUERY NAME } ';

Sorry.. not sure what I should put into the WHERE clause ..

But, taking off the WHERE it returns me 600 rows

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On 11 May 2016 at 09:50, Melvin Davidson <melvin6925@gmail.com> wrote:

My bad, WHERE indexrelname = ' {YOUR INDEX NAME } ';

Oh! ok...

public ja_feedlog ix_ja_feedlog_visitids 1 94 GB 1939 MB 0 CREATE INDEX
"ix_ja_feedlog_visitids" ON "ja_feedlog" USING "btree" ("visitid")

public ja_feedlog ix_ja_feedlog_visitids 1 94 GB 1939 MB 0 CREATE INDEX
"ix_ja_feedlog_visitids" ON "ja_feedlog" USING "btree" ("visitid")

Two rows for the same index.

Hello Melvin, how are you doing?

And what happens if you run this query?

SELECT idstat.schemaname AS schema,
idstat.relname AS table_name,
indexrelname AS index_name,
idstat.idx_scan AS times_used,
pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) ||
'.' || quote_ident(idstat.relname))) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) ||
'.' || quote_ident(indexrelname))) AS index_size,
n_tup_upd + n_tup_ins + n_tup_del as num_writes,
indexdef AS definition
FROM pg_stat_user_indexes AS idstat
JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE indexrelname = ' {YOUR QUERY NAME } ';

I've added some new indexes this week into my prod environment, and I used
your query to see if they're being used or not.

Query:

SELECT idstat.schemaname AS schema,
idstat.relname AS table_name,
indexrelname AS index_name,
idstat.idx_scan AS times_used,
pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) ||
'.' || quote_ident(idstat.relname))) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(idstat.schemaname) ||
'.' || quote_ident(indexrelname))) AS index_size,
n_tup_upd + n_tup_ins + n_tup_del as num_writes,
indexdef AS definition
FROM pg_stat_user_indexes AS idstat
JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE indexrelname = 'ix_ja_jobs_clientid_title_time_job';

Returns:

schema table_name index_name times_used table_size
index_size num_writes definition

------ ---------- ---------------------------------- ---------- ----------
---------- ----------
-------------------------------------------------------------------------------------------------------------------------------------------------------
public ja_jobs ix_ja_jobs_clientid_title_time_job 41536 3526 MB
484 MB 38266927 CREATE INDEX "ix_ja_jobs_clientid_title_time_job" ON
"ja_jobs" USING "btree" ("clientid", "lower"(("title")::"text")
"varchar_pattern_ops", "time_job")

That index has been added just 3 hours ago, and you can see that the
times_used goes over 41000.... How is that possible??

Don't think the query is right.

Can you please check ?

Thank you.

On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai <drum.lucas@gmail.com> wrote:

That index has been added just 3 hours ago, and you can see that the
times_used goes over 41000.... How is that possible??

Well, that is what it is there for, right , to be used? My ancient
laptop can use an index that many times in less than 20 seconds,
running flat out.

Don't think the query is right.

Can you please check ?

The query seems right to me.

Cheers,

Jeff

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 24 May 2016 at 12:18, Jeff Janes <jeff.janes@gmail.com> wrote:

On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai <drum.lucas@gmail.com>
wrote:

That index has been added just 3 hours ago, and you can see that the
times_used goes over 41000.... How is that possible??

Well, that is what it is there for, right , to be used? My ancient
laptop can use an index that many times in less than 20 seconds,
running flat out.

Yeah!
But I mean... over 70.000 times in 4 hours? that's a lot of usage! ahhahaha

Don't think the query is right.

Can you please check ?

The query seems right to me.

Cheers,

Jeff

I think the query is ok.. just wanna understand if that value is correct :O

If it is.. I'm happy with that. Just shows the work of finding and creating
the index worthed it.

On Mon, May 23, 2016 at 8:33 PM, Lucas Possamai <drum.lucas@gmail.com>
wrote:

On 24 May 2016 at 12:18, Jeff Janes <jeff.janes@gmail.com> wrote:

On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai <drum.lucas@gmail.com>
wrote:

That index has been added just 3 hours ago, and you can see that the
times_used goes over 41000.... How is that possible??

Well, that is what it is there for, right , to be used? My ancient
laptop can use an index that many times in less than 20 seconds,
running flat out.

Yeah!
But I mean... over 70.000 times in 4 hours? that's a lot of usage! ahhahaha

Don't think the query is right.

Can you please check ?

The query seems right to me.

Cheers,

Jeff

I think the query is ok.. just wanna understand if that value is correct :O

If it is.. I'm happy with that. Just shows the work of finding and
creating the index worthed it.

​I don't have the answer off hand but what is it counting? If it counts,
say, each lookup into the index during a nested loop evaluation the
difference in perception​ could be easily explained.

David J.

Here is the url which explains the columns in pg_stat_all_indexes view
http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ALL-INDEXES-VIEW

On Mon, May 23, 2016 at 8:42 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Mon, May 23, 2016 at 8:33 PM, Lucas Possamai <drum.lucas@gmail.com>
wrote:

On 24 May 2016 at 12:18, Jeff Janes <jeff.janes@gmail.com> wrote:

On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai <drum.lucas@gmail.com>
wrote:

That index has been added just 3 hours ago, and you can see that the
times_used goes over 41000.... How is that possible??

Well, that is what it is there for, right , to be used? My ancient
laptop can use an index that many times in less than 20 seconds,
running flat out.

Yeah!
But I mean... over 70.000 times in 4 hours? that's a lot of usage!
ahhahaha

Don't think the query is right.

Can you please check ?

The query seems right to me.

Cheers,

Jeff

I think the query is ok.. just wanna understand if that value is correct
:O

If it is.. I'm happy with that. Just shows the work of finding and
creating the index worthed it.

​I don't have the answer off hand but what is it counting? If it counts,
say, each lookup into the index during a nested loop evaluation the
difference in perception​ could be easily explained.

David J.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.