3 ms·

I think it's a neat feature. But note that orderedness isn't just useful for range queries, it also helps to satisfy ORDER BY and to allow for merge joins witho

I think it's a neat feature. But note that orderedness isn't just useful for range queries, it also helps to satisfy ORDER BY and to allow for merge joins without a sort steps. There's also no yet support for index-only scans (probably never), no constraints, and no multi-column index support. There's also still some performance kinks to work out with the current hash index performance - large when growing the index quickly or under very high concurrency.

Tostino 9y ago
Do you know how that'd work out for those of us with UUID primary keys rather than integer primary keys?
- anarazel 9y ago
  Not quite sure I understand exactly what you're getting at? Are you wondering if those constraints are less important because you less frequently order by UUIDs? If so, I don't think it makes a huge difference - merge joins are still relevant, and you'll often ORDER BY anyway just to get stable resultsets. If the question is if there's bigger benefits to hash joins for largely unordered data? Then yes, that's true.
brlewis 9y ago
Stop it! You're giving away all possible answers to one of my favorite interview questions. Edit: not seriously asking you to stop, but the interview question part is true
- Illniyar 9y ago
  I hope those are interview questions for dbas because it's way outside common knowledge for backend developers.
  - tatersolid 9y ago
    Why are back-end developers not supposed to have a deep understanding of their tools? I hear this sort of statement all the time, it’s infuriating. I once even had a dev team lead claim that it was not a dev’s responsibility at all if the app generates bad SQL; the DBA is supposed to make the queries fast (this person no longer works for me, but I consistently hear similar claims from consultants and others in the dev community). DBAs are expected to understand the crappy inefficient row-at-a-time ORM in use by the devs, which implies understanding a particular programming language, but not the other way around? There’s nothing magical about SQL databases that a decent developer can’t understand.
    - marcosdumay 9y ago
      > Why are back-end developers not supposed to have a deep understanding of their tools? You mean all their tools? Like how does modern processors handle branch predictions in the face of out of order operations, or what differences in server cooling will affect throughput or peak performance? Do you have a deep understanding of all the tools you use? Because I honestly doubt so.
    - BOBOTWINSTON 9y ago
      I think there is a very large gap between being able to answer that interview question effectively and having decent understanding of how to not misuse your ORM. Specifically with ORMs, It does seem that a lot of devs don't understand or care about the generated SQL... but that question was way above the knowledge level I would expect/want out of someone IMO.
  - brlewis 9y ago
    Not for DBAs, but I also don't expect candidates to get all the answers in that comment. It's a decent data structure question to ask why databases generally use binary trees for indexes when hash lookups are faster. Non-DBAs usually have to think a little bit.
    - jeltz 9y ago
      Databases do not use binary trees for indexes and tables, they use B-trees. B-trees are a family of self balancing tree data structures which usually are not binary because in a database you want the tree to have few levels.
      - virmundi 9y ago
        Thank you. I remembe having to balance a B-Tree in my DB final. 5 nodes deep. Had to show all of the work. I think it ended up with 20 some revisions based on all the insert and delete statements.
gfodor 9y ago
Thanks! The merge join issue seems like one that probably would affect many workloads. Ordering by key less so but good to realize. I guess it would be helpful for this feature to also include some documentation about if/when to use hash indexes, since up to this point the db has issued a warning basically saying "don't use these." Right now I'd imagine that there will be a lot of confusion around it given the feature's history so people will just not consider it.

Tostino 9y ago

Do you know how that'd work out for those of us with UUID primary keys rather than integer primary keys?

anarazel 9y ago
Not quite sure I understand exactly what you're getting at? Are you wondering if those constraints are less important because you less frequently order by UUIDs? If so, I don't think it makes a huge difference - merge joins are still relevant, and you'll often ORDER BY anyway just to get stable resultsets. If the question is if there's bigger benefits to hash joins for largely unordered data? Then yes, that's true.

brlewis 9y ago

Stop it! You're giving away all possible answers to one of my favorite interview questions. Edit: not seriously asking you to stop, but the interview question part is true

Illniyar 9y ago
I hope those are interview questions for dbas because it's way outside common knowledge for backend developers.
- tatersolid 9y ago
  Why are back-end developers not supposed to have a deep understanding of their tools? I hear this sort of statement all the time, it’s infuriating. I once even had a dev team lead claim that it was not a dev’s responsibility at all if the app generates bad SQL; the DBA is supposed to make the queries fast (this person no longer works for me, but I consistently hear similar claims from consultants and others in the dev community). DBAs are expected to understand the crappy inefficient row-at-a-time ORM in use by the devs, which implies understanding a particular programming language, but not the other way around? There’s nothing magical about SQL databases that a decent developer can’t understand.
  - marcosdumay 9y ago
    > Why are back-end developers not supposed to have a deep understanding of their tools? You mean all their tools? Like how does modern processors handle branch predictions in the face of out of order operations, or what differences in server cooling will affect throughput or peak performance? Do you have a deep understanding of all the tools you use? Because I honestly doubt so.
  - BOBOTWINSTON 9y ago
    I think there is a very large gap between being able to answer that interview question effectively and having decent understanding of how to not misuse your ORM. Specifically with ORMs, It does seem that a lot of devs don't understand or care about the generated SQL... but that question was way above the knowledge level I would expect/want out of someone IMO.
- brlewis 9y ago
  Not for DBAs, but I also don't expect candidates to get all the answers in that comment. It's a decent data structure question to ask why databases generally use binary trees for indexes when hash lookups are faster. Non-DBAs usually have to think a little bit.
  - jeltz 9y ago
    Databases do not use binary trees for indexes and tables, they use B-trees. B-trees are a family of self balancing tree data structures which usually are not binary because in a database you want the tree to have few levels.
    - virmundi 9y ago
      Thank you. I remembe having to balance a B-Tree in my DB final. 5 nodes deep. Had to show all of the work. I think it ended up with 20 some revisions based on all the insert and delete statements.

gfodor 9y ago

Thanks! The merge join issue seems like one that probably would affect many workloads. Ordering by key less so but good to realize. I guess it would be helpful for this feature to also include some documentation about if/when to use hash indexes, since up to this point the db has issued a warning basically saying "don't use these." Right now I'd imagine that there will be a lot of confusion around it given the feature's history so people will just not consider it.