From: | Daniel Crisan <d(dot)crisan(at)ibcp(dot)fr> |
---|---|
To: | PostgreSQL <pgsql-performance(at)postgresql(dot)org> |
Subject: | multi-column index |
Date: | 2005-03-16 16:08:59 |
Message-ID: | 42385A1B.9090305@ibcp.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2005-03-16 16:28:07 | Re: Performance problem on delete from for 10k rows. May |
Previous Message | Greg Stark | 2005-03-16 15:42:52 | Re: cpu_tuple_cost |