Re: Optimising Foreign Key checks

On Sun, Jun 02, 2013 at 10:45:21AM +0100, Simon Riggs wrote:
> For clarity the 4 problems are
> 1. SQL execution overhead
> 2. Memory usage
> 3. Memory scrolling
> 4. Locking overhead, specifically FPWs and WAL records from FK checks
> probably in that order or thereabouts.
>
> The above is why I went for a technique that avoided SQL execution
> entirely, as well as conserving memory by de-duplicating the values in
> a hash table as we go, which avoids all three problems. The fourth was
> solved by the more extreme approach to locking.

That nicely frames the benefits of your proposals. Makes sense.

> I think it might be worth considering joining the after trigger queue
> directly to the referenced table(s), something like this...
>
> CREATE OR REPLACE FUNCTION after_trigger_queue() RETURNS SETOF tid AS $$
> ...
> $$ LANGUAGE SQL;
>
> EXPLAIN
> SELECT 1 FROM ONLY "order"
> WHERE orderid IN
> (SELECT orderid FROM ONLY order_line WHERE ctid IN (SELECT
> after_trigger_queue FROM after_trigger_queue() ))
> FOR KEY SHARE;

Agreed.

> But we could optimise that even further if we had a "BlockScan", which
> would be a block-oriented version of the tid scan where we simply
> provide a bitmap of blocks needing to be scanned, just like the output
> of an BitmapIndexScan. The reason for mentioning that here is that
> parallel query will eventually need the ability to do a scan of a
> subset of blocks, as does tablesample. So I can see 3 callers of such
> a Scan type.

Interesting. I was going to say that could lock more keys than needed, but
perhaps you would afterward filter by xmin/cmin.

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com