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
"pkey1" primary key, btree ("index", sgaccn)
Foreign-key constraints:
"fk_sgaccn1" FOREIGN KEY (sgaccn) REFERENCES main_tbl(sgaccn) ON UPDATE

   * 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
"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;
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 :

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 ?


Celine Charavay

---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?


Reply via email to