Re: [GENERAL] Unused indexes - PostgreSQL 9.2
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.johns...@gmail.com> wrote: > On Mon, May 23, 2016 at 8:33 PM, Lucas Possamai > wrote: > >> >> >> On 24 May 2016 at 12:18, Jeff Janes wrote: >> >>> On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai >>> 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.
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
On Mon, May 23, 2016 at 8:33 PM, Lucas Possamai wrote: > > > On 24 May 2016 at 12:18, Jeff Janes wrote: > >> On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai >> 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.
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
On 24 May 2016 at 12:18, Jeff Janes wrote: > On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai > 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.
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
On Mon, May 23, 2016 at 4:23 PM, Lucas Possamai 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
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
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_jobsix_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.
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
On 11 May 2016 at 09:50, Melvin Davidson 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.
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
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 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.
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
> > > 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
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
On Tue, May 10, 2016 at 5:23 PM, Lucas Possamai 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.
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
> > >> > >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 =\
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
On Tue, May 10, 2016 at 5:17 PM, Lucas Possamai 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.
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
> > > 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
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
On Tue, May 10, 2016 at 5:06 PM, Lucas Possamai 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.
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
On 11 May 2016 at 09:06, Lucas Possamai 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: > -rw--- 1 postgres postgres 263110 May 10 21:12 pgstat.stat
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
> > >> > 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
Re: [GENERAL] Unused indexes - PostgreSQL 9.2
On Tue, May 10, 2016 at 4:40 PM, Lucas Possamai 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.
[GENERAL] Unused indexes - PostgreSQL 9.2
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?
Re: [GENERAL] Unused Indexes
Mike Mascari wrote: Tim McAuley wrote: Hi, I have a table which I have populated with over 5000 entries. There is a combined index placed on two of the columns (both bigint). I am trying a simple select (i.e. select id where col1 = 1 and col2 = 1) covering these two columns and it keeps using a seq scan. Is this correct? I would have thought that with this number of entries that an index scan should be used. You must cast the 1 to a bigint: SELECT id WHERE col1 = 1::bigint AND col1 = 2::bigint This should probably be listed under FAQ 4.8, but it isn't. That's it! I had actually just come across that before reading this email. It gets the explain back into shape anyway! Thanks! Tim ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Unused Indexes
You need to convert the int's to bigints. select id where col1 = 1::bigint and col2 = 1::bigint Regards, Ed -Original Message- From: Tim McAuley <[EMAIL PROTECTED]> Date: Wed, 30 Jul 2003 13:46:46 To:[EMAIL PROTECTED] Subject: [GENERAL] Unused Indexes Hi, I have a table which I have populated with over 5000 entries. There is a combined index placed on two of the columns (both bigint). I am trying a simple select (i.e. select id where col1 = 1 and col2 = 1) covering these two columns and it keeps using a seq scan. Is this correct? I would have thought that with this number of entries that an index scan should be used. I am testing this using postgresql 7.3.3 on windows 2000 using cygwin. Doing "set enable_seqscan to off" does not change the results of the explain operation. I also tried setting a single index on just one of the columns and running an appropriate search; it still uses a seq scan. At what stage will the planner normally start using an index scan? Any hints appreciated. Tim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Unused Indexes
Assuming you have done a 'VACUUM ANALYZE' on the table in question you are most likely running into a type coercion issue. So explicitly cast your constants to bigint and the index should start being considered. select id from where col2 = 1::bigint and col2 = 1::bigint Tim McAuley wrote: Hi, I have a table which I have populated with over 5000 entries. There is a combined index placed on two of the columns (both bigint). I am trying a simple select (i.e. select id where col1 = 1 and col2 = 1) covering these two columns and it keeps using a seq scan. Is this correct? I would have thought that with this number of entries that an index scan should be used. I am testing this using postgresql 7.3.3 on windows 2000 using cygwin. Doing "set enable_seqscan to off" does not change the results of the explain operation. I also tried setting a single index on just one of the columns and running an appropriate search; it still uses a seq scan. At what stage will the planner normally start using an index scan? Any hints appreciated. Tim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Unused Indexes
Mike Mascari wrote: > Tim McAuley wrote: > >>Hi, >> >>I have a table which I have populated with over 5000 entries. There is a >>combined index placed on two of the columns (both bigint). I am trying >>a simple select (i.e. select id where col1 = 1 and col2 = 1) covering >>these two columns and it keeps using a seq scan. Is this correct? I >>would have thought that with this number of entries that an index scan >>should be used. > > > You must cast the 1 to a bigint: > > SELECT id WHERE col1 = 1::bigint AND col1 = 2::bigint That should read: SELECT id WHERE col1 = 1::bigint AND col2 = 1::bigint. My dyslexia is kicking in... Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Unused Indexes
Tim McAuley wrote: > Hi, > > I have a table which I have populated with over 5000 entries. There is a > combined index placed on two of the columns (both bigint). I am trying > a simple select (i.e. select id where col1 = 1 and col2 = 1) covering > these two columns and it keeps using a seq scan. Is this correct? I > would have thought that with this number of entries that an index scan > should be used. You must cast the 1 to a bigint: SELECT id WHERE col1 = 1::bigint AND col1 = 2::bigint This should probably be listed under FAQ 4.8, but it isn't. Hope that helps, Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Unused Indexes
Hi, I have a table which I have populated with over 5000 entries. There is a combined index placed on two of the columns (both bigint). I am trying a simple select (i.e. select id where col1 = 1 and col2 = 1) covering these two columns and it keeps using a seq scan. Is this correct? I would have thought that with this number of entries that an index scan should be used. I am testing this using postgresql 7.3.3 on windows 2000 using cygwin. Doing "set enable_seqscan to off" does not change the results of the explain operation. I also tried setting a single index on just one of the columns and running an appropriate search; it still uses a seq scan. At what stage will the planner normally start using an index scan? Any hints appreciated. Tim ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster