4 ms·
Interesting write up! Did you happen to try this with a CTE instead of a subquery? ie something like: WITH tmp AS (SELECT accounts_other.id, COUNT(*) n
by tqi 5y ago
Interesting write up! Did you happen to try this with a CTE instead of a subquery? ie something like:
WITH
tmp AS (SELECT accounts_other.id, COUNT(*) n
FROM accounts accounts_other
JOIN stacks stacks_other ON accounts_other.id = stacks_other.account_id
JOIN runs runs_other ON stacks_other.id = runs_other.stack_id
WHERE (stacks_other.worker_pool_id IS NULL OR
stacks_other.worker_pool_id = worker_pools.id)
AND runs_other.worker_id IS NOT NULL
GROUP BY 1
)
SELECT COUNT(*) as "count",
COALESCE(MAX(EXTRACT(EPOCH FROM age(now(), runs.created_at)))::bigint, 0) AS "max_age"
FROM runs
JOIN stacks ON runs.stack_id = stacks.id
JOIN worker_pools ON worker_pools.id = stacks.worker_pool_id
JOIN accounts ON stacks.account_id = accounts.id
LEFT JOIN tmp ON tmp.id = accounts.id
WHERE worker_pools.is_public = true
AND runs.type IN (1, 4)
AND runs.state = 1
AND runs.worker_id IS NULL
AND accounts.max_public_parallelism / 2 > COALESCE(tmp.n, 0)
I don't know this for sure, but in my experience it has seemed like Postgres is bad at optimizing subqueries, and will execute once per row.
- dragonwriter 5y agoAren't CTEs still optimization barriers in Postgres, so wouldn't that make any subquert optimization problem worse?
- CBLT 5y agoWasn't this solved in Postgres 12?[0] [0] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...
- kristjansson 5y agoMostly no, since PostgreSQL 12
- deleted 5y ago[deleted]
- srcreigh 5y agoDepends if materialization helps. As a silly example, if the CTE materializes 1/100th of a table via table scan, scanning 1/100th of a table N times may be faster than scanning the entire table N times.
- briffle 5y agoThe fun thing is, they have corrected a bunch of that in newer versions of postgresql. I'm surprised that the problem stemmed from an index where the 'AND NOT NULL' took forevery to scan, and they didn't include a partial index, or an index in a different order since they are sorted.
- yxhuvud 5y agoThat could even be a feature if used to compute just the list of active jobs. Later pgs might need to be explicit to reinsert the optimization barrier, as the implicit one for all Cates was removed.
- cube2222 5y agoHaven't run it, but based on the query plan in the first picture, PostgreSQL already does that part kind of right. It precalculates it for all accounts and then does a three-way hash join.
- aeyes 5y agoFor Postgres 12+ you'll have to use to force the plan > WITH tmp AS MATERIALIZED