pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: charavay <c.charavay@ibcp.fr>
To: pgsql-performance@postgresql.org
Subject: Indexes performance
Date: Mon, 18 Oct 2004 10:50:21 +0200
Message-ID: <417383CD.3050802@ibcp.fr> (raw)

Hello !
We have difficulties with the use of indexes. For example, we have two 
tables :

    * table lnk :  
     
Table "public.lnk"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 index  | integer               | not null
 sgaccn | character varying(12) | not null
Indexes:
    "pkey1" primary key, btree ("index", sgaccn)
Foreign-key constraints:
    "fk_sgaccn1" FOREIGN KEY (sgaccn) REFERENCES main_tbl(sgaccn) ON UPDATE
CASCADE ON DELETE CASCADE

    * table dic :

Table "public.dic"
 Column |         Type          |                             Modifiers
                     
--------+-----------------------+--------------------------------------------------------------------
 index  | integer               | not null default 
nextval('public.dic_index_seq'::text)
 word   | character varying(60) | not null
Indexes:
    "dic_pkey" primary key, btree ("index")
    "dic_word_idx" unique, btree (word)
    "dic_word_key" unique, btree (word)



The table lnk contains 33 000 000 tuples and table dic contains 303 000 
tuples.

When we try to execute a join between these two tables, the planner 
proposes to excute a hash-join plan :

explain select sgaccn from dic, lnk where dic.index=lnk.index;
                                    QUERY PLAN
   
-----------------------------------------------------------------------------------
 Hash Join  (cost=6793.29..1716853.80 rows=33743101 width=11)
   Hash Cond: ("outer"."index" = "inner"."index")
   ->  Seq Scan on lnk  (cost=0.00..535920.00 rows=33743100 width=15)
   ->  Hash  (cost=4994.83..4994.83 rows=303783 width=4)
         ->  Seq Scan dic  (cost=0.00..4994.83 rows=303783 width=4)
(5 rows)

So the planner decides to scan 33 000 000 of tuples and we would like to 
force it to scan the table dic (303 000 tuples) and to use
the index on the integer index to execute the join. So we have set the 
parameters enable_hashjoin and enable_mergejoin to off. So the planner 
proposes the following query :

                                            QUERY PLAN
                  
--------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..102642540.60 rows=33743101 width=11)
   ->  Seq Scan on refs_ra_lnk1  (cost=0.00..535920.00 rows=33743100 
width=15)
   ->  Index Scan using refs_ra_dic_new_pkey on refs_ra_dic_new  
(cost=0.00..3.01 rows=1 width=4)
         Index Cond: (refs_ra_dic_new."index" = "outer"."index")
(4 rows)

We were surprised of this response because the planner continues to 
propose us to scan the 33 000 000 of tuples instead of the smaller 
table. Is there any way to force it to scan the smaller table ?

Thanks

Celine Charavay




view thread (3+ messages)  latest in thread

Message-ID: <417383CD.3050802@ibcp.fr>
Permalink:  ../417383CD.3050802@ibcp.fr/
Also on:    postgresql.org/message-id/417383CD.3050802@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: c.charavay@ibcp.fr
  Subject: Re: Indexes performance
  In-Reply-To: <417383CD.3050802@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