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

31–40 of 59 posts

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

#31

Post by jacobsenscott »

devmunchies wrote: post #27
jacobsenscott wrote: post #20

Earlier quoted context omitted.

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.

This doesn’t really make sense to me. You don’t just design a good table schema independently and start querying it. The design isn’t a step that comes before the queries. The queries and the design are created in tandem. You design the schemas with the indices and queries in mind, write queries that use your indices. A good design doesn’t magically scale. It’s based on how you set up your indices and write SQL. A go…

And's and or's are trivial in any good ORM. There are valid reasons to not want to use an ORM, but they are more around the the object/relational impedance mismatch, coupling table design to the domain model, etc.

But the alternative to an ORM is not opaque blobs if SQL hard coded into the app all over. How do you handle SQL injection attacks for example? What if you add/rename/drop a column? Do you just grep you code and edit every blob of sql in the app?

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

#32

Post by petergeoghegan »

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?

Most of that stuff depends on explicit CREATE STATISTICS commands being run in order to work around column correlations and stuff like that. The general assumption of independence among columns/attributes is pretty universal (as the paper actually says).

One of the most useful areas for future improvement is making plans more robust against misestimations during execution, for example by using techniques like role-reversal during hash joins, or Hellerstein's "Eddies".

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

#33

Post by devmunchies »

jacobsenscott wrote: post #31
devmunchies wrote: post #27

Earlier quoted context omitted.

This doesn’t really make sense to me. You don’t just design a good table schema independently and start querying it. The design isn’t a step that comes before the queries. The queries and the design are created in tandem. You design the schemas with the indices and queries in mind, write queries that use your indices. A good design doesn’t magically scale. It’s based on how you set up your indices and write SQL. A go…

And's and or's are trivial in any good ORM. There are valid reasons to not want to use an ORM, but they are more around the the object/relational impedance mismatch, coupling table design to the domain model, etc. But the alternative to an ORM is not opaque blobs if SQL hard coded into the app all over. How do you handle SQL injection attacks for example? What if you add/rename/drop a column? Do you just grep you cod…

Why would we have table references all over the app? We still use centralized models, just not ORMs.

Have a class representing a table and methods where you hit the database and map the response to an instance of the class.

It’s nice in a typed language when I map what the query will return and the compiler enforces it.

But not all my queries map to a class, but it’s not a big mess since we only use statically typed languages on the server so I still need to map the result to a tuple or dictionary of not a class.

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

#34

Post by jasonwatkinspdx »

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.

This has long been a pet peeve of mine.

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

#35

Post by aeyes »

devmunchies wrote: post #27
jacobsenscott wrote: post #20

Earlier quoted context omitted.

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.

This doesn’t really make sense to me. You don’t just design a good table schema independently and start querying it. The design isn’t a step that comes before the queries. The queries and the design are created in tandem. You design the schemas with the indices and queries in mind, write queries that use your indices. A good design doesn’t magically scale. It’s based on how you set up your indices and write SQL. A go…

> You don’t just design a good table schema independently and start querying it

This is only true for trivial problems, in the real world you are going to have tradeoffs. You can't design a schema for transactional and analytical workload at the same time, yet every type of business needs some sort of analytics on their data.

The great thing about SQL is that you don't exactly need to know what your future queries are going to look like. Or your dataset.

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

#36

Post by grey-area »

devmunchies wrote: post #33
jacobsenscott wrote: post #31

Earlier quoted context omitted.

And's and or's are trivial in any good ORM. There are valid reasons to not want to use an ORM, but they are more around the the object/relational impedance mismatch, coupling table design to the domain model, etc. But the alternative to an ORM is not opaque blobs if SQL hard coded into the app all over. How do you handle SQL injection attacks for example? What if you add/rename/drop a column? Do you just grep you cod…

Why would we have table references all over the app? We still use centralized models, just not ORMs. Have a class representing a table and methods where you hit the database and map the response to an instance of the class. It’s nice in a typed language when I map what the query will return and the compiler enforces it. But not all my queries map to a class, but it’s not a big mess since we only use statically typed…

Ah ok, so you wrote your own mapping of object relations.

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

#37

Post by jzoch »

petergeoghegan wrote: post #28
jzoch wrote: post #22

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.

That may be true, but that doesn't seem like the important takeaway to me. The important takeaway is "In contrast to cardinality estimation, the contribution of the cost model to the overall query performance is limited". Actually, the paper itself says "This improvement [the 34% one you mention] is not insignificant, but on the other hand, it is dwarfed by improvement in query runtime observed when we replace estima…

Oh definitely agree. Mainly I find the cost model interesting because it’s so simple and contained. Cardinality estimation is a hard problem and requires real expertise. But the easy wins you get by just throwing out something based on old assumptions like the cost model is fun!

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

#38

Post by iracic »

why-el wrote: post #25
stubish wrote: post #16

Earlier quoted context omitted.

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.

You can also disable seq_scan and force pg to consider indexes, usually that's enough.

Access method is just part of the story. Same index may be accessed in different ways, you might also want to combine them. Sometimes you may change table join order to see how it estimates (or executes). Usually there are two parts 1) cost and reasoning for some estimation 2) how it executes (timings, resource usage, locks/contention)

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

#39

Post by ergl »

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.

With CS conference papers, it's quite easy to see the date on the bottom-left of the first page. You see the copyright year, conference name, etc

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

#40

Post by tomjen3 »

jacobsenscott wrote: post #31
devmunchies wrote: post #27

Earlier quoted context omitted.

This doesn’t really make sense to me. You don’t just design a good table schema independently and start querying it. The design isn’t a step that comes before the queries. The queries and the design are created in tandem. You design the schemas with the indices and queries in mind, write queries that use your indices. A good design doesn’t magically scale. It’s based on how you set up your indices and write SQL. A go…

And's and or's are trivial in any good ORM. There are valid reasons to not want to use an ORM, but they are more around the the object/relational impedance mismatch, coupling table design to the domain model, etc. But the alternative to an ORM is not opaque blobs if SQL hard coded into the app all over. How do you handle SQL injection attacks for example? What if you add/rename/drop a column? Do you just grep you cod…

You still separate your views from your controllers and sql injection is not an issue when you bind params, but even if you are using an ORM you can't change and add columns without some impact on the code (you need to update forms to add the new value to the created objects, show it, and, presumably, do something useful with it).

And that also assumes that you are using databases as an object store. Databases are also useful to answer questions like: show me the number of users who have signed up each day for the last month.

Post reply on HN