5 ms·

Ok, from battle trenches: even if there is a perfect index for your query, the slower plan might still win and there is little you can do about it. So, often yo

Ok, from battle trenches: even if there is a perfect index for your query, the slower plan might still win and there is little you can do about it. So, often you rewrite query or even restructure tables to archive performance. If rewriting query and remodelling data are out of question, the options are much more limited. Second, not only queries have rps, they have hourly, weekly and seasonal distributions. They evolve, become deprecated, and have different SLAs, tables have different write to read ratios. There was an instance recently when I slowed down a query, quite intentionally by deleting a very good index for the query and substituted it with worse, but smaller BRIN index. The thing is, this particular query did not matter as much and SLA permitted slowdown, write path was far more important.

joelthelion 3y ago
> Ok, from battle trenches: even if there is a perfect index for your query, the slower plan might still win and there is little you can do about it I often wonder why, in addition to sql, we don't have a low level language allowing you to specify exactly how you want your query executed?
- silon42 3y ago
  +1.. for production I'd like this and disable statistics and also disable table scans on most queries.
  - hobs 3y ago
    If your query doesn't know its own cardinality and can't scan do you just know you're always going to return one row? I ask because otherwise in my mind disabling statistics is usually a Bad Plan.
- fulafel 3y ago
  How good are the language interfaces (eg C) in PG for doing this? If I wanted to do reimplement join, for example.
- mcc1ane 3y ago
  Postgres (and core team)-specific - https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
  - masklinn 3y ago
    Query hints are a very different thing. What I would like, and I assume GP as well, is the ability to write the low-level querying, as in bypass SQL and the planner, and give postgres the post-planner bytecode itself.
    - joelthelion 3y ago
      Exactly. I use hints as well, but in the end, you wonder, why waste your time trying to get the optimizer to do what you want, when you could bypass it entirely?

joelthelion 3y ago

> Ok, from battle trenches: even if there is a perfect index for your query, the slower plan might still win and there is little you can do about it I often wonder why, in addition to sql, we don't have a low level language allowing you to specify exactly how you want your query executed?

silon42 3y ago
+1.. for production I'd like this and disable statistics and also disable table scans on most queries.
- hobs 3y ago
  If your query doesn't know its own cardinality and can't scan do you just know you're always going to return one row? I ask because otherwise in my mind disabling statistics is usually a Bad Plan.
fulafel 3y ago
How good are the language interfaces (eg C) in PG for doing this? If I wanted to do reimplement join, for example.
mcc1ane 3y ago
Postgres (and core team)-specific - https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
- masklinn 3y ago
  Query hints are a very different thing. What I would like, and I assume GP as well, is the ability to write the low-level querying, as in bypass SQL and the planner, and give postgres the post-planner bytecode itself.
  - joelthelion 3y ago
    Exactly. I use hints as well, but in the end, you wonder, why waste your time trying to get the optimizer to do what you want, when you could bypass it entirely?