13.1. EXPLAIN¤ÎÍøÍÑ

PostgreSQL¤ÏÍ¿¤¨¤é¤ì¤¿Ì䤤¹ç¤ï¤»¤«¤éÌ䤤¹ç¤ï¤»·×²è¤òºî¤ê½Ð¤·¤Þ¤¹¡£ Ì䤤¹ç¤ï¤»¤Î¹½Â¤¤È´Þ¤Þ¤ì¤ë¥Ç¡¼¥¿¤ÎÀ­¼Á¤ËŬ¤·¤¿Àµ¤·¤¤Ì䤤¹ç¤ï¤»·×²è¤òÁªÂò¤¹¤ë¤³¤È¤¬Îɤ¤À­Ç½¤òÆÀ¤ë¤¿¤á¤ËÈó¾ï¤Ë½ÅÍפˤʤê¤Þ¤¹¡£ EXPLAIN¥³¥Þ¥ó¥É¤ò»È¤¨¤Ð¡¢Ç¤°Õ¤ÎÌ䤤¹ç¤ï¤»¤ËÂФ·¤Æ¥·¥¹¥Æ¥à¤¬¤É¤Î¤è¤¦¤ÊÌ䤤¹ç¤ï¤»·×²è¤òºî¤Ã¤¿¤Î¤«¤ï¤«¤ê¤Þ¤¹¡£ Ì䤤¹ç¤ï¤»·×²è¤òÆɤߤ³¤Ê¤¹¤³¤È¤Ë¤Ä¤¤¤Æ¤Ï¡¢ÊÌÅÓ¹­ÈϰϤˤ錄¤ë¥Á¥å¡¼¥È¥ê¥¢¥ë¤òºî¤é¤Ê¤±¤ì¤Ð¤Ê¤é¤Ê¤¤¤Û¤É¤ÎÏÓ¤¬É¬ÍפǤ¹¡£ ¤³¤Îʸ½ñ¤Ï¤½¤³¤Þ¤Ç¥«¥Ð¡¼¤·¤Þ¤»¤ó¤¬¡¢´ðËÜŪ¤Ê¾ðÊó¤ò¤¤¤¯¤Ä¤«Ä󶡤·¤Þ¤¹¡£

¸½ºß¤Î¤È¤³¤íEXPLAIN¤¬½ÐÎϤ¹¤ë¿ôÃͤˤϰʲ¼¤Î¤â¤Î¤¬¤¢¤ê¤Þ¤¹¡£

• ½é´ü½èÍý¤Î¿äÄꥳ¥¹¥È(½ÐÎÏÍѤΥ¹¥­¥ã¥ó¤¬³«»Ï¤µ¤ì¤ë¤Þ¤Ç¤Ë¾ÃÈñ¤µ¤ì¤ë»þ´Ö¡¢Î㤨¤ÐSORT¥Î¡¼¥É¤Ç¼Â¹Ô¤µ¤ì¤ë¥½¡¼¥È½èÍý¤Î»þ´Ö)¡£

• Á´ÂΤοäÄꥳ¥¹¥È(·ë²Ì¤Î¹ÔÁ´ÂΤ¬Ãê½Ð¤µ¤ì¤ë¾ì¹ç¤Î¥³¥¹¥È¡£¤È¤Ï¤¤¤Ã¤Æ¤â¡¢LIMIT¶ç¤ò»È¤Ã¤¿Ì䤤¹ç¤ï¤»¤Î¾ì¹ç¤Ï¡¢Á´ÂΤΥ³¥¹¥È·×»»¤ò¤¹¤ëÁ°¤ËÄä»ß¤·¤Þ¤¹)¡£

• ¤³¤Î·×²è¥Î¡¼¥É¤¬½ÐÎϤ¹¤ë¹Ô¤Î¿äÄê¿ô(¤³¤ì¤â¡¢¼Â¹Ô¤¬´°Î»¤·¤¿¾ì¹ç¤Î¤ß¡£)

• ¤³¤Î·×²è¥Î¡¼¥É¤¬½ÐÎϤ¹¤ë¹Ô¤Î(¥Ð¥¤¥Èñ°Ì¤Ç¤Î)¿äÄêÊ¿¶ÑÉý¡£

¥³¥¹¥È¤Ï¡¢¼è¤ê½Ð¤¹¥Ç¥£¥¹¥¯¥Ú¡¼¥¸Ã±°Ì¤Ç¬Äꤵ¤ì¤Þ¤¹¡£ (CPU¤ÎÉéô¤Ë´Ø¤·¤Æ¤Î¿äÄê¤Ï¡¢Å¬Åö¤Ë×ó°ÕŪ¤Ë·è¤á¤¿´ð½à¤Ë¤·¤¿¤¬¤Ã¤Æ¥Ç¥£¥¹¥¯¥Ú¡¼¥¸Ã±°Ì¤Ç´¹»»¤µ¤ì¤Þ¤¹¡£ ¤³¤ì¤é¤Î´ð½àÃͤθú²Ì¤òÃΤꤿ¤¤¾ì¹ç¤Ï¡¢¹à16.4.5.2¤Î¼Â¹Ô»þÀßÄê¥Ñ¥é¥á¡¼¥¿¤Î°ìÍ÷¤ò»²¾È¤·¤Æ¤¯¤À¤µ¤¤¡£)

¾å°Ì¥Î¡¼¥É¤Î¥³¥¹¥È¤Ë¤Ï¡¢Á´¤Æ¤Î»Ò¥Î¡¼¥É¤Î¥³¥¹¥È¤â¤½¤ÎÃæ¤Ë´Þ¤Þ¤ì¤Æ¤¤¤ë¤³¤È¤Ë½½Ê¬Î±°Õ¤·¤Æ¤¯¤À¤µ¤¤¡£ ¤³¤Î¥³¥¹¥È¤Ï¥×¥é¥ó¥Ê¤ä¥ª¥×¥Æ¥£¥Þ¥¤¥¶¤¬´ØÍ¿¤¹¤ë¥³¥¹¥È¤Î¤ßÈ¿±Ç¤¹¤ëÅÀ¤â¤Þ¤¿½ÅÍפǤ¹¡£ ¤È¤ê¤ï¤±¡¢·ë²Ì¤Î¹Ô¤ò¥Õ¥í¥ó¥È¥¨¥ó¥É¤ËžÁ÷¤¹¤ë¥³¥¹¥È¤Ï¡¢¼ÂºÝ¤Î½èÍý»þ´Ö¤ÎÂçȾ¤òÀê¤á¤ë²ÄǽÀ­¤¬¤¢¤ë¤Ë¤â´Ø¤ï¤é¤º¡¢¹Í褵¤ì¤Þ¤»¤ó¡£ ¥×¥é¥ó¥Ê¤Ï¡¢·×²è¤ò¤¤¤«¤ËÊѹ¹¤·¤è¤¦¤È¡¢¤É¤¦¤¹¤ë¤³¤È¤â¤Ç¤­¤Ê¤¤¤¿¤á¡¢¤³¤ì¤ò̵»ë¤·¤Þ¤¹¡£ (Àµ¤·¤¤·×²è¤Ï¤É¤ó¤Ê¤â¤Î¤Ç¤¢¤ì¡¢Á´¤ÆƱ¤¸¹Ô¤ò·ë²Ì¤È¤·¤Æ½ÐÎϤ¹¤ë¤È¿®¤¸¤Æ¤¤¤Þ¤¹¡£)

½ÐÎϹԿô¤ÏÌ䤤¹ç¤ï¤»¤Ë¤è¤Ã¤Æ½èÍý¤¢¤ë¤¤¤Ï¥¹¥­¥ã¥ó¤µ¤ì¤¿¹Ô¿ô¤òɽ¤·¤Æ¤¤¤Ê¤¤¤Î¤Ç¡¢Â¿¾¯°·¤¤¤Ë¤¯¤¯¤Ê¤Ã¤Æ¤¤¤Þ¤¹¡£ ³ºÅö¥Î¡¼¥É¤ËŬÍѤµ¤ì¤ëÁ´¤Æ¤ÎWHERE¶çÀ©Ì󤫤éÍè¤ëÁªÂòÀ­¿äÄêÃͤòÈ¿±Ç¤·¡¢Ä̾ï¤Î»þ¤è¤ê¤â¾¯¤Ê¤¤¹Ô¿ô¤Ë¤Ê¤ê¤Þ¤¹¡£ ÍýÁÛŪ¤Ë¤Ï¡¢ºÇ¾å°Ì¤Î¹Ô¿ô¤Î¿äÄêÃͤϡ¢¼ÂºÝ¤ËÌ䤤¹ç¤ï¤»¤Ë¤è¤Ã¤ÆÊÖ¤µ¤ì¡¢¹¹¿·¤µ¤ì¡¢¤¢¤ë¤¤¤Ïºï½ü¤µ¤ì¤¿³µ»»¤Î¹Ô¿ô¤È¤Ê¤ê¤Þ¤¹¡£

¤¤¤¯¤Ä¤«Îã¤ò¼¨¤·¤Þ¤¹ (VACUUM ANALYZE¤ò¤«¤±¤¿¸å¤Îregression test¥Ç¡¼¥¿¥Ù¡¼¥¹¤ò»È¤Ã¤Æ¤¤¤Þ¤¹¡£»ÈÍѤ·¤¿¥½¡¼¥¹¤Ï7.3³«È¯ÈǤǤ¹)¡£

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..333.00 rows=10000 width=148)

¤³¤ì¤Ï¤Û¤È¤ó¤É¸«¤¿¤Þ¤Þ¤Ç¤¹¡£¤â¤·¡¢

SELECT * FROM pg_class WHERE relname = 'tenk1';

¤ò¼Â¹Ô¤¹¤ë¤È¡¢tenk1¤Ë¤Ï233¤Î¥Ç¥£¥¹¥¯¥Ú¡¼¥¸¤È10000¤Î¹Ô¤¬¤¢¤ë¤³¤È¤¬¤ï¤«¤ê¤Þ¤¹¡£ ¤Ç¤¹¤«¤é¡¢Ã±°Ì¤¢¤¿¤ê1.0¤È¥³¥¹¥È¤¬·è¤Þ¤Ã¤Æ¤¤¤ë233¥Ú¡¼¥¸¤ÎÆɤ߽Ф·¤Ë¡¢¸½ºß0.01¤ËÀßÄꤵ¤ì¤Æ¤¤¤ëcpu_tuple_cost¤ò10000Çܤ·¤¿¤â¤Î¤ò²Ã¤¨¤¿¤â¤Î¤¬¡¢¥³¥¹¥È¤Î¿äÄêÃͤˤʤë¤ï¤±¤Ç¤¹¡£¡ÊSHOW cpu_tuple_cost¤ò»î¤·¤Æ¤ß¤Æ¤¯¤À¤µ¤¤¡£¡Ë

¤Ç¤Ï¡¢WHERE¾ò·ï¤ò²Ã¤¨¤ÆÌ䤤¹ç¤ï¤»¤òÊѹ¹¤·¤Æ¤ß¤Þ¤¹¡£

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;

                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..358.00 rows=1033 width=148)
   Filter: (unique1 < 1000)

WHERE¶ç¤¬¤¢¤ë¤¿¤á¡¢½ÐÎϹԿô¤Î¸«ÀѤ꤬¾®¤µ¤¯¤Ê¤Ã¤Æ¤¤¤Þ¤¹¡£ ¤·¤«¤·¡¢°ÍÁ³¤È¤·¤Æ10000¹ÔÁ´¤Æ¤ò¥¹¥­¥ã¥ó¤¹¤ëɬÍפ¬¤¢¤ë¤¿¤á¡¢¥³¥¹¥È¤Ï¾®¤µ¤¯¤Ê¤Ã¤Æ¤¤¤Þ¤»¤ó¡£ ¼ÂºÝ¤Ë¤Ï¡¢WHERE¾ò·ï¤ò¸¡ºº¤¹¤ë¤¿¤á¤ËCPU»þ´Ö¤¬Í¾·×¤Ë¤«¤«¤ë¤³¤È¤òÈ¿±Ç¤·¤Æ¡¢¤Û¤ó¤Î¾¯¤·¤Ç¤¹¤¬¥³¥¹¥È¤¬¾å¾º¤·¤Æ¤¤¤Þ¤¹¡£

¤³¤ÎÌ䤤¹ç¤ï¤»¤¬ÁªÂò¤¹¤ë¼ÂºÝ¤Î¹Ô¿ô¤Ï1000¤Ç¤¹¡£ ¤·¤«¤·¡¢¿äÄê¤Ï³µ»»Ãͤˤ¹¤®¤Þ¤»¤ó¡£ ¤³¤Î¼Â¸³¤òÆó²ó¼Â¹Ô¤·¤¿¾ì¹ç¡¢¤ª¤½¤é¤¯Â¿¾¯°Û¤Ê¤ë¿äÄêÃͤòÆÀ¤ë¤Ç¤·¤ç¤¦¡£ ¤â¤Ã¤È¤¤¤¦¤È¡¢¤³¤ì¤ÏANALYZE¥³¥Þ¥ó¥É¤ò¹Ô¤¦ÅÙ¤ËÊѲ½¤·¤Þ¤¹¡£ ¤Ê¤¼¤Ê¤é¡¢ANALYZE¤ÇÀ¸À®¤µ¤ì¤ëÅý·×¾ðÊó¤Ï¡¢¥Æ¡¼¥Ö¥ë¤Î¥é¥ó¥À¥à¤ÊɸËܤ«¤é¼è¤ê½Ð¤µ¤ì¤ë¤«¤é¤Ç¤¹¡£

¤Ç¤Ï¡¢¹¹¤ËÌ䤤¹ç¤ï¤»¤òÊѹ¹¤·¡¢¾ò·ï¤ò¤è¤ê¶¯¤¯À©¸Â¤·¤Æ¤ß¤Þ¤¹¡£

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50;

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..179.33 rows=49 width=148)
   Index Cond: (unique1 < 50)

WHERE¶ç¾ò·ï¤ÎÁªÂòÀ­¤ò½½Ê¬¤Ë¹â¤á¤ë¤È¡¢¥×¥é¥ó¥Ê¤Ï¥·¡¼¥±¥ó¥·¥ã¥ë¥¹¥­¥ã¥ó¤è¤ê¤â¥¤¥ó¥Ç¥Ã¥¯¥¹¥¹¥­¥ã¥ó¤¬°Â²Á¤Ç¤¢¤ë¤È·èÄꤹ¤ë¤è¤¦¤Ë¤Ê¤ë¤³¤È¤¬Ê¬¤«¤ê¤Þ¤¹¡£ ¤³¤Î·×²è¤Ç¤Ï¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¤¬¤¢¤ë¤¿¤á¤Ë¤¿¤Ã¤¿50¹Ô¤À¤±¤Ë¥¢¥¯¥»¥¹¤¹¤ì¤Ð¤è¤¤¤³¤È¤Ë¤Ê¤ê¤Þ¤¹¡£ ¤Ç¤¹¤«¤é¡¢¥Ç¥£¥¹¥¯¥Ú¡¼¥¸Á´ÂΤòÃ༡ÆɤߤȤë¤è¤ê¤â¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ë¤è¤ë¸Ä¡¹¤Î¼è¤ê½Ð¤·¤Ï¹â²Á¤Ë¤Ä¤¯¤Ë¤â´Ø¤ï¤é¤º¡¢¤³¤Î·×²è¤¬ºÎÍѤµ¤ì¤Þ¤¹¡£

WHERE¾ò·ï¤Ë¶ç¤ò¤â¤¦1¤ÄÄɲä·¤Þ¤¹¡£

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND stringu1 = 'xxx';

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tenk1_unique1 on tenk1  (cost=0.00..179.45 rows=1 width=148)
   Index Cond: (unique1 < 50)
   Filter: (stringu1 = 'xxx'::name)

Äɲä·¤¿¶çstringu1 = 'xxx'¤Ë¤è¤ê½ÐÎϹԿô¤Î¿äÄêÃͤϸº¾¯¤¹¤ë¤â¤Î¤Î¡¢¸¡ºº¤¹¤ë¹Ô¤Î½¸¹ç¤ÏÊѤï¤é¤Ê¤¤¤¿¤á¤Ë¥³¥¹¥È¤Ï¸º¤Ã¤Æ¤¤¤Þ¤»¤ó¡£ ¤³¤Îstringu1¶ç¤Ï¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¾ò·ï¤È¤·¤Æ¤ÏŬÍѤǤ­¤Ê¤¤¤³¤È¤ËÃí°Õ¤·¤Æ¤¯¤À¤µ¤¤ (¤³¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ï¡¢unique1Îó¤Î¤ß¤òÂоݤȤ·¤Æ¤¤¤ë¤«¤é¤Ç¤¹)¡£ ¤½¤ÎÂå¤ï¤ê¡¢stringu1¶ç¤Ï¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¤Ë¤è¤Ã¤ÆÃê½Ð¤µ¤ì¤¿¹Ô¤Î¥Õ¥£¥ë¥¿¤È¤·¤ÆŬÍѤµ¤ì¤Æ¤¤¤Þ¤¹¡£ ¤³¤ì¤Ë¤è¤ê¡¢ÄɲÃʬ¤Î¥Á¥§¥Ã¥¯¤òÈ¿±Ç¤¹¤ë¤¿¤á¡¢¥³¥¹¥È¤Ï¼ÂºÝ¤Ë¤Ï¾¯¤·¾å¤¬¤ê¤Þ¤¹¡£

º£¤Þ¤ÇÀâÌÀ¤Ë»È¤Ã¤Æ¤­¤¿¥Õ¥£¡¼¥ë¥É¤ò»È¤Ã¤Æ2¤Ä¤Î¥Æ¡¼¥Ö¥ë¤ò·ë¹ç¤·¤Æ¤ß¤Þ¤·¤ç¤¦¡£

EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                               QUERY PLAN
----------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                                      (cost=0.00..179.33 rows=49 width=148)
         Index Cond: (unique1 < 50)
   ->  Index Scan using tenk2_unique2 on tenk2 t2
                                      (cost=0.00..3.01 rows=1 width=148)
         Index Cond: ("outer".unique2 = t2.unique2)

¤³¤ÎÆþ¤ì»Ò¾õ¥ë¡¼¥×·ë¹ç¤Ç¤Ï¡¢³°Éô¥¹¥­¥ã¥ó¤Ë¡¢ºÇ¸å¤ÎÎã¤Ç¼¨¤·¤¿¤Î¤ÈƱ¤¸¥¤¥ó¥Ç¥Ã¥¯¥¹¥¹¥­¥ã¥ó¤¬»È¤ï¤ì¤Æ¤¤¤Þ¤¹¡£ ¤½¤·¤Æ¡¢unique1 < 50 WHERE¶ç¤ò³ºÅö¥Î¡¼¥É¤ËŬÍѤ·¤Æ¤¤¤ë¤¿¤á¡¢¥³¥¹¥È¤È½ÐÎϹԿô¤ÏÊѤï¤Ã¤Æ¤¤¤Þ¤»¤ó¡£ ¤³¤ÎÃʳ¬¤Ç¤Ït1.unique2 = t2.unique2¶ç¤Ï´Ø·¸¤·¤Æ¤ª¤é¤º¡¢³°Éô¥¹¥­¥ã¥ó¤Ë¤ª¤±¤ë½ÐÎϹԿô¤Ë±Æ¶Á¤·¤Æ¤¤¤Þ¤»¤ó¡£ ÆâÉô¥¹¥­¥ã¥ó¤Ç¤Ï¡¢³°Éô¥¹¥­¥ã¥ó¤Ë¤ª¤±¤ë¸½ºß¤Î¹Ô¤Îunique2¤ÎÃͤ¬ÆâÉô¤Î¥¤¥ó¥Ç¥Ã¥¯¥¹¥¹¥­¥ã¥ó¤ËÅꤲ¹þ¤Þ¤ì¡¢t2.unique2 = constant¤Î¤è¤¦¤Ê¥¤¥ó¥Ç¥Ã¥¯¥¹¾ò·ï¤òÀ¸À®¤·¤Þ¤¹¡£ ½¾¤Ã¤Æ¡¢ÆâÉô¥¹¥­¥ã¥ó¤Î·×²è¤È¥³¥¹¥È¤Ï¡¢Î㤨¤ÐEXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42¤Î¤è¤¦¤ÊÌ䤤¹ç¤ï¤»¤ÈƱ¤¸¤Ç¤¹¡£ ¥ë¡¼¥×¥Î¡¼¥É¤Î¥³¥¹¥È¤Ï¡¢³°Éô¥¹¥­¥ã¥ó¤Î¥³¥¹¥È¤È¡¢³Æ¡¹¤Î³°Éô¤Î¹Ô¤ËÂФ·¤ÆÆâÉô¥¹¥­¥ã¥ó¤¬·«¤êÊÖ¤µ¤ì¤ë¤³¤È¤Ë¤è¤ë¥³¥¹¥È¡Ê¤³¤³¤Ç¤Ï49 * 3.01¡Ë¤ò²Ã¤¨¡¢¹¹¤Ë·ë¹ç½èÍý¤ò¹Ô¤¦¤¿¤á¤Î¾¯¡¹¤ÎCPU»þ´Ö¤ò²Ã¤¨¤¿¤â¤Î¤Ë¤Ê¤ê¤Þ¤¹¡£

¤³¤ÎÎã¤Ç¤Ï¡¢·ë¹ç¤Î½ÐÎϹԿô¤Ï2¤Ä¤Î¥¹¥­¥ã¥ó¤Î½ÐÎϹԿô¤ÎÀѤËÅù¤·¤¯¤Ê¤Ã¤Æ¤¤¤Þ¤¹¤¬¡¢¤¤¤Ä¤â¤½¤¦¤Ê¤ë¤ï¤±¤Ç¤Ï¤¢¤ê¤Þ¤»¤ó¡£ ¤¿¤¤¤Æ¤¤¤Î¾ì¹ç¡¢2¤Ä¤Î¥Æ¡¼¥Ö¥ë¤Ë´Ø·¸¤¹¤ëWHERE¶ç¤¬¤¢¤Ã¤Æ¡¢¤½¤ÎWHERE¶ç¤ÏÆþÎÏ¥¹¥­¥ã¥ó»þ¤Ç¤Ï¤Ê¤¯¡¢·ë¹ç¤ò¹Ô¤¦ºÝ¤ËŬÍѤµ¤ì¤ë¤«¤é¤Ç¤¹¡£ Î㤨¤Ð¡¢WHERE ... AND t1.hundred < t2.hundred¤È¤¤¤¦¶ç¤òÄɲä·¤¿¤È¤¹¤ë¤È¡¢·ë¹ç¥Î¡¼¥É¤Î½ÐÎϹԿô¤ò¸º¤é¤·¤Ï¤·¤Þ¤¹¤¬¡¢ÆþÎÏ¥¹¥­¥ã¥ó¤Ë¤Ï±Æ¶Á¤·¤Þ¤»¤ó¡£

³ÆÌ䤤¹ç¤ï¤»·×²è¤Î¼ïÎà¤ËÂФ¹¤ëenable/disable¥Õ¥é¥°¤ò»ÈÍѤ·¤Æ¡¢¥×¥é¥ó¥Ê¤¬ºÇ¤âÎɤ¤¤È¹Í¤¨¤Æ¤¤¤ëÀïά¤ò¶¯À©Åª¤Ë̵»ë¤µ¤»¤ëÊýË¡¤Ë¤è¤ê¡¢°Û¤Ê¤Ã¤¿·×²è¤ò´Ñ»¡¤¹¤ë¤³¤È¤¬¤Ç¤­¤Þ¤¹¡£ ¡ÊÈó¾ï¤Ë¸¶»ÏŪ¤Ê¥Ä¡¼¥ë¤Ç¤¹¤¬¡¢ÍøÍѲÁÃͤ¬¤¢¤ê¤Þ¤¹¡£ ¹à13.3¤â»²¾È¤¯¤À¤µ¤¤¡£)

SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                               QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=179.45..563.06 rows=49 width=296)
   Hash Cond: ("outer".unique2 = "inner".unique2)
   ->  Seq Scan on tenk2 t2  (cost=0.00..333.00 rows=10000 width=148)
   ->  Hash  (cost=179.33..179.33 rows=49 width=148)
         ->  Index Scan using tenk1_unique1 on tenk1 t1
                                    (cost=0.00..179.33 rows=49 width=148)
               Index Cond: (unique1 < 50)

¤³¤Î·×²è¤Ç¤Ï¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¥¹¥­¥ã¥ó¤ò»È¤Ã¤Ætenk1¤«¤éɬÍפÊ50¹Ô¤ò¼è¤ê½Ð¤·¤Æ¥á¥â¥ê¾å¤Î¥Ï¥Ã¥·¥å¥Æ¡¼¥Ö¥ë¤Ë³ÊǼ¤·¡¢¤½¤Î¸å¡¢tenk2¤ò¥·¡¼¥±¥ó¥·¥ã¥ë¥¹¥­¥ã¥ó¤·¤Æ¡¢tenk2¤Î¹ÔËè¤Ët1.unique2 = t2.unique2¤òËþ¤¿¤¹¤«¤É¤¦¤«¥Ï¥Ã¥·¥å¥Æ¡¼¥Ö¥ë¤ò¸¡ºº¤·¤Þ¤¹¡£ tenk1¤òÆɤ߼è¤ê¡¢¥Ï¥Ã¥·¥å¥Æ¡¼¥Ö¥ë¤òÀßÄꤹ¤ë¤¿¤á¤Î¥³¥¹¥È¤Ï¡¢tenk2¤ÎÆɤ߼è¤ê¤ò»Ï¤á¤ë¤Þ¤Ç¹Ô¤òÆþ¼ê¤·¤Þ¤»¤ó¤Î¤Ç¡¢´°Á´¤Ë¥Ï¥Ã¥·¥å·ë¹ç¤Î½é´ü¥³¥¹¥È¤È¤Ê¤ê¤Þ¤¹¡£ ¤Þ¤¿¡¢¤³¤Î·ë¹ç¤Î¿äÄêÁí»þ´Ö¤Ë¤Ï¡¢¥Ï¥Ã¥·¥å¥Æ¡¼¥Ö¥ë¤ò10000²ó¸¡ºº¤¹¤ë¡¢Â礭¤ÊCPU»þ´Ö¤¬´Þ¤Þ¤ì¤Æ¤¤¤Þ¤¹¡£ ¤·¤«¤·¡¢¤³¤Î·×²è·¿¤Ç¤Ï¥Ï¥Ã¥·¥å¥Æ¡¼¥Ö¥ë¤ÎÀßÄê¤Ï°ìÅ٤ΤߤΤ¿¤á¡¢10000x179.33ʬ¤Î¥³¥¹¥È¤Ï¤«¤«¤é¤Ê¤¤¤³¤È¤ËÃí°Õ¤·¤Æ¤¯¤À¤µ¤¤¡£

EXPLAIN ANALYZE¤ò»ÈÍѤ·¤Æ¡¢¥×¥é¥ó¥Ê¤¬¿äÄꤹ¤ë¥³¥¹¥È¤ÎÀºÅÙ¤òÅÀ¸¡¤¹¤ë¤³¤È¤¬¤Ç¤­¤Þ¤¹¡£ ¤³¤Î¥³¥Þ¥ó¥É¤Ï¼ÂºÝ¤Ë¤½¤ÎÌ䤤¹ç¤ï¤»¤ò¼Â¹Ô¤·¡¢·×²è¥Î¡¼¥ÉËè¤ËÍפ·¤¿¼ÂºÝ¤Î¼Â¹Ô»þ´Ö¤ò¡¢ÉáÄ̤ÎEXPLAIN¤¬¼¨¤¹¿äÄꥳ¥¹¥È¤ÈƱ¤¸ÃͤȰì½ï¤Ëɽ¼¨¤·¤Þ¤¹¡£ Î㤨¤Ð¡¢°Ê²¼¤Î¤è¤¦¤Ê·ë²Ì¤òÆÀ¤ë¤³¤È¤¬¤Ç¤­¤Þ¤¹¡£

EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
                                 (actual time=1.181..29.822 rows=50 loops=1)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                  (cost=0.00..179.33 rows=49 width=148)
                                 (actual time=0.630..8.917 rows=50 loops=1)
         Index Cond: (unique1 < 50)
   ->  Index Scan using tenk2_unique2 on tenk2 t2
                  (cost=0.00..3.01 rows=1 width=148)
                                 (actual time=0.295..0.324 rows=1 loops=50)
         Index Cond: ("outer".unique2 = t2.unique2)
 Total runtime: 31.604 ms

"actual time"Ãͤϼ»þ´Ö¤ò¥ß¥êÉÃñ°Ì¤Çɽ¤µ¤ì¤Æ¤¤¤ë¤³¤È¡¢"cost"¿äÄêÃͤϥǥ£¥¹¥¯Æɤ߼è¤ê¤Ë´Ø¤¹¤ë²¿¤é¤«¤Îñ°Ì¤Çɽ¤µ¤ì¤Æ¤¤¤ë¤³¤È¤ËÃí°Õ¤·¤Æ¤¯¤À¤µ¤¤¡£ ¤Ç¤¹¤«¤é¤½¤Î¤Þ¤ÞÈæ³Ó¤¹¤ë¤³¤È¤Ï¤Ç¤­¤Þ¤»¤ó¡£ ÃíÌܤ¹¤ëÅÀ¤Ï¤½¤Î³ä¹ç¤Ç¤¹¡£

Ì䤤¹ç¤ï¤»·×²è¤ÎÃæ¤Ë¤Ï¡¢²¿²ó¤âÉû·×²è¥Î¡¼¥É¤ò¼Â¹Ô¤Ç¤­¤ë¤â¤Î¤¬¤¢¤ê¤Þ¤¹¡£ Î㤨¤Ð¡¢¾å½Ò¤ÎÆþ¤ì»Ò¾õ¥ë¡¼¥×¤Î·×²è¤Ç¤Ï¡¢ÆâÉô¥¤¥ó¥Ç¥Ã¥¯¥¹¥¹¥­¥ã¥ó¤Ï³°Éô¤Î¹ÔËè¤Ë°ìÅÙ¹Ô¤ï¤ì¤Þ¤¹¡£ ¤³¤Î¤è¤¦¤Ê¾ì¹ç¡¢"loops"ÃͤϤ½¤Î¥Î¡¼¥É¤ò¼Â¹Ô¤¹¤ëÁí²ó¿ô¤òÊó¹ð¤·¡¢É½¼¨¤µ¤ì¤ë¼ÂºÝ¤Î»þ´Ö¤È¹Ô¿ô¤Ï1¼Â¹ÔÅö¤¿¤ê¤ÎÊ¿¶Ñ¤Ç¤¹¡£ ¤³¤ì¤Ïɽ¼¨¤µ¤ì¤¿¿äÄꥳ¥¹¥È¤ÈÈæ³Ó¤Ç¤­¤ëÃͤòºîÀ®¤¹¤ë¤³¤È¤Ç¹Ô¤¤¤Þ¤¹¡£ "loops"Ãͤò¤«¤±¤ë¤³¤È¤Ç¡¢¤½¤Î¥Î¡¼¥É¤Ç¼ÂºÝ¤ËÈñ¤ä¤µ¤ì¤¿Áí»þ´Ö¤òÆÀ¤ë¤³¤È¤¬¤Ç¤­¤Þ¤¹¡£

EXPLAIN ANALYZE¤Çɽ¼¨¤µ¤ì¤ëTotal runtime(Áí¼Â¹Ô»þ´Ö)¤Ë¤Ï¡¢·ë²Ì¹Ô¤òÁàºî¤¹¤ë¤¿¤á¤Î»þ´Ö¤Î¾¤Ë¡¢¥¨¥¯¥¼¥­¥å¡¼¥¿¤Îµ¯Æ°¡¢Ää»ß»þ´Ö¤â´Þ¤Þ¤ì¤Þ¤¹¡£ ²òÀϤä½ñ¤­´¹¤¨¡¢·×²èºîÀ®¤Î»þ´Ö¤Ï´Þ¤Þ¤ì¤Þ¤»¤ó¡£ SELECTÌ䤤¹ç¤ï¤»¤Ç¤Ï¡¢Áí¼Â¹Ô»þ´Ö¤ÏºÇ¾å°Ì·×²è¥Î¡¼¥É¤ÇÊó¹ð¤µ¤ì¤ëÁí»þ´Ö¤è¤ê¤âÄ̾ﾯ¤·Â礭¤¯¤Ê¤ê¤Þ¤¹¡£ INSERT¡¢UPDATE¡¢¤ª¤è¤Ó DELETE¥³¥Þ¥ó¥É¤Ç¤Ï¡¢¤½¤ÎÁí¼Â¹Ô»þ´Ö¤Ï¤«¤Ê¤êÂ礭¤¯¤Ê¤ê¤Þ¤¹¡£ ¤½¤³¤Ë¤Ï¡¢·ë²Ì¹Ô¤ò½èÍý¤¹¤ë»þ´Ö¤¬´Þ¤Þ¤ì¤ë¤«¤é¤Ç¤¹¡£ ¤³¤ì¤é¤Î¥³¥Þ¥ó¥É¤Ç¤Ï¡¢ºÇ¾å°Ì·×²è¥Î¡¼¥É¤Î»þ´Ö¤Ï¡¢´ðËÜŪ¤Ë¿·¤·¤¤¹Ô¤ò±é»»¤·¡¢¸Å¤¤¹Ô¤Î¾ì½ê¤ò·èÄꤹ¤ë¤¿¤á¤Î»þ´Ö¤ò¼¨¤·¡¢¤½¤³¤Ë¤ÏÊѹ¹¤ò¹Ô¤¦»þ´Ö¤Ï´Þ¤Þ¤ì¤Þ¤»¤ó¡£

EXPLAIN¤Î·ë²Ì¤ò¡¢»î¸³¤ò¹Ô¤Ã¤¿¤â¤Î°Ê³°¤Î¿äÄê¤Ë»È¤Ã¤Æ¤Ï¤¤¤±¤Ê¤¤¤³¤È¤Ï¡¢¤¤¤¦¤Þ¤Ç¤â¤¢¤ê¤Þ¤»¤ó¡£ Î㤨¤Ð¡¢¾®¤µ¤Ê¥Æ¡¼¥Ö¥ë¤Î·ë²Ì¤Ï¡¢µðÂç¤Ê¥Æ¡¼¥Ö¥ë¤ËŬÍѤǤ­¤ë¤È¤Ï²¾Äê¤Ç¤­¤Þ¤»¤ó¡£ ¥×¥é¥ó¥Ê¤Î¿äÄꥳ¥¹¥È¤ÏÀþ·Á¤Ç¤Ï¤Ê¤¯¡¢¤½¤Î¤¿¤á¡¢¥Æ¡¼¥Ö¥ë¤ÎÂç¾®¤Ë¤è¤Ã¤Æ°Û¤Ê¤ë·×²è¤òÁªÂò¤·¤Þ¤¹¡£ ¶Ëü¤ÊÎã¤Ç¤¹¤¬¡¢¥Æ¡¼¥Ö¥ë¤¬1¥Ç¥£¥¹¥¯¥Ú¡¼¥¸¤·¤«Àê¤á¤Ê¤¤¾ì¹ç¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¤¬»ÈÍѤǤ­¤ë¡¢¤Ç¤­¤Ê¤¤¤Ë´Ø·¸¤Ê¤¯¡¢¤Û¤È¤ó¤É¾ï¤Ë¥·¡¼¥±¥ó¥·¥ã¥ë¥¹¥­¥ã¥ó·×²è¤òÆÀ¤ë¤³¤È¤Ë¤Ê¤ê¤Þ¤¹¡£ ¥×¥é¥ó¥Ê¤Ï¡¢¤É¤Î¤è¤¦¤Ê¾ì¹ç¤Ç¤â¥Æ¡¼¥Ö¥ë¤ò½èÍý¤¹¤ë¤¿¤á¤Ë1¥Ç¥£¥¹¥¯¥Ú¡¼¥¸Æɤ߼è¤ê¤ò¹Ô¤Ê¤¦¤Î¤Ç¡¢¥¤¥ó¥Ç¥Ã¥¯¥¹¤ò»²¾È¤¹¤ë¤¿¤á¤ÎÄɲÃŪ¥Ú¡¼¥¸Æɤ߼è¤ê¤ò¹Ô¤¦²ÁÃͤ¬¤Ê¤¤¤³¤È¤òÃΤäƤ¤¤Þ¤¹¡£