Performance hit from in vs = on indexed properties

[jollygreenlaser]

• EdgeDB Version: 4.3
• EdgeDB CLI Version: EdgeDB CLI 4.0.2+500be79
• OS Version: OSX Sonoma 14.1.1

Steps to Reproduce:

1. Add index to .thing
2. Query using .thing in { option }
3. Query using .thing = option

Using edgedb analyze --expand it shows the index is never used for in but can be used for = when the filter creates an appropriately small group.

It seems that in is forcing the query planner to not account for indices on the property.

[jollygreenlaser]

I submitted a database dump and exact queries to Devon via Discord.

I tested on postgres and confirmed that postgres will use the index with in.

I can use and / or and string build my queries for now. However, this is both more annoying to code and leaves a lot of room for mistakes with injection. Would be nice to go back to array unpack.

[themajashurka]

+1 on this. Actually (on a bit different scenario), using an array literal, and an array_unpack around it uses the index:

SELECT A
FILTER .id in array_unpack(
  [
    <uuid>"042029c6-dee5-11ee-9d1f-377ec3aea48d",
    <uuid>"04202a3e-dee5-11ee-9d1f-0722bfa9b90a",
    <uuid>"04202e08-dee5-11ee-9d1f-87413e89e885", 
    <uuid>"04206e40-dee5-11ee-9d1f-af4f29f49a8f",
    <uuid>"0420cb6a-dee5-11ee-9d1f-33a575eb1f6f"
  ]
);
#0.053ms

────────────────────────────────── Fine-grained Query Plan ──────────────────────────────────
 │ Time  Cost Loops Rows Width │ Plan Info
 │  0.0 44.44   1.0  5.0    32 │ ➊ IndexOnlyScan relation_name=A, scan_direction=Forward,
 │                             │ index_name=constraint 'std::exclusive' of property 'id' of o
 │                             │ bject type 'default::A'

Whereas:

SELECT A
FILTER .id in {
   <uuid>"042029c6-dee5-11ee-9d1f-377ec3aea48d",
   <uuid>"04202a3e-dee5-11ee-9d1f-0722bfa9b90a",
   <uuid>"04202e08-dee5-11ee-9d1f-87413e89e885", 
   <uuid>"04206e40-dee5-11ee-9d1f-af4f29f49a8f",
   <uuid>"0420cb6a-dee5-11ee-9d1f-33a575eb1f6f"
};
#120ms

─────────────────── Fine-grained Query Plan ───────────────────
   │  Time     Cost Loops Rows Width │ Plan Info
┬─ │ 118.2 21104.78   1.0  5.0    32 │ ➊ SeqScan relation_name=A
╰┬ │   0.0     0.08   1.0  5.0    16 │ Append
 ├ │   0.0     0.01   1.0  1.0    16 │ Result
 ├ │   0.0     0.01   1.0  1.0    16 │ Result
 ├ │   0.0     0.01   1.0  1.0    16 │ Result
 ├ │   0.0     0.01   1.0  1.0    16 │ Result
 ╰ │   0.0     0.01   1.0  1.0    16 │ Result

[themajashurka]

I made a separate issue on this with a few numbers: #7027

[jollygreenlaser]

Another similar example:

type A {
  name: str {
     constraint exclusive;
  }
}

with
name := <str>(
  with s := json_array_unpack(to_json(<str>$0))
  select s
  filter <int16>s['rank'] = <int16>$1
)['team'],
select A
filter .name = name

This will not hit that index. But if I slap assert_single on name it will.