21–30 of 59 posts
I have really tried to let the optimizer do its thing and generally it does and everything's ok. Until its not and then I want hints to save my ass, and they are not hints, I want want to TELL the f'ing computer what to do because I know better than the optimizer period. So surprised to find out PG doesn't support hints don't think I will ever be able to move anything serious until it does, just not going to take tha…
Earlier quoted context omitted.
> Even if you can see the SQL, that doesn't mean you can tell what it's doing You can. I mentioned EXPLAIN in my comment. And the query planner isn’t a black box. Once you read the documentation on how the order of operations is determined by the engine, you can start to be thinking on the same plane as the query engine. You can infer how a query will use indices and the way the WHERE clause will be used. Admittedly…
> And the query planner isn’t a black box. Once you read the documentation on how the order of operations is determined by the engine, you can start to be thinking on the same plane as the query engine. You can infer how a query will use indices and the way the WHERE clause will be used. If you're willing to put that kind of time and effort in, you should have no trouble understanding how your ORM generates queries -…
But I don’t see how one could become an expert at writing ORM queries without knowing the underlining SQL, which means you’re putting in the time to master two languages.
I may be just be an outlier, and that’s fine. I like geeking out over SQL optimization.
One of the most important things I learned with databases was to run each of my queries using EXPLAIN (EXPLAIN QUERY PLAN in sqlite) and seeing which indexes are being used, if any. One of the reasons I don't like ORMs is that I'm not able to see the underlining query and truly optimize a service. That may be fine for a new service where performance isn't crucial, but once it needs to scale, you need to put on your e…
That depends on the ORM -- ActiveRecord has a ".explain" method on the proxy objects for its query builder which displays the SQL, and the query plan (displayed in db-specific format).
Earlier quoted context omitted.
Is the query actually slower, or is it just not using an index you want it to use? Often times PG won’t bother with an index for a variety of reasons (sequential scans can be legitimately faster in some scenarios), especially when the number of rows is small.
The cool thing about hints is you can quickly drop in a hint to confirm your suspicions and narrow down the problem, rather than trying to do this sort of diagnosis in a vacuum. But because some people use hints for evil, nobody is allowed to use them.
Earlier quoted context omitted.
> And the query planner isn’t a black box. Once you read the documentation on how the order of operations is determined by the engine, you can start to be thinking on the same plane as the query engine. You can infer how a query will use indices and the way the WHERE clause will be used. If you're willing to put that kind of time and effort in, you should have no trouble understanding how your ORM generates queries -…
It’s not that I’m willing to put in the effort, it’s that the scale I operate at requires it. I would rather become highly proficient at SQL and use it universally with any server language than become an expert at a specific ORM. But I don’t see how one could become an expert at writing ORM queries without knowing the underlining SQL, which means you’re putting in the time to master two languages. I may be just be an…
It's like working in a transpiled language: you need to understand the intermediate language a little, but you don't need to master it. Indeed I'd argue that the ORM often corresponds more clearly to what's actually going on at the query planner level than the SQL does: pulling out an entity via an indexed link to another entity is very different from scanning through a table for cases where one value matches another, and they look different in the ORM and in the query planner, but in SQL they're both just "JOIN".
Earlier quoted context omitted.
yeah but if the generated SQL doesn't look how you want it, now you gotta optimize it using the ORM's language and not SQL. Tweak the ORM, see the generated SQL, tweak again... etc. If you're looking at the generated SQL I would rather just use the SQL directly in my code. There's probably features in ORMs where you can write raw SQL and tell it how to map the result to an object but I haven't used an ORM in a while.
Generally it is the table design, and not the ORM that is the constraint on how you can write your queries. A good ORM lets you customize any part of the query, or even just write plain sql. Most performance problems that pop up as a product matures aren't because the ORM generates "slow" queries, it is because the table design didn't scale. That can't be fixed by writing plain sql.
A good table design is only good BECAUSE it enables efficient predicate use on the SQL queries.
You can’t just query any column willy nilly, you have to plan it. That’s why I like thinking in SQL with the table definition on-hand.
Example, if I write “SELECT * WHERE x OR y” and “y” isn’t indexed, then this will do a full table scan. Not ok. I need to plan my queries so it does something like “WHERE x OR (y AND z)” where “z” is indexed so it filters by “z” and then “y”. I don’t want to have to try and figure out how to get the ORM to produce that.
The simplified cost model they use was really surprising. 34% better than the complex pg one not only sounds great (incoming "simple is better" replies below) but is really nice to hear. Hopefully postgres has or will consider changing the default cost model to a simpler, more modern function that takes the current landscape into account.
Optimizers are weird.
Earlier quoted context omitted.
> Even if you can see the SQL, that doesn't mean you can tell what it's doing You can. I mentioned EXPLAIN in my comment. And the query planner isn’t a black box. Once you read the documentation on how the order of operations is determined by the engine, you can start to be thinking on the same plane as the query engine. You can infer how a query will use indices and the way the WHERE clause will be used. Admittedly…
> And the query planner isn’t a black box. Once you read the documentation on how the order of operations is determined by the engine, you can start to be thinking on the same plane as the query engine. You can infer how a query will use indices and the way the WHERE clause will be used. If you're willing to put that kind of time and effort in, you should have no trouble understanding how your ORM generates queries -…
Also, there's a feedback loop to consider. You can choose to get to know your DBMS, or you can choose to hold it at arm's length. Both can be reasonable options, but only one is going to foster expertise.
I've worked in enough places to see a clear pattern: Companies that use ORM for anything beyond really simple CRUD tend to have creeping performance problems with their database. (And typically also a healthy contingent of team members pushing to solve those problems by doing something drastic like migrating to NoSQL.) Companies that don't use ORM generally don't have the same problems.
For my part, I find micro-ORMs such as Dapper to be the sweet spot. They get the vast majority of the convenience benefit, without encouraging poor housekeeping practices.
One of the most important things I learned with databases was to run each of my queries using EXPLAIN (EXPLAIN QUERY PLAN in sqlite) and seeing which indexes are being used, if any. One of the reasons I don't like ORMs is that I'm not able to see the underlining query and truly optimize a service. That may be fine for a new service where performance isn't crucial, but once it needs to scale, you need to put on your e…
I wrote a thread on this on Twitter: https://twitter.com/PredragGruevski/status/12639165990625402... I feel that SQL aimed to be Python and became x86 assembly instead. It's no longer a simple "just works" query language the moment you have to worry about predicate flattening, join decomposition, CTEs that introduce optimization barriers, and "IN()" being faster than equivalent "JOINs". As a result, I started a proje…
So...exactly like SQL, then?