4 ms·

Is there a way to force a particular plan instead of letting the db decide? It's unfortunate the author needed to use a hack to trick the db into using the corr

Is there a way to force a particular plan instead of letting the db decide? It's unfortunate the author needed to use a hack to trick the db into using the correct plan.

tanelpoder 5y ago
Other databases have optimizer hints for that, but apparently there’s resistance to adding hints into Postgres [1]. I find hints very useful (in Oracle world) for quickly working around some optimizer glitch (and figure out the long term solution later). There actually is a “pg_hint_plan” [2] Postgres module that adds some hints to Postgres optimizer (but it’s not part of Postgres core as I understand): [1] https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion [2] https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan
- smackeyacky 5y ago
  I always use the rule of thumb that if you need optimizer hints for your query, its time to fix the query or the indexes. Of course in the teeth of an outage that generally isn't an option. The tools on SQL server for query optimization are better then postgres, but that doesn't always mean problems like the OP don't still occur. I have had more than one SQL server query plan go to hell over time as the tables grew and the optimizer started to make odd decisions. Edit: to make this a little clearer, I regard the SQL server query optimizer system as "lawful evil". It follows its own rules relentlessly.
  - tanelpoder 5y ago
    Yep, when the app code and the schema/partitioning/index design works _with_ the database as intended, not _against_ the database engine’s intended use, a whole class of SQL performance and plan stability problems tends to go away. Yep, agreed, hints should be temporary workarounds (or just experimentation aid when developing/testing SQL performance to validate some hypothesis). But many of these temporary workarounds end up being permanent temporary workarounds :-) That’s why Oracle now has hints and parameters to make the optimizer ignore all other hints in the SQL code :-)
    - smackeyacky 5y ago
      Working out which hints are operating must be difficult.
  - j16sdiz 5y ago
    For a cost-based-optimizer to work, the cost tuneables must be good. These are very difficult to get right in practice.
  - dap 5y ago
    How would you fix the query or indexes here? The CTE seems clearly a hack, effectively functioning as a hint. It doesn’t seem like it counts as fixing the query. I’m skeptical of this rule of thumb. I’m sure people misuse hints. But if someone thinks they “need” hints, my guess is they’ve tried changing the query and indexes.
SPBS 5y ago
Use named prepared statements (the ones where you have to manually DEALLOCATE to free up resources on the server). It is to my understanding that a named prepared statement runs five times using both a custom query plan and a generic query plan, and from the sixth execution onwards it picks the faster of the two and sticks to it for the rest of the prepared statement's lifetime (the query planner never gets called again, for better or worse). [Thread: SELECT slows down on sixth execution](https://postgrespro.com/list/thread-id/2070689 https://postgrespro.com/list/thread-id/2070689) If I'm wrong on this, I would desperately like someone more informed on this to correct me because it seems to me that you can force postgres to stick to a query plan, thus obviating the need for query hints
- jurple 5y ago
  Reading up on this (https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html), it seems that there are a number of exceptions where the statement will be re-planned: - DDL modifications to the used objects - updated planner statistics for the used objects - modification to search path I would expect a major Postgres version upgrade to hit these conditions, so the OP problem would still occur. Nevertheless, thanks for the pointer - I had mostly forgotten about this possibility.
  - SPBS 5y ago
    I'd like to correct myself on this point, it seems that if Postgres picks custom_plan over generic_plan it will always re-plan (that is what custom_plan is, it takes the prepared statement parameters into account while generic_plan always uses the same plan regardless of the parameters). So it's a toss up whether Postgres will actually lock in the static query plan over the dynamic query plan.

Is there a way to force a particular plan instead of letting the db decide? It's unfortunate the author needed to use a hack to trick the db into using the correct plan.

tanelpoder 5y ago

Other databases have optimizer hints for that, but apparently there’s resistance to adding hints into Postgres [1]. I find hints very useful (in Oracle world) for quickly working around some optimizer glitch (and figure out the long term solution later). There actually is a “pg_hint_plan” [2] Postgres module that adds some hints to Postgres optimizer (but it’s not part of Postgres core as I understand): [1] https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion [2] https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan

smackeyacky 5y ago
I always use the rule of thumb that if you need optimizer hints for your query, its time to fix the query or the indexes. Of course in the teeth of an outage that generally isn't an option. The tools on SQL server for query optimization are better then postgres, but that doesn't always mean problems like the OP don't still occur. I have had more than one SQL server query plan go to hell over time as the tables grew and the optimizer started to make odd decisions. Edit: to make this a little clearer, I regard the SQL server query optimizer system as "lawful evil". It follows its own rules relentlessly.
- tanelpoder 5y ago
  Yep, when the app code and the schema/partitioning/index design works _with_ the database as intended, not _against_ the database engine’s intended use, a whole class of SQL performance and plan stability problems tends to go away. Yep, agreed, hints should be temporary workarounds (or just experimentation aid when developing/testing SQL performance to validate some hypothesis). But many of these temporary workarounds end up being permanent temporary workarounds :-) That’s why Oracle now has hints and parameters to make the optimizer ignore all other hints in the SQL code :-)
  - smackeyacky 5y ago
    Working out which hints are operating must be difficult.
- j16sdiz 5y ago
  For a cost-based-optimizer to work, the cost tuneables must be good. These are very difficult to get right in practice.
- dap 5y ago
  How would you fix the query or indexes here? The CTE seems clearly a hack, effectively functioning as a hint. It doesn’t seem like it counts as fixing the query. I’m skeptical of this rule of thumb. I’m sure people misuse hints. But if someone thinks they “need” hints, my guess is they’ve tried changing the query and indexes.

SPBS 5y ago

Use named prepared statements (the ones where you have to manually DEALLOCATE to free up resources on the server). It is to my understanding that a named prepared statement runs five times using both a custom query plan and a generic query plan, and from the sixth execution onwards it picks the faster of the two and sticks to it for the rest of the prepared statement's lifetime (the query planner never gets called again, for better or worse). [Thread: SELECT slows down on sixth execution](https://postgrespro.com/list/thread-id/2070689 https://postgrespro.com/list/thread-id/2070689) If I'm wrong on this, I would desperately like someone more informed on this to correct me because it seems to me that you can force postgres to stick to a query plan, thus obviating the need for query hints

jurple 5y ago
Reading up on this (https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html), it seems that there are a number of exceptions where the statement will be re-planned: - DDL modifications to the used objects - updated planner statistics for the used objects - modification to search path I would expect a major Postgres version upgrade to hit these conditions, so the OP problem would still occur. Nevertheless, thanks for the pointer - I had mostly forgotten about this possibility.
- SPBS 5y ago
  I'd like to correct myself on this point, it seems that if Postgres picks custom_plan over generic_plan it will always re-plan (that is what custom_plan is, it takes the prepared statement parameters into account while generic_plan always uses the same plan regardless of the parameters). So it's a toss up whether Postgres will actually lock in the static query plan over the dynamic query plan.