From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Rod Taylor <rbt@rbt.ca>
Cc: ow <oneway_111@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: 7.4 - FK constraint performance
Date: Fri, 13 Feb 2004 10:27:27 -0500
Message-ID: <2455.1076686047@sss.pgh.pa.us> (raw)
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>
Stephan Szabo <sszabo@megazone.bigpanda.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, sszabo@megazone.bigpanda.com, rbt@rbt.ca, oneway_111@yahoo.com
Subject: Re: 7.4 - FK constraint performance
In-Reply-To: <2455.1076686047@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox