To Index Or Not, That’s Not The Question (DjangoCon Europe 2017)

My talk from DjangoCon Europe 2017

Speaker notes at https://markusholtermann.eu/2017/04/to-index-or-not-is-not-the-question/

Transcript

1. To Index Or Not That’s Not The Question @m_holtermann • github.com/MarkusH • markusholtermann.eu
2. Some Background Theory
4. How Database Lookups Work
5. id name age 1 Emma 3 2 Mia 37 3 Ben 41 4 Jonas 11 5 Anna 19 6 Lena 5 7 Paul 13
6. id name age 1 Emma 3 2 Mia 37 3 Ben 41 4 Jonas 11 5 Anna 19 6 Lena 5 7 Paul 13 SELECT * FROM people WHERE age = 19;
7. id name age 1 Emma 3 2 Mia 37 3 Ben 41 4 Jonas 11 5 Anna 19 6 Lena 5 7 Paul 13 SELECT * FROM people WHERE age = 19;
8. What are Indexes?
9. B-Tree / B+ Tree
11. 11 37 < 11 ≥ 11 and < 37 ≥ 37
12. 11 37 3 5 11 13 19 37 41 < 11 ≥ 11 and < 37 ≥ 37
13. 11 37 3 5 11 13 19 37 41 < 11 ≥ 11 and < 37 ≥ 37 3 5 11 13 37 41 19 age =
14. 11 37 3 5 11 13 19 37 41 < 11 ≥ 11 and < 37 ≥ 37 3 5 11 13 37 41 19 age =
15. id name age 1 Emma 3 2 Mia 37 3 Ben 41 4 Jonas 11 5 Anna 19 6 Lena 5 7 Paul 13 3 5 11 13 19 37 41
16. SELECT * FROM people WHERE age = 19;
17. 11 37 3 5 11 13 19 37 41 id name age … 5 Anna 19 …
18. SELECT COUNT(*) FROM people WHERE age BETWEEN 5 AND 13; SELECT COUNT(*) FROM people WHERE age >= 5 AND age <= 13;
19. 11 37 3 5 11 13 19 37 41
20. Indexes Are Awesome
21. • db_index=True
22. • db_index=True • index_together=(('name', 'age'),)
23. • db_index=True • index_together=(('name', 'age'),) • ForeignKey() / OneToOneField()
24. • db_index=True • index_together=(('name', 'age'),) • ForeignKey() / OneToOneField() • primary_key=True
26. Google Summer of Code 2016
27. Introducing Index(fields, name)
28. from django.db import models class Person(models.Model): name = models.CharField(max_length=200) class Meta: indexes = [ models.Index( fields=['name'], name='name_idx', ), ]
29. from django.db import models class Person(models.Model): name = models.CharField(max_length=200) age = models.PositiveSmallIntegerField() class Meta: indexes = [ models.Index( fields=['name', 'age'], name='name_age_idx', ), ]
30. from django.contrib.postgres.fields import JSONField from django.contrib.postgres.indexes import GinIndex from django.db import models class Doc(models.Model): data = JSONField() class Meta: indexes = [ GinIndex( fields=['data'], name='data_gin', ), ]
31. $ python manage.py sqlmigrate someapp 0001 BEGIN; -- -- Create model Doc -- CREATE TABLE "someapp_doc" ( "id" serial NOT NULL PRIMARY KEY, "data" jsonb NOT NULL); -- -- Create index data_gin on field(s) data of model doc -- CREATE INDEX "data_gin" ON "someapp_doc" USING gin ("data"); COMMIT;
32. Feature Ideas
33. Functional Indexes from django.db import models class Author(models.Model): name = models.CharField(max_length=200) class Meta: indexes = [ FuncIndex( expression=Lower('name'), name='name_lower_idx', ), ]
34. db_index=<IndexClass> from django.db import models class Author(models.Model): name = models.CharField( max_length=200, db_index=HashIndex )
35. Field.default_index_class from django.contrib.postgres.fields import JSONField from django.contrib.postgres.indexes import GinIndex from django.db import models # Somewhere in Django’s JSONField implementation: # JSONField.default_index_class = GinIndex class Document(models.Model): data = JSONField(db_index=True)
36. Refactor index_together and db_index
37. Add GiSTIndex
38. Sprints April 6th - 7th
39. Thank You
40. I’m Markus Holtermann @m_holtermann • github.com/MarkusH • markusholtermann.eu @laterpay • laterpay.net • Django Core Developer • Software Engineer at
41. I’m Markus Holtermann @m_holtermann • github.com/MarkusH • markusholtermann.eu @laterpay • laterpay.net • Django Core Developer • Software Engineer at W e’re hiring
42. Where We’re At
43. Functional Indexes from django.db import models class Author(models.Model): name = models.CharField(max_length=200) class Meta: indexes = [ FuncIndex( expression=Lower(Col('name', None)), name='name_lower_idx', ), ]