Skip site navigation (1) Skip section navigation (2)

Re: index not used again

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Jan Kesten <jan(dot)kesten(at)web(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: index not used again
Date: 2006-03-31 14:44:44
Message-ID: 20060331064252.J61282@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.


In response to

Responses

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2006-03-31 15:47:53
Subject: Re: simple join uses indexes, very slow
Previous:From: Jan KestenDate: 2006-03-31 09:16:37
Subject: index not used again

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group