ANALYZE

Ãí¼á

ANALYZE¤òÄê´üŪ¤Ë¡¢¤â¤·¤¯¤Ï¡¢¥Æ¡¼¥Ö¥ë¤ÎÆâÍƤËÂ礭¤ÊÊѹ¹¤¬¤¢¤Ã¤¿¸å¤Ë¹Ô¤Ê¤¦¤³¤È¤ò¿ä¾©¤·¤Þ¤¹¡£Àµ³Î¤ÊÅý·×¾ðÊó¤Ë¤è¤ê¡¢¥×¥é¥ó¥Ê¤¬ºÇ¤âŬÀÚ¤ÊÌ䤤¹ç¤ï¤»·×²è¤òÁªÂò¤Ç¤­¤ë¤è¤¦¤Ë¤Ê¤ê¤Þ¤¹¡£½¾¤Ã¤Æ¡¢Ì䤤¹ç¤ï¤»½èÍý¤Î®ÅÙ¤¬¸þ¾å¤·¤Þ¤¹¡£VACUUM ¤È ANALYZE ¤Î¼Â¹Ô¤Ë¤Ä¤¤¤ÆÎɤ¯»ÈÍѤµ¤ì¤ëÊýË¡¤Ï¡¢1Æü1²ó¡¢¤¢¤Þ¤ê»ÈÍѤµ¤ì¤Æ¤¤¤Ê¤¤»þ´ÖÂӤ˼¹Ԥ¹¤ë¤³¤È¤Ç¤¹¡£

VACUUM FULL ¤È¤Ï°Û¤Ê¤ê¡¢ANALYZE ¤Ï¡¢ÂоݤȤ¹¤ë¥Æ¡¼¥Ö¥ë¤ÎÆɤ߼è¤ê¥í¥Ã¥¯¤Î¤ß¤òɬÍפȤ·¤Þ¤¹¡£¤Ç¤¹¤Î¤Ç¡¢¤½¤Î¥Æ¡¼¥Ö¥ë¤ËÂФ¹¤ë¾¤ÎÁàºî¤ÈʹԤ·¤Æ¼Â¹Ô¤¹¤ë¤³¤È¤¬¤Ç¤­¤Þ¤¹¡£

µðÂç¤Ê¥Æ¡¼¥Ö¥ë¤Ç¤Ï¡¢ANALYZE ¤Ï¡¢Á´¤Æ¤Î¹Ô¤ò¸¡ºº¤¹¤ë¤Î¤Ç¤Ï¤Ê¤¯¥Æ¡¼¥Ö¥ë¤ÎÃ椫¤é¥é¥ó¥À¥à¤Ë¥µ¥ó¥×¥ë¤ò¼è¤ê½Ð¤·¤Þ¤¹¡£¤³¤ì¤Ë¤è¤ê¡¢Èó¾ï¤ËµðÂç¤Ê¥Æ¡¼¥Ö¥ë¤Î²òÀϤâû¤¤»þ´Ö¤Ç½ª¤ê¤Þ¤¹¡£¤·¤«¤·¡¢¤½¤ÎÅý·×¾ðÊó¤Ï¤ª¤ª¤è¤½¤Î¤â¤Î¤Ç¤·¤«¤Ê¤¯¡¢¥Æ¡¼¥Ö¥ë¤ÎÆâÍƤËÊѹ¹¤¬¤Ê¤¯¤Æ¤â ANALYZE ¤ò¼Â¹Ô¤¹¤ëÅÙ¤ËÊѹ¹¤µ¤ì¤Æ¤·¤Þ¤¦¤³¤È¤ËÃí°Õ¤·¤Æ²¼¤µ¤¤¡£¤³¤ì¤Ë¤è¤ê¡¢ EXPLAIN ¤¬É½¼¨¤¹¤ë¡¢¥×¥é¥ó¥Ê¤Î¿äÄꥳ¥¹¥È¤¬Â¿¾¯°Û¤Ê¤ë¤³¤È¤Ë¤Ê¤ê¤Þ¤¹¡£

Ä̾½¸·×¤µ¤ì¤¿Åý·×¾ðÊó¤Ï³ÆÎó¤Îŵ·¿Åª¤ÊÃͤȳÆÎó¤Î¥Ç¡¼¥¿Ê¬ÉۤγµÍפò¼¨¤¹ÅÙ¿ôʬÉۤǤ¹¡£ANALYZE ¤¬¤¢¤Þ¤ê°ÕÌ£¤¬¤Ê¤¤¤È¤ß¤Ê¤·¤¿¾ì¹ç (Î㤨¤Ð¡¢°ì°ÕÀ­À©Ìó¥­¡¼¤ÎÎó¤Ç¤Ï¡¢Åµ·¿Åª¤ÊÃͤϤ¢¤ê¤Þ¤»¤ó¡£) ¤äÎó¤Î¥Ç¡¼¥¿·¿¤¬Å¬Àڤʱ黻»Ò¤ò¥µ¥Ý¡¼¥È¤·¤Æ¤¤¤Ê¤¤¾ì¹ç¤Ï¡¢ÊÒÊý¤â¤·¤¯¤ÏξÊý¤È¤â¾Êά¤µ¤ì¤Æ¤·¤Þ¤¦²ÄǽÀ­¤¬¤¢¤ê¤Þ¤¹¡£¥æ¡¼¥¶¥¬¥¤¥É ¤Ë¤Ï¡¢¤³¤ÎÅý·×¾ðÊó¤Ë¤Ä¤¤¤Æ¤Î¤â¤Ã¤È¿¤¯¤Î¾ðÊ󤬵­ºÜ¤µ¤ì¤Æ¤¤¤Þ¤¹¡£

ALTER TABLE ALTER COLUMN SET STATISTICS ¤ò»ÈÍѤ·¤ÆÎóñ°Ì¤ÎÅý·×ÂоݤòÄ´À°¤¹¤ë¤³¤È¤Ç¡¢²òÀϤÎÈϰϤòÀ©¸æ¤¹¤ë¤³¤È¤¬¤Ç¤­¤Þ¤¹¡£( ALTER TABLE ¤ò»²¾È¤·¤Æ²¼¤µ¤¤¡£)¤³¤ÎÂоÝÃͤÏŵ·¿Åª¤ÊÃÍ°ìÍ÷¤Î¥¨¥ó¥È¥ê¿ô¤ÎºÇÂçÃͤÈÅÙ¿ôʬÉۤΥӥó¤ÎºÇÂç¿ô ¤òÀßÄꤷ¤Þ¤¹¡£¥Ç¥Õ¥©¥ë¥È¤ÎÂоÝÃÍ¤Ï 10 ¤Ç¤¹¡£¤·¤«¤·¡¢¥×¥é¥ó¥Ê¤Î¿äÄêÀºÅÙ¤ÈANALYZE ¤Î½èÍý»þ´Ö¤È pg_statistic ¤ÎÀê¤á¤ëÍÆÎ̤ȤΥȥ졼¥É¥ª¥Õ¤Ë¤è¤Ã¤ÆÁý¸º¤µ¤»¤ë¤³¤È¤¬¤Ç¤­¤Þ¤¹¡£ÆäËÂоÝÃͤò 0 ¤ËÀßÄꤹ¤ë¤È¡¢¤½¤ÎÎó¤Ë´Ø¤¹¤ëÅý·×¾ðÊó¤Î½¸·×¤Ï̵¸ú¤Ë¤Ê¤ê¤Þ¤¹¡£¤½¤ÎÎ󤬷褷¤Æ Ì䤤¹ç¤ï¤»¤Î WHERE¡¢GROUP BY¡¢ORDER BY ¶ç¤Ë»ÈÍѤµ¤ì¤Ê¤¤¾ì¹ç¤Ï¤³¤ì¤ÏÍ­ÍѤǤ¹¡£¥×¥é¥ó¥Ê¤Ï¤½¤Î¤è¤¦¤ÊÎó¤ÎÅý·×¾ðÊó¤ò»ÈÍѤ·¤Ê¤¤¤«¤é¤Ç¤¹¡£

²òÀϤµ¤ì¤ëÎóÆâ¤ÎÅý·×¾ðÊóÂоÝÃͤκÇÂçÃͤˤè¤Ã¤Æ¡¢Åý·×¾ðÊó¤òºîÀ®¤¹¤ë¤¿¤á¤ËÃê½Ð¤¹¤ë¥Æ¡¼¥Ö¥ë¤Î¹Ô¤Î¿ô¤ò·èÄꤷ¤Þ¤¹¡£¤½¤ÎÂоݤòÁý²Ã¤µ¤»¤ë¤³¤È¤Ï¡¢ÈæÎãŪ¤Ë¡¢ ANALYZE ¤ËɬÍפȤµ¤ì¤ë»þ´Ö¤ÈÍÆÎ̤òÁý²Ã¤µ¤»¤Þ¤¹¡£

SQL92

SQL92 ¤Ë¤Ï ANALYZE ʸ¤Ï¤¢¤ê¤Þ¤»¤ó¡£