multi-column index

Поиск
Список
Период
Сортировка
От Daniel Crisan
Тема multi-column index
Дата
Msg-id 42385A1B.9090305@ibcp.fr
обсуждение исходный текст
Ответы Re: multi-column index  (Josh Berkus <josh@agliodbs.com>)
Список pgsql-performance
Hello.

I have a problem concerning multi-column indexes.

I have a table containing some 250k lines.

Table "public.descriptionprodftdiclnk"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 idword      | integer | not null
 idqualifier | integer | not null
Indexes:
    "descriptionprodftdiclnk_pkey" primary key, btree (idword, idqualifier)
    "ix_descriptionprodftdiclnk_idqualif" btree (idqualifier)

When analyzing a simple query on the idword column the query planner
displays:

explain analyze select * from descriptionprodftdiclnk where idword=44;
                                                          QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on descriptionprodftdiclnk  (cost=0.00..4788.14 rows=44388
width=8) (actual time=87.582..168.041 rows=43792 loops=1)
   Filter: (idword = 44)
 Total runtime: 195.339 ms
(3 rows)

I don't understand why the query planner would not use the default
created multi-column index
on the primary key. According to the Postgres online documentation it
should. By setting the
"enable_seqscan" parameter to off, i can force the planner to use the index:

explain analyze select * from descriptionprodftdiclnk where idword=44;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using descriptionprodftdiclnk_pkey on
descriptionprodftdiclnk  (cost=0.00..36720.39 rows=44388 width=8)
(actual time=0.205..73.489 rows=43792 loops=1)
   Index Cond: (idword = 44)
 Total runtime: 100.564 ms
(3 rows)



On the other hand, by defining a new index on the idword column (and
"enable_seqscan" set to on),
the query uses the index:

create index ix_tempIndex on descriptionprodftdiclnk(idword);
CREATE INDEX
explain analyze select * from descriptionprodftdiclnk where idword=44;
                                                                   QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ix_tempindex on descriptionprodftdiclnk
(cost=0.00..916.24 rows=44388 width=8) (actual time=0.021..79.879
rows=43792 loops=1)
   Index Cond: (idword = 44)
 Total runtime: 107.081 ms
(3 rows)

Could someone provide an explanation for the planner's behaviour?

Thanks for your help,
Daniel


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

Предыдущее
От: Greg Stark
Дата:
Сообщение: Re: cpu_tuple_cost
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: Performance problem on delete from for 10k rows. May