On Tue, May 10, 2016 at 4:40 PM, Lucas Possamai <drum.lu...@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.
