Re: 7.4 - FK constraint performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: Rod Taylor <rbt(at)rbt(dot)ca>, ow <oneway_111(at)yahoo(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: 7.4 - FK constraint performance
Date: 2004-02-13 15:27:27
Message-ID: 2455.1076686047@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-sql

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephan Szabo 2004-02-13 15:49:59 Re: 7.4 - FK constraint performance
Previous Message Stephan Szabo 2004-02-13 15:02:51 Re: 7.4 - FK constraint performance

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2004-02-13 15:49:59 Re: 7.4 - FK constraint performance
Previous Message Jatinder Sangha 2004-02-13 15:24:48 Passing composite values to functions