Thread: Re: [GENERAL] 7.4Beta
On Fri, 15 Aug 2003, Christopher Kings-Lynne wrote: > > > I can also attest to the horrendously long time it takes to restore the > ADD > > > FOREIGN KEY section... > > > > That really needs to be rewritten to do a single check over the table > > rather than running the constraint for every row. I keep meaning to get > > around to it and never actually do. :( I'm not sure that in practice > > you'll get a better plan at restore time depending on what the default > > statistics give you. > > Surely in the default case it would reduce to using the new hashed IN() > feature, so it'd be a lot faster? If we wrote the query using IN that'd be the hope (I've not played with it enough to guarantee that) However, on a simple test comparing 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) and select * from fk where key in (select key from pk) and key is not null on a pk table with 100k rows and fk table with 1m rows gives me a difference of about 2x on my machine. But that's with a single column int4 key, I haven't tried multi-column keys or larger key types.
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 ...)
regards, tom lane
On Fri, 15 Aug 2003, 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. Yeah, but it was pretty much the best I could do testing on the command line. And it was still a fair bit more expensive than using IN (my tests on various key types showed anywhere from 15% to 300% better speed on IN over exists for this).
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 #
Jan Wieck <JanWieck@Yahoo.com> writes:
> 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).
Hmm. This is probably more feasible now than it would have been a year
ago, because I did some cleanup work to ensure that executor state is
localized into a specific memory context. I'm not certain about the
amount of overhead either, but it's surely worth a try.
regards, tom lane
Is there a TODO here? --------------------------------------------------------------------------- Tom Lane wrote: > Jan Wieck <JanWieck@Yahoo.com> writes: > > 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). > > Hmm. This is probably more feasible now than it would have been a year > ago, because I did some cleanup work to ensure that executor state is > localized into a specific memory context. I'm not certain about the > amount of overhead either, but it's surely worth a try. > > regards, tom lane > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > Is there a TODO here? Maybe!? It's one of these premature things noone can tell by now. So the TODO would be "investigation" for now. Jan > > --------------------------------------------------------------------------- > > Tom Lane wrote: >> Jan Wieck <JanWieck@Yahoo.com> writes: >> > 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). >> >> Hmm. This is probably more feasible now than it would have been a year >> ago, because I did some cleanup work to ensure that executor state is >> localized into a specific memory context. I'm not certain about the >> amount of overhead either, but it's surely worth a try. >> >> regards, tom lane >> > -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #