How good are query optimizers, really? [pdf] (2015)
1–10 of 59 posts
Re: How good are query optimizers, really? [pdf] (2015)
#2One 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 engineering hat, get your hands dirty, and optimize queries.
You'll find you need to re-write queries so that there isn't complex nesting in the WHERE statement and flatten your logic so that the SQL optimizer can use your indexes. You may need to put SELECT statements within SELECT statements, where the innermost SELECT uses indexes and the outer queries are using the result of the inner query, which is smaller than the whole table.
Re: How good are query optimizers, really? [pdf] (2015)
#3One 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 project that allows you to write read-only database-agnostic queries called GraphQL compiler: https://graphql-compiler.readthedocs.io/ https://github.com/kensho-technologies/graphql-compiler
The core idea of the project is to get us the convenience of specifying the "what question I want answered," but without the inconvenience of "how is the answer computed / with which specific set of queries / where did the data come from?" -- unless you want to peek under the hood, of course. All the visibility into the nitty-gritty details available on demand, but without the tedium of having to hand-optimize queries and know all the "magic" ways in which queries get faster or slower for each individual kind of database.
Re: How good are query optimizers, really? [pdf] (2015)
#4This paper is from 2015 it appears.
Can anyone comment on how relevant this is with the enhanced statistics types in Postgres 10, 11, 12?
Re: How good are query optimizers, really? [pdf] (2015)
#5One 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.
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...
Re: How good are query optimizers, really? [pdf] (2015)
#6One 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. 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.
Re: How good are query optimizers, really? [pdf] (2015)
#7This 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.
Re: How good are query optimizers, really? [pdf] (2015)
#8This 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.
Agreed, especially in fast-changing fields or after recent breakthroughs. One trick I use is look at the References and find the approx. max year cited. Generally the same or pretty close to the year of the paper.
Re: How good are query optimizers, really? [pdf] (2015)
#9I 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 that kind of risk.
I have played the whole rewrite query to try and convince the optimizer what to do with barrier tricks, no thanks, give me some hints and I will tell it exactly what to do when thanks.
Re: How good are query optimizers, really? [pdf] (2015)
#10This paper is from 2015 it appears. Can anyone comment on how relevant this is with the enhanced statistics types in Postgres 10, 11, 12?
Here's a neat extension that tries to use genetic algorithms to learn better planning for one's queries, includes slides which cite this & have TPC numbers