CODEWITHSUNDEEP
djangopostgresql
racinjasin
racinjasin

Reputation: 1052

Index not being used for django desc

I have a django model with the following indexes:

class Meta:
    indexes = [
        models.Index(fields=['-current']),
        models.Index(fields=['current']),
    ]

The relevant field is defined as:

current = models.IntegerField(null=True)

These were added and I ran the migration and saw the results:

  companies/migrations/0294_auto_20220110_1155.py
    - Create index companies_c_current_f2c815_idx on field(s) -current of model company
    - Create index companies_c_current_c8bcb7_idx on field(s) current of model company

I've found that running a django-rest-framework query with ordering=current is ~5x faster than with ordering=-current. Using PSQL explain I get the following:

# explain analyze select * from company order by current desc nulls last limit 100;
                                            

    QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=41028.75..41040.42 rows=100 width=1223) (actual time=866.587..867.650 rows=100 loops=1)   ->  Gather Merge  (cost=41028.75..68747.19 rows=237570 width=1223) (actual time=866.585..867.644 rows=100 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=40028.73..40325.69 rows=118785 width=1223) (actual time=849.292..849.296 rows=48 loops=3)
               Sort Key: current DESC NULLS LAST
               Sort Method: top-N heapsort  Memory: 275kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 314kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 299kB
               ->  Parallel Seq Scan on companies_company  (cost=0.00..35488.85 rows=118785 width=1223) (actual time=0.278..756.498 rows=95028 loops=3)
 Planning Time: 0.444 ms
 Execution Time: 867.759 ms
(12 rows)


# explain analyze select * from company order by current asc nulls last limit 100;
                                                         

    QUERY PLAN                                                                                    
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..49.79 rows=100 width=1223) (actual time=1.700..13.268 rows=100 loops=1)
   ->  Index Scan Backward using companies_c_current_f2c815_idx on companies_company  (cost=0.42..140727.06 rows=285084 width=1223) (actual time=1.698..13.244 rows=100 loops=1)
 Planning Time: 1.139 ms
 Execution Time: 13.426 ms
(4 rows)

It seems clear from the above that the asc is using the index while the desc is not, which explains the time difference. My question is: why not? Is there a different way the index needs to be added to make sure it's used for both asc and desc? I originally tried adding it to the model field definition with db_index=True but that yielded the same issue.

Upvotes: 2

Views: 584

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246123

An index can be scanned in both directions, but it needs to be sorted exactly like the ORDER BY clause it is intended by default. A plain index is sorted in ASC NULLS LAST order, so it can support that order or the reverse, namely DESC NULLS FIRST. To create an index that can support your ORDER BY clause, use

CREATE INDEX ON companies_company (current ASC NULLS FIRST);

Upvotes: 1

Related Questions