Improve performance of _get_state_groups_from_groups_txn

[ilmari]

The query keeps showing up in my slow query log.

This changes the plan under the top-level Sort node from

WindowAgg  (cost=280335.88..292963.15 rows=561212 width=80) (actual time=138.651..160.562 rows=27112 loops=1)
  ->  Sort  (cost=280335.88..281738.91 rows=561212 width=84) (actual time=138.597..140.622 rows=27112 loops=1)
        Sort Key: state_groups_state.type, state_groups_state.state_key, state_groups_state.state_group
        Sort Method: quicksort  Memory: 4581kB
        ->  Nested Loop  (cost=2.83..226745.22 rows=561212 width=84) (actual time=21.548..47.657 rows=27112 loops=1)
              ->  HashAggregate  (cost=2.27..3.28 rows=101 width=8) (actual time=21.526..21.535 rows=20 loops=1)
                    Group Key: state.state_group
                    ->  CTE Scan on state  (cost=0.00..2.02 rows=101 width=8) (actual time=21.280..21.493 rows=20 loops=1)
              ->  Index Scan using state_groups_state_type_idx on state_groups_state  (cost=0.56..2189.40 rows=5557 width=84) (actual time=0.005..0.991 rows=1356 loops=20)
                    Index Cond: (state_group = state.state_group)

to

Nested Loop  (cost=2.83..226745.22 rows=561212 width=84) (actual time=24.194..52.834 rows=27112 loops=1)
  ->  HashAggregate  (cost=2.27..3.28 rows=101 width=8) (actual time=24.130..24.138 rows=20 loops=1)
        Group Key: state.state_group
        ->  CTE Scan on state  (cost=0.00..2.02 rows=101 width=8) (actual time=23.887..24.113 rows=20 loops=1)
  ->  Index Scan using state_groups_state_type_idx on state_groups_state  (cost=0.56..2189.40 rows=5557 width=84) (actual time=0.016..1.159 rows=1356 loops=20)
        Index Cond: (state_group = state.state_group)

This cuts the execution time from ~190ms to ~130ms, i.e. a reduction
of ~30%.

The full plans are visualised at https://explain.depesz.com/s/WpbT and
https://explain.depesz.com/s/KlEk

Pull Request Checklist

• Pull request is based on the develop branch
• Pull request includes a changelog file. The entry should:
  • Be a short description of your change which makes sense to users. "Fixed a bug that prevented receiving messages from other servers." instead of "Moved X method from EventStore to EventWorkerStore.".
  • Use markdown where necessary, mostly for code blocks.
  • End with either a period (.) or an exclamation mark (!).
  • Start with a capital letter.
• Pull request includes a sign off
• Code style is correct (run the linters)

[anoadragon453]

Since this is failing on all worker tests but not monolith tests, I have a horrible feeling that SyTest was relying on this SQL to be slow.

I know @erikjohnston did some race fixes recently. I'll try rebasing this PR to see if CI fares any better a second time.

[ilmari]

@anoadragon453 Turns out I messed up when adapting the query from my experimentation to the code, and was sorting the state_groups_state rows by event_id, not by state_group.

I've fixed it in the last force push

[erikjohnston]

Oooh, interesting. I didn't realise DISTINCT ON had a defined ordering for postgres, the relevant part of the documentation explaining that is here.

[erikjohnston]

I do indeed believe this works! Awesome, thank you!