This worklog has been replaced with mariadb.org/jira

This site is here for historical purposes only. Do not add or edit tasks here!

 
 
 

WorkLog Frontpage Log in / Register
High-Level Description | Task Dependencies | High-Level Specification | Low-Level Design | File Attachments | User Comments | Time Estimates | Funding and Votes | Progress Reports

 Subquery optimization: Efficient NOT IN execution with NULLs
Title
Task ID68
Queue
Version
Status
Priority
Copies to
Created byTimour27 Nov 2009Done
Supervisor   
Lead Architect    
Architecture Review  
Implementor  
Code Review  
QA  
Documentation  
 High-Level Description
The goal of this task is to implement efficient execution of NOT IN
subquery predicates of the form:
  <oe_1,...,oe_n> NOT IN <non_correlated subquery>
when either some oe_i, or some subqury result column contains NULLs.

The problem with such predicates is that it is possible to use index
lookups only when neither argument of the predicate contains NULLs.
If some argument contains a NULL, then due to NULL semantics, it
plays the role of a wildcard. If we were to use regular index lookups,
then we would get 'no match' for some outer tuple (thus the predicate
evaluates to FALSE), while the SQL semantics means 'partial match', and
the predicate should evaluate to NULL.

This task implements an efficient algorithm to compute such 'parial
matches', where a NULL matches any value.
 Task Dependencies
Others waiting for Task 68Task 68 is waiting forGraph
91 MariaDB 5.3
 
 High-Level Specification
Contents
========================================================================

1. Initial idea as proposed by Igor
2. Algorithm for IN execution with partial matching
3. Directions for improvement


1. Initial idea as proposed by Igor
========================================================================

For each left side tuple (v_1,...,v_n) we have to find the following
set of rowids for the temp table containing N rows as the result of
materialization of the subquery:

  R= INTERSECT (rowid{a_i=v_i} UNION rowid{a_i is null} where i runs
trough all indexes from  [1..n] such that v_i is not null.

Bear in mind the following specifics of this intersection:
 (1) For each i: rowid{a_i=v_i} and rowid{a_i is null} are disjoint
 (2) For each i: rowid{a_i is null} is the same for each tuple,
     that is, this set is independent of the left-side tuples.

Due to (2) it makes sense to build  rowid{a_i is null} only once.
A good representation for such sets would be bitmaps:
- it requires minimum memory: not more than  N*n bits in total
- search of an element in a set is extremely cheap

Taken all above into account I could  suggest the following algorithm
to build R:

  Using indexes (read about them below) for each column participating
  in the intersection, merge ordered sets rowid{a_i=v_i} in the
  following manner.
  If a rowid r has been encountered maximum in k sets
    rowid{a_i1=v_i1},...,rowid(a_ik=v_ik),
  then it has to be checked against all rowid{a_i=v_i} such that i is
  not in {i1,...,ik}.
  As soon as we fail to find r in one of  these sets we discard it.
  If r has been found in all of them then r belongs to the set R.

Here we use the property (1):
any r from rowid{a_i=v_i} UNION rowid{a_i is null} is either
belongs to rowid{a_i=v_i} or to rowid{a_i is null}. From this we can
infer that for any r from R indexes a_i can be uniquely divided into
two groups:
- one contains indexes a_i where r belongs to the sets rowid{a_i=v_i},
- the other contains indexes a_j such that r belongs to
  rowid{a_j is null}.

Now let's talk how to get elements from rowid{a_i=v_i} in a sorted
order needed for the merge procedure. We could use BTREE indexes for
temp table. But they are rather expensive and take a lot of memory as
the are implemented with RB trees.
I would suggest creating for each column from the temporary table just
an array of rowids sorted by the value from column a.
Index lookup in such an array is cheap. It's also rather cheap to check
that the next rowid refers to a row with a different value in column a.
The array can be created on demand.

2. Algorithm for IN execution with partial matching
========================================================================

2.1 Below is shown the top-level algorithm to execute an IN predicate
with partial matching. This algorithm is essentially the implementation
of Item_subselect:exec().

int lookup_with_null_semantics(outer_ref[], mat_subquery)
{
  if (index_lookup(outer_ref, mat_subquery)
    return TRUE
  else
  {
    /*
      Check if there is a partial match (UNKNOWN) or no match (NULL).
    */
    if (this is the first partial match)
    {
      vkey[] = build array of value keys for each NULL-able column
               of mat_subquery.
      nkey[] = build a bitmap NULL index for each column of mat_subquery
               that contains NULLs
      nonull_key = build a key over all non-NULL columns of mat_subquery
    }
    if (partial_match(outer_ref, vkey[], nkey[], nonull_key)
      return UNKNOWN
    else
      return FALSE
  }
}

2.2 The implementation of partial matching is as follows

/*
  Assumptions:
  - It has already been checked if there is a complete match by a
    regular index lookup, and the test failed.
  - It has already been checked if there is a complete NULL row,
    and if there was we wouldn't call this function. Thus we assume
    that there is no complete NULL row.
  - Not all vidx_i are empty, but some can be empty. If all were empty,
    then the only possibility for a match is a complete NULL row, which
    we already checked.

  @param outer_ref - the uter (left) IN argument.
  @param vidx[] - array of value keys
    Ordered sequences of rowids of the corresponding columns a_i, such
    that all rowids in idx_i are the ones where column a_i contains some
    value or NULL. Each idx_i is derived dynamically, for each different
    left argument of an IN predicate.
  @param nidx[] - array of NULL keys
    Bitmpas, one per each column, where a bit is set if the corresponding
    row has a NULL value for the corresponding column.
  @nonull_key - the only key over all columns of the materialized subquery
                that do not contain NULLs

  @returns
    @retval FALSE if there is no match
    @retval TRUE if there is a partial match
*/

Boolean partial_match(outer_ref, vkey[], nkey[], nonull_key)
{
  /* Set of the keys (columns) that form a partial match. */
  Set matching_keys = {}
  /* A subset of all keys that need to be checked for NULL matches. */
  Set null_keys = {}
  Int min_key /* Key that contains the current minimum position. */
  Int min_row /* Current row number of min_key. */
  Int cur_min_key, cur_min_row
  PriorityQueue pq

  if (nonull_key && ! nonull_key->lookup(outer_ref))
    return FALSE

  for (i = 1; i <= n; i++)
  {
    if (vkey[i] != nonull_key)
      vkey[i].lookup(outer_ref)
    if (! vkey[i].is_eof())
      pq.insert(i)
  }
  /*
    Not all value keys are empty, thus we don't have only NULL
    keys. If we had, the only possible match is a NULL row, and
    we cheked there is no such row, therefore the result is known
    to be FALSE.
    In fact this algorithm makes sense for at least two non-NULL
    columns.
  */
  assert(pq.elements > 1)

  (min_key, min_row) = pq.pop()
  matching_keys.add(min_key)
  vkey[min_key].next()
  if (! vkey[min_key].is_eof())
    pq.insert(min_key)

  while (TRUE)
  {
    (cur_min_key, cur_min_row) = pq.pop()

    if (cur_min_row == min_row)
    {
      matching_keys.add(cur_min_key)
      /* There cannot be a complete match, as we already checked for one. */
      assert(matching_keys.elements < n)
    }
    else if (vkey[cur_min_key] == nonull_key)
    {
      /*
        The non-NULL key has no corresponding NULL index, so we know for
        sure that the row 'min_row' is not a match.
      */
      (min_key, min_row) = (cur_min_key, cur_min_row)
      matching_keys = {min_key}
    }
    else
    {
      assert(cur_min_row > min_row) /* Follows from the use of PQ. */
      null_keys = set_difference(all keys vkey[], matching_keys)
      /*
        Check if all null_keys contain a NULL at row 'min_row'. The procedure
        internally checks all keys in a special precomputed order. A prior
        procedure determines an optimal order and a mapping idx_no -> idx_order
        (encoded as an array).

        This procedure makes sure not to match the non-NULL column.
      */
      if (test_null_row(null_keys, min_row))
        return TRUE
      else
      {
        (min_key, min_row) = (cur_min_key, cur_min_row)
        matching_keys = {min_key}
      }
    }

    vkey[cur_min_key].next()
    if (! vkey[cur_min_key].is_eof())
      pq.insert(cur_min_key)
    else if (vkey[cur_min_key] == nonull_key)
    {
      /*
        If there can't be more matches for the nonull_key, we know for sure
        there is no match, since there is no possible NULL match.
      */
      return FALSE
    }

    if (pq.is_empty())
    {
      /* Check the last row of the last column in PQ for NULL matches. */
      null_keys = set_difference(all keys vkey[], matching_keys)
      if (test_null_row(null_keys, min_row))
        return TRUE
      else
        return FALSE
    }
  }

  /* We should never get here. */
  assert(FALSE)
  return FALSE
}


3. Directions for improvement
========================================================================

Other consideration that may be taken into account:

1. If columns a_j1,...,a_jm do not contain null values in the temporary
table at all and v_j1,...,v_jm cannot be null, create for these columns
only one index array (and of course do not create any bitmaps for them).
[done]

2. Consider the ratio d(a_i)=N'(a_i)/V(a_i), where N'(a_i) is the number
of rows, where a_i  is not null  and V(a_i) is the number of distinct
values for a_i excluding nulls.
If d(a_i) is close to N'(a_i) then do not create any index array: check
whether there is a match running through  the records that have been
filtered in. Anyway if d(a_i) is close to N'(a_i) then the intersection
 with rowid{a_i=v_i} will not reduce the number of remaining rowids
significantly.
In other words is V(a_i) exceeds some threshold there is no sense to
create an index for a_i.
If additionally N-N'(a_i) is small do not create a bitmap for this
column either.

3. If for a column a_i d(a_i) is not close to N'(a_i), but N-N'(a_i) is
small  a sorted array of rowids from the set  rowid{a_i is null} can be
used instead of a bitmap.

4. We always have a match if R0= INTERSECT rowid{a_i is null} is not
empty. Here i runs through all indexes from  [1..n] such that v_i is not
null. For a given subset of columns this fact has to be checked only
once. It can be easily done with bitmap intersection.

5. If v1,...,vn never can be a null, then indexes (sorted arrays) can be
created only for rows with nulls.

6. If v1,...,vn never can be a null and number of rows with nulls is
small do not create indexes and do not create bitmaps.

7. If you get a row with nulls in all columns stop filling the temporary
table and return UNKNOWN for any tuple <v1,...,vn>.
[This is wrong, because if we don't fill the whole temp table, there may
 be some tuple(s) that would match some outer tuple. In such cases, if we
 stop filling the temp table, we would miss a TRUE result. Having a partial
 match doesn't preclude us from having a complete match].

8. [timour]
   Consider that due to materialization, we already have a unique index
on all columns <a_1,..., a_n>. We can use the first key part of this index
over column a_1, instead of the index rowid{a_i=v_i}. Thus we can avoid
creating the index rowid{a_i=v_i}.
 Low-Level Design
 File Attachments
 NameTypeSizeByDate
 User Comments
 Time Estimates
NameHours WorkedLast Updated
Total0 
 Hrs WorkedProgressCurrentOriginal
Total000
 
 Funding and Votes
Votes: 0: 0%
 Make vote: Useless    Nice to have    Important    Very important    

Funding: 0 offers, total 0 Euro
 Progress Reports
(Psergey - Sat, 09 Oct 2010, 10:51
    
Dependency created: WL#91 now depends on WL#68

(Guest - Sun, 13 Jun 2010, 16:38
    
Dependency deleted: WL#91 no longer depends on WL#68

(Psergey - Sun, 28 Feb 2010, 14:56
    
Dependency created: WL#91 now depends on WL#68

(Psergey - Sun, 28 Feb 2010, 14:54
    
Dependency deleted: WL#94 no longer depends on WL#68

(Psergey - Sun, 28 Feb 2010, 14:08
    
Dependency created: WL#94 now depends on WL#68

(Guest - Sat, 27 Feb 2010, 10:11
    
Status updated.
No change.

(Guest - Sat, 27 Feb 2010, 10:11
    
Status updated.
--- /tmp/wklog.68.old.24229	2010-02-27 10:11:57.000000000 +0000
+++ /tmp/wklog.68.new.24229	2010-02-27 10:11:57.000000000 +0000
@@ -1 +1 @@
-Assigned
+In-Progress

(Timour - Mon, 22 Feb 2010, 17:39
    
High-Level Specification modified.
--- /tmp/wklog.68.old.17116	2010-02-22 17:39:48.000000000 +0200
+++ /tmp/wklog.68.new.17116	2010-02-22 17:39:48.000000000 +0200
@@ -233,6 +233,7 @@
 1. If columns a_j1,...,a_jm do not contain null values in the temporary
 table at all and v_j1,...,v_jm cannot be null, create for these columns
 only one index array (and of course do not create any bitmaps for them).
+[done]
 
 2. Consider the ratio d(a_i)=N'(a_i)/V(a_i), where N'(a_i) is the number
 of rows, where a_i  is not null  and V(a_i) is the number of distinct
@@ -264,6 +265,10 @@
 
 7. If you get a row with nulls in all columns stop filling the temporary
 table and return UNKNOWN for any tuple <v1,...,vn>.
+[This is wrong, because if we don't fill the whole temp table, there may
+ be some tuple(s) that would match some outer tuple. In such cases, if we
+ stop filling the temp table, we would miss a TRUE result. Having a partial
+ match doesn't preclude us from having a complete match].
 
 8. [timour]
    Consider that due to materialization, we already have a unique index

(Timour - Tue, 19 Jan 2010, 18:44
    
High-Level Specification modified.
--- /tmp/wklog.68.old.22569	2010-01-19 18:44:01.000000000 +0200
+++ /tmp/wklog.68.new.22569	2010-01-19 18:44:01.000000000 +0200
@@ -132,11 +132,10 @@
 
   if (nonull_key && ! nonull_key->lookup(outer_ref))
     return FALSE
-  if (nonull_key)
-    pq.insert(nonull_key)
 
   for (i = 1; i <= n; i++)
   {
+    if (vkey[i] != nonull_key)
     vkey[i].lookup(outer_ref)
     if (! vkey[i].is_eof())
       pq.insert(i)
@@ -167,7 +166,7 @@
       /* There cannot be a complete match, as we already checked for one. */
       assert(matching_keys.elements < n)
     }
-    else if (cur_min_key == nonull_key)
+    else if (vkey[cur_min_key] == nonull_key)
     {
       /*
         The non-NULL key has no corresponding NULL index, so we know for
@@ -183,8 +182,10 @@
       /*
         Check if all null_keys contain a NULL at row 'min_row'. The procedure
         internally checks all keys in a special precomputed order. A prior
-        procedure determines an optimal order and a mapping
-        idx_no -> idx_order (encoded as an array).
+        procedure determines an optimal order and a mapping idx_no -> idx_order
+        (encoded as an array).
+
+        This procedure makes sure not to match the non-NULL column.
       */
       if (test_null_row(null_keys, min_row))
         return TRUE
@@ -198,6 +199,14 @@
     vkey[cur_min_key].next()
     if (! vkey[cur_min_key].is_eof())
       pq.insert(cur_min_key)
+    else if (vkey[cur_min_key] == nonull_key)
+    {
+      /*
+        If there can't be more matches for the nonull_key, we know for sure
+        there is no match, since there is no possible NULL match.
+      */
+      return FALSE
+    }
 
     if (pq.is_empty())
     {
@@ -216,7 +225,6 @@
 }
 
 
-
 3. Directions for improvement
 ========================================================================
 

(Timour - Tue, 19 Jan 2010, 18:29
    
High-Level Specification modified.
--- /tmp/wklog.68.old.21045	2010-01-19 18:29:12.000000000 +0200
+++ /tmp/wklog.68.new.21045	2010-01-19 18:29:12.000000000 +0200
@@ -132,6 +132,8 @@
 
   if (nonull_key && ! nonull_key->lookup(outer_ref))
     return FALSE
+  if (nonull_key)
+    pq.insert(nonull_key)
 
   for (i = 1; i <= n; i++)
   {

(Guest - Tue, 19 Jan 2010, 18:15
    
High-Level Specification modified.
--- /tmp/wklog.68.old.19825	2010-01-19 18:15:30.000000000 +0200
+++ /tmp/wklog.68.new.19825	2010-01-19 18:15:30.000000000 +0200
@@ -1,8 +1,16 @@
-This a copy of the initial algorithm proposed by Igor:
-======================================================
+Contents
+========================================================================
 
-For each left side tuple (v_1,...,v_n) we have to find the following set
-of rowids for the temp table containing N rows as the result of
+1. Initial idea as proposed by Igor
+2. Algorithm for IN execution with partial matching
+3. Directions for improvement
+
+
+1. Initial idea as proposed by Igor
+========================================================================
+
+For each left side tuple (v_1,...,v_n) we have to find the following
+set of rowids for the temp table containing N rows as the result of
 materialization of the subquery:
 
   R= INTERSECT (rowid{a_i=v_i} UNION rowid{a_i is null} where i runs
@@ -18,38 +26,198 @@
 - it requires minimum memory: not more than  N*n bits in total
 - search of an element in a set is extremely cheap
 
-Taken all above into account I could  suggest the following algorithm to
-build R:
+Taken all above into account I could  suggest the following algorithm
+to build R:
 
-  Using indexes (read about them below) for each column participating in the
-  intersection,
-  merge ordered sets rowid{a_i=v_i} in the following manner.
+  Using indexes (read about them below) for each column participating
+  in the intersection, merge ordered sets rowid{a_i=v_i} in the
+  following manner.
   If a rowid r has been encountered maximum in k sets
-rowid{a_i1=v_i1},...,rowid(a_ik=v_ik),
+    rowid{a_i1=v_i1},...,rowid(a_ik=v_ik),
   then it has to be checked against all rowid{a_i=v_i} such that i is
-not in {i1,...,ik}.
+  not in {i1,...,ik}.
   As soon as we fail to find r in one of  these sets we discard it.
   If r has been found in all of them then r belongs to the set R.
 
-Here we use the property (1): any r from rowid{a_i=v_i} UNION rowid{a_i
-is null} is either
+Here we use the property (1):
+any r from rowid{a_i=v_i} UNION rowid{a_i is null} is either
 belongs to rowid{a_i=v_i} or to rowid{a_i is null}. From this we can
-infer that for any r from R
-indexes a_i can be uniquely divided into two groups: one contains
-indexes a_i where r belongs to
-the sets  rowid{a_i=v_i}, the other contains indexes a_j such that r
-belongs to  rowid{a_j is null}.
-
-Now let's talk how to get elements from rowid{a_i=v_i} in a sorted order
-needed for the merge procedure. We could use BTREE indexes for temp
-table. But they are rather expensive and
-take a lot of memory as the are implemented with RB trees.
+infer that for any r from R indexes a_i can be uniquely divided into
+two groups:
+- one contains indexes a_i where r belongs to the sets rowid{a_i=v_i},
+- the other contains indexes a_j such that r belongs to
+  rowid{a_j is null}.
+
+Now let's talk how to get elements from rowid{a_i=v_i} in a sorted
+order needed for the merge procedure. We could use BTREE indexes for
+temp table. But they are rather expensive and take a lot of memory as
+the are implemented with RB trees.
 I would suggest creating for each column from the temporary table just
 an array of rowids sorted by the value from column a.
 Index lookup in such an array is cheap. It's also rather cheap to check
 that the next rowid refers to a row with a different value in column a.
 The array can be created on demand.
 
+2. Algorithm for IN execution with partial matching
+========================================================================
+
+2.1 Below is shown the top-level algorithm to execute an IN predicate
+with partial matching. This algorithm is essentially the implementation
+of Item_subselect:exec().
+
+int lookup_with_null_semantics(outer_ref[], mat_subquery)
+{
+  if (index_lookup(outer_ref, mat_subquery)
+    return TRUE
+  else
+  {
+    /*
+      Check if there is a partial match (UNKNOWN) or no match (NULL).
+    */
+    if (this is the first partial match)
+    {
+      vkey[] = build array of value keys for each NULL-able column
+               of mat_subquery.
+      nkey[] = build a bitmap NULL index for each column of mat_subquery
+               that contains NULLs
+      nonull_key = build a key over all non-NULL columns of mat_subquery
+    }
+    if (partial_match(outer_ref, vkey[], nkey[], nonull_key)
+      return UNKNOWN
+    else
+      return FALSE
+  }
+}
+
+2.2 The implementation of partial matching is as follows
+
+/*
+  Assumptions:
+  - It has already been checked if there is a complete match by a
+    regular index lookup, and the test failed.
+  - It has already been checked if there is a complete NULL row,
+    and if there was we wouldn't call this function. Thus we assume
+    that there is no complete NULL row.
+  - Not all vidx_i are empty, but some can be empty. If all were empty,
+    then the only possibility for a match is a complete NULL row, which
+    we already checked.
+
+  @param outer_ref - the uter (left) IN argument.
+  @param vidx[] - array of value keys
+    Ordered sequences of rowids of the corresponding columns a_i, such
+    that all rowids in idx_i are the ones where column a_i contains some
+    value or NULL. Each idx_i is derived dynamically, for each different
+    left argument of an IN predicate.
+  @param nidx[] - array of NULL keys
+    Bitmpas, one per each column, where a bit is set if the corresponding
+    row has a NULL value for the corresponding column.
+  @nonull_key - the only key over all columns of the materialized subquery
+                that do not contain NULLs
+
+  @returns
+    @retval FALSE if there is no match
+    @retval TRUE if there is a partial match
+*/
+
+Boolean partial_match(outer_ref, vkey[], nkey[], nonull_key)
+{
+  /* Set of the keys (columns) that form a partial match. */
+  Set matching_keys = {}
+  /* A subset of all keys that need to be checked for NULL matches. */
+  Set null_keys = {}
+  Int min_key /* Key that contains the current minimum position. */
+  Int min_row /* Current row number of min_key. */
+  Int cur_min_key, cur_min_row
+  PriorityQueue pq
+
+  if (nonull_key && ! nonull_key->lookup(outer_ref))
+    return FALSE
+
+  for (i = 1; i <= n; i++)
+  {
+    vkey[i].lookup(outer_ref)
+    if (! vkey[i].is_eof())
+      pq.insert(i)
+  }
+  /*
+    Not all value keys are empty, thus we don't have only NULL
+    keys. If we had, the only possible match is a NULL row, and
+    we cheked there is no such row, therefore the result is known
+    to be FALSE.
+    In fact this algorithm makes sense for at least two non-NULL
+    columns.
+  */
+  assert(pq.elements > 1)
+
+  (min_key, min_row) = pq.pop()
+  matching_keys.add(min_key)
+  vkey[min_key].next()
+  if (! vkey[min_key].is_eof())
+    pq.insert(min_key)
+
+  while (TRUE)
+  {
+    (cur_min_key, cur_min_row) = pq.pop()
+
+    if (cur_min_row == min_row)
+    {
+      matching_keys.add(cur_min_key)
+      /* There cannot be a complete match, as we already checked for one. */
+      assert(matching_keys.elements < n)
+    }
+    else if (cur_min_key == nonull_key)
+    {
+      /*
+        The non-NULL key has no corresponding NULL index, so we know for
+        sure that the row 'min_row' is not a match.
+      */
+      (min_key, min_row) = (cur_min_key, cur_min_row)
+      matching_keys = {min_key}
+    }
+    else
+    {
+      assert(cur_min_row > min_row) /* Follows from the use of PQ. */
+      null_keys = set_difference(all keys vkey[], matching_keys)
+      /*
+        Check if all null_keys contain a NULL at row 'min_row'. The procedure
+        internally checks all keys in a special precomputed order. A prior
+        procedure determines an optimal order and a mapping
+        idx_no -> idx_order (encoded as an array).
+      */
+      if (test_null_row(null_keys, min_row))
+        return TRUE
+      else
+      {
+        (min_key, min_row) = (cur_min_key, cur_min_row)
+        matching_keys = {min_key}
+      }
+    }
+
+    vkey[cur_min_key].next()
+    if (! vkey[cur_min_key].is_eof())
+      pq.insert(cur_min_key)
+
+    if (pq.is_empty())
+    {
+      /* Check the last row of the last column in PQ for NULL matches. */
+      null_keys = set_difference(all keys vkey[], matching_keys)
+      if (test_null_row(null_keys, min_row))
+        return TRUE
+      else
+        return FALSE
+    }
+  }
+
+  /* We should never get here. */
+  assert(FALSE)
+  return FALSE
+}
+
+
+
+3. Directions for improvement
+========================================================================
+
 Other consideration that may be taken into account:
 
 1. If columns a_j1,...,a_jm do not contain null values in the temporary

(Timour - Sun, 06 Dec 2009, 14:36
    
High-Level Specification modified.
--- /tmp/wklog.68.old.12919	2009-12-06 14:36:18.000000000 +0200
+++ /tmp/wklog.68.new.12919	2009-12-06 14:36:18.000000000 +0200
@@ -87,3 +87,8 @@
 7. If you get a row with nulls in all columns stop filling the temporary
 table and return UNKNOWN for any tuple <v1,...,vn>.
 
+8. [timour]
+   Consider that due to materialization, we already have a unique index
+on all columns <a_1,..., a_n>. We can use the first key part of this index
+over column a_1, instead of the index rowid{a_i=v_i}. Thus we can avoid
+creating the index rowid{a_i=v_i}.

(Timour - Fri, 04 Dec 2009, 14:04
    
High-Level Specification modified.
--- /tmp/wklog.68.old.16724	2009-12-04 14:04:28.000000000 +0200
+++ /tmp/wklog.68.new.16724	2009-12-04 14:04:28.000000000 +0200
@@ -10,7 +10,8 @@
 
 Bear in mind the following specifics of this intersection:
  (1) For each i: rowid{a_i=v_i} and rowid{a_i is null} are disjoint
- (2) For each i: rowid{a_i is null} is the same for each tuple
+ (2) For each i: rowid{a_i is null} is the same for each tuple,
+     that is, this set is independent of the left-side tuples.
 
 Due to (2) it makes sense to build  rowid{a_i is null} only once.
 A good representation for such sets would be bitmaps:

(Timour - Fri, 04 Dec 2009, 11:27
    
Version updated.
--- /tmp/wklog.68.old.5257	2009-12-04 11:27:11.000000000 +0200
+++ /tmp/wklog.68.new.5257	2009-12-04 11:27:11.000000000 +0200
@@ -1 +1 @@
-Benchmarks-3.0
+Server-9.x

(Timour - Fri, 04 Dec 2009, 11:27
    
Category updated.
--- /tmp/wklog.68.old.5242	2009-12-04 11:27:02.000000000 +0200
+++ /tmp/wklog.68.new.5242	2009-12-04 11:27:02.000000000 +0200
@@ -1 +1 @@
-Server-RawIdeaBin
+Server-Sprint

(Timour - Fri, 04 Dec 2009, 11:27
    
Status updated.
--- /tmp/wklog.68.old.5242	2009-12-04 11:27:02.000000000 +0200
+++ /tmp/wklog.68.new.5242	2009-12-04 11:27:02.000000000 +0200
@@ -1 +1 @@
-Un-Assigned
+Assigned

(Timour - Fri, 04 Dec 2009, 11:26
    
High-Level Specification modified.
--- /tmp/wklog.68.old.5182	2009-12-04 11:26:25.000000000 +0200
+++ /tmp/wklog.68.new.5182	2009-12-04 11:26:25.000000000 +0200
@@ -50,23 +50,39 @@
 The array can be created on demand.
 
 Other consideration that may be taken into account:
+
 1. If columns a_j1,...,a_jm do not contain null values in the temporary
-table at all, create for them only one index array (and of course do not
-create any bitmaps for them).
-2. Consider the ratio d(a_i)=N'/ V(a_i), where N' is the number of rows
-where a_i  is not null  and V(a_i) is the number of distinct values for
-a_i excluding nulls.
- If  d(a_i) is close to 1 then do not create any index array: check
+table at all and v_j1,...,v_jm cannot be null, create for these columns
+only one index array (and of course do not create any bitmaps for them).
+
+2. Consider the ratio d(a_i)=N'(a_i)/V(a_i), where N'(a_i) is the number
+of rows, where a_i  is not null  and V(a_i) is the number of distinct
+values for a_i excluding nulls.
+If d(a_i) is close to N'(a_i) then do not create any index array: check
 whether there is a match running through  the records that have been
-filtered in. Anyway if d(a_i) is close to 1 then a intersection  with
-rowid{a_i=v_i} would not reduce the number of remaining rowids
+filtered in. Anyway if d(a_i) is close to N'(a_i) then the intersection
+ with rowid{a_i=v_i} will not reduce the number of remaining rowids
 significantly.
- If additionally N-N' is small do not create a bitmap for this column
-either.
-3. If for a column a_i d(a_i) is not close to 1, but N-N' is small  a
-sorted array of rowids from the set  rowid{a_i is null} can be used
-instead of a bitmap.
+In other words is V(a_i) exceeds some threshold there is no sense to
+create an index for a_i.
+If additionally N-N'(a_i) is small do not create a bitmap for this
+column either.
+
+3. If for a column a_i d(a_i) is not close to N'(a_i), but N-N'(a_i) is
+small  a sorted array of rowids from the set  rowid{a_i is null} can be
+used instead of a bitmap.
+
 4. We always have a match if R0= INTERSECT rowid{a_i is null} is not
 empty. Here i runs through all indexes from  [1..n] such that v_i is not
 null. For a given subset of columns this fact has to be checked only
 once. It can be easily done with bitmap intersection.
+
+5. If v1,...,vn never can be a null, then indexes (sorted arrays) can be
+created only for rows with nulls.
+
+6. If v1,...,vn never can be a null and number of rows with nulls is
+small do not create indexes and do not create bitmaps.
+
+7. If you get a row with nulls in all columns stop filling the temporary
+table and return UNKNOWN for any tuple <v1,...,vn>.
+

(Timour - Fri, 27 Nov 2009, 13:23
    
High-Level Specification modified.
--- /tmp/wklog.68.old.17140	2009-11-27 11:23:17.000000000 +0000
+++ /tmp/wklog.68.new.17140	2009-11-27 11:23:17.000000000 +0000
@@ -1 +1,72 @@
+This a copy of the initial algorithm proposed by Igor:
+======================================================
 
+For each left side tuple (v_1,...,v_n) we have to find the following set
+of rowids for the temp table containing N rows as the result of
+materialization of the subquery:
+
+  R= INTERSECT (rowid{a_i=v_i} UNION rowid{a_i is null} where i runs
+trough all indexes from  [1..n] such that v_i is not null.
+
+Bear in mind the following specifics of this intersection:
+ (1) For each i: rowid{a_i=v_i} and rowid{a_i is null} are disjoint
+ (2) For each i: rowid{a_i is null} is the same for each tuple
+
+Due to (2) it makes sense to build  rowid{a_i is null} only once.
+A good representation for such sets would be bitmaps:
+- it requires minimum memory: not more than  N*n bits in total
+- search of an element in a set is extremely cheap
+
+Taken all above into account I could  suggest the following algorithm to
+build R:
+
+  Using indexes (read about them below) for each column participating in the
+  intersection,
+  merge ordered sets rowid{a_i=v_i} in the following manner.
+  If a rowid r has been encountered maximum in k sets
+rowid{a_i1=v_i1},...,rowid(a_ik=v_ik),
+  then it has to be checked against all rowid{a_i=v_i} such that i is
+not in {i1,...,ik}.
+  As soon as we fail to find r in one of  these sets we discard it.
+  If r has been found in all of them then r belongs to the set R.
+
+Here we use the property (1): any r from rowid{a_i=v_i} UNION rowid{a_i
+is null} is either
+belongs to rowid{a_i=v_i} or to rowid{a_i is null}. From this we can
+infer that for any r from R
+indexes a_i can be uniquely divided into two groups: one contains
+indexes a_i where r belongs to
+the sets  rowid{a_i=v_i}, the other contains indexes a_j such that r
+belongs to  rowid{a_j is null}.
+
+Now let's talk how to get elements from rowid{a_i=v_i} in a sorted order
+needed for the merge procedure. We could use BTREE indexes for temp
+table. But they are rather expensive and
+take a lot of memory as the are implemented with RB trees.
+I would suggest creating for each column from the temporary table just
+an array of rowids sorted by the value from column a.
+Index lookup in such an array is cheap. It's also rather cheap to check
+that the next rowid refers to a row with a different value in column a.
+The array can be created on demand.
+
+Other consideration that may be taken into account:
+1. If columns a_j1,...,a_jm do not contain null values in the temporary
+table at all, create for them only one index array (and of course do not
+create any bitmaps for them).
+2. Consider the ratio d(a_i)=N'/ V(a_i), where N' is the number of rows
+where a_i  is not null  and V(a_i) is the number of distinct values for
+a_i excluding nulls.
+ If  d(a_i) is close to 1 then do not create any index array: check
+whether there is a match running through  the records that have been
+filtered in. Anyway if d(a_i) is close to 1 then a intersection  with
+rowid{a_i=v_i} would not reduce the number of remaining rowids
+significantly.
+ If additionally N-N' is small do not create a bitmap for this column
+either.
+3. If for a column a_i d(a_i) is not close to 1, but N-N' is small  a
+sorted array of rowids from the set  rowid{a_i is null} can be used
+instead of a bitmap.
+4. We always have a match if R0= INTERSECT rowid{a_i is null} is not
+empty. Here i runs through all indexes from  [1..n] such that v_i is not
+null. For a given subset of columns this fact has to be checked only
+once. It can be easily done with bitmap intersection.


Report Generator:
 
Saved Reports:

WorkLog v4.0.0
  © 2010  Sergei Golubchik and Monty Program AB
  © 2004  Andrew Sweger <yDNA@perlocity.org> and Addnorya
  © 2003  Matt Wagner <matt@mysql.com> and MySQL AB