Indexes performance

From: charavay <c(dot)charavay(at)ibcp(dot)fr>
To: pgsql-performance(at)postgresql(dot)org
Subject: Indexes performance
Date: 2004-10-18 08:50:21
Message-ID: 417383CD.3050802@ibcp.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Mark Wong 2004-10-18 15:28:10 Re: mmap (was First set of OSDL Shared Mem scalability results, some wierdness ...
Previous Message Gaetano Mendola 2004-10-18 00:22:08 Re: [HACKERS] Getting rid of AtEOXact Buffers (was Re: [Testperf-general]