Re: 7.4 - FK constraint performance - Mailing list pgsql-sql

From Tom Lane
Subject Re: 7.4 - FK constraint performance
Date
Msg-id 2455.1076686047@sss.pgh.pa.us
Whole thread Raw
In response to Re: 7.4 - FK constraint performance  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Responses Re: 7.4 - FK constraint performance
List pgsql-sql
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


pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: column alias and group by/having/order
Next
From: Stephan Szabo
Date:
Subject: Re: 7.4 - FK constraint performance