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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
