cube222, does the schema above look more or less correct to you?

Below are the query plans I got when running the query against empty tables in PostgreSQL 15dev (not Aurora Serverless), for comparison.

Would be nice to populate the tables and add indexes, to make a real comparison. Would help a lot with some rough estimates on the number of rows in each table and count(distinct ...) of each column, etc?

Unoptimized version:

                                                                        QUERY PLAN
    ---------------------------------------------------------------------------------------------------------------------------------------------------
     Aggregate  (cost=82.14..82.16 rows=1 width=16) (actual time=0.009..0.012 rows=1 loops=1)
       ->  Hash Join  (cost=46.05..82.13 rows=1 width=8) (actual time=0.005..0.007 rows=0 loops=1)
             Hash Cond: (stacks.worker_pool_id = worker_pools.id)
             Join Filter: ((accounts.max_public_parallelism / 2) > (SubPlan 1))
             ->  Nested Loop  (cost=0.30..36.38 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=1)
                   ->  Nested Loop  (cost=0.15..36.18 rows=1 width=24) (actual time=0.004..0.005 rows=0 loops=1)
                         ->  Seq Scan on runs  (cost=0.00..28.00 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)
                               Filter: ((worker_id IS NULL) AND (type = ANY ('{1,4}'::integer[])) AND (state = 1))
                         ->  Index Scan using stacks_pkey on stacks  (cost=0.15..8.17 rows=1 width=24) (never executed)
                               Index Cond: (id = runs.stack_id)
                   ->  Index Scan using accounts_pkey on accounts  (cost=0.15..0.20 rows=1 width=12) (never executed)
                         Index Cond: (id = stacks.account_id)
             ->  Hash  (cost=32.00..32.00 rows=1100 width=8) (never executed)
                   ->  Seq Scan on worker_pools  (cost=0.00..32.00 rows=1100 width=8) (never executed)
                         Filter: is_public
             SubPlan 1
               ->  Aggregate  (cost=66.89..66.90 rows=1 width=8) (never executed)
                     ->  Nested Loop  (cost=33.72..66.89 rows=1 width=0) (never executed)
                           ->  Hash Join  (cost=33.56..58.71 rows=1 width=8) (never executed)
                                 Hash Cond: (runs_other.stack_id = stacks_other.id)
                                 ->  Seq Scan on runs runs_other  (cost=0.00..22.00 rows=1194 width=8) (never executed)
                                       Filter: (worker_id IS NOT NULL)
                                 ->  Hash  (cost=33.55..33.55 rows=1 width=16) (never executed)
                                       ->  Seq Scan on stacks stacks_other  (cost=0.00..33.55 rows=1 width=16) (never executed)
                                             Filter: (((worker_pool_id IS NULL) OR (worker_pool_id = worker_pools.id)) AND (account_id = accounts.id))
                           ->  Index Only Scan using accounts_pkey on accounts accounts_other  (cost=0.15..8.17 rows=1 width=8) (never executed)
                                 Index Cond: (id = accounts.id)
                                 Heap Fetches: 0

Optimized version:

                                                                  QUERY PLAN
    --------------------------------------------------------------------------------------------------------------------------------------
     Aggregate  (cost=82.14..82.16 rows=1 width=16) (actual time=0.010..0.012 rows=1 loops=1)
       ->  Hash Join  (cost=46.05..82.13 rows=1 width=8) (actual time=0.006..0.007 rows=0 loops=1)
             Hash Cond: (stacks.worker_pool_id = worker_pools.id)
             Join Filter: ((accounts.max_public_parallelism / 2) > (SubPlan 2))
             ->  Nested Loop  (cost=0.30..36.38 rows=1 width=28) (actual time=0.005..0.006 rows=0 loops=1)
                   ->  Nested Loop  (cost=0.15..36.18 rows=1 width=24) (actual time=0.005..0.005 rows=0 loops=1)
                         ->  Seq Scan on runs  (cost=0.00..28.00 rows=1 width=16) (actual time=0.004..0.005 rows=0 loops=1)
                               Filter: ((worker_id IS NULL) AND (type = ANY ('{1,4}'::integer[])) AND (state = 1))
                         ->  Index Scan using stacks_pkey on stacks  (cost=0.15..8.17 rows=1 width=24) (never executed)
                               Index Cond: (id = runs.stack_id)
                   ->  Index Scan using accounts_pkey on accounts  (cost=0.15..0.20 rows=1 width=12) (never executed)
                         Index Cond: (id = stacks.account_id)
             ->  Hash  (cost=32.00..32.00 rows=1100 width=8) (never executed)
                   ->  Seq Scan on worker_pools  (cost=0.00..32.00 rows=1100 width=8) (never executed)
                         Filter: is_public
             SubPlan 2
               ->  Aggregate  (cost=9851.00..9851.01 rows=1 width=8) (never executed)
                     ->  Seq Scan on runs runs_other  (cost=0.00..9850.00 rows=398 width=0) (never executed)
                           Filter: ((worker_id IS NOT NULL) AND ((SubPlan 1) > 0))
                           SubPlan 1
                             ->  Aggregate  (cost=8.18..8.19 rows=1 width=8) (never executed)
                                   ->  Index Scan using stacks_pkey on stacks stacks_1  (cost=0.15..8.18 rows=1 width=0) (never executed)
                                         Index Cond: (id = runs_other.stack_id)
                                         Filter: ((account_id = accounts.id) AND (worker_pool_id = worker_pools.id))