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))