Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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/

Markus H

April 04, 2017
Tweet

More Decks by Markus H

Other Decks in Technology

Transcript

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

    github.com/MarkusH • markusholtermann.eu
  2. 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. 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;
  4. 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;
  5. 11 37 3 5 11 13 19 37 41 <

    11 ≥ 11 and < 37 ≥ 37
  6. 11 37 3 5 11 13 19 37 41 <

    11 ≥ 11 and < 37 ≥ 37 3 5 11 13 37 41 19 age =
  7. 11 37 3 5 11 13 19 37 41 <

    11 ≥ 11 and < 37 ≥ 37 3 5 11 13 37 41 19 age =
  8. 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
  9. 11 37 3 5 11 13 19 37 41 id

    name age … 5 Anna 19 …
  10. SELECT COUNT(*) FROM people WHERE age BETWEEN 5 AND 13;

    SELECT COUNT(*) FROM people WHERE age >= 5 AND age <= 13;
  11. 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', ), ]
  12. 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', ), ]
  13. 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', ), ]
  14. $ 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;
  15. 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', ), ]
  16. 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)
  17. I’m Markus Holtermann @m_holtermann • github.com/MarkusH • markusholtermann.eu @laterpay •

    laterpay.net • Django Core Developer • Software Engineer at
  18. I’m Markus Holtermann @m_holtermann • github.com/MarkusH • markusholtermann.eu @laterpay •

    laterpay.net • Django Core Developer • Software Engineer at W e’re hiring
  19. 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', ), ]