6 ms·

I'm usually the guy trashing Django's ORM pretty hard, but I don't think this comment is very fair. It says in the article: "A new field has been introduced t

I'm usually the guy trashing Django's ORM pretty hard, but I don't think this comment is very fair. It says in the article: "A new field has been introduced to the model. This way the compatibility does not brake (sic) with other modules that use the existing schema" Using an indexed range type is a totally valid approach. I doubt it performs better than a multi-column index, but it's unlikely to be worse and you get to use range operators, like overlaps (&&) and others: https://www.postgresql.org/docs/current/functions-range.html https://www.postgresql.org/docs/current/functions-range.html

qeternity 5y ago
Both of our comments can be correct. I’m not sure what the new field has to do with it. Irrespective of anything else, setting db_index on both columns individually, instead of creating a new single composite index, suggests the authors are far from experts in Django’s ORM.
- michelpp 5y ago
  A composite, or multi-column index, is not the same thing as two separately indexed columns. They are distinct patterns with different trade-offs: "A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned." https://www.postgresql.org/docs/current/indexes-multicolumn.html https://www.postgresql.org/docs/current/indexes-multicolumn....
  - inshadows 5y ago
    Why not both then? I mean three... 2 for each column, one multi-column.
    - jimktrains2 5y ago
      Storage overhead and the overhead of keeping the index up to date. if you have a write-heavy workload or one where transactions can lock the index, that can cause throughput issues.
    - aeyes 5y ago
      If you have a multi-column index on (number_start, number_end) Postgres will use it even if you are only filtering on number_start. An additional single column index on number_start would be redundant.
  - qeternity 5y ago
    Yes, I know, this is my entire point. They should have tried a composite index, which they didn’t.
zmmmmm 5y ago
> I doubt it performs better than a multi-column index Is that true? I am curious because in general there are specific algorithms and data structures used for range ops so it is odd if postgresql would not use them and fall back to equivalent of a multicolumn index.

qeternity 5y ago

Both of our comments can be correct. I’m not sure what the new field has to do with it. Irrespective of anything else, setting db_index on both columns individually, instead of creating a new single composite index, suggests the authors are far from experts in Django’s ORM.

michelpp 5y ago
A composite, or multi-column index, is not the same thing as two separately indexed columns. They are distinct patterns with different trade-offs: "A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned." https://www.postgresql.org/docs/current/indexes-multicolumn.html https://www.postgresql.org/docs/current/indexes-multicolumn....
- inshadows 5y ago
  Why not both then? I mean three... 2 for each column, one multi-column.
  - jimktrains2 5y ago
    Storage overhead and the overhead of keeping the index up to date. if you have a write-heavy workload or one where transactions can lock the index, that can cause throughput issues.
  - aeyes 5y ago
    If you have a multi-column index on (number_start, number_end) Postgres will use it even if you are only filtering on number_start. An additional single column index on number_start would be redundant.
- qeternity 5y ago
  Yes, I know, this is my entire point. They should have tried a composite index, which they didn’t.

zmmmmm 5y ago

> I doubt it performs better than a multi-column index Is that true? I am curious because in general there are specific algorithms and data structures used for range ops so it is odd if postgresql would not use them and fall back to equivalent of a multicolumn index.