Re: [PERFORM] index not used again
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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] index not used again
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 signature.asc Description: OpenPGP digital signature
Re: [PERFORM] index not used again
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 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 _idx_token on using (token) where token=1; > > After that I run vacuum analyse to update statistics and changed my to: > > update 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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] 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 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 _idx_token on using (token) where token=1; After that I run vacuum analyse to update statistics and changed my to: update 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 signature.asc Description: OpenPGP digital signature