Thread: index not used again
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) transfer=> set enable_seqscan to on; SET 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=93903.379..93903.379 rows=0 loops=1) Filter: (token = 1) Total runtime: 93904.679 ms (3 rows) 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=223.721..223.721 rows=0 loops=1) Index Cond: (token = 1) Total runtime: 226.851 ms (3 rows) Now I'm a bit confused. The costs are nearly the same if using index or not - but runtime is about 70 times faster? Any idea how I can fix this issue - I thought a partial index would be the right way? Cheers, Jan
Attachment
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.
Stephan Szabo schrieb: > 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. I for myself did not, but as there are runnig automatic jobs periodically I can't tell, if one ran in the time while I was testing (but I guess not). At starting my tests all rows contained a zero for all tokens and there should be no ones at all. In my case rows with token set to one are really rare, about one of a thousand rows. I looked for fast way to find therse rows. I'll try again after a successful run - not resetting the token (not using analyse this time). Cheers, Jan
Attachment
On Sun, 2 Apr 2006, Jan Kesten wrote: > Stephan Szabo schrieb: > > > 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. > > I for myself did not, but as there are runnig automatic jobs > periodically I can't tell, if one ran in the time while I was testing > (but I guess not). At starting my tests all rows contained a zero for > all tokens and there should be no ones at all. The reason I asked is that the explain analyze output for the first query on fak6 (using a seqscan) seemed to imply 24k rows actually matched the condition and were updated, so comparisons to the later times may be skewed.