pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedindex not used again
4+ messages / 2 participants
[nested] [flat]
* index not used again
@ 2006-03-31 09:16 Jan Kesten <jan.kesten@web.de>
2006-03-31 14:44 ` Re: index not used again Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jan Kesten @ 2006-03-31 09:16 UTC (permalink / raw)
To: pgsql-performance
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
Attachments:
signature.asc (application/pgp-signature, 274B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: index not used again
2006-03-31 09:16 index not used again Jan Kesten <jan.kesten@web.de>
@ 2006-03-31 14:44 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-04-02 16:20 ` Re: index not used again Jan Kesten <jan.kesten@web.de>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2006-03-31 14:44 UTC (permalink / raw)
To: Jan Kesten <jan.kesten@web.de>; +Cc: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: index not used again
2006-03-31 09:16 index not used again Jan Kesten <jan.kesten@web.de>
2006-03-31 14:44 ` Re: index not used again Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2006-04-02 16:20 ` Jan Kesten <jan.kesten@web.de>
2006-04-02 19:46 ` Re: index not used again Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jan Kesten @ 2006-04-02 16:20 UTC (permalink / raw)
To: pgsql-performance
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
Attachments:
signature.asc (application/pgp-signature, 274B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: index not used again
2006-03-31 09:16 index not used again Jan Kesten <jan.kesten@web.de>
2006-03-31 14:44 ` Re: index not used again Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-04-02 16:20 ` Re: index not used again Jan Kesten <jan.kesten@web.de>
@ 2006-04-02 19:46 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2006-04-02 19:46 UTC (permalink / raw)
To: Jan Kesten <jan.kesten@web.de>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-04-02 19:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-31 09:16 index not used again Jan Kesten <jan.kesten@web.de>
2006-03-31 14:44 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-04-02 16:20 ` Jan Kesten <jan.kesten@web.de>
2006-04-02 19:46 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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