Compiler makes suboptimal SQL when filtering on multi cardinality

[themajashurka]

Two differently written queries with the same meaning yield wildly different plans.

• EdgeDB Version: 5.7
• EdgeDB CLI Version: 6.2 dev
• OS Version: Linux Mint 22

Steps to Reproduce:

Slower

analyze 
select (select Product filter .model in {'1', '2'})
.<product[is ExactProduct]

──────────────────────────────────────────────────────────────────────────────── Fine-grained Query Plan ────────────────────────────────────────────────────────────────────────────────
   │ Time   Cost  Loops   Rows Width │ Plan Info
┬─ │ 15.5 547.73    1.0   16.0    32 │ HashJoin join_type=Inner
├─ │  8.3 321.29    1.0 9000.0    32 │ ➊ IndexScan relation_name=ExactProduct, scan_direction=Forward, index_name=ExactProduct.product index
╰┬ │  5.5 190.28    1.0    2.0    16 │ ➋ Hash hash_buckets=1024, hash_batches=1, peak_memory_usage=9kB
 │ │  5.5 190.28    1.0    2.0    16 │ IndexScan relation_name=Product, scan_direction=Forward, index_name=constraint 'std::exclusive' of property 'id' of object type 'default::Product'
 ╰ │  2.0   0.03 2000.0    0.0     0 │ ValuesScan

Faster

analyze 
select {
  (select Product filter .model = '1'),
  (select Product filter .model = '2')
}.<product[is ExactProduct]

───────────────────────────────────────────────────────────────── Fine-grained Query Plan ─────────────────────────────────────────────────────────────────
   │ Time Cost Loops Rows Width │ Plan Info
┬─ │  0.1 9.41   1.0 16.0    32 │ NestedLoop join_type=Inner
├┬ │  0.1 4.62   1.0  2.0    16 │ ➊ Unique
││ │  0.1 4.61   1.0  2.0    16 │ Sort sort_method=quicksort, sort_space_used=25kB, sort_space_type=Memory
││ │  0.0  4.6   1.0  2.0    16 │ Append
│├ │  0.0 2.29   1.0  1.0    16 │ ➋ IndexScan relation_name=Product, scan_direction=Forward, index_name=index of object type 'default::Product' on (.model)
│╰ │  0.0 2.29   1.0  1.0    16 │ ➌ IndexScan relation_name=Product, scan_direction=Forward, index_name=index of object type 'default::Product' on (.model)
╰─ │  0.0 2.35   2.0  8.0    32 │ ➊ IndexScan relation_name=ExactProduct, scan_direction=Forward, index_name=ExactProduct.product index

Schema:

type Product {
    required property model -> str;
    index on (.model);
}

type ExactProduct {
        required link product -> Product;
}

[themajashurka]

Ohh, turning off:

enable_hashagg = off
enable_hashjoin = off

changes the plan significantly:

──────────────────────────────────────────────────────── Fine-grained Query Plan ────────────────────────────────────────────────────────
   │ Time   Cost Loops Rows Width │ Plan Info
┬─ │  1.1 558.64   1.0 16.0    32 │ MergeJoin join_type=Inner
├─ │  0.1 321.29   1.0 17.0    32 │ ➊ IndexScan relation_name=ExactProduct, scan_direction=Forward, index_name=ExactProduct.product index
╰┬ │  1.0 167.36   1.0  2.0    16 │ ➋ Sort sort_method=quicksort, sort_space_used=25kB, sort_space_type=Memory
 │ │  1.0 115.03   1.0  2.0    16 │ SeqScan relation_name=Product
 ╰ │  0.0   0.03   1.0  2.0    32 │ ValuesScan