{"id":2854,"date":"2013-06-20T17:41:49","date_gmt":"2013-06-20T08:41:49","guid":{"rendered":"http:\/\/www.techscore.com\/blog\/?p=2854"},"modified":"2018-11-14T16:33:54","modified_gmt":"2018-11-14T07:33:54","slug":"postgresql-index-only-scan-%e5%a5%ae%e9%97%98%e8%a8%98-%e3%81%9d%e3%81%ae5","status":"publish","type":"post","link":"https:\/\/www.techscore.com\/blog\/2013\/06\/20\/postgresql-index-only-scan-%e5%a5%ae%e9%97%98%e8%a8%98-%e3%81%9d%e3%81%ae5\/","title":{"rendered":"PostgreSQL Index Only Scan \u596e\u95d8\u8a18 \u305d\u306e5"},"content":{"rendered":"<p>\u3069\u3046\u3082\u3002\u6751\u4e0a\u3067\u3059\u3002<\/p>\n<p>\u4eca\u56de\u304c\u304d\u3063\u3068\u6700\u7d42\u56de\u306e5\u56de\u76ee\u3067\u3059\u3002<\/p>\n<p>\u3067\u306f\u3001\u6700\u5f8c\u3068\u306a\u308b\u300cIndex Only Scan \u596e\u95d8\u8a18\u300d\u3092\u3002<\/p>\n<h2>Index Only Scan \u306e explain<\/h2>\n<p>\u524d\u56de\u3067\u3082Index Only Scan\u306e\u5b9f\u884c\u8a08\u753b\u3092\u307f\u307e\u3057\u305f\u3002<br \/>\n\u4eca\u56de\u306f\u524d\u56de\u3088\u308a\u8907\u96d1\u306aIndex Only Scan\u306eexplain\u3092\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n<p>\u30c6\u30fc\u30d6\u30eb\u306f\u3053\u3093\u306a\u611f\u3058\u3067\u3059\u3002<\/p>\n<pre class=\"lang:sql decode:true\"># \\d users\r\n                                        Table \"public.users\"\r\n      Column      |            Type             |                     Modifiers\r\n------------------+-----------------------------+----------------------------------------------------\r\n id               | integer                     | not null default nextval('users_id_seq'::regclass)\r\n login            | character varying(255)      | not null\r\n mail_address     | character varying(255)      | not null\r\n family_name      | character varying(255)      | not null\r\n first_name       | character varying(255)      | not null\r\n family_name_kana | character varying(255)      |\r\n first_name_kana  | character varying(255)      |\r\n birthday         | date                        |\r\n gender           | integer                     |\r\n post_number      | character varying(255)      |\r\n prefecture       | character varying(255)      |\r\n city             | character varying(255)      |\r\n address          | character varying(255)      |\r\n building         | character varying(255)      |\r\n telephone        | character varying(255)      |\r\n created_at       | timestamp without time zone |\r\n updated_at       | timestamp without time zone |\r\nIndexes:\r\n    \"users_pkey\" PRIMARY KEY, btree (id)\r\n    \"users_login_idx\" btree (login)\r\n    \"users_name_idx\" btree (family_name, first_name)<\/pre>\n<h3>Primary Key\u3067select<\/h3>\n<pre class=\"lang:sql decode:true\">=# explain select id from users;\r\n                                       QUERY PLAN\r\n-----------------------------------------------------------------------------------------\r\n Index Only Scan using users_pkey on users  (cost=0.00..259699.22 rows=10000000 width=4)<\/pre>\n<h3>\u8907\u5408Index<\/h3>\n<pre class=\"lang:sql decode:true\">=# explain select family_name, first_name from users;\r\n                                          QUERY PLAN\r\n----------------------------------------------------------------------------------------------\r\n Index Only Scan using users_name_idx on users  (cost=0.00..307880.04 rows=10000000 width=12)<\/pre>\n<h3>\u8907\u5408Index\u306e\u7247\u65b9<\/h3>\n<pre class=\"lang:sql decode:true\">=# explain select first_name from users;\r\n                                         QUERY PLAN\r\n---------------------------------------------------------------------------------------------\r\n Index Only Scan using users_name_idx on users  (cost=0.00..307880.04 rows=10000000 width=6)<\/pre>\n<h3>GROUP BY<\/h3>\n<pre class=\"lang:sql decode:true\">=# explain select login from users group by login;\r\n                                             QUERY PLAN\r\n-----------------------------------------------------------------------------------------------------\r\n Group  (cost=0.00..371911.94 rows=10000000 width=19)\r\n   -&gt;  Index Only Scan using users_login_idx on users  (cost=0.00..346911.94 rows=10000000 width=19)<\/pre>\n<h3>\u526f\u554f\u3044\u5408\u308f\u305b<\/h3>\n<pre class=\"lang:sql decode:true\">=# explain select family_name from users where id in (select id from users);\r\n                                          QUERY PLAN\r\n-----------------------------------------------------------------------------------------------\r\n Merge Semi Join  (cost=0.00..954471.44 rows=10000000 width=6)\r\n   Merge Cond: (public.users.id = public.users.id)\r\n   -&gt;  Index Scan using users_pkey on users  (cost=0.00..544772.22 rows=10000000 width=10)\r\n   -&gt;  Index Only Scan using users_pkey on users  (cost=0.00..259699.22 rows=10000000 width=4)<\/pre>\n<p>\u3059\u3079\u3066\u306b\u3064\u3044\u3066Index Only Scan\u304c\u4f7f\u7528\u3055\u308c\u307e\u3057\u305f\u306d\u3002<\/p>\n<p><strong>GOOD<\/strong><\/p>\n<h2>Index Only Scan \u304c\u52b9\u304b\u306a\u3044\uff01\uff1f<\/h2>\n<p>users\u30c6\u30fc\u30d6\u30eb\u306e\u9805\u76ee\u3092\u6e1b\u3089\u3057\u305fsimple_users\u3092\u4f5c\u6210\u3057\u307e\u3057\u305f\u3002<br \/>\n\u30c7\u30fc\u30bf\u4ef6\u6570\u306fusers\u3068\u540c\u30581000\u4e07\u4ef6\u3067\u3059\u3002<\/p>\n<pre class=\"lang:sql decode:true\">=# \\d simple_users;\r\n                                      Table \"public.simple_users\"\r\n    Column    |            Type             |                         Modifiers\r\n--------------+-----------------------------+-----------------------------------------------------------\r\n id           | integer                     | not null default nextval('simple_users_id_seq'::regclass)\r\n login        | character varying(255)      | not null\r\n mail_address | character varying(255)      | not null\r\n family_name  | character varying(255)      | not null\r\n first_name   | character varying(255)      | not null\r\n created_at   | timestamp without time zone |\r\n updated_at   | timestamp without time zone |\r\nIndexes:\r\n    \"simple_users_pkey\" PRIMARY KEY, btree (id)\r\n    \"simple_users_login_idx\" btree (login)\r\n    \"simple_users_name_idx\" btree (family_name, first_name)<\/pre>\n<p>\u3053\u306e\u30c6\u30fc\u30d6\u30eb\u306b\u5bfe\u3057\u3066\u3001Explain\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n<pre class=\"lang:sql decode:true\">=# explain select id from simple_users;\r\n                              QUERY PLAN\r\n-----------------------------------------------------------------------\r\n Seq Scan on simple_users  (cost=0.00..246700.38 rows=9999838 width=4)\r\n\r\n=# explain select login from simple_users;\r\n                               QUERY PLAN\r\n------------------------------------------------------------------------\r\n Seq Scan on simple_users  (cost=0.00..246700.38 rows=9999838 width=19)\r\n\r\n=# explain select family_name, first_name from simple_users;\r\n                               QUERY PLAN\r\n------------------------------------------------------------------------\r\n Seq Scan on simple_users  (cost=0.00..246700.38 rows=9999838 width=12)<\/pre>\n<p>\u3042\u308c\uff1f\uff1f\uff1f<br \/>\n\u3059\u3079\u3066Seq Scan\u306b\uff01\uff1f<\/p>\n<p>vacuum analyze\u306e\u3057\u5fd8\u308c\u3067\u306f\u3042\u308a\u307e\u305b\u3093\u3002<\/p>\n<p>Seq Scan\u3092off\u306b\u3057\u3066Explain\u3057\u3066\u307f\u308b\u3068<\/p>\n<pre class=\"lang:sql decode:true\">=# set enable_seqscan = off;\r\nSET\r\n=# explain select login from simple_users;\r\n                                                 QUERY PLAN\r\n------------------------------------------------------------------------------------------------------------\r\n Index Only Scan using simple_users_login_idx on simple_users  (cost=0.00..346909.51 rows=9999838 width=19)<\/pre>\n<p>Index Only Scan\u304c\u4f7f\u308f\u308c\u308b\u3088\u3046\u3067\u3059\u3002<\/p>\n<p>\u3058\u3083\u3042\u3001\u30aa\u30d7\u30c6\u30a3\u30de\u30a4\u30b6\u30fc\u304cIndex Only Scan \u3088\u308a Seq Scan\u306e\u65b9\u304c\u826f\u3044\u3068\u5224\u65ad\u3057\u305f\u306e\u304b\u306a\uff1f<br \/>\n\u30b3\u30b9\u30c8\u306fSeq Scan\u306e\u65b9\u304c\u3088\u3055\u305d\u3046\u306a\u306e\u3067\u3001<\/p>\n<p>explain analyze\u3067\u901f\u5ea6\u3092\u898b\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n<pre class=\"lang:sql decode:true\">=# explain analyze select login from simple_users;\r\n                                                         QUERY PLAN\r\n----------------------------------------------------------------------------------------------------------------------------\r\n Seq Scan on simple_users  (cost=0.00..246700.38 rows=9999838 width=19) (actual time=6.561..5064.454 rows=10000000 loops=1)\r\n Total runtime: 5885.929 ms\r\n\r\n=# set enable_seqscan = off;\r\n\r\n=# explain analyze select login from simple_users;\r\n                                                         QUERY PLAN\r\n----------------------------------------------------------------------------------------------------------------------------\r\n Index Only Scan using simple_users_login_idx on simple_users  (cost=0.00..346909.51 rows=9999838 width=19) (actual time=0.065..4020.799 rows=10000000 loops=1)\r\n   Heap Fetches: 0\r\n Total runtime: 4869.963 ms<\/pre>\n<p>\u78ba\u304b\u306b\u305d\u308c\u307b\u3069\u901f\u5ea6\u5dee\u306f\u306a\u3044\u304b\u306a\uff1f\uff1f<\/p>\n<p>\u3067\u3082select count(*)\u3067\u898b\u308b\u3068<\/p>\n<pre class=\"lang:sql decode:true\">=# explain analyze select count(*) from simple_users;\r\n                                                           QUERY PLAN\r\n---------------------------------------------------------------------------------------------------------------------------------\r\n Aggregate  (cost=271699.97..271699.98 rows=1 width=0) (actual time=5827.386..5827.386 rows=1 loops=1)\r\n   -&gt;  Seq Scan on simple_users  (cost=0.00..246700.38 rows=9999838 width=0) (actual time=2.346..4504.506 rows=10000000 loops=1)\r\n Total runtime: 5827.434 ms\r\n\r\n=# set enable_seqscan = off;\r\n\r\n=# explain analyze select count(*) from simple_users;\r\n                                                         QUERY PLAN\r\n---------------------------------------------------------------------------------------------------------------------------\r\n Aggregate  (cost=284692.38..284692.39 rows=1 width=0) (actual time=3110.858..3110.858 rows=1 loops=1)\r\n   -&gt;  Index Only Scan using simple_users_pkey on simple_users  (cost=0.00..259692.79 rows=9999838 width=0) (actual time=0.038..1876.960 rows=10000000 loops=1)\r\n         Heap Fetches: 0\r\n Total runtime: 3110.943 ms<\/pre>\n<p>\u305d\u3053\u305d\u3053\u901f\u5ea6\u5dee\u306f\u3042\u308b\u3002\u3002\u3002<\/p>\n<p>\u3068\u3044\u3046\u3053\u3068\u306f\u5217\u306e\u30b5\u30a4\u30ba\u306b\u3088\u3063\u3066\u30b3\u30b9\u30c8\u8a08\u7b97\u304c\u3053\u3068\u306a\u308a\u3001Index Only Scan\u304c\u4f7f\u7528\u3055\u308c\u305f\u308a\u3001\u3055\u308c\u306a\u304b\u3063\u305f\u308a\u3059\u308b\uff1f\uff1f\uff1f<\/p>\n<p>\u3061\u306a\u307f\u306b\u300cHeap Fetches\u300d\u306f\u30c6\u30fc\u30d6\u30eb\u306b\u30a2\u30af\u30bb\u30b9\u3057\u305f\u4ef6\u6570\u3067\u3059\u3002<br \/>\n\u4eca\u306f0\u4ef6\u306a\u306e\u3067\u3001\u30c6\u30fc\u30d6\u30eb\u30a2\u30af\u30bb\u30b9\u306a\u3057\u3067\u3059\u3002<\/p>\n<pre class=\"lang:sql decode:true\">=# update simple_users set mail_address = 'hoge_var@sample.ne.jp' where id in (select id from simple_users limit 100);\r\nUPDATE 100\r\n\r\n=# explain analyze select count(*) from simple_users;\r\n                                                           QUERY PLAN\r\n---------------------------------------------------------------------------------------------------------------------------------\r\n Aggregate  (cost=284704.76..284704.77 rows=1 width=0) (actual time=3049.392..3049.392 rows=1 loops=1)\r\n   -&gt;  Index Only Scan using simple_users_pkey on simple_users  (cost=0.00..259704.83 rows=9999974 width=0) (actual time=0.024..1841.731 rows=10000000 loops=1)\r\n         Heap Fetches: 275\r\n Total runtime: 3049.433 ms<\/pre>\n<p>\u3068\u306a\u308a\u307e\u3059\u3002<\/p>\n<pre class=\"lang:sql decode:true\">=# vacuum analyze simple_users ;\r\nVACUUM\r\n=# explain analyze select count(*) from simple_users;\r\n                                                                           QUERY PLAN\r\n----------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n Aggregate  (cost=284694.25..284694.26 rows=1 width=0) (actual time=3231.098..3231.098 rows=1 loops=1)\r\n   -&gt;  Index Only Scan using simple_users_pkey on simple_users  (cost=0.00..259694.96 rows=9999716 width=0) (actual time=0.026..1975.628 rows=10000000 loops=1)\r\n         Heap Fetches: 0\r\n Total runtime: 3231.187 ms<\/pre>\n<p>vacuum analyze\u3059\u308b\u30680\u4ef6\u306b\u306a\u308a\u307e\u3059\u3002<\/p>\n<h2>\u4ef6\u6570\u3092\u6e1b\u3089\u3059\u3068\u3002\u3002\u3002<\/h2>\n<p>\u4e0d\u601d\u8b70\u306a\u3053\u3068\u306b\u4ef6\u6570\u3092\u6e1b\u3089\u3059\u3068Index Only Scan\u304c\u4f7f\u7528\u3055\u308c\u307e\u3059\u3002<\/p>\n<pre class=\"lang:sql decode:true\">=# explain select count(*) from simple_users where id &lt; 5000000;\r\n                                                 QUERY PLAN\r\n------------------------------------------------------------------------------------------------------------\r\n Aggregate  (cost=155359.00..155359.01 rows=1 width=0)\r\n   -&gt;  Index Only Scan using simple_users_pkey on simple_users  (cost=0.00..142818.53 rows=5016189 width=0)\r\n         Index Cond: (id &lt; 5000000)<\/pre>\n<p>\u3067\u3082\u4ef6\u6570\u3092\u5897\u3084\u3059\u3068\u3002\u3002\u3002<\/p>\n<pre class=\"lang:sql decode:true\">=# explain select count(*) from simple_users where id &lt; 9800000;\r\n                                 QUERY PLAN\r\n-----------------------------------------------------------------------------\r\n Aggregate  (cost=296188.20..296188.21 rows=1 width=0)\r\n   -&gt;  Seq Scan on simple_users  (cost=0.00..271700.45 rows=9795098 width=0)\r\n         Filter: (id &lt; 9800000)<\/pre>\n<p>Index Only Scan\u304c\u52b9\u304b\u306a\u3044\u3002\u3002\u3002<br \/>\n\u3063\u3066\u304b\u3001Index \u304c\u52b9\u304b\u306a\u3044\uff1f\uff1f<br \/>\n\u4ef6\u6570\u304c\u591a\u3044\u6642\u307b\u3069\u52b9\u3044\u3066\u6b32\u3057\u3044\u3093\u3067\u3059\u304c\u3002\u3002\u3002<\/p>\n<h2>\u596e\u95d8\u8a18\u306f\u7d9a\u304f<\/h2>\n<p>\u3068\u3044\u3046\u3053\u3068\u3067\u3001Index Only Scan\u3092\u4f7f\u3046\u306b\u306f<\/p>\n<ul>\n<li>\u5bfe\u8c61\u30c6\u30fc\u30d6\u30eb\u306bIndex\u3092\u4f5c\u6210\u3059\u308b<\/li>\n<li>vacuum analyze\u3092\u3059\u308b<\/li>\n<li>Index\u30ad\u30fc\u306e\u5217\u3060\u3051\u3092\u53c2\u7167\u3059\u308bselect\u3092\u3059\u308b<\/li>\n<li>\u305d\u308c\u306a\u308a\u306b\u5217\u306e\u304c\u3042\u308b\u30c6\u30fc\u30d6\u30eb\u3058\u3083\u306a\u3044\u3068\u52b9\u304d\u307e\u305b\u3093(?)<\/li>\n<\/ul>\n<p>\u3067\u3059\u3002<\/p>\n<p>\u6700\u5f8c\u306f\u8b0e\u3002\u3002\u3002<br \/>\n\u4e0a\u8a18\u4ee5\u5916\u306b\u3082\u306e\u3044\u308d\u3044\u308d\u8abf\u3079\u307e\u3057\u305f\u304c\u3001\u7d50\u5c40\u8b0e\u3002\u3002\u3002<\/p>\n<p>\u304a\u305d\u3089\u304f\u30aa\u30d7\u30c6\u30a3\u30de\u30a4\u30b6\u30fc\u306e\u4ed5\u69d8\u304c\u305d\u3046\u306a\u3063\u3066\u3044\u308b\u3093\u3067\u3057\u3087\u3046\u3002<br \/>\n\u6b21\u306e\u30d0\u30fc\u30b8\u30e7\u30f3\u30a2\u30c3\u30d7\u306f\u304d\u3063\u3068\u89e3\u6d88\u3055\u308c\u308b\u3093\u3067\u3057\u3087\u3046\u3002<\/p>\n<p><strong>\u305d\u306e\u6642\u306b\u3001\u3053\u306e\u596e\u95d8\u8a18\u3092\u518d\u3073\u59cb\u3081\u307e\u3059\uff01\uff01<\/strong><\/p>\n<p>\u6700\u5f8c\u306b\u306a\u308a\u307e\u3057\u305f\u304c\u3001\u3053\u306e\u9023\u8f09\u3092\u8aad\u3093\u3067\u3044\u305f\u3060\u3044\u3066\u3042\u308a\u304c\u3068\u3046\u3054\u3056\u3044\u307e\u3059\u3002<br \/>\n\u6b21\u3082\u9023\u8f09\u7cfb\u306e\u30cd\u30bf\u304c\u3042\u308c\u3070\u3084\u3089\u305b\u3066\u3044\u305f\u3060\u304d\u307e\u3059\u304c\u3001\u3057\u3070\u3089\u304f\u306f\u5358\u767a\u7cfb\u306e\u30cd\u30bf\u3092\u3059\u308b\u3064\u3082\u308a\u3067\u3059\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u3069\u3046\u3082\u3002\u6751\u4e0a\u3067\u3059\u3002<\/p>\n<p>\u4eca\u56de\u304c\u304d\u3063\u3068\u6700\u7d42\u56de\u306e5\u56de\u76ee\u3067\u3059\u3002<\/p>\n<p>\u3067\u306f\u3001\u6700\u5f8c\u3068\u306a\u308b\u300cIndex Only Scan \u596e\u95d8\u8a18\u300d\u3092\u3002<br \/>\nIndex Only Scan \u306e explain<br \/>\n\u524d\u56de\u3067\u3082Index Only Scan\u306e\u5b9f\u884c\u8a08\u753b\u3092\u307f\u307e\u3057\u305f\u3002<br \/><a href=\"https:\/\/www.techscore.com\/blog\/2013\/06\/20\/postgresql-index-only-scan-%e5%a5%ae%e9%97%98%e8%a8%98-%e3%81%9d%e3%81%ae5\/\">\u7d9a\u304d\u3092\u8aad\u3080...<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[97],"tags":[206],"_links":{"self":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/2854"}],"collection":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/comments?post=2854"}],"version-history":[{"count":1,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/2854\/revisions"}],"predecessor-version":[{"id":17923,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/posts\/2854\/revisions\/17923"}],"wp:attachment":[{"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/media?parent=2854"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/categories?post=2854"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.techscore.com\/blog\/wp-json\/wp\/v2\/tags?post=2854"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}