[HN Gopher] Increase Performance via Range Fields in Django Quer...
       ___________________________________________________________________
        
       Increase Performance via Range Fields in Django Querysets on
       PostgreSQL
        
       Author : todsacerdoti
       Score  : 29 points
       Date   : 2021-05-24 14:07 UTC (8 hours ago)
        
 (HTM) web link (nezhar.com)
 (TXT) w3m dump (nezhar.com)
        
       | aeyes wrote:
       | Was a multicolumn index tested? I don't see why it shouldn't
       | yield the same result.
        
         | qeternity wrote:
         | Yeah I struggle to take this seriously when their attempt at
         | indexing is to set db_index on both fields, which yields two
         | independent b trees.
        
           | michelpp wrote:
           | 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
        
             | qeternity wrote:
             | 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 wrote:
               | 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....
        
       ___________________________________________________________________
       (page generated 2021-05-24 23:01 UTC)