Multi join aggregation performance

[martinlehoux]

Hi! I wanted to play around EdgeDB, and especially to compare it's performances to postgres.

I wrote a blog post months ago that explained how to improve on a naive multi join aggregation (https://blog.kagamino.dev/posts/postgres-aggregates/), so I wanted to see if EdgeDB was up to the task.

This is what I used for my test

schema:

module default {
  type Product {
    required name: str;
    required description: str;
    required price: decimal;
    required created_at: datetime {
      default := datetime_current();
    }
    multi orders: Order;
    multi reviews: Review;
  }
  type Order { 
    required order_date: datetime;
    required customer_name: str;
  }
  type Review {
    required review_date: datetime;
    required comment: str;
  }
}

data generation:

with module default

for i in (select range_unpack(range(0, 10_000)))
  insert Product {
    name := 'product_' ++ <str>i,
    description := 'This is a product description.',
    price := <decimal>(random() * 1000 + 1),
    orders := (
      for j in (select range_unpack(range(0, <int64>(random() * 20 + 1))))
        insert Order {
          order_date := datetime_current(),
          customer_name := 'customer_' ++ <str>j,
        }
    ),
    reviews := (
      for j in (select range_unpack(range(0, <int64>(random() * 200 + 1))))
        insert Review {
          review_date := datetime_current(),
          comment := 'Comment for review #' ++ <str>j,
        }
    )
  }

Then I tried to measure this query: select Product { id, order_count := count(.orders), review_count := count(.reviews) };. I don't have exact results yet, but it seems to run is about the same as my first test in Postgres.

So my question: is there a better way to write this query in EdgeDB, so that it would run faster?

[winnerlein]

You're using many-to-many links in EdgeDB (multi link without contraint exclusive), while you're using one-to-many links in SQL. EdgeDB implicitly creates a join-table for multi links. I'd change the schema to use a single link from review/order to product, and a computed backlink for ease of use.

[martinlehoux]

Oh OK, I see I didn't think I did that.

One issue I had with a previous test, using single link and backlinks, was that I was not able to create my test data. Do you have an idea of how to create this random data using the schema you suggest?

[winnerlein]

I think a WITH clause should solve that problem. Something like:

for i in <product_range>
    with
        product := insert Product { ... },
    for j in <order_range>
        insert Order { product := product }

[martinlehoux]

Thanks a lot for your help! I ended up with a python script to generate the data, but the query benchmarks is twice as fast as my previous attemp :)