3 ms·
Deduced schema based on the naming of tables/columns: CREATE TABLE accounts ( id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, max_public_p
by JoelJacobson 5y ago
Deduced schema based on the naming of tables/columns:
CREATE TABLE accounts (
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
max_public_parallelism integer NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE worker_pools (
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
is_public boolean NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE stacks (
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
worker_pool_id bigint NOT NULL,
account_id bigint NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (worker_pool_id) REFERENCES worker_pools,
FOREIGN KEY (account_id) REFERENCES accounts
);
CREATE TABLE runs (
id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
stack_id bigint,
worker_id bigint,
created_at timestamptz NOT NULL DEFAULT now(),
type integer NOT NULL,
state integer NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (stack_id) REFERENCES stacks
);
- JoelJacobson 5y agocube222, 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))