From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Jan Kesten <jan.kesten@web.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: index not used again
Date: Fri, 31 Mar 2006 06:44:44 -0800 (PST)
Message-ID: <20060331064252.J61282@megazone.bigpanda.com> (raw)
In-Reply-To: <442CF375.2080903@web.de>
References: <442CF375.2080903@web.de>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sszabo@megazone.bigpanda.com, jan.kesten@web.de
Subject: Re: index not used again
In-Reply-To: <20060331064252.J61282@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox