Session model has a useless LIKE index.

[Ran Benita]

The Session model has a CharField primary key, session_key. As a primary key, it is indexed, but as a CharField, it has two indexes created, one for equality matches and one for LIKE matches. Sessions are not (and should not) be matched with LIKE, so the index is unused. It will be better to remove it -- will reduce space usage and slightly reduce INSERT and DELETE overhead.

---

I use PostgreSQL, version 11. I checked whether the index is used with the following query:

select * from pg_stat_all_indexes where indexrelname like 'django_session_session_key_%_like';

Surprisingly, it is used, every time a user logs out. For some reason, postgres decides to use it on DELETE (and not for SELECT):

# explain delete from django_session where session_key = '1000';
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Delete on django_session  (cost=0.43..8.45 rows=1 width=6)
   ->  Index Scan using django_session_session_key_1136ae038ed61e94_like on django_session  (cost=0.43..8.45 rows=1 width=6)
         Index Cond: ((session_key)::text = '1000'::text)

However, when the index is dropped, the pkey index is used instead:

# drop index django_session_session_key_1136ae038ed61e94_like; 
DROP INDEX
# explain delete from django_session where session_key = '1001';
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Delete on django_session  (cost=0.43..8.45 rows=1 width=6)
   ->  Index Scan using django_session_pkey on django_session  (cost=0.43..8.45 rows=1 width=6)
         Index Cond: ((session_key)::text = '1001'::text)

so the LIKE index is not actually helpful.

[Mariusz Felisiak]

Thanks for this report, however this is a generic behavior that we cannot change only for django_session moreover as far as I'm concerned we need to create a second index with varchar/text operator. Please take a look at rationale in the #12234.