4 ms·

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 ha

by jitl 3y ago

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 :-(

kiwicopple 3y ago
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 https://github.com/DrPostgres/pg_plan_guarantee
- jitl 3y ago
  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.
  - jaggederest 3y ago
    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.
    - jitl 3y ago
      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.
      - jaggederest 3y ago
        Ah you made me look, it's a shame you're only hiring in SF, I love these kinds of problems. Cheers!
jaggederest 3y ago
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.
- krembo 3y ago
  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
  - jaggederest 3y ago
    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 :)
  - jitl 3y ago
    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).
justinclift 3y ago
Guessing you've seen this already and it's not doing what you want? https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan
- jitl 3y ago
  Good suggestion!
polishdude20 3y ago
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!
- jitl 3y ago
  Yep, we permuted the join order, although I’m not sure we tried the full combinatorics for explicit vs implicit joins
arronax 3y ago
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

by jitl 3y ago

kiwicopple 3y ago

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 https://github.com/DrPostgres/pg_plan_guarantee

jitl 3y ago
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.
- jaggederest 3y ago
  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.
  - jitl 3y ago
    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.
    - jaggederest 3y ago
      Ah you made me look, it's a shame you're only hiring in SF, I love these kinds of problems. Cheers!

jaggederest 3y ago

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.

krembo 3y ago
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
- jaggederest 3y ago
  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 :)
- jitl 3y ago
  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).

justinclift 3y ago

Guessing you've seen this already and it's not doing what you want? https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan

jitl 3y ago
Good suggestion!

polishdude20 3y ago

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!

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

arronax 3y ago

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