2 ms·
Generally I'd say do the same as you would with your compiler when trying to optimize your code: spend time with the query analyzer. What the query optimizer w
by magicalhippo 5y ago
Generally I'd say do the same as you would with your compiler when trying to optimize your code: spend time with the query analyzer.
What the query optimizer will or won't do depends on your database software and your data, in addition to your query of course.
For example, the article states "publish_date < now() - interval '3 days'" is always faster than "publish_date + interval '3 days' < now()".
Well not for the database server we use, because it evaluates now() per row turning both into table scans... yay.
In addition to the suggestions in the article, I'd say use common table expressions (CTE), or sub-query if your database server doesn't have CTEs or treats them as an optimization barrier.
I've found the optimizer tries to be smart but doesn't recognize which of the joins will be the most specific filter, causing it to join tons of rows, just to throw most of them away with a later join. Using a CTE or sub-query can force the optimizers hand.
Also I've often found it useful to use CTEs or sub-queries for returning row values, typically aggregates. This can especially work well if you're limiting the rows returned using TOP or LIMIT. Again this can avoid the DB throwing away work.
- Something1234 5y agowhat database server evaluates now() per row?
- rileymat2 5y agoThat seems like it would cause a lot of problems, other than the optimization.
- magicalhippo 5y agoThat's how my colleague described it to me, seems he misspoke or I misremembered. I checked the documentation and it says now() is evaluated at most once per request. The point was now() and friends prevents optimization on the server we use, because they're not treated as constants.
- rileymat2 5y agoIt depends on context, for example this is true for SQL server if you have a filtered index based on datetime. If you use a constant or set a @variable ahead of time, it will use the filtered index, but if you just use =now() equivalent (or in SQL server GETDATE()) it will not. (You may also need to use OPTION(Recompile))
- teej 5y agoIn Postgres terminology, all functions have a volatility classification: IMMUTABLE (input X will always return y forever), STABLE (input x will always return y for the current statement) and VOLATILE (input x can return anything). That classification impacts how the query gets planned and optimized. All the dbs I know have some version of this. now() should be STABLE in pretty much every db, but not every db gracefully handles STABLE function calls in predicates. You probably know all this but figured I'd share the database jargon for everyone else.
- deleted 5y ago[deleted]
- rmbyrro 5y agoI have the opinion non-deterministic queries should be avoided in the first place.