X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E1BB4564CD for ; Wed, 16 Mar 2005 16:09:12 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31739-07 for ; Wed, 16 Mar 2005 16:09:04 +0000 (GMT) Received: from audi.ibcp.fr (audi.ibcp.fr [193.51.160.127]) by svr1.postgresql.org (Postfix) with ESMTP id 4A7A95514D for ; Wed, 16 Mar 2005 16:09:00 +0000 (GMT) Received: from [193.51.160.65] (gdeleage14.ibcp.fr [193.51.160.65]) by audi.ibcp.fr (Postfix) with ESMTP id 3E01C1AF286 for ; Wed, 16 Mar 2005 17:08:59 +0100 (CET) Message-ID: <42385A1B.9090305@ibcp.fr> Date: Wed, 16 Mar 2005 17:08:59 +0100 From: Daniel Crisan User-Agent: Mozilla Thunderbird 0.9 (X11/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: PostgreSQL Subject: multi-column index Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/326 X-Sequence-Number: 11031 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