Thread: Re: [GENERAL] 7.4Beta

Re: [GENERAL] 7.4Beta

From
Stephan Szabo
Date:
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.



Re: [GENERAL] 7.4Beta

From
Tom Lane
Date:
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


Re: [GENERAL] 7.4Beta

From
Stephan Szabo
Date:
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).




Re: [GENERAL] 7.4Beta

From
Jan Wieck
Date:
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 #





Re: [GENERAL] 7.4Beta

From
Tom Lane
Date:
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


Re: [GENERAL] 7.4Beta

From
Bruce Momjian
Date:
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
 


Re: [GENERAL] 7.4Beta

From
Jan Wieck
Date:
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 #