On Fri, 31 Mar 2006, Jan Kesten wrote: > > Hi folks! > > I have just a issue again with unused indexes. I have a database with a > couple of tables and I have to do an sync job with them. For marking > which row has to be transfered I added a new column token (integer, I > will need some more tokens in near future) to every table. > > Before determining wich rows to mark I first had a simple > > update <table> set token=0; > > Okay, this uses seq scan of course. For speeding things up, I created an > partial index on every table like this: > > create index <table>_idx_token on <table> using (token) where token=1; > > After that I run vacuum analyse to update statistics and changed my to: > > update <table> set token=0 where token=1; > > I think this should be able to use my index, and indeed on one table > this works quite fine: > > transfer=> explain analyse update ku set token=0 where token=1; > > QUERY PLAN > ------------------------------------------------------------------------ > Index Scan using ku_idx_token on ku (cost=0.00..1.01 rows=1 > width=1871) (actual time=0.169..0.169 rows=0 loops=1) > Index Cond: (token = 1) > Total runtime: 3.816 ms > (3 rows) > > But on most of the other tables a seq scan is still used: > > transfer=> explain analyse update fak6 set token=0 where token=1; > > QUERY PLAN > ------------------------------------------------------------------------ > Seq Scan on fak6 (cost=0.00..301618.71 rows=24217 width=1895) (actual > time=96987.417..127020.919 rows=24251 loops=1) > Filter: (token = 1) > Total runtime: 181828.281 ms > (3 rows) > > So I tried to force using an index with setting enable_seqscan to off, > here are the results: > > transfer=> set enable_seqscan to off; > SET > transfer=> explain analyse update fak6 set token=0 where token=1; > > QUERY PLAN > ------------------------------------------------------------------------ > Index Scan using fak6_idx_token on fak6 (cost=0.00..301697.93 > rows=24217 width=1895) (actual time=1271.273..1271.273 rows=0 loops=1) > Index Cond: (token = 1) > Total runtime: 1272.572 ms > (3 rows) Did you reset the table contents between these two (remember that explain analyze actually runs the query)? The second appears to be changing no rows from the output.
