6 ms·

Seems a bit unconventional to manually specify which index you'd like to use when writing queries. This would normally be something handled by a query optimizer

by hendzen 13y ago

Seems a bit unconventional to manually specify which index you'd like to use when writing queries. This would normally be something handled by a query optimizer. Of course, many DBMS provide some facility for specifying indices manually through query hinting, but for non-trivial queries, the optimizer often can do a better job than a programmer at picking the most efficient query plan.

jdoliner 13y ago
Hi, I implemented secondary indexes in RethinkDb. This was an intentional choice we made for a couple of reasons. The most pragmatic one was that we don't have an optimizer and we thought that secondary indexes could still be useful to a lot of people without one. And we've found it's always better to ship early and get feedback sooner rather than later. Another reason though is we're not totally sure we're sold on the state of the art of query optimizers. From what we've seen they can introduce a number of non intuitive behaviors and it can be hard to tell which operations will use which index. Query optimizers are nice when they work correctly but they don't always work correctly so for the first release of secondary indexes we opted to make something that had very clear precise semantics and then build on it. We will eventually have a query optimizer but it's a big problem and we don't just want to implement what others have because we think we can do better.
- antirez 13y ago
  +1, a good approach could be to still make users able to specify manually what index to use even when the optimizer will be available. Sometimes humans just know better.
  - jdoliner 13y ago
    That's something we'll make sure to keep around indefintely. I always hate things that are too magically, which optimizers frequently seem to me, but I also get why they're useful to people. The best compromise I think is to give people the magic and a way to opt out of the magic.
    - coffeemug 13y ago
      The best compromise I think is to give people the magic and a way to opt out of the magic. This is actually a surprisingly succinct way to describe the philosophy behind most RethinkDB features. This approached worked really well so far.
      - jdoliner 13y ago
        Get thee to a tweetery.
- hendzen 13y ago
  Thanks for the reply. Do you have any resources you could share regarding the pitfalls of query optimizers? My experience with them comes primarily from studying the System R optimizer where the literature presented query optimization as a boon to performance without mentioning such drawbacks. Congratulations on the release by the way.
  - coffeemug 13y ago
    The primary problem is that picking indexes and execution algorithms essentially involves traversing an exponential space (and taking guesses about costs of things). Modern DB optimizers have hundreds (or thousands) of heuristics to do this well, but every once in a while they pick the wrong path and run a suboptimal query. This might be ok for offline analytics, but can be disastrous for production OLTP environments because a small change in statistical information can trip up such an edge case and the live system will crawl to a halt. For example, Postgres optimizer has been around for a long time and is very mature, but they still fix these bugs quite often AFAIK. In many production environments admins end up having to hack the queries to "trick" the optimizer into doing what they want, which of course defeats the whole purpose. So, for real-time systems, being able to specify indexes manually is actually a productivity boost, because you often know exactly how you want the query executed. I'll try to dig up some info on this, I don't have any links off the top of my head.
    - jeffdavis 13y ago
      Even if you manually write the query plan, you are still subject to similar disasters. That's because your data is changing, which can cause the query performance to fall off a cliff without changing the plan.
- gleb 13y ago
  A.k.a. "worse is better" approach. Seems like a good call to me.
- sergei 13y ago
  A decent cost based optimizer will be right most of the time, and you have hints for the cases where it falters. Your approach is simply hiding the query optimizer choices inside of the app, making it more fragile. Let's say I have two fields: a and b, both of which have an index. And then I have a query with predicates across both a and b. For example, a = 10 and b = 20. One of the core facilities within an optimizer is selectivity estimation. By looking at the statistics, the optimizer will see that a = 10 might look at 10,000 rows while b = 20 might only look at 10. So the optimal and desired choice would be to use index over b. However, the exact same query construct with different parameters (e.g. a = 50 and b = 3) might flip the index selection. Now let's imagine I had to implement this inside of my app. Every time I have such a constraint, the app has to become aware of selectivity to know which indexes to use based on input parameters.
  - josegonzalez 13y ago
    Which is good, because now your developers don't just add queries to the application without thinking about performance. You don't have to be a dba to understand how using one index vs another will affect the performance - and thus conversion rate - of your application.
    - dennis82 13y ago
      no, it's categorically not good to make an application more fragile. A weakness restated is not a strength. Every database should have an optimizer, period.
      - josegonzalez 13y ago
        I did not state that an optimizer should not exist for a database - I think thats key actually - but rather that the tradeoff they made this time around was fundamentally good in that - at least for now - it forces the developer to think about application performance. If that happens to make an application more fragile, I think that is more of a code organization/tooling issue than anything else.
      - 13y ago
- mfenniak 13y ago
  Hi Joe, I just wanted to pipe in and say that's excellent reasoning for this design, especially since the ability to create indexes on arbitrary ReQL expressions would make an intelligent query planner into a lot of work. I'm looking forward to giving these a try.
  - jdoliner 13y ago
    Thank you.
- wpietri 13y ago
  Bless you. The thing that makes me crazy about database engines is the "60% the time, it works every time" phenomenon, wherein things are magically fast until they are magically slow. Then the discussion goes seamlessly from "don't worry your pretty head, just build your app" to "of course you have to really understand <proprietary, opaque, uncontrollable internal technology hidden by abstractions>." With no recognition that these are basically contradictory messages. If you can really hide the complexity so it almost never matters, great. But here, where you couldn't, I think you made the right choice by exposing it.
  - jdoliner 13y ago
    Thanks, we've gotten a lot of mileage out of the following ethos: Let users ignore danger, but never let users ignore the fact that they're ignoring danger. Always give users a way to learn exactly what the dangers are. In the case of the query optimizer we haven't gotten to giving people a way to ignore these dangers yet but we will eventually.
    - vosper 13y ago
      This is a great philosophy, I'd love to see it applied more broadly.
- JulianMorrison 13y ago
  Deterministic semantics are something I approve of. I'd rather have indexes under my control than the DB's. Is it one index per chained operation (filter with index A then join with index B, for example)? Or can you only use one index per complete query?
  - jdoliner 13y ago
    You can use multiple indexes in some queries. The case you described will work. However you can't use multiple indexes from the same table. For example: table.between(...).between(...) won't work. You'd have to use a filter (and thus a linear scan to do the second between. This seems like it would be a good thing for us to add to the FAQ because the rules on it can be a bit complicated. However the best way to find out might actually just be to try. Because we're not using an optimizer in the background you have to explicitly say which indexes you're using and thus they system will just refuse to do queries if it can't use indexes the way you're asking it to.
  - coffeemug 13y ago
    You can't currently do index intersection (so you can't chain `between` commands), but you can absolutely use multiple indexes with joins. E.g. `table.between(...).eq_join(...).eq_join(...)` will use three indexes in this example. EDIT: err, Joe beat me to it.

by hendzen 13y ago

jdoliner 13y ago

Hi, I implemented secondary indexes in RethinkDb. This was an intentional choice we made for a couple of reasons. The most pragmatic one was that we don't have an optimizer and we thought that secondary indexes could still be useful to a lot of people without one. And we've found it's always better to ship early and get feedback sooner rather than later. Another reason though is we're not totally sure we're sold on the state of the art of query optimizers. From what we've seen they can introduce a number of non intuitive behaviors and it can be hard to tell which operations will use which index. Query optimizers are nice when they work correctly but they don't always work correctly so for the first release of secondary indexes we opted to make something that had very clear precise semantics and then build on it. We will eventually have a query optimizer but it's a big problem and we don't just want to implement what others have because we think we can do better.

antirez 13y ago
+1, a good approach could be to still make users able to specify manually what index to use even when the optimizer will be available. Sometimes humans just know better.
- jdoliner 13y ago
  That's something we'll make sure to keep around indefintely. I always hate things that are too magically, which optimizers frequently seem to me, but I also get why they're useful to people. The best compromise I think is to give people the magic and a way to opt out of the magic.
  - coffeemug 13y ago
    The best compromise I think is to give people the magic and a way to opt out of the magic. This is actually a surprisingly succinct way to describe the philosophy behind most RethinkDB features. This approached worked really well so far.
    - jdoliner 13y ago
      Get thee to a tweetery.
hendzen 13y ago
Thanks for the reply. Do you have any resources you could share regarding the pitfalls of query optimizers? My experience with them comes primarily from studying the System R optimizer where the literature presented query optimization as a boon to performance without mentioning such drawbacks. Congratulations on the release by the way.
- coffeemug 13y ago
  The primary problem is that picking indexes and execution algorithms essentially involves traversing an exponential space (and taking guesses about costs of things). Modern DB optimizers have hundreds (or thousands) of heuristics to do this well, but every once in a while they pick the wrong path and run a suboptimal query. This might be ok for offline analytics, but can be disastrous for production OLTP environments because a small change in statistical information can trip up such an edge case and the live system will crawl to a halt. For example, Postgres optimizer has been around for a long time and is very mature, but they still fix these bugs quite often AFAIK. In many production environments admins end up having to hack the queries to "trick" the optimizer into doing what they want, which of course defeats the whole purpose. So, for real-time systems, being able to specify indexes manually is actually a productivity boost, because you often know exactly how you want the query executed. I'll try to dig up some info on this, I don't have any links off the top of my head.
  - jeffdavis 13y ago
    Even if you manually write the query plan, you are still subject to similar disasters. That's because your data is changing, which can cause the query performance to fall off a cliff without changing the plan.
gleb 13y ago
A.k.a. "worse is better" approach. Seems like a good call to me.
sergei 13y ago
A decent cost based optimizer will be right most of the time, and you have hints for the cases where it falters. Your approach is simply hiding the query optimizer choices inside of the app, making it more fragile. Let's say I have two fields: a and b, both of which have an index. And then I have a query with predicates across both a and b. For example, a = 10 and b = 20. One of the core facilities within an optimizer is selectivity estimation. By looking at the statistics, the optimizer will see that a = 10 might look at 10,000 rows while b = 20 might only look at 10. So the optimal and desired choice would be to use index over b. However, the exact same query construct with different parameters (e.g. a = 50 and b = 3) might flip the index selection. Now let's imagine I had to implement this inside of my app. Every time I have such a constraint, the app has to become aware of selectivity to know which indexes to use based on input parameters.
- josegonzalez 13y ago
  Which is good, because now your developers don't just add queries to the application without thinking about performance. You don't have to be a dba to understand how using one index vs another will affect the performance - and thus conversion rate - of your application.
  - dennis82 13y ago
    no, it's categorically not good to make an application more fragile. A weakness restated is not a strength. Every database should have an optimizer, period.
    - josegonzalez 13y ago
      I did not state that an optimizer should not exist for a database - I think thats key actually - but rather that the tradeoff they made this time around was fundamentally good in that - at least for now - it forces the developer to think about application performance. If that happens to make an application more fragile, I think that is more of a code organization/tooling issue than anything else.
    - 13y ago
mfenniak 13y ago
Hi Joe, I just wanted to pipe in and say that's excellent reasoning for this design, especially since the ability to create indexes on arbitrary ReQL expressions would make an intelligent query planner into a lot of work. I'm looking forward to giving these a try.
- jdoliner 13y ago
  Thank you.
wpietri 13y ago
Bless you. The thing that makes me crazy about database engines is the "60% the time, it works every time" phenomenon, wherein things are magically fast until they are magically slow. Then the discussion goes seamlessly from "don't worry your pretty head, just build your app" to "of course you have to really understand <proprietary, opaque, uncontrollable internal technology hidden by abstractions>." With no recognition that these are basically contradictory messages. If you can really hide the complexity so it almost never matters, great. But here, where you couldn't, I think you made the right choice by exposing it.
- jdoliner 13y ago
  Thanks, we've gotten a lot of mileage out of the following ethos: Let users ignore danger, but never let users ignore the fact that they're ignoring danger. Always give users a way to learn exactly what the dangers are. In the case of the query optimizer we haven't gotten to giving people a way to ignore these dangers yet but we will eventually.
  - vosper 13y ago
    This is a great philosophy, I'd love to see it applied more broadly.
JulianMorrison 13y ago
Deterministic semantics are something I approve of. I'd rather have indexes under my control than the DB's. Is it one index per chained operation (filter with index A then join with index B, for example)? Or can you only use one index per complete query?
- jdoliner 13y ago
  You can use multiple indexes in some queries. The case you described will work. However you can't use multiple indexes from the same table. For example: table.between(...).between(...) won't work. You'd have to use a filter (and thus a linear scan to do the second between. This seems like it would be a good thing for us to add to the FAQ because the rules on it can be a bit complicated. However the best way to find out might actually just be to try. Because we're not using an optimizer in the background you have to explicitly say which indexes you're using and thus they system will just refuse to do queries if it can't use indexes the way you're asking it to.
- coffeemug 13y ago
  You can't currently do index intersection (so you can't chain `between` commands), but you can absolutely use multiple indexes with joins. E.g. `table.between(...).eq_join(...).eq_join(...)` will use three indexes in this example. EDIT: err, Joe beat me to it.