4 ms·

I've personally experienced cases where making a slight change to a query in a non-obvious way resulted in a 10x or more speedup. My personal favorite is I was

I've personally experienced cases where making a slight change to a query in a non-obvious way resulted in a 10x or more speedup. My personal favorite is I was generating a query that pulled out a dozen different fields from a large JSONb column. Naturally, you would think Postgres would read the JSONb field once, then pull out the individual fields from it. Instead, Postgres was reading the JSONb column once per each field. I figured out this was the case because the number of blocks read from EXPLAIN (ANALYZE, BUFFERS) went up proportionally to the number of fields I extracted from the JSONb column. Reading the JSONb column was especially expensive because Postgres needed to deTOAST[0] the JSONb column. The obvious fix is to write a subquery to read the entire JSONb column and then have the outer query extract the individual fields, but that doesn't work! Postgres will inline the subquery, basically undoing your attempt to prevent the unnecessary accesses. In the end, the solution wound up being to add OFFSET 0 to the end of the subquery. That doesn't change the semantics of the query, but it does prevent Postgres from inlining the JSONb column access. [0] https://malisper.me/postgres-toast/ https://malisper.me/postgres-toast/

hobs 6y ago
Here's some one's you can do in SQL Server and probably easily attain that in your garbage code: 1. Remove table variables, use temp tables. 2. Remove user defined functions from your predicate if its job is to return the same fixed subset, insert that into a temp table. The first one can be done by effectively changing an @ to a # and mildly changing the create statements (from declare to create) - doing just this change I took a a 22+ hour long query(didnt want to wait any longer) to a <1 minute query.
andreareina 6y ago
Did you happen to try it with a CTE/WITH expression? Prior versions are not supposed to inline those, and the current version allows you to explicitly {pre,pro}scribe inlining: https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ https://paquier.xyz/postgresql-2/postgres-12-with-materializ...
- malisper 6y ago
  A CTE probably would have fixed it, but we weren't able to use them. We were using Citus which at the time didn't support CTEs. Using a CTE to do this would also be suboptimial since it materializes the entire result of the subquery in memory. This is as opposed to the OFFSET 0 which would only materialize one row at a time.
deleted 6y ago
[deleted]
murkt 6y ago
Oh wow, good to know, I’ll check if I have such things in my codebase. Which version of the Postgres are you using?
ris 6y ago
Something you'll discover when you do enough query optimization is that postgres' query planner isn't clause-order invariant. i.e. a AND b won't necessarily give you the same query plan as b AND a. My first reaction was that this was awful, but the more I thought about it, the more I was grateful that postgres (accidentally) gave me this knob to play with. Optimization of complex queries is a very tricky thing, and most engines won't do a good job of it 100% of the time. What I realized would have been a worse situation is if postgres sometimes picked a bad plan and there was little I could do to avoid it without reforming the query (a pain when you're generating the query with a query compiler already). Reordering the clauses effectively allowed me to ask the planner to roll the dice again. Following this, I may or may not have gone on to build a cache for the system that timed queries and kept notes of "good" clause orders for common queries, resorting to random ones otherwise....
- shoo 6y ago
  A variation of this is that the query planner may produce better plan if you add additional redundant constraints to the query that are logically implied by the constraints that are already there. Personally, it feels like a bit of a fragile mess trying to trick a sometimes-clever-sometimes-dumb optimiser into doing what you want by subtle indirect hacks - because the interface doesn't give you a way to directly override bad automated optimiser decisions.
  - ris 6y ago
    > a fragile mess Oh, I'm not saying it wasn't a fragile mess... Seriously I actually considered the fragility of it to be a positive, precisely because it wasn't a hard "optimizer decision" that I was forcing. If you force something like that, you've got to take full responsibility for it - the planner will lose all intelligence over a certain decision and no longer do the clever thing query planners do and take the current distribution of the database's data into account to allow it to make better decisions. When you upgrade the database and the query planner gets smarter (or dumber), you've got to re-evaluate whether that's still the right decision. I could imagine an app with a bunch of out of date hard-forced decisions to be crippling performance wise. A look-aside of automatically deduced hints with a validity of less than a week seemed like a much lighter touch.
aidos 6y ago
I ran into that this week too! Actually, it was a filter that contained a Json path select and it appears that it’s evaluating the path for every row check (maybe because the path lookups can be more dynamic?).
cosmotic 6y ago
This sounds like a bug in postgres (or a low-hanging-fruit optimization); adding the hacky offset 0 to bypass the inlining may help work around the bug now but may hinder future optimizations. My experience teaches me to avoid tricking an optimizer down a path. Write the query in the most easy-to-understand way to avoid massive future pain. I would also avoid json/xml 'support', it's a recipe for unintended consequences.

hobs 6y ago

Here's some one's you can do in SQL Server and probably easily attain that in your garbage code: 1. Remove table variables, use temp tables. 2. Remove user defined functions from your predicate if its job is to return the same fixed subset, insert that into a temp table. The first one can be done by effectively changing an @ to a # and mildly changing the create statements (from declare to create) - doing just this change I took a a 22+ hour long query(didnt want to wait any longer) to a <1 minute query.

andreareina 6y ago

Did you happen to try it with a CTE/WITH expression? Prior versions are not supposed to inline those, and the current version allows you to explicitly {pre,pro}scribe inlining: https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

malisper 6y ago
A CTE probably would have fixed it, but we weren't able to use them. We were using Citus which at the time didn't support CTEs. Using a CTE to do this would also be suboptimial since it materializes the entire result of the subquery in memory. This is as opposed to the OFFSET 0 which would only materialize one row at a time.

deleted 6y ago

[deleted]

murkt 6y ago

Oh wow, good to know, I’ll check if I have such things in my codebase. Which version of the Postgres are you using?

ris 6y ago

Something you'll discover when you do enough query optimization is that postgres' query planner isn't clause-order invariant. i.e. a AND b won't necessarily give you the same query plan as b AND a. My first reaction was that this was awful, but the more I thought about it, the more I was grateful that postgres (accidentally) gave me this knob to play with. Optimization of complex queries is a very tricky thing, and most engines won't do a good job of it 100% of the time. What I realized would have been a worse situation is if postgres sometimes picked a bad plan and there was little I could do to avoid it without reforming the query (a pain when you're generating the query with a query compiler already). Reordering the clauses effectively allowed me to ask the planner to roll the dice again. Following this, I may or may not have gone on to build a cache for the system that timed queries and kept notes of "good" clause orders for common queries, resorting to random ones otherwise....

shoo 6y ago
A variation of this is that the query planner may produce better plan if you add additional redundant constraints to the query that are logically implied by the constraints that are already there. Personally, it feels like a bit of a fragile mess trying to trick a sometimes-clever-sometimes-dumb optimiser into doing what you want by subtle indirect hacks - because the interface doesn't give you a way to directly override bad automated optimiser decisions.
- ris 6y ago
  > a fragile mess Oh, I'm not saying it wasn't a fragile mess... Seriously I actually considered the fragility of it to be a positive, precisely because it wasn't a hard "optimizer decision" that I was forcing. If you force something like that, you've got to take full responsibility for it - the planner will lose all intelligence over a certain decision and no longer do the clever thing query planners do and take the current distribution of the database's data into account to allow it to make better decisions. When you upgrade the database and the query planner gets smarter (or dumber), you've got to re-evaluate whether that's still the right decision. I could imagine an app with a bunch of out of date hard-forced decisions to be crippling performance wise. A look-aside of automatically deduced hints with a validity of less than a week seemed like a much lighter touch.

aidos 6y ago

I ran into that this week too! Actually, it was a filter that contained a Json path select and it appears that it’s evaluating the path for every row check (maybe because the path lookups can be more dynamic?).

cosmotic 6y ago

This sounds like a bug in postgres (or a low-hanging-fruit optimization); adding the hacky offset 0 to bypass the inlining may help work around the bug now but may hinder future optimizations. My experience teaches me to avoid tricking an optimizer down a path. Write the query in the most easy-to-understand way to avoid massive future pain. I would also avoid json/xml 'support', it's a recipe for unintended consequences.