Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 7C54A47597A for ; Tue, 30 Apr 2002 17:51:09 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 19E30475461 for ; Tue, 30 Apr 2002 17:51:08 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g3ULp8F02243; Tue, 30 Apr 2002 17:51:08 -0400 (EDT) To: Jean-Luc Lachance Cc: pgsql-sql@postgresql.org Subject: Re: performance on update table from a join In-reply-to: <3CCF0DFE.1B0E583D@nsd.ca> References: <20020430181519.5E5D9475985@postgresql.org> <21326.1020191577@sss.pgh.pa.us> <21413.1020192353@sss.pgh.pa.us> <21990.1020197073@sss.pgh.pa.us> <3CCEFFDC.516131CC@nsd.ca> <753.1020199796@sss.pgh.pa.us> <3CCF0DFE.1B0E583D@nsd.ca> Comments: In-reply-to Jean-Luc Lachance message dated "Tue, 30 Apr 2002 17:34:54 -0400" Date: Tue, 30 Apr 2002 17:51:08 -0400 Message-ID: <2240.1020203468@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200204/370 X-Sequence-Number: 7634 Jean-Luc Lachance writes: > Hash Join (cost=1943.04..136718.39 rows=100 width=660) > -> Seq Scan on c (cost=0.00..112409.04 rows=1156604 width=519) > -> Hash (cost=1939.63..1939.63 rows=1365 width=141) > -> Merge Join (cost=0.00..1939.63 rows=1365 width=141) > -> Index Scan using routes_str_mun on routes r > (cost=0.00..1053.46 rows=13928 width=77) > -> Index Scan using rs_stname on rs s (cost=0.00..704.11 > rows=10942 width=64) Okay, those numbers look more believable. Offhand this seems like a perfectly fine plan to me; computing the r/s join once and forming it into an in-memory hashtable seems better than probing the r and s tables again for each of 1M+ rows of c. If the planner is way off about the size of that join (ie, it's not 1365 rows but many more) then maybe this isn't a good plan --- but you haven't given us any info about that. > I have also the following indecies that are ot being used: > create index routes_ex_str_mun on routes( exchangeno, street, > municipality); > create index rs_ostr_omun on rs( oldstname, oldmuni); That routes index could be used for the mergejoin if you had a corresponding index on rs (ie, one on exchangeno,stname,municipality). Not sure that this would make any significant improvement though. Merging on street name is probably plenty good enough. My thought is that the plan is fine, and if you are having a performance problem with the update, it's more likely due to operations triggered by the update rather than anything the planner can alter. Do you have rules, triggers, foreign keys on the c table, foreign keys pointing to that table, a large number of indexes for that table? regards, tom lane