Re: 7.4 - FK constraint performance

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)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