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:
signature.asc
Description: OpenPGP digital signature
