index not used again

Поиск
Список
Период
Сортировка
От Jan Kesten
Тема index not used again
Дата
Msg-id 442CF375.2080903@web.de
обсуждение исходный текст
Ответы Re: index not used again  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список 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


Вложения

В списке pgsql-performance по дате отправления:

Предыдущее
От: Guido Neitzer
Дата:
Сообщение: Re: Decide between Postgresql and Mysql (help of
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: index not used again