Re: [MonetDB-users] Why does MonetDB take indexes not into account?

On Tue, Nov 27, 2007 at 11:53:09PM +0100, Markus Gritsch wrote:

On 27/11/2007, Niels Nes wrote:

...

...

On 27/11/2007, Niels Nes wrote:

...

On Mon, Nov 26, 2007 at 09:49:53AM +0100, Markus Gritsch wrote:

...

On Nov 26, 2007 9:42 AM, Niels Nes wrote:

...

I will look into this problem, but changing the optimizer is always

Looking forward to it :)

any change I could have access to the test data set your using? This would make it much easier to test the performance improvements.

No, they are somehow confidential, but I will try to produce some dummy dataset, which the same behavior.

On Tue, Nov 27, 2007 at 10:32:52PM +0100, Markus Gritsch wrote: thats fine, thanks

You can download a dataset from http://xile.org/le/data.zip

...

mclient.exe -lsql < DDL.sql mclient.exe -lsql < DATA2.sql > log.txt

SELECT * FROM entry WHERE entry.note LIKE ('%kawilokare%')

SELECT * FROM entry, act WHERE entry.act_id = act.id AND act.is_closed = 0 AND entry.note LIKE ('%kawilokare%')

Both queries take about 0.5 seconds on my Notebook using MonetDB.

I got the second query down too 0.125 seconds (on my desktop). It also had about 0.5 seconds before. This is currently tested only on the current. I'll have to backport it to the stable and make it less specific too your query. To shortly explain the problem. The optimizer nicely finds out that you have an foreign key. This foreign key comes with a join index. Unfortunately when having these indices we do the selects on the base columns, ie on the columns of entry and act independend of the join. And only after that we use the index to combine these results. The improved optimizer will first do the select on the primary key 'column', use the join-index, then the select on this reduced column. Niels

Markus

------------------------------------------------------------------------- SF.Net email is sponsored by: The Future of Linux Business White Paper from Novell. From the desktop to the data center, Linux is going mainstream. Let it simplify your IT future. http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4 _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl