Received: from leela.one-2-one.net (leela.one-2-one.net [217.115.142.65]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f956ZSn80179 for ; Fri, 5 Oct 2001 02:35:28 -0400 (EDT) (envelope-from twanger@bluetwanger.de) Received: from [192.168.0.14] (pD9501206.dip.t-dialin.net [217.80.18.6]) by leela.one-2-one.net (8.11.0/8.11.0) with ESMTP id f956cqi07163; Fri, 5 Oct 2001 08:38:52 +0200 Subject: Re: strange query execution times From: Markus Bertheau To: Tom Lane Cc: postgres sql list In-Reply-To: <9106.1002037789@sss.pgh.pa.us> References: <1001958754.2485.31.camel@entwicklung01.cenes.de> <9106.1002037789@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/0.14 (Preview Release) Date: 05 Oct 2001 08:48:57 +0200 Message-Id: <1002264548.15228.1.camel@entwicklung01.cenes.de> Mime-Version: 1.0 X-Archive-Number: 200110/76 X-Sequence-Number: 5031 On Tue, 2001-10-02 at 17:49, Tom Lane wrote: > Markus Bertheau writes: > > r_kunden_anbieter describes the relationship between customers and > > suppliers. there are five status, 0 to 4 in attribute beziehung. both > > queries return the same results. they select all customers which have a > > certain relationship (beziehung = 3) to a given supplier. > > personen has 484 rows, r_kunden_anbieter 327. > > How many rows actually satisfy "rka.a_id = 620 and rka.beziehung = 3"? > > The issue appears to be that the planner estimates one matching row > in the one case and two matching rows in the second. Given the estimate > of one row, it decides to go for the low-overhead nested loop plan. > I am guessing that there are really considerably more than two matching > rows, and so the nested loop plan loses badly compared to the mergejoin, > which takes longer to set up but is better able to cope with many rows. > > FWIW, 7.2 has better statistics and should be better able to pick the > right plan in this context ... 13 rows do. Is there a way to force 7.0.3 (or 7.1.3) to use the mergejoin with the straightforward condition? Markus Bertheau Cenes Data GmbH