Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7LJ6eP40619 for ; Tue, 21 Aug 2001 15:06:41 -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 f7LJ6SY10091; Tue, 21 Aug 2001 15:06:28 -0400 (EDT) To: Joseph Shraibman cc: Stephan Szabo , pgsql-sql Subject: Re: exists In-reply-to: <3B82A39A.4060307@selectacast.net> References: <3B82A39A.4060307@selectacast.net> Comments: In-reply-to Joseph Shraibman message dated "Tue, 21 Aug 2001 14:08:26 -0400" Date: Tue, 21 Aug 2001 15:06:28 -0400 Message-ID: <10088.998420788@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200108/277 X-Sequence-Number: 4524 Joseph Shraibman writes: > Well the total cost should be at least as big as the sub-costs, no? Not if the sub-plan in question is for an EXISTS. The sub-plan cost is stated in terms of cost to retrieve all rows --- but the outer level EXISTS isn't going to retrieve all rows, it's going to stop as soon as it gets even one. So the cost estimate that propagates up is 3035.22/1363. BTW, this sort of consideration is why 7.0 and later state plan costs in terms of startup and total cost: if a plan has a nontrivial startup cost, just dividing total cost by number of tuples isn't a good way to estimate the costs of partial retrieval. Really the cost estimate is figured as startup_cost + (total_cost-startup_cost) * tuples_retrieved/total_tuples. This is important for EXISTS, LIMIT, and maybe a couple other things. Without this, we'd not be bright enough to choose fast-startup plans over least-total-cost plans in cases where fast-startup is what you want. regards, tom lane