Thread: queries stop using indexes
I have a table with 15 cols , there is an joint index on 5 of them there are ~15million records in the table, and it gets heavy use via update & insert i recently noticed that after a day of use, the index is no longer being respected in some/all queries and a seq scan on the table is used i ran analyze, no change. i ran vacuum analyze, no change. i dropped the index, and recreated it -- its now used again. this happened 3x already this week. can anyone offer a suggestion? // Jonathan Vanasco | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | FindMeOn.com - The cure for Multiple Web Personality Disorder | Web Identity Management and 3D Social Networking | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | RoadSound.com - Tools For Bands, Stuff For Fans | Collaborative Online Management And Syndication Tools | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
On lau, 2007-03-31 at 18:51 -0400, Jonathan Vanasco wrote: > I have a table with 15 cols , there is an joint index on 5 of them > > there are ~15million records in the table, and it gets heavy use via > update & insert > > i recently noticed that after a day of use, the index is no longer > being respected in some/all queries and a seq scan on the table is used > > i ran analyze, no change. > i ran vacuum analyze, no change. > > i dropped the index, and recreated it -- its now used again. if you analyze the table after recreating the index, does it then stop using it? possibly you need to increase statistic targets on some or all of the index columns. a EXPLAIN ANALYZE could help figuring this out. gnari
On Apr 2, 2007, at 7:40 AM, Ragnar wrote: > if you analyze the table after recreating the index, does it then stop > using it? > > possibly you need to increase statistic targets on some or all > of the index columns. > > a EXPLAIN ANALYZE could help figuring this out. no, analyzing the virgin index is fine. heavy use on the index just seems to convince the planner to not use it -- and no amount of analyze or vaccum analyze will change that. i'll post some verbose debugging on the queries and analyze the next time the index blows // Jonathan Vanasco | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | SyndiClick.com | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | FindMeOn.com - The cure for Multiple Web Personality Disorder | Web Identity Management and 3D Social Networking | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | RoadSound.com - Tools For Bands, Stuff For Fans | Collaborative Online Management And Syndication Tools | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -