3 ms·
> Improve database schemas, e.g., through normalization, to promote efficient filtering. Cries in ActiveRecord type columns. In less sad news, I recently lear
by bhaak 3y ago
> Improve database schemas, e.g., through normalization, to promote efficient filtering.
Cries in ActiveRecord type columns.
In less sad news, I recently learned that Postgres can have serious problems with joining a lot of tables. I looked at a join of several tables (table A 80MM, table B 30MM, table C 1MM rows) and instead of using the WHERE parameter to reduce the result set of table A to a few thousands, it fetched all rows from table C first.
The applied heuristic in this case apparently being "join tables by order of lower row count".
Setting "join_collapse_limit" to 1 forces the query planner to join the table in exactly the order as they appear in the query and that solved my performance issues neatly.
- nerdponx 3y agoNow that CTEs are no longer optimization fences in recent versions of Postgres, is there a way to somehow manually declare an optimization fence around a certain region of the query? I've had similar problems in Snowflake with the query planner taking a bad path, and no obvious way to hint otherwise.
- bhaak 3y agoFunctions are hard barriers for optimization and I don't see that changing any time soon. Which goes even so far that EXPLAIN doesn't work on them and you just get a black box of "Function call to ..." in the execution plan. Using auto_explain is the only workaround known to me for that.
- baq 3y agoWITH MATERIALIZED restores the fence.
- anarazel 3y ago> The applied heuristic in this case apparently being "join tables by order of lower row count". Not really - it's all cost based analysis. There are a few main culprits that might be at work: 1) Selectivity estimates can get very off if you have cross-column correlations or your joins are over multiple aggregations / DISTINCT or such. 2) Join order planning is exponential in the number of joins. Once there are more than geqo_threshold (default = 12) joins, we don't explore the whole space anymore. 3) If you have more than join_collapse_limit / from_collapse_limit joins, subqueries in the from list, we'll not flatten them anymore, to prevent the join ordering problem to become even harder. It might be worth trying to just increase them with your query.
- bhaak 3y agoInteresting info. Thanks. In my particular case it doesn't help that I only have limited control over the query and the query designer has a bias towards large, complicated db functions.