pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Daniel Crisan <d.crisan@ibcp.fr>
To: PostgreSQL <pgsql-performance@postgresql.org>
Subject: multi-column index
Date: Wed, 16 Mar 2005 17:08:59 +0100
Message-ID: <42385A1B.9090305@ibcp.fr> (raw)

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




view thread (10+ messages)  latest in thread

Message-ID: <42385A1B.9090305@ibcp.fr>
Permalink:  ../42385A1B.9090305@ibcp.fr/
Also on:    postgresql.org/message-id/42385A1B.9090305@ibcp.fr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: d.crisan@ibcp.fr
  Subject: Re: multi-column index
  In-Reply-To: <42385A1B.9090305@ibcp.fr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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