
ããŒãã©ã¹ã®èµããšããã¢è¹é·
ã€ã³ããã¯ã¹ã¯ããªã¬ãŒã·ã§ãã«ããŒã¿ããŒã¹ã§æã匷åãªããŒã«ã®1ã€ã§ãã ããã€ãã®å€ããã°ããèŠã€ããå¿ èŠãããå ŽåãããŒã¿ããŒã¹ãçµåããå ŽåãSQLã¹ããŒãã¡ã³ãã®äœæ¥ãé«éåããå¿ èŠãããå Žåãªã©ã«äœ¿çšããŸãã ããããã€ã³ããã¯ã¹ãšã¯äœã§ããïŒ ãŸããããŒã¿ããŒã¹æ€çŽ¢ã®é«éåã«ã©ã®ããã«åœ¹ç«ã¡ãŸããïŒ ãããã®è³ªåã«çããããã«ãPostgreSQLã®ãœãŒã¹ã³ãŒãã調æ»ããåçŽãªæååå€ã®ã€ã³ããã¯ã¹ã®æ€çŽ¢æ¹æ³ã远跡ããŸããã è€éãªã¢ã«ãŽãªãºã ãšå¹ççãªããŒã¿æ§é ãèŠã€ããããšãæåŸ ãããŠããŸããã ãããŠèŠã€ããã
ããã§ã¯ãã€ã³ããã¯ã¹ãã©ã®ããã«é 眮ãããã©ã®ããã«æ©èœãããã«ã€ããŠèª¬æããŸãã ãã ããã³ã³ãã¥ãŒã¿ãŒãµã€ãšã³ã¹ã«åºã¥ããŠãããšã¯æã£ãŠããŸããã§ããã ã€ã³ããã¯ã¹ã®å å€ãç解ããããšã¯ãPostgresãã©ã®ããã«æ©èœãããã ãã§ãªãããªããã®ããã«æ©èœãããã説æããã³ãŒãå ã®ã³ã¡ã³ãã«ãã£ãŠãå©ããããŸããã
é åæ€çŽ¢
Postgresã®ã·ãŒã±ã³ã¹æ€çŽ¢ã¢ã«ãŽãªãºã ã«ã¯å¥åŠãªç¹ããããŸããäœããã®çç±ã§ãããŒãã«å ã®ãã¹ãŠã®å€ã調ã¹ãŸãã ååã®æçš¿ã§ã¯ã次ã®ç°¡åãªSQLã¹ããŒãã¡ã³ãã䜿çšããŠããCaptain Nemoãã®å€ãæ€çŽ¢ããŸããã

Postgresã¯ãªã¯ãšã¹ããã¹ããŒã¹ãåæãããã³èšç»ããŸããã 次ã«ã ExecSeqScan ïŒSEQSCANã·ãŒã±ã³ã¹æ€çŽ¢ãå®è£ ããçµã¿èŸŒã¿Cèšèªé¢æ°ïŒãããã«ç®çã®å€ãèŠã€ããŸããã

ãããããã®åŸã説æãããŠããªãçç±ã§ãPostgresã¯ããŒã¿ããŒã¹å šäœãã¹ãã£ã³ãç¶ããåå€ãæ€çŽ¢ãšæ¯èŒããŸãããããã§ã«èŠã€ãã£ãŠããŸãïŒ

ããŒãã«ã«äœçŸäžãã®å€ãå«ãŸããŠããå Žåãæ€çŽ¢ã«ã¯éåžžã«é·ãæéãããããŸãã ãã¡ãããããã¯ããœãŒããåé€ããæåã«èŠã€ãã£ãäžèŽã§åæ¢ããããã«ã¯ãšãªãæžãæããããšã§åé¿ã§ããŸãã ããããåé¡ã¯ããæ·±ããPostgresèªäœã®æ€çŽ¢ãšã³ãžã³ã®éå¹çæ§ã«ãããŸãã ã·ãŒã±ã³ã¹æ€çŽ¢ã䜿çšããŠããŒãã«å ã®åå€ãæ¯èŒããã®ã¯é ããéå¹ççã§ãããã¬ã³ãŒããé 眮ãããé åºã«äŸåããŸãã å¥ã®æ¹æ³ãå¿ èŠã§ãïŒ
解決çã¯ç°¡åã§ããã€ã³ããã¯ã¹ãäœæããå¿ èŠããããŸãã
ã€ã³ããã¯ã¹äœæ
ãããè¡ãã«ã¯ç°¡åã§ããã³ãã³ããå®è¡ããã ãã§ãïŒ

Rubyéçºè ã¯ãåãCREATE INDEXã³ãã³ããå®è¡ããActiveRecordã§add_index移è¡ã䜿çšããããšã奜ã¿ãŸãã éåžžãselectãåèµ·åãããšãPostgresã¯ãã©ã³ãã³ã°ããªãŒãäœæããŸãã ãã ãããã®å Žåã¯å°ãç°ãªããŸãã

äžéšã§ã¯ãSEQSCANã®ä»£ããã«INDEXSCANã䜿çšããŠããããšã«æ³šæããŠãã ããã INDEXSCANã¯ãSEQSCANãšã¯ç°ãªããããŒã¿ããŒã¹å šäœãã¹ãã£ã³ããŸããã 代ããã«ãäœæããã°ããã®ã€ã³ããã¯ã¹ã䜿çšããŠãå¿ èŠãªã¬ã³ãŒãããã°ããå¹ççã«æ€çŽ¢ããŸãã
ã€ã³ããã¯ã¹ãäœæãããšãããã©ãŒãã³ã¹ã®åé¡ã¯è§£æ±ºããŸããã次ã®ãããªå€ãã®è³ªåã«å¯Ÿããçãã¯åŸãããŸããã
- Postgresã®ã€ã³ããã¯ã¹ãšã¯æ£ç¢ºã«ã¯äœã§ããïŒ
- æ£ç¢ºã«ã¯ã©ã®ããã«èŠããŸããããã®æ§é ã¯äœã§ããïŒ
- ã€ã³ããã¯ã¹ã¯ã©ã®ããã«æ€çŽ¢ãé«éåããŸããïŒ
Cã®ãœãŒã¹ãèŠãŠããããã®è³ªåã«çããŸãããã
Postgresã®ã€ã³ããã¯ã¹ãšã¯
CREATE INDEXã³ãã³ãã®ããã¥ã¡ã³ãããå§ããŸãããïŒ

ã€ã³ããã¯ã¹ã®äœæã«äœ¿çšã§ãããã¹ãŠã®ãã©ã¡ãŒã¿ãŒãããã«è¡šç€ºãããŸãã USINGã¡ãœãããã©ã¡ãŒã¿ã«æ³šæããŠãã ãããããã¯ãå¿ èŠãªã€ã³ããã¯ã¹ã®çš®é¡ã瀺ããŸãã åãããŒãžã«ã¯ãã¡ãœãããUSINGããŒã¯ãŒãåŒæ°ã«é¢ããæ å ±ãèšèŒãããŠããŸãã

Postgresã«ã¯4çš®é¡ã®ã€ã³ããã¯ã¹ããããŸãã ãããã¯ãããŸããŸãªã¿ã€ãã®ããŒã¿ã«ããŸãã¯ç¶æ³ã«å¿ããŠäœ¿çšã§ããŸãã USINGãã©ã¡ãŒã¿ãå®çŸ©ããªãã£ããããindex_users_on_nameã¯ããã©ã«ãã§ãbtreeãïŒBããªãŒïŒã®åœ¢åŒã®ã€ã³ããã¯ã¹ã§ãã
BããªãŒãšã¯äœã§ãããããã«é¢ããæ å ±ã¯ã©ãã§å ¥æã§ããŸããïŒ ãããè¡ãã«ã¯ã察å¿ããPostgresãœãŒã¹ãã¡ã€ã«ã調ã¹ãŸãã

READMEã®èª¬æã¯æ¬¡ã®ãšããã§ãã

ãšããã§ãREADMEèªäœã¯12ããŒãžã®ããã¥ã¡ã³ãã§ãã ã€ãŸããCã³ãŒãã®æçšãªã³ã¡ã³ãã ãã§ãªããããŒã¿ããŒã¹ãµãŒããŒã®çè«ãšç¹å®ã®å®è£ ã«é¢ãããã¹ãŠã®å¿ èŠãªæ å ±ãå©çšã§ããŸãã å€ãã®å ŽåããªãŒãã³ãœãŒã¹ãããžã§ã¯ãã®ã³ãŒãã®èªã¿åããšè§£æã¯å°é£ãªäœæ¥ã§ãããPostgresã§ã¯å°é£ã§ãã éçºè ã¯ã圌ãã®çºæ¡ã®èšèšãç解ããããã»ã¹ãä¿é²ããããšããŸããã
æåã®æã«ã¯ãBããªãŒãäœã§ãããïŒãããã£ãŠãPostgresã§ã®ã€ã³ããã¯ã¹ã®ä»çµã¿ïŒã説æããç§åŠåºçç©ãžã®ãªã³ã¯ãããããšã«æ³šæããŠãã ããããšã€ãªã
BããªãŒãšã¯äœã§ããïŒ
ãã®èšäºã§ã¯ãäœè ã1981幎ã«BããªãŒã¢ã«ãŽãªãºã ã«å ããæ¹åã«ã€ããŠèª¬æããŠããŸãã ããã«ã€ããŠã¯åŸã»ã©èª¬æããŸãã ã¢ã«ãŽãªãºã èªäœã¯1972幎ã«éçºãããŸãããããã¯åçŽãªBããªãŒã®äŸã§ãã

ååã¯è±èªã®ç¥ã§ãã ããã©ã³ã¹ã®åããæšãã ãã®ã¢ã«ãŽãªãºã ã«ãããæ€çŽ¢æäœãé«éåã§ããŸãã ããšãã°ãå€53ãèŠã€ããå¿ èŠããããŸããå€40ãå«ãã«ãŒãããŒãããå§ããŸãããã

ç®çã®å€ãšæ¯èŒãããŸãã 53> 40ãªã®ã§ãããªãŒã®å³ã®ãã©ã³ãã«åŸããŸãã ãŸããããšãã°å€29ãæ¢ããŠããå Žåã29 <40ã§ãããããå·Šã®åå²ã«æ²¿ã£ãŠé²ã¿ãŸããå³ã®åå²ã«ç¶ããŠã2ã€ã®å€ãå«ãåããŒãã«å°éããŸãã

ä»åã¯ã53ãå€47ããã³62ãšæ¯èŒããŸãã47<53 <62ãããŒãå ã®å€ããœãŒããããŠããããšã«æ³šæããŠãã ããã ç®çã®å€ã¯1ããå°ãããä»ã®å€ããã倧ãããããäžå€®ã®åå²ããã©ã£ãŠã3ã€ã®å€ãå«ãåããŒãã«å ¥ããŸãã

ãœãŒããããå€ã®ãªã¹ãïŒ51 <53 <56ïŒãšæ¯èŒãã4ã€ã®ãã©ã³ãã®ãã¡2çªç®ã®ãã©ã³ãã«æ²¿ã£ãŠé²ã¿ãæçµçã«ç®çã®å€ãæã€åããŒãã«å°éããŸãã

ãã®ã¢ã«ãŽãªãºã ãæ€çŽ¢ãé«éåããããïŒ
- åããŒãå ã®å€ïŒããŒïŒããœãŒããããŸãã
- ã¢ã«ãŽãªãºã ã®ãã©ã³ã¹ãåããŠããŸããããŒã¯ããŒãå šäœã«åçã«åæ£ãããé·ç§»ã®æ°ãæå°éã«æããããŸãã åãã©ã³ãã¯ãä»ã®ãã¹ãŠã®åããŒããšã»ãŒåãæ°ã®ããŒãå«ãåããŒãã«ã€ãªãããŸãã
Postgresã§ã®ã€ã³ããã¯ã¹ã®å€èŠ³
ãªãŒãã³ãšã€ãªã¯30幎以äžåã«å³ãæããããçŸä»£ã®Postgresãšã¯äœã®é¢ä¿ãããã®ãââïŒ äœæããindex_users_on_nameã¯ããã®å³ã«éåžžã«äŒŒãŠããããšãããããŸãã CREATE INDEXã³ãã³ããå®è¡ããããšãPostgresã¯ãŠãŒã¶ãŒããŒãã«ã®ãã¹ãŠã®å€ãBããªãŒããªãŒã®ããŒãšããŠä¿åããŸãã ããã¯ãã€ã³ããã¯ã¹ããŒãã®å€èŠ³ã§ãã

ã€ã³ããã¯ã¹ã®åãšã³ããªã¯ãIndexTupleDataãšåŒã°ããCã®æ§é ã§æ§æãããå€ãšãããããããå«ã¿ãŸãã åŸè ã¯ã¹ããŒã¹ãç¯çŽããããã«äœ¿çšãããããŒã®ã€ã³ããã¯ã¹ã®å±æ§ãNULLã§ãããã©ããã«é¢ããæ å ±ãæžã蟌ãŸããŸãã å€èªäœã¯ãã€ã³ããã¯ã¹å ã®ããããããã«åŸããŸãã
IndexTupleDataæ§é ã¯æ¬¡ã®ããã«ãªããŸãã

t_tid ïŒããã¯ãããŒã¿ããŒã¹å ã®ä»ã®ã€ã³ããã¯ã¹ãŸãã¯ã¬ã³ãŒããžã®ãã€ã³ã¿ãŒã§ãã ããã¯ãCããã®ç©çã¡ã¢ãªãžã®ãã€ã³ã¿ã§ã¯ãªãããšã«æ³šæããŠãã ãããPostgresãã¡ã¢ãªã®äžèŽããããŒãžãæ€çŽ¢ããããã®ããŒã¿ãå«ãŸããŠããŸãã
t_info ïŒããã«ã¯ãã€ã³ããã¯ã¹ã¢ã€ãã ã«é¢ããæ å ±ãå«ãŸããŸãã ããšãã°ãäœåã®å€ãæ ŒçŽãããŠããããããããNULLã§ããããªã©ã
ç解ãæ·±ããããã«ã index_users_on_nameã®ããã€ãã®ãšã³ããªãæ€èšããŠãã ãã ã

ããã§ã¯ãå€ã®ä»£ããã«ãããŒã¿ããŒã¹ããããã€ãã®ååãæ¿å ¥ãããŸãã æäžäœããªãŒããŒãã«ã¯ãããŒãDr. Edna Kundeâãšâ Julius PowlowskiâããããŠæäžäœããŒãã¯â Julius Powlowskiâãšâ Juston Quitzonâã§ãã ãªãŒãã³ããã³ã€ãªå³ãšã¯ç°ãªããPostgresã¯ååããŒãã§èŠªããŒãç¹°ãè¿ããŸãã ããšãã°ããJulius Powlowskiãã¯æäžäœããªãŒãšåããŒãã®ããŒã§ãã t_tidãã€ã³ã¿ãŒã¯ãäžäœããŒãã®JuliusãäžäœããŒãã®åãååã«åç §ããŸãã ããŒå€ãBããªãŒããŒãã«ä¿åããæ¹æ³ã詳ãã調ã¹ããå Žåã¯ãitup.hãã¡ã€ã«ãåç §ããŠãã ããã

å€ãå«ãBããªãŒããŒããæ€çŽ¢ãã
å ã®SELECTã¹ããŒãã¡ã³ãã«æ»ããŸãã
Postgresã¯index_users_on_nameã§ãCaptain Nemoããã©ã®ãããæ£ç¢ºã«æ€çŽ¢ããŸããïŒ æ€çŽ¢ãæ¯èŒãããããé«éã«ã€ã³ããã¯ã¹ã䜿çšããã®ã¯ãªãã§ããïŒ ã€ã³ããã¯ã¹ã«ä¿åãããŠããååã®ããã€ããèŠãŠã¿ãŸãããã

ããã¯ãindex_users_on_nameã®ã«ãŒãããŒãã§ãã ååãå šäœã«åãããã«ããªãŒãå±éããŸããã 4ã€ã®ååãš1ã€ã®NULLå€ããããŸãã ã€ã³ããã¯ã¹èªäœãäœæããããšããã«ãPostgresã¯ãã®ã«ãŒãããŒããèªåçã«äœæããŸããã ã€ã³ããã¯ã¹ã®å é ã瀺ãNULLãé€ããä»ã®4ã€ã®ååã¯ã¢ã«ãã¡ãããé ã«ãªã£ãŠããããšã«æ³šæããŠãã ããã
èŠããŠããããã«ãBããªãŒã¯ãã©ã³ã¹ã®åããããªãŒã§ãã ãããã£ãŠããã®äŸã§ã¯ãããªãŒã«ã¯5ã€ã®åããŒãããããŸãã
- ãDr. ãšããã»ã¯ã³ãâ
- ãDr. ãšããã»ã¯ã³ãâãšâãžã¥ãªã¢ã¹ã»ããŠããŠã¹ããŒâ
- ãJulius PowlowskiããšãMonte Nicolasãã®éã®åå
...ãªã©
ãCaptain Nemoããæ¢ããŠãããããPostgresã¯å³ã®æåã®ãã©ã³ãã«æ²¿ã£ãŠé²ã¿ãŸãïŒã¢ã«ãã¡ãããé ã®ãœãŒãã§ã¯ãæãŸããå€ã¯ãDr. Edna Kundeãã«ãªããŸãïŒã

å³ãããããããã«ãPostgresã¯ç®çã®å€ãæã€ããŒããèŠã€ããŸãã ãã¹ãã®ããã«ãããŒãã«ã«1000åã®ååãè¿œå ããŸããã ãã®å³ã®çµã³ç®ã«ã¯240åãå«ãŸããŠããŸããã ãããã£ãŠãæ®ãã®760åã®å€ãè¹å€ã«æ®ããããããããªãŒã¯æ€çŽ¢ããã»ã¹ãå€§å¹ ã«å éããŸããã
BããªãŒã§ç®çã®ããŒããèŠã€ããããã®ã¢ã«ãŽãªãºã ã«ã€ããŠè©³ããç¥ãããå Žåã¯ã_bt_searché¢æ°ã®ã³ã¡ã³ããåç §ããŠãã ããã

ããŒãå ã®å€ãæ€çŽ¢ããŸã
ãã®ãããPostgresã¯240åã®ååãå«ãããŒãã«ç§»åããŸãããããã®äžã§å€ãCaptain NemoããèŠã€ããå¿ èŠããããŸããã

ãã®ããã«ãã·ãŒã±ã³ã¹æ€çŽ¢ã§ã¯ãªãããã€ããªæ€çŽ¢ã¢ã«ãŽãªãºã ã䜿çšãããŸãã æåã«ãã·ã¹ãã ã¯ãªã¹ãã®äžå€®ïŒäœçœ®50ïŒ ïŒã«ããããŒãæ¯èŒããŸãã

åžæã®å€ã¯ãBreana Wittingãã®åŸã«ã¢ã«ãã¡ãããé ã«ç¶ããããPostgresã¯75ïŒ ïŒãªã¹ãã®4åã®3ïŒã«ããããŒã«ãžã£ã³ãããŸãã

ä»åãç§ãã¡ã®äŸ¡å€ã¯ãã£ãšé«ããªããã°ãªããŸããã ãã®åŸãPostgresã¯ããå€ãé«ããžã£ã³ãããŸãã ç§ã®å Žåãç®çã®å€ãæçµçã«èŠã€ãããŸã§ãã·ã¹ãã ã¯ããŒãå ã®ããŒã®ãªã¹ãã8åãžã£ã³ãããå¿ èŠããããŸããã

_bt_binsrché¢æ°ã®ã³ã¡ã³ãã§ãããŒãå ã®å€ã®æ€çŽ¢ã¢ã«ãŽãªãºã ã«ã€ããŠè©³ããèªãããšãã§ããŸãã

ãã®ä»ã®èå³æ·±ãããš
ãåžæã®å ŽåãBããªãŒã«é¢ããçè«ã®äžéšã¯ãç§åŠè«æã BããªãŒã§ã®åææäœã®å¹ççãªããã¯ãããåéã§ããŸãã
BããªãŒã«ããŒãè¿œå ããŸã ã ããªãŒã«æ°ããããŒãè¿œå ããæé ã¯ãéåžžã«èå³æ·±ãã¢ã«ãŽãªãºã ã«åŸã£ãŠå®è¡ãããŸãã éåžžãããŒã¯ãåãå ¥ãããããœãŒãã«åŸã£ãŠããŒãã«æžã蟌ãŸããŸãã ããããããŒãã«ç©ºãã¹ããŒã¹ããªããªã£ããã©ãããŸããïŒ ãã®å ŽåãPostgresã¯ããŒãã2ã€ã®å°ããªããŒãã«åå²ãããã®ãã¡ã®1ã€ã«ããŒãæ¿å ¥ããŸãã ãŸãããã¹ããªãããã€ã³ããããã®ããŒãšæ°ããåããŒããžã®ãã€ã³ã¿ãŒã芪ããŒãã«è¿œå ããŸãã ãã¡ããããã®ããŒãæ¿å ¥ããããã«èŠªããŒããåå²ããå¿ èŠããããŸã;ãã®çµæã1ã€ã®ããŒãããªãŒã«è¿œå ããæé ã¯è€éãªååž°æäœã«å€ãããŸãã
BããªãŒããããŒãåé€ããŸã ã ãŸããéåžžã«å¥åŠãªæé ã PostgresããŒãããããŒãåé€ããå Žåãå¯èœã§ããã°ãåããŒããçµåããŸãã ååž°çãªæäœãå¯èœã§ãã
Bãªã³ã¯ããªãŒããªãŒ å®éããªãŒãã³ãšã€ãªã®ç 究ã¯ãåäžã®ããªãŒãè€æ°ã®ã¹ã¬ããã§äœ¿çšãããŠããå Žåã«ã䞊åæ§ãšããããã³ã°ã«é¢é£ããŠèæ¡ãããé©æ°ã説æããŠããŸãã Postgresã³ãŒããšãã®ã¢ã«ãŽãªãºã ã¯ãè€æ°ã®ã¯ã©ã€ã¢ã³ããåæã«ã¢ã¯ã»ã¹ïŒãŸãã¯å€æŽïŒã§ããããããã«ãã¹ã¬ããããµããŒãããå¿ èŠããããŸãã åããŒããã次ã®åããŒãïŒãå³ç¢å°ãïŒã«ãã€ã³ã¿ãŒãè¿œå ãããšãäžæ¹ã®ã¹ã¬ãããããªãŒãæ€çŽ¢ã§ããããäžæ¹ã®ã¹ã¬ããã¯ã€ã³ããã¯ã¹å šäœããããã¯ããã«ããŒããå ±æã§ããŸãã

æ¢æ€ããããšãæããªãã§ãã ãã
ãããããããªãã¯Postgresã®äœ¿ãæ¹ã«é¢ãããã¹ãŠãç¥ã£ãŠããŸããããããå éšã«ã©ã®ããã«é 眮ãããŠãããããå éšãã«ãããã®ãç¥ã£ãŠããŸããïŒ ã³ã³ãã¥ãŒã¿ãŒãµã€ãšã³ã¹ã®ç 究ã¯ãçŸåšã®ãããžã§ã¯ãã«åãçµãã§ããã ãã§ãªããåãªã嚯楜ã§ã¯ãªããéçºè ã®éçºããã»ã¹ã®äžéšã§ãã 幎ã ãåœç€Ÿã®ãœãããŠã§ã¢ããŒã«ã¯ããè€éã§ãå€é¢çã§ãããåªãããã®ã«ãªãããµã€ããã¢ããªã±ãŒã·ã§ã³ãç°¡åã«äœæã§ããããã«ãªããŸããã ãããããã®ãã¹ãŠã®åºç€ãã³ã³ãã¥ãŒã¿ãŒãµã€ãšã³ã¹ã®ç§åŠã§ããããšãå¿ããŠã¯ãªããŸããã Postgresã®ãªãŒãã³ãœãŒã¹éçºè ã³ãã¥ããã£å šäœãšåæ§ã«ãLehmanãYaoãªã©ã®åä»»è ã®è©ã«ç«ã£ãŠããŸãã ãããã£ãŠãæ¥åžžçã«äœ¿çšããããŒã«ãç²ç®çã«ä¿¡é Œãããããã€ã¹ã調ã¹ãŠãã ããã ããã¯ãããªããããé«ãå°éçã¬ãã«ãéæããã®ãå©ããŸããããªãã¯ãããªãã以åã¯æ°ã¥ããªãã£ãæ å ±ãšè§£æ±ºçãèªåã§èŠã€ããããšãã§ããŸãã