Hacker News Reader

Comment by jitl | Hacker News Reader

I’ve been needing this and also HypoPG, which I didn’t realize had been available on RDS for a year plus!

The issue I’m struggling with these days is that I have an index that I want Postgres to use for one of the relations in a 3+ wide join, but unless I put a limit on a CTE of one of the tables, Postgres tries to run each join in parallel and ends up trying to join a bazillion rows like an asshole.

These days wrangling the query planner has me on the verge of breaking up with pg :-(

View on HN

You can set max_parallel_workers_per_gather to zero in the session where you're running the problem query, if that's helpful. That will disable the parallel query behavior entirely. You can just reset it back to what it was once the query is complete. I've run into this issue before and that was my go-to fix.

I'd strongly oppose setting the max parallel workers on session level just to bypass an execution plab. I stead understand the logic why pg behave the way it is, and change the query accordingly

Yeah you can file a patch with postgres and get it into the next point release or something but in the mean time you really want to keep that query from OOMing your database :)

I understand why it’s making the choice it’s making, I’m at a loss for how to convince it to make the choice I consider optimal. Given the available statistics knobs, it seems like my options are [actionable suggestions like the parent and the other helpful commenters], redesign the schema (lots of work), or pick another database (also lots of work but perhaps less than redesigning the schema only to hit a similar problem again).

While pinning a single plan or disabling parallel operations is a valid quick fix, I'd recommend trying to actually understand why the optimizer is doing what it's doing. It's all "just" a simple math of execution node costs. The problem with plan hints, or disabling parallel, is that unless you have a perfect understanding of where the data will be in, say, a year, you may just have created another problem, just delayed. Some column will go from 10 to 10,000 unique values and your good plan will become way off. And at the end, perhaps you just need to tell PG that an index access is not 4 times as expensive as a plain heap scan.

ps fighting the optimizer is the worst

i don't know how far it was developed, but I believe pg_plan_guarantee[0] was created to solve this sort of issue

> This extension provides a mechanism, using which you can set your execution plan in stone; that is, Postgres will execute the plan you give it, or it will throw an error, but it will never try to guess a plan for you.

[0] https://github.com/DrPostgres/pg_plan_guarantee

Ugh I would love LOVE to have something like this or even better a stable API where I can manually specify my own physical query plan. As is, I’m on RDS and this thing is experimental so I have no way to use it.

For what it's worth, depending on how many people and/or budget you have available to man on call rotations, running your own postgresql server with replication is not that hard. The RDS premium is like a whole engineer's salary by the time you get to Large Databases (tm), and if you're running production, staging, and maybe a data warehouse, it starts to rapidly be able to pay for a whole team.

People aka eng time is our scarcest resource. Luckily the thing I’m struggling with is nice-to-have, not critical, will try pg_hint_plan as suggested, I didn’t realize it’s available on RDS. Otherwise will abandon.

Ah you made me look, it's a shame you're only hiring in SF, I love these kinds of problems. Cheers!

Guessing you've seen this already and it's not doing what you want?

https://github.com/ossc-db/pg_hint_plan

Good suggestion!

Have you tried changing the order of the joins? I know it shouldn't matter but I've found sometimes it tricks the query planner into doing the right things!

Yep, we permuted the join order, although I’m not sure we tried the full combinatorics for explicit vs implicit joins

Reply on news.ycombinator.com