Thread: uh-oh
I think I screwed up. I was running something in the background to update a table based on the jobs output (once every 1-10 seconds) and while that was running I created an index on the same table. Now that index is not used according to explain plans. It does show up when I type '\di' But I can't DROP INDEX. I think I'm in some trouble but I don't know how much.
> I think I screwed up. > > I was running something in the background to update a table based on the jobs > output (once every 1-10 seconds) and while that was running I created an > index on the same table. > > Now that index is not used according to explain plans. > It does show up when I type '\di' > But I can't DROP INDEX. > > I think I'm in some trouble but I don't know how much. Have you vacuum analyzed that table? Maybe the statistics still think a table scan is the best option?
Philip Hallstrom wrote: >> I think I screwed up. >> >> I was running something in the background to update a table based on >> the jobs output (once every 1-10 seconds) and while that was running I >> created an index on the same table. >> >> Now that index is not used according to explain plans. >> It does show up when I type '\di' >> But I can't DROP INDEX. >> >> I think I'm in some trouble but I don't know how much. > > > Have you vacuum analyzed that table? Maybe the statistics still think a > table scan is the best option? > Is that a normal to run vacuum analyze on a table after building indexes? I can give it a try, but I'm asking for "care and feeding" reasons. I did run vacuum and analyze seperately with no affect. Given 2.6 million rows and a cost of >80,000 pages I would have anticipated a full table scan to be avoided. I'll get back to it later. I've had to learn how to dump/restore really quick because somewhere the indexes were built with some "illegal" names and I couldn't drop them. The names where "public.email_address" instead of "email_address" for a table in the public schema. pgaccess is not my friend anymore. I'm not sure I did the dump/restore correctly. The man pages instructions didn't match real life. pg_dump -d email -c -f email.out pg_restore -d email -f email.out give all kinds of errors last night. I'll have to make a little database and test it until I get them right.
Tom Allison <tallison@tacocat.net> writes: > I'll get back to it later. I've had to learn how to dump/restore really quick > because somewhere the indexes were built with some "illegal" names and I > couldn't drop them. The names where "public.email_address" instead of > "email_address" for a table in the public schema. pgaccess is not my friend > anymore. Think you need to learn the rules for double-quoted identifiers: http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS regards, tom lane
In the last exciting episode, tallison@tacocat.net (Tom Allison) wrote: > I think I screwed up. > > I was running something in the background to update a table based on > the jobs output (once every 1-10 seconds) and while that was running I > created an index on the same table. > > Now that index is not used according to explain plans. > It does show up when I type '\di' > But I can't DROP INDEX. > > I think I'm in some trouble but I don't know how much. That doesn't sound like something I'd expect to cause a problem. 1. Is it possible that the index isn't useful for the plans you have been considering? 2. Have you run ANALYZE on the table recently? If not, it may have poor statistics that are leading it to not consider using the index. -- "cbbrowne","@","cbbrowne.com" http://linuxdatabases.info/info/x.html "Another of Fortran's breakthroughs was the GOTO statement, which was a uniquely simple and understandable means of structuring and modularizing programs." -- Article on Backus' "Draper Prize"