Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How good are query optimizers, really? [pdf] (2015)

vldb.org

Post reply on HN
Print view View original

11–20 of 59 posts

Re: How good are query optimizers, really? [pdf] (2015)

#11

Post by ris »

devmunchies wrote: post #2

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…

> 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.

The vast majority of your queries will not fall into the category of "bottlenecks that need to be optimized" though, and you (and your probably more inexperienced team) will benefit massively from the less error-prone & more extensible nature of ORMs (I never again want to have to deal with an attempt at SQL code reuse that has grown into a string-formatted, quadruple-manifestation, triple-escaped nightmare)

A good ORM will also ease the transition into more manual SQL too, so that you can still retain the benefits of e.g. uniform abstract objects app-side.

Re: How good are query optimizers, really? [pdf] (2015)

#12

Post by ninkendo »

SigmundA wrote: post #9

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…

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.

Re: How good are query optimizers, really? [pdf] (2015)

#13

Post by lmm »

devmunchies wrote: post #2

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…

This kind of thing is the reasons why I do like ORMs. Even if you can see the SQL, that doesn't mean you can tell what it's doing: you don't know what indices will be used or not used, you don't know why the query planner will do things one way or another, you're not actually "close to the metal" at all. So might as well have the convenience of an ORM; sure, you'll have to do some fiddly profiling as and when you have performance problems, but even if you were using hand-written SQL you'd still have to do that.

Re: How good are query optimizers, really? [pdf] (2015)

#14

Post by devmunchies »

lmm wrote: post #13
devmunchies wrote: post #2

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…

This kind of thing is the reasons why I do like ORMs. Even if you can see the SQL, that doesn't mean you can tell what it's doing: you don't know what indices will be used or not used, you don't know why the query planner will do things one way or another, you're not actually "close to the metal" at all. So might as well have the convenience of an ORM; sure, you'll have to do some fiddly profiling as and when you hav…

> 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 it’s not as easy as using an ORM, but if you’re a SQL expert then you can make queries much more optimized. You’ll never internalize how a SQL interpreter reads your queries unless you do it.

I’m talking about huge tables that are hit many times a second, where you need to start thinking like a Formula One team, being creative with queries to shave off hundreds of milliseconds.

Re: How good are query optimizers, really? [pdf] (2015)

#16

Post by stubish »

ninkendo wrote: post #12
SigmundA wrote: post #9

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…

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.

Re: How good are query optimizers, really? [pdf] (2015)

#17

Post by Ar-Curunir »

jzelinskie wrote: post #7
abernard1 wrote: post #4

This paper is from 2015 it appears. Can anyone comment on how relevant this is with the enhanced statistics types in Postgres 10, 11, 12?

I can't comment on your question, but thank you for finding the year this paper was written. Having read many older papers, it's sometimes like solving a murder mystery figuring out what year a paper was written. The year a paper is written is vital for understanding social context of the research being presented in addition to any context cited in the paper.

Isn’t it usually a matter of just googling the title?

Re: How good are query optimizers, really? [pdf] (2015)

#18

Post by rst »

devmunchies wrote: post #2

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).

Re: How good are query optimizers, really? [pdf] (2015)

#19

Post by lmm »

devmunchies wrote: post #14
lmm wrote: post #13

Earlier quoted context omitted.

This kind of thing is the reasons why I do like ORMs. Even if you can see the SQL, that doesn't mean you can tell what it's doing: you don't know what indices will be used or not used, you don't know why the query planner will do things one way or another, you're not actually "close to the metal" at all. So might as well have the convenience of an ORM; sure, you'll have to do some fiddly profiling as and when you hav…

> 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 - IME they tend to be far clearer, better documented, and more introspectable than database query planners.

Re: How good are query optimizers, really? [pdf] (2015)

#20

Post by jacobsenscott »

devmunchies wrote: post #6
vsareto wrote: post #5

Earlier quoted context omitted.

>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. I thought most of them had some feature where you could dump the query before it gets sent to the DB. Stuff like this: https://stackoverflow.com/questions/1412863/how-do-i-view-th...

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.
Post reply on HN