[MonetDB-users] Performance issues with queries in SQL
I have tables with around 12 million rows and large primary keys. I find the performance for various queries to be rather strange. * Why is Q2 (see below) so much slower than Q1? It is actually faster for me to use Q1 and do the rest of the matching myself. * Why is Q3 so immensely much faster than Q1? I understand that the first part of the primary key is selective in Q3. Do I understand right if Q1 scans until "type" matches, then scans until "name" matches, then scans until "value" matches? No indexes are put on disk, right? Then the matching for Q3 should be scan until "value" matches, which shouldn't be more than at most three times faster, right (if three columns are read instead of one for Q1)? I do Q1 twice to look for cash effects, but none are seen... Could some columns from the table "value" be cached and kept, without giving room for those from "type_name_value"? * Q4 has the same performance as Q1, as expected. * Why isn't Q5 faster than Q6? 82% of the rows have type=3. Fewer should be scanned to find that "!!foo" doesn't exist than for "~~foo", as "!" has a low ASCII value, while "~" is high. (See COUNT queries at the bottom.) Klem fra Nils Q1: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' Q2: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' AND doc = 1 AND nenc = '1.1.1.1' AND pos = 4 Q3: SELECT * FROM value WHERE value = 'Jurgen Annevelink' AND type = 3 AND name = 'author' Q4: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = '__bar' Q5: SELECT * FROM type_name_value WHERE type = 3 AND name = '!!foo' AND value = '__bar' Q6: SELECT * FROM type_name_value WHERE type = 3 AND name = '~~foo' AND value = '__bar' Q7: SELECT * FROM type_name_value WHERE type = 3000 AND name = '__foo' AND value = '__bar' See runs below. Number of rows SELECT COUNT(type) FROM type_name_value % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 12165109 ] Query time: 0.066192 SELECT COUNT(type) FROM value % nilsgri.value # table_name % count_no_nil_type # name % int # type % 8 # length [ 12165109 ] Query time: 0.058111 The PRIMARY KEY of type_name_value is (type,name,value,doc,nenc,pos) The PRIMARY KEY of value is (value,doc,nenc,pos) Q1: Search 3 first, match 3 first. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 12, 8, 3, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.42.2.1", 1071, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 107, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 56, true ] Query time: 1.643438 Q1: Repeat to check for disk cache effects. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 12, 8, 3, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.42.2.1", 1071, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 107, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 56, true ] Query time: 1.615900 Q2: Search entire, match entire. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' AND doc = 1 AND nenc = '1.1.1.1' AND pos = 4 % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 7, 1, 1, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] Query time: 11.073827 Q3: Use table value instead. SELECT * FROM value WHERE value = 'Jurgen Annevelink' AND type = 3 AND name = 'author' % nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value # table_name % value, doc, nenc, pos, type, name, pathid, complete # name % clob, int, varchar, int, int, varchar, int, boolean # type % 17, 1, 12, 8, 1, 6, 3, 5 # length [ "Jurgen Annevelink", 1, "1.1.1.1", 4, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.42.2.1", 1071, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 3, "author", 107, true ] [ "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 3, "author", 56, true ] Query time: 0.000477 Q4: Search 3 first, match 2 first. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 1.617259 Q5: Search 3 first, match 1 first. '!' early in alphabet. SELECT * FROM type_name_value WHERE type = 3 AND name = '!!foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.530727 Q6: Search 3 first, match 1 first. '~' late in alphabet. SELECT * FROM type_name_value WHERE type = 3 AND name = '~~foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.534592 Q7: Search 3 first, match 0 first. SELECT * FROM type_name_value WHERE type = 3000 AND name = '__foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.000407 Number of rows with type and name mathcing. SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name = 'author' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 7 # length [ 2730959 ] Query time: 0.683863 Number of rows with type mathcing. SELECT COUNT(type) FROM type_name_value WHERE type = 3 % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 10020569 ] Query time: 0.032573 SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name < '!!foo' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 1 # length [ 0 ] Query time: 0.161186 SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name < '~~foo' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 10020569 ] Query time: 0.341732 Number of rows with type mathcing. real 0m18.372s user 0m0.005s sys 0m0.002s
On Wed, Apr 30, 2008 at 03:58:11PM +0200, Nils Grimsmo wrote:
I have tables with around 12 million rows and large primary keys. I find the performance for various queries to be rather strange.
* Why is Q2 (see below) so much slower than Q1? It is actually faster for me to use Q1 and do the rest of the matching myself.
* Why is Q3 so immensely much faster than Q1? I understand that the first part of the primary key is selective in Q3. Do I understand right if Q1 scans until "type" matches, then scans until "name" matches, then scans until "value" matches? No indexes are put on disk, right? Then the matching for Q3 should be scan until "value" matches, which shouldn't be more than at most three times faster, right (if three columns are read instead of one for Q1)? I do Q1 twice to look for cash effects, but none are seen... Could some columns from the table "value" be cached and kept, without giving room for those from "type_name_value"?
* Q4 has the same performance as Q1, as expected.
* Why isn't Q5 faster than Q6? 82% of the rows have type=3. Fewer should be scanned to find that "!!foo" doesn't exist than for "~~foo", as "!" has a low ASCII value, while "~" is high. (See COUNT queries at the bottom.)
The performance differences are related to the order in which the where conditions are applied. Please send us also the table create statement, then we can use 'explain' to see which order is taken. You could also run your queries prefixed with 'trace' to see where the time goes. Niels
Klem fra Nils
Q1: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' Q2: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' AND doc = 1 AND nenc = '1.1.1.1' AND pos = 4 Q3: SELECT * FROM value WHERE value = 'Jurgen Annevelink' AND type = 3 AND name = 'author' Q4: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = '__bar' Q5: SELECT * FROM type_name_value WHERE type = 3 AND name = '!!foo' AND value = '__bar' Q6: SELECT * FROM type_name_value WHERE type = 3 AND name = '~~foo' AND value = '__bar' Q7: SELECT * FROM type_name_value WHERE type = 3000 AND name = '__foo' AND value = '__bar'
See runs below.
Number of rows SELECT COUNT(type) FROM type_name_value % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 12165109 ] Query time: 0.066192 SELECT COUNT(type) FROM value % nilsgri.value # table_name % count_no_nil_type # name % int # type % 8 # length [ 12165109 ] Query time: 0.058111
The PRIMARY KEY of type_name_value is (type,name,value,doc,nenc,pos) The PRIMARY KEY of value is (value,doc,nenc,pos)
Q1: Search 3 first, match 3 first. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 12, 8, 3, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.42.2.1", 1071, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 107, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 56, true ] Query time: 1.643438
Q1: Repeat to check for disk cache effects. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 12, 8, 3, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.42.2.1", 1071, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 107, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 56, true ] Query time: 1.615900
Q2: Search entire, match entire. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' AND doc = 1 AND nenc = '1.1.1.1' AND pos = 4 % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 7, 1, 1, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] Query time: 11.073827
Q3: Use table value instead. SELECT * FROM value WHERE value = 'Jurgen Annevelink' AND type = 3 AND name = 'author' % nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value # table_name % value, doc, nenc, pos, type, name, pathid, complete # name % clob, int, varchar, int, int, varchar, int, boolean # type % 17, 1, 12, 8, 1, 6, 3, 5 # length [ "Jurgen Annevelink", 1, "1.1.1.1", 4, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.42.2.1", 1071, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 3, "author", 107, true ] [ "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 3, "author", 56, true ] Query time: 0.000477
Q4: Search 3 first, match 2 first. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 1.617259
Q5: Search 3 first, match 1 first. '!' early in alphabet. SELECT * FROM type_name_value WHERE type = 3 AND name = '!!foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.530727
Q6: Search 3 first, match 1 first. '~' late in alphabet. SELECT * FROM type_name_value WHERE type = 3 AND name = '~~foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.534592
Q7: Search 3 first, match 0 first. SELECT * FROM type_name_value WHERE type = 3000 AND name = '__foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.000407
Number of rows with type and name mathcing. SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name = 'author' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 7 # length [ 2730959 ] Query time: 0.683863
Number of rows with type mathcing. SELECT COUNT(type) FROM type_name_value WHERE type = 3 % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 10020569 ] Query time: 0.032573
SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name < '!!foo' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 1 # length [ 0 ] Query time: 0.161186
SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name < '~~foo' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 10020569 ] Query time: 0.341732
Number of rows with type mathcing.
real 0m18.372s user 0m0.005s sys 0m0.002s
------------------------------------------------------------------------- This SF.net email is sponsored by the 2008 JavaOne(SM) Conference Don't miss this year's exciting event. There's still time to save $100. Use priority code J8TL2D2. http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javao... _______________________________________________ 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
Niels Nes wrote:
On Wed, Apr 30, 2008 at 03:58:11PM +0200, Nils Grimsmo wrote:
I have tables with around 12 million rows and large primary keys. I find the performance for various queries to be rather strange.
* Why is Q2 (see below) so much slower than Q1? It is actually faster for me to use Q1 and do the rest of the matching myself.
* Why is Q3 so immensely much faster than Q1? I understand that the first part of the primary key is selective in Q3. Do I understand right if Q1 scans until "type" matches, then scans until "name" matches, then scans until "value" matches? No indexes are put on disk, right? Then the matching for Q3 should be scan until "value" matches, which shouldn't be more than at most three times faster, right (if three columns are read instead of one for Q1)? I do Q1 twice to look for cash effects, but none are seen... Could some columns from the table "value" be cached and kept, without giving room for those from "type_name_value"?
* Q4 has the same performance as Q1, as expected.
* Why isn't Q5 faster than Q6? 82% of the rows have type=3. Fewer should be scanned to find that "!!foo" doesn't exist than for "~~foo", as "!" has a low ASCII value, while "~" is high. (See COUNT queries at the bottom.)
The performance differences are related to the order in which the where conditions are applied. Please send us also the table create statement, then we can use 'explain' to see which order is taken. You could also run your queries prefixed with 'trace' to see where the time goes.
Thanks! The tables: CREATE TABLE type_name_value AS \ SELECT type,name,value,doc,nenc,pos,pathID,complete \ FROM base WHERE value IS NOT NULL \ ORDER BY type,name,value,doc,nenc,pos WITH DATA; ALTER TABLE type_name_value \ ADD PRIMARY KEY(type,name,value,doc,nenc,pos); CREATE TABLE value AS \ SELECT value,doc,nenc,pos,type,name,pathID,complete \ FROM base WHERE value IS NOT NULL \ ORDER BY value,doc,nenc,pos WITH DATA; ALTER TABLE value \ ADD PRIMARY KEY(value,doc,nenc,pos); The base table: CREATE TABLE base( \ doc INT NOT NULL, \ nenc VARCHAR(255) NOT NULL, \ pos INT NOT NULL, \ type INT NOT NULL, \ name VARCHAR(255) NOT NULL, \ pathID INT NOT NULL, \ complete BOOLEAN NOT NULL, \ value CLOB); See traces of Q1, Q2 and Q3 below. Let me know if you would also like to see the others. For Q1 the bulk of the time seems to be an algebra.uselect on "author", an algebra.semijoin, and an algebra uselect on "Jurgen Annevelink". The column value is referenced first, then name, then type. Should this be? For Q2 an algebra.select takes up almost half the time, while a nil statement takes up more than half. Clean-up of temp data? Trace for Q1: echo "TRACE SELECT * FROM type_name_value WHERE type=3 AND name='author' AND value='Jurgen Annevelink';" | mclient --language=sql --database=proto --user=nilsgri --passwd=nilsgri --time --pager=less [ 1 usec # mdb.setTimer(_2=true) ] [ 17 usec # _4:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="type_name_value", _7="value", _8=0) ] [ 2 usec # _9:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="type_name_value", _7="value", _10=1) ] [ 26 usec # _11 := algebra.kunion(_4=<tmp_26450>bat[:oid,:str]{12165109}, _9=<tmp_26466>bat[:oid,:str]{0}) ] [ 2 usec # _4:bat[:oid,:str] := nil; ] [ 0 usec # _9:bat[:oid,:str] := nil; ] [ 2 usec # _12:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="type_name_value", _7="value", _13=2) ] [ 7 usec # _14 := algebra.kdifference(_11=<tmp_25032>bat[:oid,:str]{12165109}, _12=<tmp_26165>bat[:oid,:str]{0}) ] [ 11 usec # _11 := nil; ] [ 3 usec # _15 := algebra.kunion(_14=<tmp_25203>bat[:oid,:str]{12165109}, _12=<tmp_26165>bat[:oid,:str]{0}) ] [ 2 usec # _14 := nil; ] [ 1 usec # _12:bat[:oid,:str] := nil; ] [ 2 usec # _16:bat[:oid,:oid] := sql.bind_dbat(_5="nilsgri", _6="type_name_value", _10=1) ] [ 3 usec # _17 := bat.reverse(_16=<tmp_26430>bat[:oid,:oid]{0}) ] [ 1 usec # _16:bat[:oid,:oid] := nil; ] [ 4 usec # _18 := algebra.kdifference(_15=<tmp_25032>bat[:oid,:str]{12165109}, _17=<~tmp_26430>bat[:oid,:oid]{0}) ] [ 2 usec # _19:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="type_name_value", _20="name", _8=0) ] [ 1 usec # _21:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="type_name_value", _20="name", _10=1) ] [ 5 usec # _22 := algebra.kunion(_19=<tmp_26475>bat[:oid,:str]{12165109}, _21=<tmp_26460>bat[:oid,:str]{0}) ] [ 1 usec # _19:bat[:oid,:str] := nil; ] [ 1 usec # _21:bat[:oid,:str] := nil; ] [ 2 usec # _23:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="type_name_value", _20="name", _13=2) ] [ 4 usec # _24 := algebra.kdifference(_22=<tmp_26634>bat[:oid,:str]{12165109}, _23=<tmp_26461>bat[:oid,:str]{0}) ] [ 2 usec # _22 := nil; ] [ 3 usec # _25 := algebra.kunion(_24=<tmp_26627>bat[:oid,:str]{12165109}, _23=<tmp_26461>bat[:oid,:str]{0}) ] [ 2 usec # _24 := nil; ] [ 1 usec # _23:bat[:oid,:str] := nil; ] [ 4 usec # _26 := algebra.kdifference(_25=<tmp_26634>bat[:oid,:str]{12165109}, _17=<~tmp_26430>bat[:oid,:oid]{0}) ] [ 1 usec # _17 := nil; ] [ 2 usec # _27:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="type_name_value", _28="type", _8=0) ] [ 8380 usec # _29 := algebra.uselect(_27=<tmp_26500>bat[:oid,:int]{12165109}, A0=3) ] [ 12 usec # _30:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="type_name_value", _28="type", _10=1) ] [ 12 usec # _31 := algebra.uselect(_30=<tmp_26427>bat[:oid,:int]{0}, A0=3) ] [ 7 usec # _32 := algebra.kunion(_29=<tmp_25117>bat[:oid,:oid]{10020569}, _31=<tmp_26631>bat[:oid,:oid]{0}) ] [ 4 usec # _29 := nil; ] [ 2 usec # _31 := nil; ] [ 2 usec # _33:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="type_name_value", _28="type", _13=2) ] [ 5 usec # _34 := algebra.kdifference(_32=<tmp_21205>bat[:oid,:oid]{10020569}, _33=<tmp_26456>bat[:oid,:int]{0}) ] [ 1 usec # _32 := nil; ] [ 12 usec # _35 := algebra.uselect(_33=<tmp_26456>bat[:oid,:int]{0}, A0=3) ] [ 3 usec # _36 := algebra.kunion(_34=<tmp_26631>bat[:oid,:oid]{10020569}, _35=<tmp_26370>bat[:oid,:oid]{0}) ] [ 2 usec # _34 := nil; ] [ 2 usec # _35 := nil; ] [ 3 usec # _37:bat[:oid,:oid] := sql.bind_dbat(_5="nilsgri", _6="type_name_value", _10=1) ] [ 4 usec # _38 := bat.reverse(_37=<tmp_26430>bat[:oid,:oid]{0}) ] [ 1 usec # _37:bat[:oid,:oid] := nil; ] [ 2 usec # _39 := algebra.kdifference(_36=<tmp_25117>bat[:oid,:oid]{10020569}, _38=<~tmp_26430>bat[:oid,:oid]{0}) ] [ 1 usec # _36 := nil; ] [ 1 usec # _38 := nil; ] [ 8 usec # _40 := algebra.semijoin(_26=<tmp_26627>bat[:oid,:str]{12165109}, _39=<tmp_26631>bat[:oid,:oid]{10020569}) ] [ 2 usec # _26 := nil; ] [ 1 usec # _39 := nil; ] [617213 usec # _41 := algebra.uselect(_40=<~tmp_25117>bat[:oid,:str]{10020569}, A1="author") ] [ 21167 usec # _40 := nil; ] [622211 usec # _42 := algebra.semijoin(_18=<tmp_25203>bat[:oid,:str]{12165109}, _41=<tmp_26631>bat[:oid,:oid]{2730959}) ] [ 18 usec # _18 := nil; ] [ 5 usec # _41 := nil; ] [343252 usec # _43 := algebra.uselect(_42=<tmp_25117>bat[:oid,:str]{2730959}, A2="Jurgen Annevelink") ] [ 12167 usec # _42 := nil; ] [ 27 usec # _45 := algebra.markT(_43=<tmp_26370>bat[:oid,:oid]{8}, _44=0@0) ] [ 3 usec # _43 := nil; ] [ 3 usec # _46 := bat.reverse(_45=<tmp_25117>bat[:oid,:oid]{8}) ] [ 1 usec # _45 := nil; ] [ 15 usec # _47 := algebra.kunion(_27=<tmp_26500>bat[:oid,:int]{12165109}, _30=<tmp_26427>bat[:oid,:int]{0}) ] [ 1 usec # _27:bat[:oid,:int] := nil; ] [ 1 usec # _30:bat[:oid,:int] := nil; ] [ 24 usec # _48 := algebra.kdifference(_47=<tmp_26631>bat[:oid,:int]{12165109}, _33=<tmp_26456>bat[:oid,:int]{0}) ] [ 6 usec # _47 := nil; ] [ 4 usec # _49 := algebra.kunion(_48=<tmp_25203>bat[:oid,:int]{12165109}, _33=<tmp_26456>bat[:oid,:int]{0}) ] [ 2 usec # _48 := nil; ] [ 1 usec # _33:bat[:oid,:int] := nil; ] [ 16 usec # _50 := algebra.join(_46=<~tmp_25117>bat[:oid,:oid]{8}, _49=<tmp_26631>bat[:oid,:int]{12165109}) ] [ 2 usec # _49 := nil; ] [ 17 usec # _51 := algebra.join(_46=<~tmp_25117>bat[:oid,:oid]{8}, _25=<tmp_26634>bat[:oid,:str]{12165109}) ] [ 3 usec # _25 := nil; ] [ 13 usec # _52 := algebra.join(_46=<~tmp_25117>bat[:oid,:oid]{8}, _15=<tmp_25032>bat[:oid,:str]{12165109}) ] [ 3 usec # _15 := nil; ] [ 13 usec # _53:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="type_name_value", _54="doc", _8=0) ] [ 2 usec # _55:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="type_name_value", _54="doc", _10=1) ] [ 4 usec # _56 := algebra.kunion(_53=<tmp_26424>bat[:oid,:int]{12165109}, _55=<tmp_24572>bat[:oid,:int]{0}) ] [ 1 usec # _53:bat[:oid,:int] := nil; ] [ 1 usec # _55:bat[:oid,:int] := nil; ] [ 2 usec # _57:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="type_name_value", _54="doc", _13=2) ] [ 4 usec # _58 := algebra.kdifference(_56=<tmp_25032>bat[:oid,:int]{12165109}, _57=<tmp_25072>bat[:oid,:int]{0}) ] [ 2 usec # _56 := nil; ] [ 4 usec # _59 := algebra.kunion(_58=<tmp_26634>bat[:oid,:int]{12165109}, _57=<tmp_25072>bat[:oid,:int]{0}) ] [ 2 usec # _58 := nil; ] [ 1 usec # _57:bat[:oid,:int] := nil; ] [ 4 usec # _60 := algebra.join(_46=<~tmp_25117>bat[:oid,:oid]{8}, _59=<tmp_25032>bat[:oid,:int]{12165109}) ] [ 2 usec # _59 := nil; ] [ 2 usec # _61:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="type_name_value", _62="nenc", _8=0) ] [ 2 usec # _63:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="type_name_value", _62="nenc", _10=1) ] [ 4 usec # _64 := algebra.kunion(_61=<tmp_26463>bat[:oid,:str]{12165109}, _63=<tmp_25712>bat[:oid,:str]{0}) ] [ 1 usec # _61:bat[:oid,:str] := nil; ] [ 1 usec # _63:bat[:oid,:str] := nil; ] [ 2 usec # _65:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="type_name_value", _62="nenc", _13=2) ] [ 3 usec # _66 := algebra.kdifference(_64=<tmp_25032>bat[:oid,:str]{12165109}, _65=<tmp_26223>bat[:oid,:str]{0}) ] [ 2 usec # _64 := nil; ] [ 3 usec # _67 := algebra.kunion(_66=<tmp_26634>bat[:oid,:str]{12165109}, _65=<tmp_26223>bat[:oid,:str]{0}) ] [ 2 usec # _66 := nil; ] [ 0 usec # _65:bat[:oid,:str] := nil; ] [ 12 usec # _68 := algebra.join(_46=<~tmp_25117>bat[:oid,:oid]{8}, _67=<tmp_25032>bat[:oid,:str]{12165109}) ] [ 2 usec # _67 := nil; ] [ 3 usec # _69:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="type_name_value", _70="pos", _8=0) ] [ 2 usec # _71:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="type_name_value", _70="pos", _10=1) ] [ 4 usec # _72 := algebra.kunion(_69=<tmp_26222>bat[:oid,:int]{12165109}, _71=<tmp_26274>bat[:oid,:int]{0}) ] [ 1 usec # _69:bat[:oid,:int] := nil; ] [ 1 usec # _71:bat[:oid,:int] := nil; ] [ 3 usec # _73:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="type_name_value", _70="pos", _13=2) ] [ 5 usec # _74 := algebra.kdifference(_72=<tmp_25032>bat[:oid,:int]{12165109}, _73=<tmp_26355>bat[:oid,:int]{0}) ] [ 3 usec # _72 := nil; ] [ 3 usec # _75 := algebra.kunion(_74=<tmp_26634>bat[:oid,:int]{12165109}, _73=<tmp_26355>bat[:oid,:int]{0}) ] [ 2 usec # _74 := nil; ] [ 1 usec # _73:bat[:oid,:int] := nil; ] [ 5 usec # _76 := algebra.join(_46=<~tmp_25117>bat[:oid,:oid]{8}, _75=<tmp_25032>bat[:oid,:int]{12165109}) ] [ 2 usec # _75 := nil; ] [ 4 usec # _77:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="type_name_value", _78="pathid", _8=0) ] [ 1 usec # _79:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="type_name_value", _78="pathid", _10=1) ] [ 4 usec # _80 := algebra.kunion(_77=<tmp_11660>bat[:oid,:int]{12165109}, _79=<tmp_26374>bat[:oid,:int]{0}) ] [ 1 usec # _77:bat[:oid,:int] := nil; ] [ 0 usec # _79:bat[:oid,:int] := nil; ] [ 2 usec # _81:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="type_name_value", _78="pathid", _13=2) ] [ 3 usec # _82 := algebra.kdifference(_80=<tmp_25032>bat[:oid,:int]{12165109}, _81=<tmp_26527>bat[:oid,:int]{0}) ] [ 2 usec # _80 := nil; ] [ 4 usec # _83 := algebra.kunion(_82=<tmp_26634>bat[:oid,:int]{12165109}, _81=<tmp_26527>bat[:oid,:int]{0}) ] [ 2 usec # _82 := nil; ] [ 0 usec # _81:bat[:oid,:int] := nil; ] [ 4 usec # _84 := algebra.join(_46=<~tmp_25117>bat[:oid,:oid]{8}, _83=<tmp_25032>bat[:oid,:int]{12165109}) ] [ 2 usec # _83 := nil; ] [ 2 usec # _85:bat[:oid,:bit] := sql.bind(_5="nilsgri", _6="type_name_value", _86="complete", _8=0) ] [ 2 usec # _87:bat[:oid,:bit] := sql.bind(_5="nilsgri", _6="type_name_value", _86="complete", _10=1) ] [ 5 usec # _88 := algebra.kunion(_85=<tmp_26473>bat[:oid,:bit]{12165109}, _87=<tmp_24251>bat[:oid,:bit]{0}) ] [ 1 usec # _85:bat[:oid,:bit] := nil; ] [ 1 usec # _87:bat[:oid,:bit] := nil; ] [ 2 usec # _89:bat[:oid,:bit] := sql.bind(_5="nilsgri", _6="type_name_value", _86="complete", _13=2) ] [ 3 usec # _90 := algebra.kdifference(_88=<tmp_25032>bat[:oid,:bit]{12165109}, _89=<tmp_14322>bat[:oid,:bit]{0}) ] [ 2 usec # _88 := nil; ] [ 4 usec # _91 := algebra.kunion(_90=<tmp_26634>bat[:oid,:bit]{12165109}, _89=<tmp_14322>bat[:oid,:bit]{0}) ] [ 2 usec # _90 := nil; ] [ 1 usec # _89:bat[:oid,:bit] := nil; ] [ 5 usec # _92 := algebra.join(_46=<~tmp_25117>bat[:oid,:oid]{8}, _91=<tmp_25032>bat[:oid,:bit]{12165109}) ] [ 4 usec # _46 := nil; ] [ 2 usec # _91 := nil; ] [ 4 usec # _93 := sql.resultSet(_94=8, _10=1, _50=<tmp_26605>bat[:oid,:int]{8}) ] [ 6 usec # sql.rsColumn(_93=0, _96="nilsgri.type_name_value", _28="type", _97="int", _98=32, _8=0, _50=<tmp_26605>bat[:oid,:int]{8}) ] [ 1 usec # _50 := nil; ] [ 2 usec # sql.rsColumn(_93=0, _96="nilsgri.type_name_value", _20="name", _100="varchar", _101=255, _8=0, _51=<tmp_13267>bat[:oid,:str]{8}) ] [ 1 usec # _51 := nil; ] [ 2 usec # sql.rsColumn(_93=0, _96="nilsgri.type_name_value", _7="value", _103="clob", _8=0, _8=0, _52=<tmp_17622>bat[:oid,:str]{8}) ] [ 1 usec # _52 := nil; ] [ 2 usec # sql.rsColumn(_93=0, _96="nilsgri.type_name_value", _54="doc", _97="int", _98=32, _8=0, _60=<tmp_26630>bat[:oid,:int]{8}) ] [ 1 usec # _60 := nil; ] [ 2 usec # sql.rsColumn(_93=0, _96="nilsgri.type_name_value", _62="nenc", _100="varchar", _101=255, _8=0, _68=<tmp_17712>bat[:oid,:str]{8}) ] [ 1 usec # _68 := nil; ] [ 2 usec # sql.rsColumn(_93=0, _96="nilsgri.type_name_value", _70="pos", _97="int", _98=32, _8=0, _76=<tmp_25077>bat[:oid,:int]{8}) ] [ 1 usec # _76 := nil; ] [ 1 usec # sql.rsColumn(_93=0, _96="nilsgri.type_name_value", _78="pathid", _97="int", _98=32, _8=0, _84=<tmp_26621>bat[:oid,:int]{8}) ] [ 1 usec # _84 := nil; ] [ 2 usec # sql.rsColumn(_93=0, _96="nilsgri.type_name_value", _86="complete", _109="boolean", _10=1, _8=0, _92=<tmp_24211>bat[:oid,:bit]{8}) ] [ 1 usec # _92 := nil; ] % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 12, 8, 3, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.42.2.1", 1071, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 107, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 56, true ] [ 46 usec # sql.exportResult(_93=0, _111="") ] [1626253 usec # user.s15_1(_4=3, _5="author", _6="Jurgen Annevelink") ] Trace for Q2: echo "TRACE SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' AND doc = 1 AND nenc = '1.1.1.1' AND pos = 4;" | mclient --language=sql --database=proto --user=nilsgri --passwd=nilsgri --time > trace.txt 2>&1 [ 1 usec # mdb.setTimer(_2=true) ] [ 15 usec # _7:bat[:oid,:int] := sql.bind(_8="nilsgri", _9="type_name_value", _10="pos", _11=0) ] [ 2 usec # _12:bat[:oid,:int] := sql.bind(_8="nilsgri", _9="type_name_value", _10="pos", _13=1) ] [ 22 usec # _14 := algebra.kunion(_7=<tmp_26222>bat[:oid,:int]{12165109}, _12=<tmp_26274>bat[:oid,:int]{0}) ] [ 3 usec # _7:bat[:oid,:int] := nil; ] [ 1 usec # _12:bat[:oid,:int] := nil; ] [ 2 usec # _15:bat[:oid,:int] := sql.bind(_8="nilsgri", _9="type_name_value", _10="pos", _16=2) ] [ 7 usec # _17 := algebra.kdifference(_14=<tmp_25032>bat[:oid,:int]{12165109}, _15=<tmp_26355>bat[:oid,:int]{0}) ] [ 9 usec # _14 := nil; ] [ 4 usec # _18 := algebra.kunion(_17=<tmp_25117>bat[:oid,:int]{12165109}, _15=<tmp_26355>bat[:oid,:int]{0}) ] [ 2 usec # _17 := nil; ] [ 1 usec # _15:bat[:oid,:int] := nil; ] [ 3 usec # _19:bat[:oid,:oid] := sql.bind_dbat(_8="nilsgri", _9="type_name_value", _13=1) ] [ 3 usec # _20 := bat.reverse(_19=<tmp_26430>bat[:oid,:oid]{0}) ] [ 1 usec # _19:bat[:oid,:oid] := nil; ] [ 3 usec # _21 := algebra.kdifference(_18=<tmp_25032>bat[:oid,:int]{12165109}, _20=<~tmp_26430>bat[:oid,:oid]{0}) ] [ 3 usec # _22:bat[:oid,:str] := sql.bind(_8="nilsgri", _9="type_name_value", _23="nenc", _11=0) ] [ 2 usec # _24:bat[:oid,:str] := sql.bind(_8="nilsgri", _9="type_name_value", _23="nenc", _13=1) ] [ 5 usec # _25 := algebra.kunion(_22=<tmp_26463>bat[:oid,:str]{12165109}, _24=<tmp_25712>bat[:oid,:str]{0}) ] [ 1 usec # _22:bat[:oid,:str] := nil; ] [ 1 usec # _24:bat[:oid,:str] := nil; ] [ 2 usec # _26:bat[:oid,:str] := sql.bind(_8="nilsgri", _9="type_name_value", _23="nenc", _16=2) ] [ 4 usec # _27 := algebra.kdifference(_25=<tmp_26634>bat[:oid,:str]{12165109}, _26=<tmp_26223>bat[:oid,:str]{0}) ] [ 2 usec # _25 := nil; ] [ 3 usec # _28 := algebra.kunion(_27=<tmp_26631>bat[:oid,:str]{12165109}, _26=<tmp_26223>bat[:oid,:str]{0}) ] [ 2 usec # _27 := nil; ] [ 0 usec # _26:bat[:oid,:str] := nil; ] [ 4 usec # _29 := algebra.kdifference(_28=<tmp_26634>bat[:oid,:str]{12165109}, _20=<~tmp_26430>bat[:oid,:oid]{0}) ] [ 3 usec # _30:bat[:oid,:int] := sql.bind(_8="nilsgri", _9="type_name_value", _31="doc", _11=0) ] [ 1 usec # _32:bat[:oid,:int] := sql.bind(_8="nilsgri", _9="type_name_value", _31="doc", _13=1) ] [ 5 usec # _33 := algebra.kunion(_30=<tmp_26424>bat[:oid,:int]{12165109}, _32=<tmp_24572>bat[:oid,:int]{0}) ] [ 1 usec # _30:bat[:oid,:int] := nil; ] [ 1 usec # _32:bat[:oid,:int] := nil; ] [ 2 usec # _34:bat[:oid,:int] := sql.bind(_8="nilsgri", _9="type_name_value", _31="doc", _16=2) ] [ 4 usec # _35 := algebra.kdifference(_33=<tmp_25203>bat[:oid,:int]{12165109}, _34=<tmp_25072>bat[:oid,:int]{0}) ] [ 2 usec # _33 := nil; ] [ 4 usec # _36 := algebra.kunion(_35=<tmp_26627>bat[:oid,:int]{12165109}, _34=<tmp_25072>bat[:oid,:int]{0}) ] [ 2 usec # _35 := nil; ] [ 1 usec # _34:bat[:oid,:int] := nil; ] [ 4 usec # _37 := algebra.kdifference(_36=<tmp_25203>bat[:oid,:int]{12165109}, _20=<~tmp_26430>bat[:oid,:oid]{0}) ] [ 1 usec # _20 := nil; ] [ 2 usec # _38:bat[:oid,:str] := sql.bind(_8="nilsgri", _9="type_name_value", _39="value", _11=0) ] [ 2 usec # _40:bat[:oid,:str] := sql.bind(_8="nilsgri", _9="type_name_value", _39="value", _13=1) ] [ 6 usec # _41 := algebra.kunion(_38=<tmp_26450>bat[:oid,:str]{12165109}, _40=<tmp_26466>bat[:oid,:str]{0}) ] [ 1 usec # _38:bat[:oid,:str] := nil; ] [ 1 usec # _40:bat[:oid,:str] := nil; ] [ 2 usec # _42:bat[:oid,:str] := sql.bind(_8="nilsgri", _9="type_name_value", _39="value", _16=2) ] [ 4 usec # _43 := algebra.kdifference(_41=<tmp_21205>bat[:oid,:str]{12165109}, _42=<tmp_26165>bat[:oid,:str]{0}) ] [ 2 usec # _41 := nil; ] [ 3 usec # _44 := algebra.kunion(_43=<tmp_26604>bat[:oid,:str]{12165109}, _42=<tmp_26165>bat[:oid,:str]{0}) ] [ 2 usec # _43 := nil; ] [ 1 usec # _42:bat[:oid,:str] := nil; ] [ 2 usec # _45:bat[:oid,:oid] := sql.bind_dbat(_8="nilsgri", _9="type_name_value", _13=1) ] [ 2 usec # _46 := bat.reverse(_45=<tmp_26430>bat[:oid,:oid]{0}) ] [ 1 usec # _45:bat[:oid,:oid] := nil; ] [ 4 usec # _47 := algebra.kdifference(_44=<tmp_21205>bat[:oid,:str]{12165109}, _46=<~tmp_26430>bat[:oid,:oid]{0}) ] [ 3 usec # _48:bat[:oid,:str] := sql.bind(_8="nilsgri", _9="type_name_value", _49="name", _11=0) ] [ 2 usec # _50:bat[:oid,:str] := sql.bind(_8="nilsgri", _9="type_name_value", _49="name", _13=1) ] [ 4 usec # _51 := algebra.kunion(_48=<tmp_26475>bat[:oid,:str]{12165109}, _50=<tmp_26460>bat[:oid,:str]{0}) ] [ 1 usec # _48:bat[:oid,:str] := nil; ] [ 1 usec # _50:bat[:oid,:str] := nil; ] [ 2 usec # _52:bat[:oid,:str] := sql.bind(_8="nilsgri", _9="type_name_value", _49="name", _16=2) ] [ 4 usec # _53 := algebra.kdifference(_51=<tmp_25235>bat[:oid,:str]{12165109}, _52=<tmp_26461>bat[:oid,:str]{0}) ] [ 2 usec # _51 := nil; ] [ 3 usec # _54 := algebra.kunion(_53=<tmp_24505>bat[:oid,:str]{12165109}, _52=<tmp_26461>bat[:oid,:str]{0}) ] [ 2 usec # _53 := nil; ] [ 0 usec # _52:bat[:oid,:str] := nil; ] [ 3 usec # _55 := algebra.kdifference(_54=<tmp_25235>bat[:oid,:str]{12165109}, _46=<~tmp_26430>bat[:oid,:oid]{0}) ] [ 4 usec # _56:bat[:oid,:int] := sql.bind_idxbat(_8="nilsgri", _9="type_name_value", _57="type_name_value_type_name_value_doc_nenc_pos_pkey", _11=0) ] [ 2 usec # _58:bat[:oid,:int] := sql.bind_idxbat(_8="nilsgri", _9="type_name_value", _57="type_name_value_type_name_value_doc_nenc_pos_pkey", _13=1) ] [ 4 usec # _59 := algebra.kunion(_56=<tmp_26233>bat[:oid,:int]{12165109}, _58=<tmp_26547>bat[:oid,:int]{0}) ] [ 1 usec # _56:bat[:oid,:int] := nil; ] [ 1 usec # _58:bat[:oid,:int] := nil; ] [ 2 usec # _60:bat[:oid,:int] := sql.bind_idxbat(_8="nilsgri", _9="type_name_value", _57="type_name_value_type_name_value_doc_nenc_pos_pkey", _16=2) ] [ 4 usec # _61 := algebra.kdifference(_59=<tmp_23542>bat[:oid,:int]{12165109}, _60=<tmp_25617>bat[:oid,:int]{0}) ] [ 2 usec # _59 := nil; ] [ 4 usec # _62 := algebra.kunion(_61=<tmp_22511>bat[:oid,:int]{12165109}, _60=<tmp_25617>bat[:oid,:int]{0}) ] [ 2 usec # _61 := nil; ] [ 1 usec # _60:bat[:oid,:int] := nil; ] [ 3 usec # _63 := algebra.kdifference(_62=<tmp_23542>bat[:oid,:int]{12165109}, _46=<~tmp_26430>bat[:oid,:oid]{0}) ] [ 2 usec # _62 := nil; ] [ 1 usec # _46 := nil; ] [ 2 usec # _64:bat[:oid,:int] := sql.bind(_8="nilsgri", _9="type_name_value", _65="type", _11=0) ] [ 8317 usec # _66 := algebra.uselect(_64=<tmp_26500>bat[:oid,:int]{12165109}, A0=3) ] [ 12 usec # _67:bat[:oid,:int] := sql.bind(_8="nilsgri", _9="type_name_value", _65="type", _13=1) ] [ 11 usec # _68 := algebra.uselect(_67=<tmp_26427>bat[:oid,:int]{0}, A0=3) ] [ 8 usec # _69 := algebra.kunion(_66=<tmp_26632>bat[:oid,:oid]{10020569}, _68=<tmp_26624>bat[:oid,:oid]{0}) ] [ 4 usec # _66 := nil; ] [ 2 usec # _68 := nil; ] [ 2 usec # _70:bat[:oid,:int] := sql.bind(_8="nilsgri", _9="type_name_value", _65="type", _16=2) ] [ 5 usec # _71 := algebra.kdifference(_69=<tmp_23542>bat[:oid,:oid]{10020569}, _70=<tmp_26456>bat[:oid,:int]{0}) ] [ 2 usec # _69 := nil; ] [ 12 usec # _72 := algebra.uselect(_70=<tmp_26456>bat[:oid,:int]{0}, A0=3) ] [ 3 usec # _73 := algebra.kunion(_71=<tmp_26624>bat[:oid,:oid]{10020569}, _72=<tmp_26370>bat[:oid,:oid]{0}) ] [ 2 usec # _71 := nil; ] [ 2 usec # _72 := nil; ] [ 3 usec # _74:bat[:oid,:oid] := sql.bind_dbat(_8="nilsgri", _9="type_name_value", _13=1) ] [ 3 usec # _75 := bat.reverse(_74=<tmp_26430>bat[:oid,:oid]{0}) ] [ 1 usec # _74:bat[:oid,:oid] := nil; ] [ 4 usec # _76 := algebra.kdifference(_73=<tmp_26632>bat[:oid,:oid]{10020569}, _75=<~tmp_26430>bat[:oid,:oid]{0}) ] [ 2 usec # _73 := nil; ] [ 1 usec # _75 := nil; ] [ 8 usec # _77 := algebra.semijoin(_63=<tmp_22511>bat[:oid,:int]{12165109}, _76=<tmp_26624>bat[:oid,:oid]{10020569}) ] [ 2 usec # _63 := nil; ] [ 2 usec # _76 := nil; ] [ 2 usec # _78 := calc.hash(A0=3) ] [ 2 usec # _80 := calc.rotate_xor_hash(_78=3, _79=4, A1="author") ] [ 2 usec # _81 := calc.rotate_xor_hash(_80=-2134957464, _79=4, A2="Jurgen Annevelink") ] [ 1 usec # _82 := calc.rotate_xor_hash(_81=1237315708, _79=4, A3=1) ] [ 1 usec # _83 := calc.rotate_xor_hash(_82=-1677785147, _79=4, A4="1.1.1.1") ] [ 1 usec # _84 := calc.rotate_xor_hash(_83=-1663796859, _79=4, A5=4) ] [4902331 usec # _85 := algebra.select(_77=<~tmp_26632>bat[:oid,:int]{10020569}, _84=-850945955) ] [6075682 usec # _77 := nil; ] [ 37 usec # _86 := algebra.semijoin(_55=<tmp_24505>bat[:oid,:str]{12165109}, _85=<tmp_26434>bat[:oid,:int]{1}) ] [ 7 usec # _55 := nil; ] [ 3 usec # _85 := nil; ] [ 13 usec # _87 := algebra.uselect(_86=<tmp_17630>bat[:oid,:str]{1}, A1="author") ] [ 1 usec # _86 := nil; ] [ 5 usec # _88 := algebra.semijoin(_47=<tmp_26604>bat[:oid,:str]{12165109}, _87=<tmp_26370>bat[:oid,:oid]{1}) ] [ 3 usec # _47 := nil; ] [ 2 usec # _87 := nil; ] [ 3 usec # _89 := algebra.uselect(_88=<tmp_17630>bat[:oid,:str]{1}, A2="Jurgen Annevelink") ] [ 1 usec # _88 := nil; ] [ 4 usec # _90 := algebra.semijoin(_37=<tmp_26627>bat[:oid,:int]{12165109}, _89=<tmp_26370>bat[:oid,:oid]{1}) ] [ 3 usec # _37 := nil; ] [ 1 usec # _89 := nil; ] [ 4 usec # _91 := algebra.uselect(_90=<tmp_26434>bat[:oid,:int]{1}, A3=1) ] [ 2 usec # _90 := nil; ] [ 5 usec # _92 := algebra.semijoin(_29=<tmp_26631>bat[:oid,:str]{12165109}, _91=<tmp_26370>bat[:oid,:oid]{1}) ] [ 2 usec # _29 := nil; ] [ 1 usec # _91 := nil; ] [ 3 usec # _93 := algebra.uselect(_92=<tmp_17630>bat[:oid,:str]{1}, A4="1.1.1.1") ] [ 2 usec # _92 := nil; ] [ 4 usec # _94 := algebra.semijoin(_21=<tmp_25117>bat[:oid,:int]{12165109}, _93=<tmp_26370>bat[:oid,:oid]{1}) ] [ 2 usec # _21 := nil; ] [ 1 usec # _93 := nil; ] [ 2 usec # _95 := algebra.uselect(_94=<tmp_26434>bat[:oid,:int]{1}, A5=4) ] [ 1 usec # _94 := nil; ] [ 12 usec # _97 := algebra.markT(_95=<tmp_26370>bat[:oid,:oid]{1}, _96=0@0) ] [ 1 usec # _95 := nil; ] [ 4 usec # _98 := bat.reverse(_97=<tmp_25117>bat[:oid,:oid]{1}) ] [ 1 usec # _97 := nil; ] [ 11 usec # _99 := algebra.kunion(_64=<tmp_26500>bat[:oid,:int]{12165109}, _67=<tmp_26427>bat[:oid,:int]{0}) ] [ 1 usec # _64:bat[:oid,:int] := nil; ] [ 1 usec # _67:bat[:oid,:int] := nil; ] [ 6 usec # _100 := algebra.kdifference(_99=<tmp_26631>bat[:oid,:int]{12165109}, _70=<tmp_26456>bat[:oid,:int]{0}) ] [ 2 usec # _99 := nil; ] [ 3 usec # _101 := algebra.kunion(_100=<tmp_26627>bat[:oid,:int]{12165109}, _70=<tmp_26456>bat[:oid,:int]{0}) ] [ 1 usec # _100 := nil; ] [ 1 usec # _70:bat[:oid,:int] := nil; ] [ 13 usec # _102 := algebra.join(_98=<~tmp_25117>bat[:oid,:oid]{1}, _101=<tmp_26631>bat[:oid,:int]{12165109}) ] [ 2 usec # _101 := nil; ] [ 10 usec # _103 := algebra.join(_98=<~tmp_25117>bat[:oid,:oid]{1}, _54=<tmp_25235>bat[:oid,:str]{12165109}) ] [ 2 usec # _54 := nil; ] [ 9 usec # _104 := algebra.join(_98=<~tmp_25117>bat[:oid,:oid]{1}, _44=<tmp_21205>bat[:oid,:str]{12165109}) ] [ 2 usec # _44 := nil; ] [ 4 usec # _105 := algebra.join(_98=<~tmp_25117>bat[:oid,:oid]{1}, _36=<tmp_25203>bat[:oid,:int]{12165109}) ] [ 2 usec # _36 := nil; ] [ 9 usec # _106 := algebra.join(_98=<~tmp_25117>bat[:oid,:oid]{1}, _28=<tmp_26634>bat[:oid,:str]{12165109}) ] [ 2 usec # _28 := nil; ] [ 5 usec # _107 := algebra.join(_98=<~tmp_25117>bat[:oid,:oid]{1}, _18=<tmp_25032>bat[:oid,:int]{12165109}) ] [ 2 usec # _18 := nil; ] [ 15 usec # _108:bat[:oid,:int] := sql.bind(_8="nilsgri", _9="type_name_value", _109="pathid", _11=0) ] [ 2 usec # _110:bat[:oid,:int] := sql.bind(_8="nilsgri", _9="type_name_value", _109="pathid", _13=1) ] [ 5 usec # _111 := algebra.kunion(_108=<tmp_11660>bat[:oid,:int]{12165109}, _110=<tmp_26374>bat[:oid,:int]{0}) ] [ 1 usec # _108:bat[:oid,:int] := nil; ] [ 1 usec # _110:bat[:oid,:int] := nil; ] [ 2 usec # _112:bat[:oid,:int] := sql.bind(_8="nilsgri", _9="type_name_value", _109="pathid", _16=2) ] [ 3 usec # _113 := algebra.kdifference(_111=<tmp_25032>bat[:oid,:int]{12165109}, _112=<tmp_26527>bat[:oid,:int]{0}) ] [ 2 usec # _111 := nil; ] [ 3 usec # _114 := algebra.kunion(_113=<tmp_26634>bat[:oid,:int]{12165109}, _112=<tmp_26527>bat[:oid,:int]{0}) ] [ 1 usec # _113 := nil; ] [ 1 usec # _112:bat[:oid,:int] := nil; ] [ 4 usec # _115 := algebra.join(_98=<~tmp_25117>bat[:oid,:oid]{1}, _114=<tmp_25032>bat[:oid,:int]{12165109}) ] [ 3 usec # _114 := nil; ] [ 5 usec # _116:bat[:oid,:bit] := sql.bind(_8="nilsgri", _9="type_name_value", _117="complete", _11=0) ] [ 2 usec # _118:bat[:oid,:bit] := sql.bind(_8="nilsgri", _9="type_name_value", _117="complete", _13=1) ] [ 5 usec # _119 := algebra.kunion(_116=<tmp_26473>bat[:oid,:bit]{12165109}, _118=<tmp_24251>bat[:oid,:bit]{0}) ] [ 1 usec # _116:bat[:oid,:bit] := nil; ] [ 0 usec # _118:bat[:oid,:bit] := nil; ] [ 2 usec # _120:bat[:oid,:bit] := sql.bind(_8="nilsgri", _9="type_name_value", _117="complete", _16=2) ] [ 4 usec # _121 := algebra.kdifference(_119=<tmp_25032>bat[:oid,:bit]{12165109}, _120=<tmp_14322>bat[:oid,:bit]{0}) ] [ 2 usec # _119 := nil; ] [ 3 usec # _122 := algebra.kunion(_121=<tmp_26634>bat[:oid,:bit]{12165109}, _120=<tmp_14322>bat[:oid,:bit]{0}) ] [ 2 usec # _121 := nil; ] [ 0 usec # _120:bat[:oid,:bit] := nil; ] [ 5 usec # _123 := algebra.join(_98=<~tmp_25117>bat[:oid,:oid]{1}, _122=<tmp_25032>bat[:oid,:bit]{12165109}) ] [ 3 usec # _98 := nil; ] [ 1 usec # _122 := nil; ] [ 4 usec # _124 := sql.resultSet(_125=8, _13=1, _102=<tmp_26605>bat[:oid,:int]{1}) ] [ 6 usec # sql.rsColumn(_124=0, _127="nilsgri.type_name_value", _65="type", _128="int", _129=32, _11=0, _102=<tmp_26605>bat[:oid,:int]{1}) ] [ 1 usec # _102 := nil; ] [ 2 usec # sql.rsColumn(_124=0, _127="nilsgri.type_name_value", _49="name", _131="varchar", _132=255, _11=0, _103=<tmp_17712>bat[:oid,:str]{1}) ] [ 1 usec # _103 := nil; ] [ 2 usec # sql.rsColumn(_124=0, _127="nilsgri.type_name_value", _39="value", _134="clob", _11=0, _11=0, _104=<tmp_17622>bat[:oid,:str]{1}) ] [ 1 usec # _104 := nil; ] [ 2 usec # sql.rsColumn(_124=0, _127="nilsgri.type_name_value", _31="doc", _128="int", _129=32, _11=0, _105=<tmp_26621>bat[:oid,:int]{1}) ] [ 1 usec # _105 := nil; ] [ 2 usec # sql.rsColumn(_124=0, _127="nilsgri.type_name_value", _23="nenc", _131="varchar", _132=255, _11=0, _106=<tmp_13267>bat[:oid,:str]{1}) ] [ 1 usec # _106 := nil; ] [ 2 usec # sql.rsColumn(_124=0, _127="nilsgri.type_name_value", _10="pos", _128="int", _129=32, _11=0, _107=<tmp_25077>bat[:oid,:int]{1}) ] [ 1 usec # _107 := nil; ] [ 2 usec # sql.rsColumn(_124=0, _127="nilsgri.type_name_value", _109="pathid", _128="int", _129=32, _11=0, _115=<tmp_26630>bat[:oid,:int]{1}) ] [ 1 usec # _115 := nil; ] [ 2 usec # sql.rsColumn(_124=0, _127="nilsgri.type_name_value", _117="complete", _140="boolean", _13=1, _11=0, _123=<tmp_24211>bat[:oid,:bit]{1}) ] [ 1 usec # _123 := nil; ] % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 7, 1, 1, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] [ 210 usec # sql.exportResult(_124=0, _142="") ] [10988684 usec # user.s29_1(_4=3, _5="author", _6="Jurgen Annevelink", _7=1, _8="1.1.1.1", _9=4) ] Timer 10989.450 msec Trace for Q3: echo "TRACE SELECT * FROM value WHERE value = 'Jurgen Annevelink' AND type = 3 AND name = 'author';" | mclient --language=sql --database=proto --user=nilsgri --passwd=nilsgri --time > trace_Q3.txt 2>&1 [ 1 usec # mdb.setTimer(_2=true) ] [ 18 usec # _4:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="value", _7="name", _8=0) ] [ 2 usec # _9:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="value", _7="name", _10=1) ] [ 22 usec # _11 := algebra.kunion(_4=<tmp_22443>bat[:oid,:str]{12165109}, _9=<tmp_16672>bat[:oid,:str]{0}) ] [ 2 usec # _4:bat[:oid,:str] := nil; ] [ 0 usec # _9:bat[:oid,:str] := nil; ] [ 2 usec # _12:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="value", _7="name", _13=2) ] [ 6 usec # _14 := algebra.kdifference(_11=<tmp_25203>bat[:oid,:str]{12165109}, _12=<tmp_16423>bat[:oid,:str]{0}) ] [ 9 usec # _11 := nil; ] [ 6 usec # _15 := algebra.kunion(_14=<tmp_25032>bat[:oid,:str]{12165109}, _12=<tmp_16423>bat[:oid,:str]{0}) ] [ 2 usec # _14 := nil; ] [ 1 usec # _12:bat[:oid,:str] := nil; ] [ 4 usec # _16:bat[:oid,:oid] := sql.bind_dbat(_5="nilsgri", _6="value", _10=1) ] [ 3 usec # _17 := bat.reverse(_16=<tmp_26465>bat[:oid,:oid]{0}) ] [ 2 usec # _16:bat[:oid,:oid] := nil; ] [ 5 usec # _18 := algebra.kdifference(_15=<tmp_25203>bat[:oid,:str]{12165109}, _17=<~tmp_26465>bat[:oid,:oid]{0}) ] [ 4 usec # _19:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="value", _20="type", _8=0) ] [ 1 usec # _21:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="value", _20="type", _10=1) ] [ 5 usec # _22 := algebra.kunion(_19=<tmp_26633>bat[:oid,:int]{12165109}, _21=<tmp_26640>bat[:oid,:int]{0}) ] [ 0 usec # _19:bat[:oid,:int] := nil; ] [ 1 usec # _21:bat[:oid,:int] := nil; ] [ 2 usec # _23:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="value", _20="type", _13=2) ] [ 4 usec # _24 := algebra.kdifference(_22=<tmp_24505>bat[:oid,:int]{12165109}, _23=<tmp_17546>bat[:oid,:int]{0}) ] [ 4 usec # _22 := nil; ] [ 4 usec # _25 := algebra.kunion(_24=<tmp_25117>bat[:oid,:int]{12165109}, _23=<tmp_17546>bat[:oid,:int]{0}) ] [ 2 usec # _24 := nil; ] [ 1 usec # _23:bat[:oid,:int] := nil; ] [ 4 usec # _26 := algebra.kdifference(_25=<tmp_24505>bat[:oid,:int]{12165109}, _17=<~tmp_26465>bat[:oid,:oid]{0}) ] [ 1 usec # _17 := nil; ] [ 3 usec # _27:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="value", _6="value", _8=0) ] [ 26 usec # _28 := algebra.uselect(_27=<tmp_25160>bat[:oid,:str]{12165109}, A0="Jurgen Annevelink") ] [ 2 usec # _29:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="value", _6="value", _10=1) ] [ 7 usec # _30 := algebra.uselect(_29=<tmp_26610>bat[:oid,:str]{0}, A0="Jurgen Annevelink") ] [ 3 usec # _31 := algebra.kunion(_28=<tmp_25235>bat[:oid,:oid]{8}, _30=<tmp_26627>bat[:oid,:oid]{0}) ] [ 2 usec # _28 := nil; ] [ 1 usec # _30 := nil; ] [ 2 usec # _32:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="value", _6="value", _13=2) ] [ 5 usec # _33 := algebra.kdifference(_31=<tmp_26632>bat[:oid,:oid]{8}, _32=<tmp_22612>bat[:oid,:str]{0}) ] [ 2 usec # _31 := nil; ] [ 8 usec # _34 := algebra.uselect(_32=<tmp_22612>bat[:oid,:str]{0}, A0="Jurgen Annevelink") ] [ 3 usec # _35 := algebra.kunion(_33=<tmp_26627>bat[:oid,:oid]{8}, _34=<tmp_26370>bat[:oid,:oid]{0}) ] [ 1 usec # _33 := nil; ] [ 1 usec # _34 := nil; ] [ 2 usec # _36:bat[:oid,:oid] := sql.bind_dbat(_5="nilsgri", _6="value", _10=1) ] [ 2 usec # _37 := bat.reverse(_36=<tmp_26465>bat[:oid,:oid]{0}) ] [ 0 usec # _36:bat[:oid,:oid] := nil; ] [ 2 usec # _38 := algebra.kdifference(_35=<tmp_25235>bat[:oid,:oid]{8}, _37=<~tmp_26465>bat[:oid,:oid]{0}) ] [ 1 usec # _35 := nil; ] [ 1 usec # _37 := nil; ] [ 7 usec # _39 := algebra.semijoin(_26=<tmp_25117>bat[:oid,:int]{12165109}, _38=<tmp_26627>bat[:oid,:oid]{8}) ] [ 2 usec # _26 := nil; ] [ 2 usec # _38 := nil; ] [ 7 usec # _40 := algebra.uselect(_39=<~tmp_25235>bat[:oid,:int]{8}, A1=3) ] [ 2 usec # _39 := nil; ] [ 3 usec # _41 := algebra.semijoin(_18=<tmp_25032>bat[:oid,:str]{12165109}, _40=<tmp_26370>bat[:oid,:oid]{8}) ] [ 2 usec # _18 := nil; ] [ 2 usec # _40 := nil; ] [ 6 usec # _42 := algebra.uselect(_41=<~tmp_26627>bat[:oid,:str]{8}, A2="author") ] [ 2 usec # _41 := nil; ] [ 4 usec # _44 := algebra.markT(_42=<tmp_26370>bat[:oid,:oid]{8}, _43=0@0) ] [ 1 usec # _42 := nil; ] [ 2 usec # _45 := bat.reverse(_44=<tmp_26627>bat[:oid,:oid]{8}) ] [ 0 usec # _44 := nil; ] [ 4 usec # _46 := algebra.kunion(_27=<tmp_25160>bat[:oid,:str]{12165109}, _29=<tmp_26610>bat[:oid,:str]{0}) ] [ 1 usec # _27:bat[:oid,:str] := nil; ] [ 1 usec # _29:bat[:oid,:str] := nil; ] [ 3 usec # _47 := algebra.kdifference(_46=<tmp_25032>bat[:oid,:str]{12165109}, _32=<tmp_22612>bat[:oid,:str]{0}) ] [ 2 usec # _46 := nil; ] [ 4 usec # _48 := algebra.kunion(_47=<tmp_25235>bat[:oid,:str]{12165109}, _32=<tmp_22612>bat[:oid,:str]{0}) ] [ 2 usec # _47 := nil; ] [ 1 usec # _32:bat[:oid,:str] := nil; ] [ 14 usec # _49 := algebra.join(_45=<~tmp_26627>bat[:oid,:oid]{8}, _48=<tmp_25032>bat[:oid,:str]{12165109}) ] [ 2 usec # _48 := nil; ] [ 2 usec # _50:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="value", _51="doc", _8=0) ] [ 2 usec # _52:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="value", _51="doc", _10=1) ] [ 4 usec # _53 := algebra.kunion(_50=<tmp_25133>bat[:oid,:int]{12165109}, _52=<tmp_26504>bat[:oid,:int]{0}) ] [ 2 usec # _50:bat[:oid,:int] := nil; ] [ 1 usec # _52:bat[:oid,:int] := nil; ] [ 2 usec # _54:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="value", _51="doc", _13=2) ] [ 5 usec # _55 := algebra.kdifference(_53=<tmp_25032>bat[:oid,:int]{12165109}, _54=<tmp_26366>bat[:oid,:int]{0}) ] [ 3 usec # _53 := nil; ] [ 3 usec # _56 := algebra.kunion(_55=<tmp_25235>bat[:oid,:int]{12165109}, _54=<tmp_26366>bat[:oid,:int]{0}) ] [ 1 usec # _55 := nil; ] [ 1 usec # _54:bat[:oid,:int] := nil; ] [ 6 usec # _57 := algebra.join(_45=<~tmp_26627>bat[:oid,:oid]{8}, _56=<tmp_25032>bat[:oid,:int]{12165109}) ] [ 2 usec # _56 := nil; ] [ 3 usec # _58:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="value", _59="nenc", _8=0) ] [ 2 usec # _60:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="value", _59="nenc", _10=1) ] [ 4 usec # _61 := algebra.kunion(_58=<tmp_17053>bat[:oid,:str]{12165109}, _60=<tmp_16113>bat[:oid,:str]{0}) ] [ 1 usec # _58:bat[:oid,:str] := nil; ] [ 1 usec # _60:bat[:oid,:str] := nil; ] [ 1 usec # _62:bat[:oid,:str] := sql.bind(_5="nilsgri", _6="value", _59="nenc", _13=2) ] [ 3 usec # _63 := algebra.kdifference(_61=<tmp_25032>bat[:oid,:str]{12165109}, _62=<tmp_17121>bat[:oid,:str]{0}) ] [ 2 usec # _61 := nil; ] [ 3 usec # _64 := algebra.kunion(_63=<tmp_25235>bat[:oid,:str]{12165109}, _62=<tmp_17121>bat[:oid,:str]{0}) ] [ 2 usec # _63 := nil; ] [ 1 usec # _62:bat[:oid,:str] := nil; ] [ 6 usec # _65 := algebra.join(_45=<~tmp_26627>bat[:oid,:oid]{8}, _64=<tmp_25032>bat[:oid,:str]{12165109}) ] [ 1 usec # _64 := nil; ] [ 3 usec # _66:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="value", _67="pos", _8=0) ] [ 2 usec # _68:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="value", _67="pos", _10=1) ] [ 4 usec # _69 := algebra.kunion(_66=<tmp_16612>bat[:oid,:int]{12165109}, _68=<tmp_14342>bat[:oid,:int]{0}) ] [ 1 usec # _66:bat[:oid,:int] := nil; ] [ 1 usec # _68:bat[:oid,:int] := nil; ] [ 3 usec # _70:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="value", _67="pos", _13=2) ] [ 4 usec # _71 := algebra.kdifference(_69=<tmp_25032>bat[:oid,:int]{12165109}, _70=<tmp_25242>bat[:oid,:int]{0}) ] [ 4 usec # _69 := nil; ] [ 5 usec # _72 := algebra.kunion(_71=<tmp_25235>bat[:oid,:int]{12165109}, _70=<tmp_25242>bat[:oid,:int]{0}) ] [ 2 usec # _71 := nil; ] [ 1 usec # _70:bat[:oid,:int] := nil; ] [ 9 usec # _73 := algebra.join(_45=<~tmp_26627>bat[:oid,:oid]{8}, _72=<tmp_25032>bat[:oid,:int]{12165109}) ] [ 2 usec # _72 := nil; ] [ 6 usec # _74 := algebra.join(_45=<~tmp_26627>bat[:oid,:oid]{8}, _25=<tmp_24505>bat[:oid,:int]{12165109}) ] [ 1 usec # _25 := nil; ] [ 6 usec # _75 := algebra.join(_45=<~tmp_26627>bat[:oid,:oid]{8}, _15=<tmp_25203>bat[:oid,:str]{12165109}) ] [ 2 usec # _15 := nil; ] [ 4 usec # _76:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="value", _77="pathid", _8=0) ] [ 1 usec # _78:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="value", _77="pathid", _10=1) ] [ 4 usec # _79 := algebra.kunion(_76=<tmp_17345>bat[:oid,:int]{12165109}, _78=<tmp_17400>bat[:oid,:int]{0}) ] [ 1 usec # _76:bat[:oid,:int] := nil; ] [ 1 usec # _78:bat[:oid,:int] := nil; ] [ 3 usec # _80:bat[:oid,:int] := sql.bind(_5="nilsgri", _6="value", _77="pathid", _13=2) ] [ 5 usec # _81 := algebra.kdifference(_79=<tmp_25203>bat[:oid,:int]{12165109}, _80=<tmp_17210>bat[:oid,:int]{0}) ] [ 3 usec # _79 := nil; ] [ 4 usec # _82 := algebra.kunion(_81=<tmp_24505>bat[:oid,:int]{12165109}, _80=<tmp_17210>bat[:oid,:int]{0}) ] [ 2 usec # _81 := nil; ] [ 1 usec # _80:bat[:oid,:int] := nil; ] [ 7 usec # _83 := algebra.join(_45=<~tmp_26627>bat[:oid,:oid]{8}, _82=<tmp_25203>bat[:oid,:int]{12165109}) ] [ 1 usec # _82 := nil; ] [ 4 usec # _84:bat[:oid,:bit] := sql.bind(_5="nilsgri", _6="value", _85="complete", _8=0) ] [ 2 usec # _86:bat[:oid,:bit] := sql.bind(_5="nilsgri", _6="value", _85="complete", _10=1) ] [ 4 usec # _87 := algebra.kunion(_84=<tmp_20260>bat[:oid,:bit]{12165109}, _86=<tmp_15204>bat[:oid,:bit]{0}) ] [ 1 usec # _84:bat[:oid,:bit] := nil; ] [ 1 usec # _86:bat[:oid,:bit] := nil; ] [ 2 usec # _88:bat[:oid,:bit] := sql.bind(_5="nilsgri", _6="value", _85="complete", _13=2) ] [ 4 usec # _89 := algebra.kdifference(_87=<tmp_25203>bat[:oid,:bit]{12165109}, _88=<tmp_26404>bat[:oid,:bit]{0}) ] [ 3 usec # _87 := nil; ] [ 5 usec # _90 := algebra.kunion(_89=<tmp_24505>bat[:oid,:bit]{12165109}, _88=<tmp_26404>bat[:oid,:bit]{0}) ] [ 2 usec # _89 := nil; ] [ 0 usec # _88:bat[:oid,:bit] := nil; ] [ 7 usec # _91 := algebra.join(_45=<~tmp_26627>bat[:oid,:oid]{8}, _90=<tmp_25203>bat[:oid,:bit]{12165109}) ] [ 2 usec # _45 := nil; ] [ 2 usec # _90 := nil; ] [ 4 usec # _92 := sql.resultSet(_93=8, _10=1, _49=<tmp_25117>bat[:oid,:str]{8}) ] [ 4 usec # sql.rsColumn(_92=0, _95="nilsgri.value", _6="value", _96="clob", _8=0, _8=0, _49=<tmp_25117>bat[:oid,:str]{8}) ] [ 1 usec # _49 := nil; ] [ 2 usec # sql.rsColumn(_92=0, _95="nilsgri.value", _51="doc", _98="int", _99=32, _8=0, _57=<tmp_26632>bat[:oid,:int]{8}) ] [ 0 usec # _57 := nil; ] [ 2 usec # sql.rsColumn(_92=0, _95="nilsgri.value", _59="nenc", _101="varchar", _102=255, _8=0, _65=<tmp_26604>bat[:oid,:str]{8}) ] [ 1 usec # _65 := nil; ] [ 2 usec # sql.rsColumn(_92=0, _95="nilsgri.value", _67="pos", _98="int", _99=32, _8=0, _73=<tmp_21205>bat[:oid,:int]{8}) ] [ 1 usec # _73 := nil; ] [ 2 usec # sql.rsColumn(_92=0, _95="nilsgri.value", _20="type", _98="int", _99=32, _8=0, _74=<tmp_25235>bat[:oid,:int]{8}) ] [ 1 usec # _74 := nil; ] [ 2 usec # sql.rsColumn(_92=0, _95="nilsgri.value", _7="name", _101="varchar", _102=255, _8=0, _75=<tmp_25032>bat[:oid,:str]{8}) ] [ 0 usec # _75 := nil; ] [ 2 usec # sql.rsColumn(_92=0, _95="nilsgri.value", _77="pathid", _98="int", _99=32, _8=0, _83=<tmp_26631>bat[:oid,:int]{8}) ] [ 14 usec # _83 := nil; ] [ 2 usec # sql.rsColumn(_92=0, _95="nilsgri.value", _85="complete", _108="boolean", _10=1, _8=0, _91=<tmp_22511>bat[:oid,:bit]{8}) ] [ 1 usec # _91 := nil; ] % nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value # table_name % value, doc, nenc, pos, type, name, pathid, complete # name % clob, int, varchar, int, int, varchar, int, boolean # type % 17, 1, 12, 8, 1, 6, 3, 5 # length [ "Jurgen Annevelink", 1, "1.1.1.1", 4, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.42.2.1", 1071, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 3, "author", 107, true ] [ "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 3, "author", 56, true ] [ 50 usec # sql.exportResult(_92=0, _110="") ] [ 51582 usec # user.s39_1(_4="Jurgen Annevelink", _5=3, _6="author") ] Timer 52.217 msec
Niels
Klem fra Nils
Q1: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' Q2: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' AND doc = 1 AND nenc = '1.1.1.1' AND pos = 4 Q3: SELECT * FROM value WHERE value = 'Jurgen Annevelink' AND type = 3 AND name = 'author' Q4: SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = '__bar' Q5: SELECT * FROM type_name_value WHERE type = 3 AND name = '!!foo' AND value = '__bar' Q6: SELECT * FROM type_name_value WHERE type = 3 AND name = '~~foo' AND value = '__bar' Q7: SELECT * FROM type_name_value WHERE type = 3000 AND name = '__foo' AND value = '__bar'
See runs below.
Number of rows SELECT COUNT(type) FROM type_name_value % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 12165109 ] Query time: 0.066192 SELECT COUNT(type) FROM value % nilsgri.value # table_name % count_no_nil_type # name % int # type % 8 # length [ 12165109 ] Query time: 0.058111
The PRIMARY KEY of type_name_value is (type,name,value,doc,nenc,pos) The PRIMARY KEY of value is (value,doc,nenc,pos)
Q1: Search 3 first, match 3 first. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 12, 8, 3, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.42.2.1", 1071, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 107, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 56, true ] Query time: 1.643438
Q1: Repeat to check for disk cache effects. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 12, 8, 3, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.42.2.1", 1071, 5, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 56, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 107, true ] [ 3, "author", "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 56, true ] Query time: 1.615900
Q2: Search entire, match entire. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = 'Jurgen Annevelink' AND doc = 1 AND nenc = '1.1.1.1' AND pos = 4 % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 6, 17, 1, 7, 1, 1, 5 # length [ 3, "author", "Jurgen Annevelink", 1, "1.1.1.1", 4, 5, true ] Query time: 11.073827
Q3: Use table value instead. SELECT * FROM value WHERE value = 'Jurgen Annevelink' AND type = 3 AND name = 'author' % nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value, nilsgri.value # table_name % value, doc, nenc, pos, type, name, pathid, complete # name % clob, int, varchar, int, int, varchar, int, boolean # type % 17, 1, 12, 8, 1, 6, 3, 5 # length [ "Jurgen Annevelink", 1, "1.1.1.1", 4, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.23579.2.1", 724888, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.2487.3.1", 81996, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.42.2.1", 1071, 3, "author", 5, true ] [ "Jurgen Annevelink", 1, "1.533986.2.1", 17294725, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.576139.3.1", 18745432, 3, "author", 56, true ] [ "Jurgen Annevelink", 1, "1.944085.1.1", 30104242, 3, "author", 107, true ] [ "Jurgen Annevelink", 1, "1.99173.2.1", 3191470, 3, "author", 56, true ] Query time: 0.000477
Q4: Search 3 first, match 2 first. SELECT * FROM type_name_value WHERE type = 3 AND name = 'author' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 1.617259
Q5: Search 3 first, match 1 first. '!' early in alphabet. SELECT * FROM type_name_value WHERE type = 3 AND name = '!!foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.530727
Q6: Search 3 first, match 1 first. '~' late in alphabet. SELECT * FROM type_name_value WHERE type = 3 AND name = '~~foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.534592
Q7: Search 3 first, match 0 first. SELECT * FROM type_name_value WHERE type = 3000 AND name = '__foo' AND value = '__bar' % nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value, nilsgri.type_name_value # table_name % type, name, value, doc, nenc, pos, pathid, complete # name % int, varchar, clob, int, varchar, int, int, boolean # type % 1, 0, 0, 1, 0, 1, 1, 5 # length Query time: 0.000407
Number of rows with type and name mathcing. SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name = 'author' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 7 # length [ 2730959 ] Query time: 0.683863
Number of rows with type mathcing. SELECT COUNT(type) FROM type_name_value WHERE type = 3 % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 10020569 ] Query time: 0.032573
SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name < '!!foo' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 1 # length [ 0 ] Query time: 0.161186
SELECT COUNT(type) FROM type_name_value WHERE type = 3 AND name < '~~foo' % nilsgri.type_name_value # table_name % count_no_nil_type # name % int # type % 8 # length [ 10020569 ] Query time: 0.341732
Number of rows with type mathcing.
real 0m18.372s user 0m0.005s sys 0m0.002s
------------------------------------------------------------------------- This SF.net email is sponsored by the 2008 JavaOne(SM) Conference Don't miss this year's exciting event. There's still time to save $100. Use priority code J8TL2D2. http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javao... _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
Niels Nes wrote:
On Wed, Apr 30, 2008 at 03:58:11PM +0200, Nils Grimsmo wrote:
I have tables with around 12 million rows and large primary keys. I find the performance for various queries to be rather strange.
* Why is Q2 (see below) so much slower than Q1? It is actually faster for me to use Q1 and do the rest of the matching myself.
* Why is Q3 so immensely much faster than Q1? I understand that the first part of the primary key is selective in Q3. Do I understand right if Q1 scans until "type" matches, then scans until "name" matches, then scans until "value" matches? No indexes are put on disk, right? Then the matching for Q3 should be scan until "value" matches, which shouldn't be more than at most three times faster, right (if three columns are read instead of one for Q1)? I do Q1 twice to look for cash effects, but none are seen... Could some columns from the table "value" be cached and kept, without giving room for those from "type_name_value"?
* Q4 has the same performance as Q1, as expected.
* Why isn't Q5 faster than Q6? 82% of the rows have type=3. Fewer should be scanned to find that "!!foo" doesn't exist than for "~~foo", as "!" has a low ASCII value, while "~" is high. (See COUNT queries at the bottom.)
The performance differences are related to the order in which the where conditions are applied. Please send us also the table create statement, then we can use 'explain' to see which order is taken. You could also run your queries prefixed with 'trace' to see where the time goes.
Been playing around a bit, and it seems to me like SELECTs are very slow unless the first part of the PRIMARY KEY is selective. ALTER TABLE base \ ADD PRIMARY KEY(doc, nenc, pos, complete) CREATE TABLE base2 \ AS SELECT nenc,doc,pos,type,name,pathID,complete,value \ FROM base \ ORDER BY nenc,doc,pos,complete WITH DATA ALTER TABLE base2 \ ADD PRIMARY KEY (nenc,doc,pos,complete) SELECT b.value \ FROM base AS b \ WHERE doc = 1 AND b.nenc = '1.1.1.1'; Timer 27344.739 msec SELECT b.value \ FROM _tmp2 AS b \ WHERE b.nenc = '1.1.1.1' AND doc = 1; Timer 0.773 msec The first query takes more than 35'000 times as long. All rows have doc=1. If no indexes are kept on disk, I do not understand why the first query should take more than twice as long as the second. Here is a query on base2 using LIKE instead. SELECT b.value \ FROM _tmp2 AS b \ WHERE b.nenc LIKE '1.1.1.1%' AND doc = 1; Timer 1538.009 msec As the condition is a proper prefix, I also don't understand why this is 2000 times slower, if no indexes are involved. (The number of results is the same.) According to Martin.Kersten@cwi.nl (in Message-id <4806471A.4090509@cwi.nl>), "Hash indices are used when needed and maintained under update. However, they are never saved on disk." Does this mean a hash index is never used in a SELECT? Or could it be just kept in memory if the table is not evicted? I could not find an answer in the Inner Core documentation. Klem fra Nils
participants (2)
-
Niels Nes -
Nils Grimsmo