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