Re: [GENERAL] 7.4Beta
От | Jan Wieck |
---|---|
Тема | Re: [GENERAL] 7.4Beta |
Дата | |
Msg-id | 3F3D14A2.6090407@Yahoo.com обсуждение исходный текст |
Ответ на | Re: [GENERAL] 7.4Beta (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: [GENERAL] 7.4Beta
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
Tom Lane wrote: > Stephan Szabo <sszabo@megazone.bigpanda.com> 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. # #================================================== JanWieck@Yahoo.com #
В списке pgsql-hackers по дате отправления: