sql function iterates instead of bulk processing

[monetdb-team]

Date: 2011-06-24 15:45:25 +0200
From: @bartscheers
To: SQL devs <>
Version: -- development
CC: @mlkersten

Last updated: 2011-07-29 10:52:44 +0200

Comment 15853

Date: 2011-06-24 15:45:25 +0200
From: @bartscheers

User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-GB; rv:1.9.2.17) Gecko/20110428 Fedora/3.6.17-1.fc14 Firefox/3.6.17
Build Identifier:

Function used in where clause iterates over column and slows down performance

Reproducible: Always

Steps to Reproduce:

1. create table tab (d double);
2. insert into tab values (1.0),(2.0),(3.0),(4.0),(5.0);
3. create function func(f real) returns real begin return log10(f); end;
4. explain select * from tab where func(d) > 1;
   +------------------------------------------------------------------------------+
   | mal |
   +==============================================================================+
   | function user.s2_1{autoCommit=true}():void; |
   | _2 := sql.mvc(); |
   | barrier _48 := language.dataflow(); |
   | _10:bat[:oid,:dbl] := sql.bind(_2,"sys","tab","d",0); |
   | _8:bat[:oid,:dbl] := sql.bind(_2,"sys","tab","d",2); |
   | _12 := algebra.kdifference(_10,_8); |
   | _13 := algebra.kunion(_12,_8); |
   | _5:bat[:oid,:dbl] := sql.bind(_2,"sys","tab","d",1); |
   | _14 := algebra.kunion(_13,_5); |
   | _15:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","tab",1); |
   | _16 := bat.reverse(_15); |
   | _17 := algebra.kdifference(_14,_16); |
   | _18 := batcalc.flt(_17); |
   | exit _48; |
   | _54 := bat.new(nil:oid,nil:flt); |
   | barrier (_57,_58,_59) := bat.newIterator(_18); |
   | _61 := user.func(_59); |
   | bat.insert(_54,_58,_61); |
   | redo (_57,_58,_59) := bat.hasMoreElements(_18); |
   | exit (_57,_58,_59); |
   | _18 := nil:BAT; |
   | _19:bat[:oid,:flt] := _54; |
   | barrier _51 := language.dataflow(); |
   | _22 := algebra.thetauselect(_19,1:flt,">"); |
   | _24 := algebra.markT(_22,0@0:oid); |
   | _25 := bat.reverse(_24); |
   | _26 := algebra.leftjoin(_25,_14); |
   | exit _51; |
   | _27 := sql.resultSet(1,1,_26); |
   | sql.rsColumn(_27,"sys.tab","d":str,"double",53,0,_26); |
   | _33 := io.stdout(); |
   | sql.exportResult(_33,_27); |
   | end s2_1; |
   +------------------------------------------------------------------------------+

Actual Results:

For larger table contents this gets too slow.

Comment 15854

Date: 2011-06-24 23:39:55 +0200
From: @mlkersten

The inline optimizer has been patched to compile simple arithmetic functions
into their BAT equivalent. Not all calc/mtime/mmath/str operations have their BAT
equivalent yet. It should remove the performance degradation caused by the iterator in many cases.

Comment 15857

Date: 2011-06-26 14:35:33 +0200
From: @mlkersten

Problem located. The system injects a sql.mvc() call in each SQL function, which was marked as potentially side-effect prone. This, in combination with looking also at the optimizer pipe to determine side-effects, was resolved in the opt_support.mx file.

Comment 15957

Date: 2011-07-17 15:27:48 +0200
From: @drstmane

Changeset 465740af8349 made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=465740af8349

Changeset description:

added test for bug #2826

Comment 16008

Date: 2011-07-29 10:52:44 +0200
From: @sjoerdmullender

The Apr2011-SP2 bugfix release is out.

Comment 17572

Date: 2012-07-25 15:47:33 +0200
From: @drstmane

Changeset 93446c850c0a made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=93446c850c0a

Changeset description:

func_iter_vs_bulk.Bug-2826: use sequential pipeline to ensure deterministic output
also on multi-core systems
This fixes bug #3132.

Comment 17630

Date: 2012-08-19 10:37:04 +0200
From: @drstmane

Changeset 69254a78c7aa made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=69254a78c7aa

Changeset description:

func_iter_vs_bulk.Bug-2826: updated stable output after rechent checkin(s)