Improve perf of get_channel_descendants_including_self

[mrnugget]

In this morning's incident this query stood out with a max runtime of

1s.

We run it in quite a few places, so we dug in and tried to optimize it.

Turns out that: a) the index on channels.parent_path wasn't used b) query can be simplifed and sped-up when using a CTE and not a cross-join

While testing this on production with EXPLAIN ANALYZE the query previously took ~145ms to run for our main zed channel. After the changes here it took ~1ms.

Release Notes:

• N/A

[github-actions[bot]]

Squawk Report

🚒 1 violations across 1 file(s)

---

crates/collab/migrations/20240129105000_add_gin_index_on_channels_pathname.sql

CREATE INDEX trigram_index_channels_on_parent_path ON channels USING GIN(parent_path gin_trgm_ops);

🚒 Rule Violations (1)

crates/collab/migrations/20240129105000_add_gin_index_on_channels_pathname.sql:1:0: warning: require-concurrent-index-creation

   1 | CREATE INDEX trigram_index_channels_on_parent_path ON channels USING GIN(parent_path gin_trgm_ops);

  note: Creating an index blocks writes.
  help: Create the index CONCURRENTLY.

---

📚 More info on rules

⚡️ Powered by Squawk (0.26.0), a linter for PostgreSQL, focused on migrations

[mrnugget]

I created the migration by hand which I'm not sure is correct (based on the timestamps I saw on the other migrations).

[maxdeviant]

I created the migration by hand which I'm not sure is correct (based on the timestamps I saw on the other migrations).

Yea, there’s a sqlx command you can use to create migrations.

I think it’s something like ./script/sqlx migrate add <migration_name>.

[maxbrunsfeld]

I wouldn't think we'd need a trigram index for this, since we just need a prefix check on the id_path. Does that seem right?

[maxbrunsfeld]

We ended up solving this problem a different way:

https://github.com/zed-industries/zed/pull/7008