VERY slow foreign key check (due to missing batcalc.isnil() ?)

[monetdb-team]

Date: 2012-04-27 00:08:28 +0200
From: @drstmane
To: SQL devs <>
Version: 11.9.1 (Apr2012) [obsolete]
CC: freddy_priyatna, @njnes

Last updated: 2012-05-25 12:58:49 +0200

Comment 17169

Date: 2012-04-27 00:08:28 +0200
From: @drstmane

Created attachment 105
plan & explain of foreign key checks

With Apr2012, foreign key check on columns that are not declared NOT NULL are 100-200(!) times slower than with Dec2011.

The reason seems to be a MAL loop (iterator) over calc.isnil(), which might be due to a missing batcalc.isnil() (?)

See attached log for details.

Attached file: FK (application/octet-stream, 23121 bytes)
Description: plan & explain of foreign key checks

Comment 17170

Date: 2012-04-27 00:13:46 +0200
From: @drstmane

hm, there seem to be batcalc.isnil() after all:

?batcalc.isnil()
command batcalc.isnil(b:bat[:any_1,:timestamp]):bat[:any_1,:bit]
address MTIMEunaryISNIL;
comment Unary check for nil over the tail of the bat

command batcalc.isnil(b:bat[:any_1,:daytime]):bat[:any_1,:bit]
address MTIMEunaryISNIL;
comment Unary check for nil over the tail of the bat

command batcalc.isnil(b:bat[:any_1,:date]):bat[:any_1,:bit]
address MTIMEunaryISNIL;
comment Unary check for nil over the tail of the bat

command batcalc.isnil(b:bat[:any_1,:str]):bat[:any_1,:bit]
address CMDunaryISNIL;
comment Unary check for nil over the tail of the bat

command batcalc.isnil(b:bat[:any_1,:dbl]):bat[:any_1,:bit]
address CMDunaryISNIL;
comment Unary check for nil over the tail of the bat

command batcalc.isnil(b:bat[:any_1,:flt]):bat[:any_1,:bit]
address CMDunaryISNIL;
comment Unary check for nil over the tail of the bat

command batcalc.isnil(b:bat[:any_1,:lng]):bat[:any_1,:bit]
address CMDunaryISNIL;
comment Unary check for nil over the tail of the bat

command batcalc.isnil(b:bat[:any_1,:wrd]):bat[:any_1,:bit]
address CMDunaryISNIL;
comment Unary check for nil over the tail of the bat

command batcalc.isnil(b:bat[:any_1,:int]):bat[:any_1,:bit]
address CMDunaryISNIL;
comment Unary check for nil over the tail of the bat

command batcalc.isnil(b:bat[:any_1,:sht]):bat[:any_1,:bit]
address CMDunaryISNIL;
comment Unary check for nil over the tail of the bat

command batcalc.isnil(b:bat[:any_1,:oid]):bat[:any_1,:bit]
address CMDunaryISNIL;
comment Unary check for nil over the tail of the bat

command batcalc.isnil(b:bat[:any_1,:bte]):bat[:any_1,:bit]
address CMDunaryISNIL;
comment Unary check for nil over the tail of the bat

command batcalc.isnil(b:bat[:any_1,:bit]):bat[:any_1,:bit]
address CMDunaryISNIL;
comment Unary check for nil over the tail of the bat

?

Comment 17171

Date: 2012-04-27 00:15:38 +0200
From: @drstmane

But calc.isnil() exists for more types (in case that matters):

?calc.isnil()
command calc.isnil(v:wkb):bit
address wkbIsnil;
comment Nil test for wkb value

command calc.isnil(v:inet):bit
address INET_isnil;
comment Nil test for inet value

command calc.isnil(v:timestamp):bit
address MTIMEtimestamp_isnil;
comment Nil test for timestamp value

command calc.isnil(v:daytime):bit
address MTIMEdaytime_isnil;
comment Nil test for daytime value

command calc.isnil(v:date):bit
address MTIMEdate_isnil;
comment Nil test for date value

command calc.isnil(v:sqlblob):bit
address BLOB_isnil;
comment Nil test for blob value

command calc.isnil(v:blob):bit
address BLOB_isnil;
comment Nil test for blob value

command calc.isnil(v:void):bit
address CALCisnil_void;
comment is a value nil?

command calc.isnil(v:BAT):bit
address CALCisnil_bat;
comment is a value nil?

command calc.isnil(v:str):bit
address CALCisnil_str;
comment is a value nil?

command calc.isnil(v:dbl):bit
address CALCisnil_dbl;
comment is a value nil?

command calc.isnil(v:flt):bit
address CALCisnil_flt;
comment is a value nil?

command calc.isnil(v:lng):bit
address CALCisnil_lng;
comment is a value nil?

command calc.isnil(v:wrd):bit
address CALCisnil_wrd;
comment is a value nil?

command calc.isnil(v:oid):bit
address CALCisnil_oid;
comment is a value nil?

command calc.isnil(v:int):bit
address CALCisnil_int;
comment is a value nil?

command calc.isnil(v:sht):bit
address CALCisnil_sht;
comment is a value nil?

command calc.isnil(v:bte):bit
address CALCisnil_bte;
comment is a value nil?

command calc.isnil(v:bit):bit
address CALCisnil_bit;
comment is a value nil?

Comment 17185

Date: 2012-04-27 14:19:30 +0200
From: @njnes

temporary solution checked in, ie added extra oid/void signatures. The proper solution (to be implemented on the default branch) is removing all virtual oid signatures using void.

Comment 17187

Date: 2012-04-27 14:21:53 +0200
From: @njnes

*** Bug #3048 has been marked as a duplicate of this bug. ***