Stephan Szabo <[EMAIL PROTECTED]> writes:select * from fk where not exists(select * from pk where pk.key=fk.key) and key is not null; (doing seq scan/subplan doing index scan - which is probably close to the current system)
Actually, even that would probably be noticeably better than the current system. I haven't profiled it (someone should) but I suspect that executor startup/shutdown time is a huge hit. Even though the trigger is caching a plan, it has to instantiate that plan for each referencing tuple --- and the executor is not designed for quick startup/shutdown.
(Of course, this would become less relevant if the triggers got rewritten to not go through SPI ...)
One of the reasons why we used SPI to generate the plans was the ease of
use. I'm not 100% sure, but I think the standard doesn't require the
referencing and referenced column(s) to be identical, only compatible.
So for example a text type foreign key can reference a varchar() and an int4 can reference int8. Not using SPI for that lookup does not reduce to a simple index- or seq-scan (depending on index availability on the foreign key attributes).
Even if the standard does require it, we did not for a couple releases and breaking that backward compatibility is IMHO not an option.
I'm thinking instead of a way to "cache" entire executors for this. Each SPI plan used during a transaction would need it's own executor, and I don't know offhand what type and how much resources an executor requires (I think it's only some memory that get's initialized and the VFD's opened). If I also remember correctly, the executor holds the pointer to the parameters in the execstate and the actual values stay just in the caller provided array. All that can change for a given plan between SPI_execp() calls is this parameter array and the maxtuple arg. If an executor is comparably cheap resource wise, SPI_execp() should be able to just manipulate the parameter array in the execstate (with propagation into the scankeys I fear) and then let it do a rescan. At transaction commit time we'd need to close all executors then, like we do with cursors.
Does that all make any sense to you?
Jan
-- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
