
ïŒïŒïŒäžä»¶ããïŒä»¶ãäžç¬ã§æ¢ããããŒã¿ããŒã¹ã®çŽ¢åŒã®ä»çµã¿ãšãDjangoã§ã®äœãæ¹
ååã®èšäºãDjangoã®ORMãšãã€ã°ã¬ãŒã·ã§ã³ã®ä»çµã¿ãã§ã¯ãæ¶ç©ºã®ãæçæå®€ããäŸã«ãDjango ã®ã¢ãã«ã PostgreSQL ã®ããŒãã«ã«ãªããŸã§ãèŠãŸããããã®äžã§ãForeignKey ã«ã¯ãã€ã³ããã¯ã¹ããèªåã§äœãããããšå°ãã ãè§ŠããŠããŸãã
ä»åã¯ããã®ç¶ãã§ããæçæå®€ã®åè¬è ã100äžäººã«å¢ãããã¡ãŒã«ã¢ãã¬ã¹ã§1äººãæ¢ããšããããŒã¿ããŒã¹ã®äžã§äœãèµ·ããŠããã®ããèŠãŠãããŸãã
玢åŒïŒã€ã³ããã¯ã¹ïŒããããšããšãªããšãã§ãæ¢ãæ¹ãã©ãå€ããã®ãã玢åŒã®äžèº«ã¯ã©ã®ãããªåœ¢ãããŠããã®ããDjango ã§ã¯ã©ãä»ããã®ãã玢åŒãå¹ããªãæ¢ãæ¹ãã玢åŒã®ä»£åã¯äœããã©ããå®éã«åãããŠæž¬ã£ãçµæã§ãŸãšããŸãããè³æã§ç¢ºèªããå 容ã«ã¯ã1ã®ãããªåºå žã®çªå·ãä»ããŠããŸãã
ãŸãçšèªããããã
玢åŒïŒã€ã³ããã¯ã¹ïŒïŒæ¬ã®å·»æ«ã«ãã玢åŒã®ããã«ãç¹å®ã®åã®å€ãããç®çã®è¡ããã°ããæ¢ãããã®ä»çµã¿ã§ãããã®èšäºã§ã¯ã玢åŒããšåŒã³ãŸãã
æ€çŽ¢ïŒãã®èšäºã§ã¯ãæ¡ä»¶ã«åãè¡ãããŒã¿ããŒã¹ããåãåºãããšãæããŸããDjango ã® filter() ããããã«åœãããŸãã
ããŒãžïŒããŒã¿ããŒã¹ãããŒã¿ãèªã¿æžããããšãã®ãã²ãšãããŸãã®åäœã§ããä»åã®ç°å¢ã§ã¯ã1ããŒãžã8KBã§ããã
å®è¡èšç»ïŒããŒã¿ããŒã¹ãããã®æ€çŽ¢ããã©ã®é çªã§ãäœã䜿ã£ãŠè¡ããããæ±ºããæé æžã§ãã
PostgreSQL ã®å ¬åŒææžãã玢åŒã®èãæ¹ãæ¬ã®å·»æ«ã®çŽ¢åŒã«ããšããŠããŸããèªè ããã調ã¹ãèšèãæŠå¿µããã¢ã«ãã¡ãããé ã«å·»æ«ã«éããŠããããšãã説æã§ã1ã

äŸã«äœ¿ããæçæå®€ãã«ãé ç®ãè¶³ã
ååã®æçæå®€ã®ã¢ãã«ã®ãã¡ãåè¬è ïŒStudentïŒã«ã¡ãŒã«ã¢ãã¬ã¹ãšå ¥äŒæ¥ãè¶³ããŸããã
class Student(models.Model):
"""åè¬è
"""
name = models.CharField("åå", max_length=50)
email = models.CharField("ã¡ãŒã«ã¢ãã¬ã¹", max_length=100, default="")
joined_on = models.DateField("å
¥äŒæ¥", null=True)python manage.py makemigrations ãš python manage.py migrate ãå®è¡ãããšãåè¬è ã®ããŒãã«ã«2ã€ã®åãè¶³ãããŸãããããŸã§ã¯ååã®èšäºãšåãæµãã§ãã
ãã®ããã§ãèšäºçšã®æ¶ç©ºã®ããŒã¿ãå ¥ããŸããã
åè¬è ïŒ100äžäººïŒååã¯æ¶ç©ºãã¡ãŒã«ã¢ãã¬ã¹ã¯ user0000001@example.com ã®åœ¢ïŒ
è¬åº§ïŒ1,000ä»¶
ç³èŸŒïŒçŽ200äžä»¶ïŒåè¬è 1人ããè¬åº§ã«1ã2ä»¶ç³ã蟌ãã 圢ïŒ
æ€èšŒã«ã¯ Django 5.2 ãš PostgreSQL 16 ã䜿ããŸããã

玢åŒããªããšãäžããå šéšãèŠã
1äººãæ¢ãã³ãŒã
ã¡ãŒã«ã¢ãã¬ã¹ã§åè¬è ã1人æ¢ãã³ãŒãã¯ãDjango ã§ã¯æ¬¡ã®1è¡ã§ãã
Student.objects.filter(email="user0765432@example.com")ãã®ã¡ãŒã«ã¢ãã¬ã¹ã®åè¬è ã¯ã100äžäººã®äžã«1人ããããŸãããããã§ãã玢åŒããªããã°ãããŒã¿ããŒã¹ã¯ãã®1人ãã©ãã«ããããç¥ããŸããã
å šä»¶ãé ã«ç¢ºããã
玢åŒããªããšããããŒã¿ããŒã¹ã¯è¡šã®æåã®è¡ããæåŸã®è¡ãŸã§ã1è¡ãã€ãã¡ãŒã«ã¢ãã¬ã¹ãäžèŽããããã確ãããŸããPostgreSQL ã®å ¬åŒææžããæºåããªããã°ãè¡šå šäœã1è¡ãã€èª¿ã¹ãŠäžèŽãããã®ãæ¢ããããªãããšèª¬æããŠããŸã1ã
æ¬ã§èšãã°ã玢åŒã䜿ããã«ã1ããŒãžç®ããé ã«ããã£ãŠç®çã®èšèãæ¢ããããªãã®ã§ããç®çã®èšèãèŠã€ãã£ãŠããã»ãã®ããŒãžã«ããããããããªããããæåŸãŸã§ãããå¿ èŠããããŸãã

.explain() ã§ãããŒã¿ããŒã¹ã®æ¢ãæ¹ãèŠã
ããŒã¿ããŒã¹ãã©ãæ¢ãããã¯ãDjango ã® QuerySet ã« .explain() ãä»ãããšç¢ºèªã§ããŸããDjango ã®å ¬åŒææžã¯ãexplain() ããããŒã¿ããŒã¹ãã©ã®ããã«åãåãããå®è¡ãããïŒäœ¿ããã玢åŒãçµåãå«ãïŒã詳ããç¥ãæ¹æ³ããšèª¬æããŠããŸã7ã
Student.objects.filter(email="user0765432@example.com").explain(analyze=True)analyze=True ãä»ãããšãå®éã«æ€çŽ¢ãå®è¡ããŠãããã£ãæéã衚瀺ãããŸããçµæã®ãã¡ã倧äºãªè¡ã ããæãåºããšæ¬¡ã®ãšããã§ãã
Parallel Seq Scan on school_student
Filter: email = 'user0765432@example.com'
Rows Removed by Filter: 333333Seq ScanïŒè¡šãæåããé ã«èªãïŒSequential ScanïŒãšããæå³ã§ãã
ParallelïŒè¡šãæåãããŠèªãã ãšããæå³ã§ããä»åã¯ãæ¬äœãš2ã€ã®äœæ¥è ïŒã¯ãŒã«ãŒïŒã®3ã€ã§åããŠããŸããã
Rows Removed by FilterïŒæ¡ä»¶ã«åããã«æšãŠãè¡ã®æ°ã§ãã1ã€ãããçŽ33äžè¡ã§ã3ã€åãåããããšã1è¡ãæ®ããŠçŽ100äžè¡ãæšãŠãŠããŸãã
枬ã£ãŠã¿ããš
ãã®æ€çŽ¢ã7åããè¿ããDjango ããèŠãæéã®çãäžã®å€ïŒäžå€®å€ïŒãåããšã25.0ããªç§ã§ãããèªãã ããŒãžã®æ°ãæ°ãããšãåè¬è ã®è¡šã®ãã¹ãŠã«ããã 9,932ããŒãžã§ããã
25ããªç§ã¯ã1åã ããªãæ°ã«ãªããªãé·ãã§ããããããå šä»¶ãèŠãæ¢ãæ¹ã§ã¯ãä»¶æ°ã2åã«ãªãã°ãèªãéããããã2åã«ãªããŸããç»é¢ãéããã³ã«äœåãæ€çŽ¢ããããå€ãã®äººãåæã«äœ¿ã£ãããããšããã®å·®ãç©ã¿éãªããŸãã

䞊ãã§ããã°ãååãã€çµãã
èŸæžã®åŒãæ¹
çŽã®èŸæžã§ããããããåŒããšãã1ããŒãžç®ããé ã«ããã人ã¯ããŸãããã ãããçãäžãéããšããã¯ãã®è¡ã®ããŒãžãåºãŸãããããã¯ãã¯ãããåŸããªã®ã§ãåã®ååã¯èŠãªããŠæžã¿ãŸãã
æ®ã£ãååã®çãäžãéããšãããã®è¡ã§ããŸã åŸãã§ãããŸãåãæšãŠãŠãæ®ãã®çãäžãéããšãããã®è¡ã«ãã©ãçããŸãã
ãããã§ããã®ã¯ãèŸæžã®èšèãäºåé³é ã«äžŠãã§ããããã§ãã䞊ãã§ããã°ã1åèŠãããšã«ãæ¢ãç¯å²ãååã«ã§ããŸãããã®æ¢ãæ¹ããäºåæ¢çŽ¢ããšåŒã³ãŸãã

100äžä»¶ãªããçŽ20å
100äžä»¶ãã1åããšã«ååã«ããŠãããšã次ã®ããã«æžã£ãŠãããŸãã
1åç®ïŒ1,000,000ä»¶ â 500,000ä»¶
2åç®ïŒ500,000ä»¶ â 250,000ä»¶
3åç®ïŒ250,000ä»¶ â 125,000ä»¶
åãããã«ç¶ããŠã20åç®ã«ã¯1件以äžã«ãªã
ãªã20åãªã®ãã¯ãéã«2ãäœåæãããš100äžãè¶ ããããèãããšåãããŸãã
2ã10åæãããšã2¹Ⱐ= 1,024
2ã20åæãããšã2²Ⱐ= 1,024 à 1,024 = 1,048,576
1,048,576 㯠1,000,000 ãã倧ããããã20åååã«ããã°ã100äžä»¶ã§ã1ä»¶ãŸã§çµã蟌ããŸãããã®ã2ãäœåæãããšããã®æ°ã«ãªãããã衚ãã®ãã2ãåºãšããå¯Ÿæ° logâ ã§ããlogâ 1,000,000 ã¯çŽ19.9ã§ãïŒå¯Ÿæ°ã®èãæ¹ã¯ãææ°ã»å¯Ÿæ°ã®èšç®èŠåã¯ããªãæãç«ã€ïŒãã§è©³ããæžããŸããïŒã
å šä»¶ãé ã«èŠãæ¢ãæ¹ã§ã¯ãææªã§100äžåã®ç¢ºèªãå¿ èŠã§ãã䞊ã¹ãŠããã°ããããçŽ20åã§æžã¿ãŸããä»¶æ°ã2åã®200äžä»¶ã«ãªã£ãŠããå šä»¶ãèŠãæ¢ãæ¹ã§ã¯ç¢ºèªã2åã«å¢ããŸãããååãã€çµãæ¢ãæ¹ã§ã¯1åå¢ããã ãã§ãã


玢åŒã¯ãäžŠã¹æ¿ãããã1ã€ã®è¡š
ãšããããåè¬è ã®è¡šã¯ãç»é²ããé ã«äžŠãã§ããŸããã¡ãŒã«ã¢ãã¬ã¹ã®é ã«ã¯äžŠãã§ããŸããã
ããã§ã衚ãšã¯å¥ã«ãã¡ãŒã«ã¢ãã¬ã¹ãšããã®è¡ãããå Žæãã ãããã¡ãŒã«ã¢ãã¬ã¹ã®é ã«äžŠã¹ããã®ãäœããŸããããã玢åŒã§ãã玢åŒã§ç®çã®ã¡ãŒã«ã¢ãã¬ã¹ãèŠã€ããããããã«æžããŠããå Žæãé Œãã«ã衚ã®è¡ãèªã¿ã«ãããŸãã
ããã§ããå Žæã¯ãã衚ã®äœããŒãžç®ã®ãäœçªç®ã®è¡ãããšããåœ¢ã§æžãããŠããŸããä»åã®äŸã§ã¯ãæ¢ããŠããåè¬è ã®è¡ã¯ã7601ããŒãžç®ã®79çªç®ãã«ãããŸããã

玢åŒã®äžèº«ïŒäœæ®µãã®æ¡å æ¿
PostgreSQL ã®æšæºã¯ãBæšã
PostgreSQL ã§çŽ¢åŒãäœããšãç¹ã«æå®ããªããã°ãBæšïŒBããªãŒãB-treeïŒããšãã圢ã®çŽ¢åŒã«ãªããŸããå ¬åŒææžã¯ãBæšãããã£ãšãäžè¬çãªå Žé¢ã«åãã玢åŒãšããŠãæšæºã«ããŠãããšèª¬æããŠããŸã2ã
Bæšã®èãæ¹ã¯ãäºåæ¢çŽ¢ã«è¿ããã®ã§ãã䞊ã¹ãå€ãããŒãžããšã«åãããã®äžã«ãã©ã®ããŒãžã«ãã©ã®ç¯å²ã®å€ããããããæžããæ¡å ã®ããŒãžãéããŸãã
倧ããªå³æžé€šã®æ¡å æ¿ã«ããšãããšåããããããªããŸããå ¥å£ã®æ¡å æ¿ã§ãæçã®æ¬ã¯3éããšåããã3éã®æ¡å æ¿ã§ãåé£ã¯æ±ã®æ£ããšåããããã®æ£ã§ç®çã®æ¬ãæ¢ããŸãã

100äžä»¶ã®çŽ¢åŒã¯3段ã ã£ã
ä»åäœã£ãã¡ãŒã«ã¢ãã¬ã¹ã®çŽ¢åŒã®åœ¢ããPostgreSQL ã®äžèº«ã調ã¹ãæ©èœã§ç¢ºãããŸããã
1段ç®ïŒãã¡ã°ãäžïŒïŒ1ããŒãžã ããããã®å€ããå ã¯ã2段ç®ã®ãã®ããŒãžããšããè¡ãå ã33åæžãããŠãã
2段ç®ïŒ33ããŒãžã1ããŒãžã«å¹³å150åã3段ç®ãžã®è¡ãå ãæžãããŠãã
3段ç®ïŒãã¡ã°ãäžïŒïŒ4,927ããŒãžã1ããŒãžã«å¹³å204ä»¶ã®ãã¡ãŒã«ã¢ãã¬ã¹ãšè¡ã®å Žæãã䞊ãã§ãã
ãã¡ã°ãäžã®æ®µã¯1ããŒãžã ãã§ãã©ã®æ€çŽ¢ãããããå§ãŸããŸãã峿žé€šã§èšãã°ãå ¥å£ã«1æã ãããæ¡å æ¿ã§ãã4,927ããŒãž à 平å204ä»¶ã§ããããã100äžä»¶ã«ãªããŸãã
ãuser0765432ããæ¢ãããšãã®éã®ãããå®éã®çŽ¢åŒã®äžèº«ã§ãã©ããšæ¬¡ã®ãšããã§ãã
1段ç®ïŒãuser0737703 ããå ã¯ã3662ããŒãžããšæžãããè¡ãå ãéžã¶
2段ç®ïŒ3662ããŒãžïŒïŒãuser0765311 ããå ã¯ã3796ããŒãžããšæžãããè¡ãå ãéžã¶
3段ç®ïŒ3796ããŒãžïŒïŒãuser0765432 ã®è¡ã¯ 7601-79ããšæžãããŠãã
3ããŒãžèªãã ãã§ãç®çã®ã¡ãŒã«ã¢ãã¬ã¹ã®è¡ã®å Žæã«ãã©ãçããŸããã
äºåæ¢çŽ¢ã¯1åã§ååã«ããçµããŸããããBæšã¯1ããŒãžãèªããã³ã«ãè¡ãå ãæ°ååã®1ããçŸæ°ååã®1ã«çµããŸããããŒã¿ããŒã¹ã«ãšã£ãŠã¯ãèªãããŒãžã®æ°ãå°ãªãã»ã©éããªãããããã®åœ¢ãåããŠããŸãã

èªãã ã®ã¯4ããŒãž
玢åŒãä»ããããšãåãæ€çŽ¢ã§èªãã ããŒãžã®æ°ãæ°ãããšã4ããŒãžã§ãããäžã§ãã©ã£ã玢åŒã®3ããŒãžïŒå ¥å£ã»3662ããŒãžã»3796ããŒãžïŒãšãããã«æžãããå Žæã7601-79ãã®è¡ãèªãããã®ã衚ã®7601ããŒãžã®1ããŒãžã§ãã
玢åŒããªããšãã¯9,932ããŒãžãèªãã§ããã®ã§ãèªãéã¯2,000åã®1以äžã«ãªããŸããã

Django ã§çŽ¢åŒãä»ãã
Meta.indexes ã«æžã
Django ã§ã¯ãã¢ãã«ã® Meta ã® indexes ã«ã玢åŒãæžãè¶³ããŸããDjango ã®å ¬åŒææžãã玢åŒã¯ Meta.indexes ã§è¶³ãããšèª¬æããŠããŸã5ã
class Student(models.Model):
# ïŒé
ç®ã¯çç¥ïŒ
class Meta:
indexes = [
models.Index(fields=["email"], name="student_email_idx"),
]fieldsïŒçŽ¢åŒãä»ããåïŒé ç®ïŒã®åå
nameïŒçŽ¢åŒã®ååãçããš Django ãèªåã§ååãä»ããŸã5
é ç®ã« db_index=True ãšæžããŠçŽ¢åŒãä»ããæ¹æ³ããããŸãããã ã Django ã®å ¬åŒææžã¯ãã§ããéã Meta.indexes ã䜿ãããå§ããŠãããdb_index ã¯å°æ¥ãªããªããããããªãããšæžããŠããŸã4ã
makemigrations ãš migrate
ã¢ãã«ãæžãæããããšã®æµãã¯ãååã®èšäºãšåãã§ããpython manage.py makemigrations ãå®è¡ãããšã次ã®ããã«è¡šç€ºãããŸããã
Migrations for 'school':
school/migrations/0004_student_email_index.py
+ Create index student_email_idx on field(s) email of model studentpython manage.py sqlmigrate school 0004 ã§ãããŒã¿ããŒã¹ã«éãããåœä»€ã確ããããšãäžèº«ã¯æ¬¡ã®1è¡ã§ããïŒååŸã® BEGIN ãš COMMIT ã¯çããŠããŸãïŒã
CREATE INDEX "student_email_idx" ON "school_student" ("email");ãschool_student ã®è¡šã® email ã®åã«ãstudent_email_idx ãšããååã®çŽ¢åŒãäœãããšããæå³ã§ããpython manage.py migrate ãå®è¡ãããšã100äžä»¶åã®çŽ¢åŒãã§ãããŸã§ã«çŽ1ç§ããããŸããã

ä»ããããšã®éã
玢åŒãä»ããããšãåãæ€çŽ¢ã®å®è¡èšç»ã¯æ¬¡ã®ããã«å€ãããŸããã
Index Scan using student_email_idx on school_student
Index Cond: email = 'user0765432@example.com'Index ScanïŒçŽ¢åŒã䜿ã£ãŠæ¢ããããšããæå³ã§ãã
Index CondïŒçŽ¢åŒã§çµã蟌ãã æ¡ä»¶ã§ãã
Django ããèŠãæéã®äžå€®å€ã¯ã25.0ããªç§ãã0.72ããªç§ã«ãªããŸããã0.72ããªç§ã®å€ãã¯ãDjango ãçµæã Python ã®ããŒã¿ã«å€ããæéãªã©ã§ãããŒã¿ããŒã¹ã®äžã®æ€çŽ¢ãã®ãã®ã¯ã0.06ããªç§ã»ã©ã§çµãã£ãŠããŸããã

Django ãèªåã§ä»ããŠãã玢åŒ
ååã®èšäºã§èŠããšããã玢åŒã®äžã«ã¯ãèªåã§æžããªããŠãäœããããã®ããããŸãã
äž»ããŒïŒidïŒïŒè¡ãèŠåããçªå·ã§ããä»åã®åè¬è ã®è¡šã«ããäž»ããŒã®çŽ¢åŒãã§ããŠããŸããã
ForeignKeyïŒDjango ã®å ¬åŒææžã¯ãForeignKey ã«ã¯çŽ¢åŒãèªåã§äœããããšèª¬æããŠããŸã4ã
unique=TrueïŒãåãå€ã2åå ¥ããããªãããšããæ±ºãŸãã«ã¯ã玢åŒãä»ããŸããå ¬åŒææžããunique ãæå®ããã°çŽ¢åŒãäœãããããã玢åŒãå¥ã«æžãå¿ èŠã¯ãªããšããŠããŸã4ã
ããšãã°ãè¬åº§500ãžã®ç³èŸŒããæ¢ã Enrollment.objects.filter(course_id=500) ã¯ãäœãè¶³ããªããŠããForeignKey ã« Django ãäœã£ã玢åŒã䜿ãããŠããŸãããååã®èšäºã§èŠããããŒã§è¡šãã€ãªããå Žé¢ã¯ããã®ãŸãŸãããŒã§æ¢ããå Žé¢ã§ããããããæåãã玢åŒãããã®ã¯çã«ããªã£ãŠããŸãã
ååã®èšäºã§è§ŠãããšãããPostgreSQL ã¯å€éšããŒå¶çŽãäœã£ãŠããåç §ããåŽã®åã«çŽ¢åŒãèªåã§ã¯äœããŸããããã®ç¹ã¯ãDjango ãè£ã£ãŠãããŠããŸãã

玢åŒãå¹ããªãæ¢ãæ¹
玢åŒãä»ããã°ãã©ããªæ€çŽ¢ã§ãéããªãããã§ã¯ãããŸãããä»åã®å®éšã§ã玢åŒããã£ãŠãå šä»¶ãèªãã äŸã3ã€ç޹ä»ããŸãã
1. ãããå«ããã§æ¢ãïŒicontainsïŒ
ã¡ãŒã«ã¢ãã¬ã¹ã«ã0765432ããå«ãåè¬è ãæ¢ããšã玢åŒããã£ãŠãå šä»¶ãé ã«èªã¿ãçŽ116ããªç§ããããŸããã
Student.objects.filter(email__icontains="0765432")玢åŒã¯ãå€ã®é ããé ã«äžŠãã§ããŸããèŸæžã§èšãã°ããããããã¯åŒããŠãããããããã§çµããèšèããããããå«ãèšèã¯ã玢åŒã®äžŠã³ããã¯æ¢ããŸããã
PostgreSQL ã®å ¬åŒææžããBæšã®çŽ¢åŒã䜿ããã®ã¯ãfoo ã§å§ãŸããã®ããã«é ã®æåãæ±ºãŸã£ãæ¢ãæ¹ã§ããbar ã§çµãããã®ãããªæ¢ãæ¹ã«ã¯äœ¿ããªããšããŠããŸããææžã®äžã§ã¯ããäœæåã§ããããã衚ã % ã®èšå·ã䜿ã£ãŠããããã foo% ãš %bar ãšæžãããŠããŸã2ã
ããã«ãicontains ã¯å€§æåãšå°æåãåºå¥ããªããããDjango ã¯åã®å€ã倧æåã«ããããŠããæ¯ã¹ãåœä»€ãäœã£ãŠããŸããã䞊ã¹ãŠããå€ãã®ãã®ãšã¯æ¯ã¹ãªãããããã®ç¹ã§ã玢åŒã¯äœ¿ãããŸããã

2. ããã§å§ãŸããã¯ã玢åŒã®äœãæ¹ãã ã
ãuser076543 ã§å§ãŸããåè¬è ãæ¢ã email__startswith ã¯ãé ãæ±ºãŸã£ãæ¢ãæ¹ã§ãããšããããäžã§äœã£ããµã€ãã®çŽ¢åŒã§ã¯ã玢åŒã䜿ããããå šä»¶ãèªãã§ããŸããã
ãªã䜿ãããªãã®ãã¯ã玢åŒã®äžŠã¹æ¹ã«ãããŸãããµã€ãã®çŽ¢åŒã¯ãèšèªããšã®äžŠã¹æ¹ã®æ±ºãŸãïŒç §åé åºïŒã«æ²¿ã£ãŠäžŠãã§ããŸããPostgreSQL ã®å ¬åŒææžã¯ãæšæºã®ãCã以å€ã®ç §åé åºã䜿ãããŒã¿ããŒã¹ã§ã¯ãããã§å§ãŸããã®æ€çŽ¢ã«çŽ¢åŒã䜿ãã«ã¯ãå°çšã®åœ¢ã®çŽ¢åŒãå¿ èŠã ãšããŠããŸã2ã
ä»åã®ããŒã¿ããŒã¹ããæšæºã®ãCããšã¯å¥ã®èšå®ïŒC.UTF-8ïŒã§äœã£ãŠããŸãããDjango ã® Meta.indexes ã§ã¯ãå°çšã®åœ¢ã opclasses ã§æå®ããŸã5ã
models.Index(
fields=["email"], name="student_email_like",
opclasses=["varchar_pattern_ops"],
),varchar_pattern_ops ã¯ãç §åé åºã§ã¯ãªãã1æåãã€é ã«æ¯ã¹ã䞊ã³ã§çŽ¢åŒãäœãæå®ã§ã6ããã®äžŠã³ã§ã¯ããuser076543 ã§å§ãŸããå€ã¯ããuser076543 以äžãuser076544 ããåããšãã1ã€ã®ç¯å²ã«ãŸãšãŸããŸãã
å®éã«ããã®çŽ¢åŒãè¶³ããããšã®å®è¡èšç»ã§ã¯ãããã§å§ãŸããã®æ¡ä»¶ããæ¬¡ã®ãããªç¯å²ã®æ¡ä»¶ã«çœ®ãæããããŠããŸããã
Index Scan using student_email_like on school_student
Index Cond: email ~>=~ 'user076543' AND email ~<~ 'user076544'~>=~ïŒ1æåãã€æ¯ã¹ãŠã以äžã
~<~ïŒ1æåãã€æ¯ã¹ãŠãããåã
玢åŒã®ãã®ç¯å²ã ããèªãã°æžããããDjango ããèŠãæéã¯27.3ããªç§ãã0.86ããªç§ã«ãªããŸããã


Meta.indexes ã«2ã€äžŠã¹ãŠæžã
ãããŸã§ã§ãã¡ãŒã«ã¢ãã¬ã¹ã«ã¯çŽ¢åŒã2ã€ä»ããããšã«ãªããŸããMeta.indexes ã«ã¯ã次ã®ããã«2ã€äžŠã¹ãŠæžããŸã5ã
class Meta:
indexes = [
models.Index(fields=["email"], name="student_email_idx"),
models.Index(
fields=["email"], name="student_email_like",
opclasses=["varchar_pattern_ops"],
),
]1ã€ç®ïŒãµã€ãã®çŽ¢åŒããïŒãã§1äººãæ¢ããšãã«äœ¿ããã
2ã€ç®ïŒopclasses ãæå®ãããããã§å§ãŸããçšã®çŽ¢åŒ
makemigrations ãå®è¡ããŠãã sqlmigrate ã§ç¢ºããããšãããŒã¿ããŒã¹ã«éãããåœä»€ã¯æ¬¡ã®2è¡ã§ããïŒååŸã® BEGIN ãš COMMIT ã¯çããŠããŸãïŒã
CREATE INDEX "student_email_idx" ON "school_student" ("email");
CREATE INDEX "student_email_like" ON "school_student" ("email" varchar_pattern_ops);æžãã models.Index ã®1ã€ãããã®ãŸãŸ CREATE INDEX ã®1è¡ã«ãªã£ãŠããŸããä»ããååããopclasses ã®æå®ãããã®ãŸãŸå ¥ã£ãŠããŸãã

3. çµã«ãã玢åŒã¯ãå é ã®åãã
æåŸã¯ã2ã€ã®åãçµã«ãã玢åŒã§ããç³èŸŒïŒEnrollmentïŒã«ããè¬åº§ããšãç³èŸŒæ¥ããçµã«ãã玢åŒãè¶³ããŸããã
class Meta:
indexes = [
models.Index(fields=["course", "applied_on"], name="enroll_course_date"),
]course ã¯ç³èŸŒã®ãè¬åº§ããapplied_on ã¯ãç³èŸŒæ¥ãã®é ç®ã§ããååã®èšäºã§äœã£ãç³èŸŒïŒEnrollmentïŒã®ã¢ãã«ã«ããã®2ã€ã®é ç®ããããŸããã
ãã®çŽ¢åŒã¯ããŸãè¬åº§ã®é ã«äžŠã³ãåãè¬åº§ã®äžã§ã¯ç³èŸŒæ¥ã®é ã«äžŠã³ãŸããé»è©±åž³ããå§ãã®é ã«äžŠã³ãåãå§ã®äžã§ã¯ãåãã®é ã«äžŠãã§ããã®ãšåã圢ã§ãã
çµæã¯ã次ã®ãšããã§ããã
ãè¬åº§500ãžã®ã2025幎4æã®ç³èŸŒãïŒçµã«ãã玢åŒã䜿ããã2.17ããªç§ãã0.64ããªç§ã«ãèªãã 衚ã®ããŒãžã¯ã1,838ããŒãžãã19ããŒãžã«æžããŸãã
ã2025幎4æ1æ¥ã®ç³èŸŒãïŒè¬åº§ã¯åããªãïŒïŒçµã«ãã玢åŒã¯äœ¿ããããå šä»¶ãé ã«èªãã§42ããªç§
çµã«ããåã¯ãForeignKey ã«èªåã§ä»ããè¬åº§ã®çŽ¢åŒã§ãè¬åº§500ã®ç³èŸŒ1,954ä»¶ãèŠã€ãããã®è¡ããã¹ãŠèªãã§ãã4æã®ãã®ãéžãã§ããŸãããçµã«ãã玢åŒã§ã¯ãè¬åº§ãšæ¥ä»ã®äž¡æ¹ã§çµã£ãããšã®19ä»¶ã ããèªãã°æžã¿ãŸãã
é»è©±åž³ã§ãå§ãåãã£ãŠããã°åãŸã§çµã蟌ããŸãããåã ãã§ã¯ãå§ã®ãã¹ãŠã®æ¬ãèŠãªããã°ãªããŸãããçµã«ãã玢åŒãåãã§ããPostgreSQL ã®å ¬åŒææžããçµã«ãã Bæšã®çŽ¢åŒã¯ãå é ïŒå·ŠåŽïŒã®åã«æ¡ä»¶ããããšãã«ãã£ãšãå¹çãããããšèª¬æããŠããŸã3ã
çµã«ããé çªã¯ãããã€ãæ¡ä»¶ã«å ¥ãåããå ã«ããã®ãåºæ¬ã§ãããã®äŸã§ã¯ãè¬åº§ã決ããŠããæ¥ä»ã§çµãäœ¿ãæ¹ãæ³å®ããŠãè¬åº§ãå ã«ããŸããã


玢åŒã®ä»£å
ãããŸã§èªããšããã¹ãŠã®åã«çŽ¢åŒãä»ããã°ããããã«èŠããŸãããããã玢åŒã«ã¯ä»£åããããŸãã
ç»é²ãæŽæ°ãé ããªã
玢åŒã¯ã衚ãšã¯å¥ã«äžŠã¹ããã®ã§ãããã®ããã衚ã«è¡ãè¶³ãããæžãæããããããã³ã«ã玢åŒã®äžŠã³ãçŽãå¿ èŠããããŸããPostgreSQL ã®å ¬åŒææžãã玢åŒãäœããšã衚ãšçŽ¢åŒããããç¶ããå¿ èŠããããããŒã¿ãæžãæããåŠçã«è² æ ãå ãããšèª¬æããŠããŸã1ã
å®éã«ãåè¬è ã10äžäººãŸãšããŠç»é²ããæéããã¡ãŒã«ã¢ãã¬ã¹ã®çŽ¢åŒ2ã€ãããå Žåãšãªãå Žåã§9åãã€æž¬ããŸããïŒæ¯åãç»é²ãåãæ¶ããŠå ã«æ»ããŠããŸãïŒã
玢åŒãªãïŒäžå€®å€ 954ããªç§
玢åŒããïŒäžå€®å€ 1,310ããªç§ïŒçŽ4å²å¢ïŒ
容éãå¢ãã
玢åŒã¯ãããèªäœãããŒã¿ããŒã¹ã®äžã§å ŽæãåããŸããä»åã®åè¬è ã®è¡šã¯78MBã§ãã¡ãŒã«ã¢ãã¬ã¹ã®çŽ¢åŒã¯1ã€ã§39MBã§ãããã¡ãŒã«ã¢ãã¬ã¹ã®çŽ¢åŒã2ã€ä»ãããšã衚ãšåããããã®å®¹éã«ãªããŸãã

ä»ãããã©ããã®èãæ¹
Django ã®å ¬åŒææžã¯ã玢åŒãè¶³ãããšããæåªå ããšãã€ã€ãããã¯ãã©ã®çŽ¢åŒãå¿ èŠããæž¬ã£ãŠç¢ºãããããšãã ãšããŠããŸã7ããã®ããã§ãfilter() ã order_by() ã§ãã䜿ãé ç®ã«çŽ¢åŒãèãããšããäžæ¹ã玢åŒãä¿ã€è² æ ããæ€çŽ¢ãéããªã广ãäžåãããšãããããšæžããŠããŸã7ã
çµã«ãã玢åŒã«ã€ããŠããPostgreSQL ã®å ¬åŒææžã¯ãæ§ããã«äœ¿ãã¹ããã§ãå€ãã®å Žåã¯1ã€ã®åã®çŽ¢åŒã§è¶³ãã容éãæéãç¯çŽã§ããããšããŠããŸã3ã
玢åŒãä»ãããšãã®ç¢ºèª
ãããŸã§ã®å 容ãã玢åŒãä»ãããšãã®ç¢ºèªãšããŠãŸãšããŸãã
ããæ¢ãæ¡ä»¶ãæŽãåºãïŒç»é¢ã®æ€çŽ¢æ¬ãäžèЧã®çµã蟌ã¿ã§ãã©ã®é ç®ãæ¡ä»¶ã«ããŠãããã確èªããŸãã
ãŸã explain() ã§æ¢ãæ¹ãèŠãïŒé ãæ€çŽ¢ã¯ãSeq ScanïŒå šä»¶ãé ã«èªãïŒã«ãªã£ãŠããªããã確èªããŸãã
èªåã§ä»ã玢åŒãæãåºãïŒäž»ããŒã»ForeignKeyã»unique ã«ã¯ããã§ã«çŽ¢åŒããããŸããåã玢åŒãéããŠäœããªãããã«ããŸãã
æ¢ãæ¹ã«åã£ã玢åŒã«ããïŒããã§å§ãŸãããªãå°çšã®åœ¢ã®çŽ¢åŒã2ã€ã®æ¡ä»¶ã§æ¢ããªãçµã«ãã玢åŒãèããŸãããããå«ããã¯ããµã€ãã®çŽ¢åŒã§ã¯éããªããŸããã
çµã«ãããšãã¯é çªã決ããïŒãã€ãæ¡ä»¶ã«å ¥ãåãå ã«ããŸãã
ä»ããããããäžåºŠæž¬ãïŒexplain() ã§ Index Scan ã«ãªã£ãããæéãã©ãå€ãã£ããã確èªããŸããç»é²ã®å€ã衚ã§ã¯ãæžã蟌ã¿ã®æéã確èªããŸãã

ãŸãšã
玢åŒããªããšãããŒã¿ããŒã¹ã¯è¡šãæåããé ã«èªãã100äžä»¶ãã1ä»¶ãæ¢ãã®ã«ã9,932ããŒãžãèªãã§ãã
䞊ãã§ããã°ãååãã€çµããã100äžä»¶ã§ãçŽ20åã§1ä»¶ã«ãã©ãçã
PostgreSQL ã®æšæºã®çŽ¢åŒã¯ Bæšã100äžä»¶ã®ã¡ãŒã«ã¢ãã¬ã¹ã®çŽ¢åŒã¯3段ã§ãèªãã ã®ã¯4ããŒãžã ã£ã
Django ã§ã¯ Meta.indexes ã«æžããmakemigrations ãš migrate ã§çŽ¢åŒãäœããããäž»ããŒã»ForeignKeyã»unique ã«ã¯èªåã§ä»ã
ãããå«ããã¯ãµã€ãã®çŽ¢åŒã§ã¯éããªããªããããã§å§ãŸããã¯å°çšã®åœ¢ã®çŽ¢åŒãçµã«ãã玢åŒã¯å é ã®åãã䜿ããã
玢åŒãä»ãããšãç»é²ãé ããªãã容éãå¢ãããããæ¢ãæ¡ä»¶ã確ãããæž¬ã£ãŠããä»ãã
ååã®èšäºã§èŠã N+1 åé¡ã¯ãåãåããã®åæ°ãã®åé¡ã§ãããä»åã®çŽ¢åŒã¯ã1åã®åãåããã§èªãéãã®åé¡ã§ããã©ã¡ãããORM ã®äŸ¿å©ãã®è£ã§ãããŒã¿ããŒã¹ãå®éã«äœãããŠããããç¥ã£ãŠãããšãé ãã®åå ãèŠã€ãããããªããŸãã
NDã·ã¹ãã ãºã®ãµãŒãã¹ã«ã€ããŠ
NDã·ã¹ãã ãºã¯ãæé¡å¶ã®ããŒã ããŒãžå¶äœãäžå¿ã«ãäºæ¥ã®ããžã¿ã«åãæ¯æŽããŠããŸããç¬èªã·ã¹ãã éçºã¯ã顧客ãåæ³šã®ç®¡çãªã©ã1ã€ã®æ¥åã«çµã£ãã·ã¹ãã ãæ¿ããŸããååã®ç¡æçžè«ïŒãªã³ã©ã€ã³ã»30åïŒã¯ããåãåããã®ãã©ãŒã ãŸãã¯ã¡ãŒã«ã§æ¿ããŸãã
åèæç®
11.1. IntroductionïŒPostgreSQL 16 å ¬åŒææžïŒïŒè±èªïŒ
11.2. Index TypesïŒPostgreSQL 16 å ¬åŒææžïŒïŒè±èªïŒ
11.3. Multicolumn IndexesïŒPostgreSQL 16 å ¬åŒææžïŒïŒè±èªïŒ
Model field referenceïŒDjango 5.2 å ¬åŒææžïŒïŒè±èªïŒ
Model index referenceïŒDjango 5.2 å ¬åŒææžïŒïŒè±èªïŒ
Database access optimizationïŒDjango 5.2 å ¬åŒææžïŒïŒè±èªïŒ
11.10. Operator Classes and Operator FamiliesïŒPostgreSQL 16 å ¬åŒææžïŒïŒè±èªïŒ
- #ãšã³ãžãã¢
- #ããã°ã©ãã³ã°
- #çŠå²¡
- #python
- #ããã©ãŒãã³ã¹
- #ã¢ã«ãŽãªãºã
- #ããã°ã©ãã³ã°åŠç¿
- #ã·ã¹ãã éçº
- #ã€ã³ããã¯ã¹
- #ããŒã¿ããŒã¹
- #SQL
- #webã¢ããªéçº
- #ä¹ çç±³
- #ããã¯ãšã³ã
- #æ å ±åŠç
- #django
- #æ¥åã·ã¹ãã
- #PostgreSQL
- #é«éå
- #ããŒã¿æ§é
- #玢åŒ
- #ãã€ã°ã¬ãŒã·ã§ã³
- #NDã·ã¹ãã ãº
- #å®è¡èšç»
- #äºåæ¢çŽ¢
- #ORM
- #explain
- #BTree
- #Bæš
- #DjangoORM