crud icon indicating copy to clipboard operation
crud copied to clipboard

crud doesn't build key from conditions

Open DifferentialOrange opened this issue 1 year ago • 1 comments

Let's consider the space with multipart unique key. One can be obtained from the playground with the following patch.

diff --git a/doc/playground.lua b/doc/playground.lua
index d4e547f..86293a7 100755
--- a/doc/playground.lua
+++ b/doc/playground.lua
@@ -75,6 +75,7 @@ box.once('customers', function()
     box.space.customers:create_index('primary_index', {
         parts = {
             {field = 1, type = 'unsigned'},
+            {field = 4, type = 'number'},
         },
     })
     box.space.customers:create_index('bucket_id', {
@@ -83,15 +84,12 @@ box.once('customers', function()
         },
         unique = false,
     })
-    box.space.customers:create_index('age', {
-        parts = {
-            {field = 4, type = 'number'},
-        },
-        unique = false,
-    })
 
     -- Fill the space.
     box.space.customers:insert({1, 477, 'Elizabeth', 12})
+    box.space.customers:insert({1, 477, 'Elizabeth', 13})
+    box.space.customers:insert({1, 477, 'Elizabeth', 14})
+    box.space.customers:insert({1, 477, 'Elizabeth', 15})
     box.space.customers:insert({2, 401, 'Mary', 46})
     box.space.customers:insert({3, 2804, 'David', 33})
     box.space.customers:insert({4, 1161, 'William', 81})

If the conditions are specified with index, it is a simple "get by key" procedure.

tarantool> crud.select('customers', {{'==', 'primary_index', {1, 15} }})
---
- metadata: [{'name': 'id', 'type': 'unsigned'}, {'name': 'bucket_id', 'type': 'unsigned'},
    {'name': 'name', 'type': 'string'}, {'name': 'age', 'type': 'number'}]
  rows:
  - [1, 477, 'Elizabeth', 15]
- null
...

tarantool> crud.stats()
---
- spaces:
    customers:
      select:
        details:
          tuples_lookup: 1
          tuples_fetched: 1
...

Stats say that we have found the requested data without any scanning.

If the conditions are specified with fields, it is a scan over all tuples satisfying the first condition.

tarantool> crud.select('customers', {{'==', 'id', 1}, {'==', 'age', 15}})
---
- metadata: [{'name': 'id', 'type': 'unsigned'}, {'name': 'bucket_id', 'type': 'unsigned'},
    {'name': 'name', 'type': 'string'}, {'name': 'age', 'type': 'number'}]
  rows:
  - [1, 477, 'Elizabeth', 15]
- null
...

tarantool> crud.stats()
---
- spaces:
    customers:
      select:
        details:
          tuples_lookup: 4
          tuples_fetched: 1
...

Stats say that we have scrolled through all id: 1 tuples until we have filtered ones satisfying age: 15 condition.

The same scan is performed in case of crud.select('customers', {{'==', 'id', 1}, {'==', 'age', 12}}): the module does not treat age: 12 condition as the part of the index both to start and to stop.

DifferentialOrange avatar Dec 19 '23 15:12 DifferentialOrange

The ticket missing bug label since it's about request optimization and not related to some behavior described with either documentation or tests. However, we already had made similar thing for sharding key extraction: https://github.com/tarantool/crud/pull/242

DifferentialOrange avatar Dec 19 '23 15:12 DifferentialOrange