Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f92Fnvn90491 for ; Tue, 2 Oct 2001 11:49:57 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) 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 f92FnnI09109; Tue, 2 Oct 2001 11:49:49 -0400 (EDT) To: Markus Bertheau cc: postgres sql list Subject: Re: strange query execution times In-reply-to: <1001958754.2485.31.camel@entwicklung01.cenes.de> References: <1001958754.2485.31.camel@entwicklung01.cenes.de> Comments: In-reply-to Markus Bertheau message dated "01 Oct 2001 19:52:23 +0200" Date: Tue, 02 Oct 2001 11:49:49 -0400 Message-ID: <9106.1002037789@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200110/19 X-Sequence-Number: 4974 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 ... regards, tom lane