10.1. EXPLAIN¤ÎÍøÍÑ

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

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

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

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

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

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

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

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

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

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

regression=# 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 ¾ò·ï¤ò²Ã¤¨¤ÆÌ䤤¹ç¤ï¤»¤òÊѹ¹¤·¤Æ¤ß¤Þ¤¹¡£

regression=# 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 ¤ÇÀ¸À®¤µ¤ì¤ëÅý·×¾ðÊó¤Ï¡¢¥Æ¡¼¥Ö¥ë¤Î¥é¥ó¥À¥à¤ÊɸËܤ«¤é¼è¤ê½Ð¤µ¤ì¤ë¤«¤é¤Ç¤¹¡£

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

regression=# 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 ¤ÄÄɲä·¤Þ¤¹¡£

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND
regression-# 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¤Ä¤Î¥Æ¡¼¥Ö¥ë¤ò·ë¹ç¤·¤Æ¤ß¤Þ¤·¤ç¤¦¡£

regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# 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 ¥Õ¥é¥°¤ò»ÈÍѤ·¤Æ¡¢¥×¥é¥ó¥Ê¤¬ºÇ¤âÎɤ¤¤È¹Í¤¨¤Æ¤¤¤ëÀïά¤ò¶¯À©Åª¤Ë̵»ë¤µ¤»¤ëÊýË¡¤Ë¤è¤ê¡¢°Û¤Ê¤Ã¤¿·×²è¤ò´Ñ»¡¤¹¤ë¤³¤È¤¬¤Ç¤­¤Þ¤¹¡£ ¡ÊÈó¾ï¤Ë¸¶»ÏŪ¤Ê¥Ä¡¼¥ë¤Ç¤¹¤¬¡¢ÍøÍѲÁÃͤ¬¤¢¤ê¤Þ¤¹¡£Section 10.3¤â»²¾È¤¯¤À¤µ¤¤¡£)

regression=# SET enable_nestloop = off;
SET
regression=# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50
regression-# 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 »þ´Ö¤¬´Þ¤Þ¤ì¤Æ¤¤¤Þ¤¹¡£ ¤·¤«¤·¡¢¤³¤Î·×²è·¿¤Ç¤Ï¥Ï¥Ã¥·¥å¥Æ¡¼¥Ö¥ë¤ÎÀßÄê¤Ï°ìÅ٤ΤߤΤ¿¤á¡¢10000 x 179.33 ʬ¤Î¥³¥¹¥È¤Ï¤«¤«¤é¤Ê¤¤¤³¤È¤ËÃí°Õ¤·¤Æ²¼¤µ¤¤¡£

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

regression=# EXPLAIN ANALYZE
regression-# SELECT * FROM tenk1 t1, tenk2 t2
regression-# 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.18..29.82 rows=50 loops=1)
   ->  Index Scan using tenk1_unique1 on tenk1 t1
                  (cost=0.00..179.33 rows=49 width=148)
                                 (actual time=0.63..8.91 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.29..0.32 rows=1 loops=50)
         Index Cond: ("outer".unique2 = t2.unique2)
Total runtime: 31.60 msec

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

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

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

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