[ASTERIXDB-3037] Array indexing not being used when it seems applicable

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 0.9.8
• Component/s: COMP - Compiler, IDX - Indexes, SQL - Translator SQL++
• Assignee: Glenn Justo Galvizo
• Reporter: Michael J. Carey
• Created: 28/May/22 00:47
• Updated: 28/May/22 05:29

Description

Why aren't array indexes useful for the queries below that have existential predicates on the bosses array?  Seems like they should apply!

 

DROP DATAVERSE HierarchyDemo IF EXISTS;
CREATE DATAVERSE HierarchyDemo;
USE HierarchyDemo;

CREATE TYPE genericType AS { _id: uuid };
CREATE COLLECTION employees(genericType)
    PRIMARY KEY _id AUTOGENERATED;
CREATE INDEX bossIdx ON employees (UNNEST bosses: INT) EXCLUDE UNKNOWN KEY;

INSERT INTO employees (
[
   {
     "eno":100, "name": "Matt", "sal": 950000,
     "level": 1,
     "boss": null,
     "bosses": []
   },
   {
     "eno":200, "name": "Gopi", "sal": 750000,
     "level": 2,
     "boss": 100,
     "bosses": [100]
   },
   {
     "eno":75, "name": "Mike", "sal": 50000,
     "level": 3,
     "boss": 200,
     "bosses": [100,200]
   },
   {
     "eno":50, "name": "Keshav", "sal": 500000,
     "level": 3,
     "boss": 200,
     "bosses": [100,200]
   },
   {
     "eno":80, "name": "Till", "sal": 400000,
     "level": 4,
     "boss": 50,
     "bosses": [100,200,50]
   },
   {
     "eno":130, "name": "Kamini", "sal": 450000,
     "level": 4,
     "boss": 50,
     "bosses": [100,200,50]
   },
   {
     "eno":1000, "name": "Dmitry", "sal": 300000,
     "level": 5,
     "boss": 80,
     "bosses": [100,200,50,80]
   },
   {
     "eno":300, "name": "Murali", "sal": 275000,
     "level": 5,
     "boss": 80,
     "bosses": [100,200,50,80]
   },
   {
     "eno":70, "name": "Sitaram", "sal": 300000,
     "level": 5,
     "boss": 130,
     "bosses": [100,200,50,130]
   }
]);

-- Show all employees
SELECT VALUE e FROM employees e;

-- Show Keshav's boss
SELECT VALUE b
FROM employees e, employees b
WHERE e.boss = b.eno
AND e.name = "Keshav";

-- Show all of Keshav's bosses (ordered by level)
LET kbossas = (SELECT VALUE k FROM employees k WHERE k.name = "Keshav")[0].bosses
SELECT VALUE e FROM employees e
WHERE e.eno IN kbossas
ORDER BY e.level DESC;

-- Show Keshav's direct reports
SELECT VALUE r
FROM employees e, employees r
WHERE r.boss = e.eno
AND e.name = "Keshav";

-- Show all of Keshav's reports
LET keshav = (SELECT VALUE t.eno FROM employees t WHERE t.name = "Keshav")[0]
SELECT VALUE e
FROM employees e
WHERE (SOME b IN e.bosses SATISFIES b = keshav);