X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4C200D1D2F7 for ; Fri, 13 Feb 2004 15:27:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 20447-08 for ; Fri, 13 Feb 2004 11:27:28 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 3AA4AD1D298 for ; Fri, 13 Feb 2004 11:27:24 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i1DFRSJU002456; Fri, 13 Feb 2004 10:27:28 -0500 (EST) To: Stephan Szabo Cc: Rod Taylor , ow , pgsql-sql@postgresql.org Subject: Re: 7.4 - FK constraint performance In-reply-to: <20040213065530.M19832@megazone.bigpanda.com> References: <20040213010341.95282.qmail@web60801.mail.yahoo.com> <25808.1076634636@sss.pgh.pa.us> <1076640825.10896.12.camel@jester> <27443.1076646336@sss.pgh.pa.us> <20040213065530.M19832@megazone.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Fri, 13 Feb 2004 07:02:51 -0800" Date: Fri, 13 Feb 2004 10:27:27 -0500 Message-ID: <2455.1076686047@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/149 X-Sequence-Number: 16840 Stephan Szabo writes: > One thing is that IIRC we're going to ask for only one row when we do the > SPI_execp_current. However, unless I misremember, the behavior of for > update and limit means that saying limit 1 is potentially unsafe (if you > block on a row that goes away). Is there anyway for us to let the planner > know this? I was looking at that last night. It seems like we could add a LIMIT at least in some contexts. In the case at hand, we're just going to error out immediately if we find a matching row, and so there's no need for FOR UPDATE, is there? However, I'm not sure it would help the OP anyway. With the stats he had, the planner would still take a seqscan, because it's going to expect that it can find a match by probing the first ten or so rows of the first page. With anything close to the normal cost parameters, that's going to look more expensive than an index probe. Possibly if the table had a few more values it would work. But in general it would be a good idea if the planner knew that plan evaluation would stop after the first row. We could look at passing that info down out-of-band instead of using LIMIT. There's already support for this to allow EXISTS() subqueries to be planned properly; see the tuple_fraction stuff in planner.c. We just can't get at it via SPI ... regards, tom lane