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

Cd7648c536b4dbe940246b74044fbc52?s=47 Markus H
April 04, 2017

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/

Cd7648c536b4dbe940246b74044fbc52?s=128

Markus H

April 04, 2017
Tweet

Transcript

  1. To Index Or Not That’s Not The Question @m_holtermann •

    github.com/MarkusH • markusholtermann.eu
  2. Some Background Theory

  3. None
  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

  10. None
  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
  25. 2016

  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', ), ]