Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 96D97475CFF for ; Tue, 30 Apr 2002 18:03:56 -0400 (EDT) Received: from beamish.nsd.ca (unknown [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id 6878C475CFE for ; Tue, 30 Apr 2002 18:03:55 -0400 (EDT) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id SAA25451; Tue, 30 Apr 2002 18:03:56 -0400 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma025449; Tue, 30 Apr 02 18:03:35 -0400 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id SAA15542; Tue, 30 Apr 2002 18:03:35 -0400 Message-ID: <3CCF15B6.D3E145D9@nsd.ca> Date: Tue, 30 Apr 2002 18:07:50 -0400 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-31 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-sql@postgresql.org Subject: Re: performance on update table from a join 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> <2240.1020203468@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200204/371 X-Sequence-Number: 7635 Tom, There is no index on c right now, and there aren't any tiggers, rules or foreign index on any of the tables. "rs" has 10941 rows ans "routes" has 13928. JLL Tom Lane wrote: > > 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