2019年07月

WHERE句でPOVをいくつかに絞る場合など、多くの場合、ORよりINの方が速い

「WHERE句でPOVをいくつかに絞る場合など、多くの場合、ORよりINの方が速いです」とユーザーさんからコメントを頂いたので、なんでそうなるのかな?と思いましたので、EXPLAINで実行計画を確認してみました。

まず、たくさんのPOVをORで指定した場合の実行計画は以下のとおりです。
ap2=# EXPLAIN SELECT '<img  src="http://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'ps.jpg">' AS 画像 ,'' || gamelist.gamename || '' AS タイトル ,gamelist.sellday as 発売日 ,CASE WHEN brandlist.url IS NOT NULL THEN '' || brandlist.brandname || '' ELSE brandlist.brandname || ' (解散)' END AS ブランド ,gamelist.median as 中央値 ,gamelist.count2 as データ数 ,(CASE WHEN gamelist.dmm_subsc IS NOT NULL THEN '有' END) AS DMMプレミアム ,SUM(CASE WHEN povgroups.pov=33 THEN 1 ELSE 0 END) AS 萌ゲー ,SUM(CASE WHEN povgroups.pov=10 THEN 1 ELSE 0 END) AS コメディ ,SUM(CASE WHEN povgroups.pov=57 THEN 1 ELSE 0 END) AS シナリオいい ,SUM(CASE WHEN povgroups.pov=193 THEN 1 ELSE 0 END) AS 主人公強い ,SUM(CASE WHEN povgroups.pov=50 THEN 1 ELSE 0 END) AS ロリ ,SUM(CASE WHEN povgroups.pov=314 THEN 1 ELSE 0 END) AS 戦う女の子 ,SUM(CASE WHEN povgroups.pov=369 THEN 1 ELSE 0 END) AS 女装 ,SUM(CASE WHEN povgroups.pov=379 THEN 1 ELSE 0 END) AS 主人公モテモテ ,SUM(CASE WHEN povgroups.pov=1 THEN 1 ELSE 0 END) AS おかず ,SUM(CASE WHEN povgroups.pov=13 THEN 1 ELSE 0 END) AS 主人公素敵 ,SUM(CASE WHEN povgroups.pov=216 THEN 1 ELSE 0 END) AS アナルSEX ,SUM(CASE WHEN povgroups.pov=250 THEN 1 ELSE 0 END) AS 陵辱レイプ ,SUM(CASE WHEN povgroups.pov=124 THEN 1 ELSE 0 END) AS 女の子に襲 ,SUM(CASE WHEN povgroups.pov=3 THEN 1 ELSE 0 END) AS ゲーム性有 ,SUM(CASE WHEN povgroups.pov=34 THEN 1 ELSE 0 END) AS キャラ取巻く世界観 ,SUM(CASE WHEN povgroups.pov=235 THEN 1 ELSE 0 END) AS 奇人変人 ,SUM(CASE WHEN povgroups.pov=72 THEN 1 ELSE 0 END) AS 主人公ダメ ,SUM(CASE WHEN povgroups.pov=528 THEN 1 ELSE 0 END) AS 個別ルートダメ FROM (gamelist INNER JOIN brandlist ON gamelist.brandname = brandlist.id) INNER JOIN povgroups ON gamelist.ID=povgroups.game WHERE gamelist.median >= 70 AND ((gamelist.count2 >=80 AND gamelist.sellday >= '2008-01-01') OR (gamelist.count2 >=40 AND gamelist.sellday >= CURRENT_DATE - interval '6 month')) AND (povgroups.pov=33 OR povgroups.pov=10 OR povgroups.pov=57 OR povgroups.pov=193 OR povgroups.pov=50 OR povgroups.pov=314 OR povgroups.pov=369 OR povgroups.pov=379 OR povgroups.pov=1 OR povgroups.pov=13 OR povgroups.pov=216 OR povgroups.pov=250 OR povgroups.pov=124 OR povgroups.pov=3 OR povgroups.pov=34 OR povgroups.pov=235 OR povgroups.pov=72 OR povgroups.pov=528) GROUP BY gamelist.sellday,gamelist.count2,gamelist.median,gamelist.dmm,gamelist.id,brandlist.url,brandlist.brandname,gamelist.dmm_subsc ORDER BY gamelist.sellday DESC,gamelist.count2 DESC;

                    QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
 Sort  (cost=65952.58..65973.13 rows=8217 width=355)
   Sort Key: gamelist.sellday DESC, gamelist.count2 DESC
   ->  GroupAggregate  (cost=64206.29..65418.30 rows=8217 width=355)
         Group Key: gamelist.id, brandlist.url, brandlist.brandname
         ->  Sort  (cost=64206.29..64226.83 rows=8217 width=126)
               Sort Key: gamelist.id, brandlist.url, brandlist.brandname
               ->  Hash Join  (cost=11426.17..63672.01 rows=8217 width=126)
                     Hash Cond: (povgroups.game = gamelist.id)
                     ->  Bitmap Heap Scan on povgroups  (cost=9403.99..60155.36 rows=376612 width=8)
                           Recheck Cond: ((pov = 33) OR (pov = 10) OR (pov = 57) OR (pov = 193) OR (pov = 50) OR (pov = 314)
 OR (pov = 369) OR (pov = 379) OR (pov = 1) OR (pov = 13) OR (pov = 216) OR (pov = 250) OR (pov = 124) OR (pov = 3) OR (pov
= 34) OR (pov = 235) OR (pov = 72) OR (pov = 528))
                           ->  BitmapOr  (cost=9403.99..9403.99 rows=408734 width=0)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..344.66 rows=18164 width=0)
                                       Index Cond: (pov = 33)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..391.88 rows=20726 width=0)
                                       Index Cond: (pov = 10)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..1231.64 rows=65495 width=0)
                                       Index Cond: (pov = 57)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..386.18 rows=20500 width=0)
                                       Index Cond: (pov = 193)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..357.75 rows=18842 width=0)
                                       Index Cond: (pov = 50)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..351.49 rows=18541 width=0)
                                       Index Cond: (pov = 314)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..106.05 rows=5549 width=0)
                                       Index Cond: (pov = 369)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..439.66 rows=23364 width=0)
                                       Index Cond: (pov = 379)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..532.97 rows=28339 width=0)
                                       Index Cond: (pov = 1)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..785.58 rows=41754 width=0)
                                       Index Cond: (pov = 13)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..106.05 rows=5549 width=0)
                                       Index Cond: (pov = 216)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..292.88 rows=15526 width=0)
                                       Index Cond: (pov = 250)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..106.05 rows=5549 width=0)
                                       Index Cond: (pov = 124)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..106.05 rows=5549 width=0)
                                       Index Cond: (pov = 3)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..1071.77 rows=56979 width=0)
                                       Index Cond: (pov = 34)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..632.53 rows=33614 width=0)
                                       Index Cond: (pov = 235)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..360.01 rows=19144 width=0)
                                       Index Cond: (pov = 72)
                                 ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..106.05 rows=5549 width=0)
                                       Index Cond: (pov = 528)
                     ->  Hash  (cost=2015.70..2015.70 rows=518 width=122)
                           ->  Hash Join  (cost=473.47..2015.70 rows=518 width=122)
                                 Hash Cond: (gamelist.brandname = brandlist.id)
                                 ->  Bitmap Heap Scan on gamelist  (cost=262.19..1797.30 rows=518 width=81)
                                       Recheck Cond: (median >= 70)
                                       Filter: (((count2 >= 80) AND (sellday >= '2008-01-01'::date)) OR ((count2 >= 40) AND
(sellday >= (('now'::cstring)::date - '6 mons'::interval))))
                                       ->  Bitmap Index Scan on tokuten_index  (cost=0.00..262.06 rows=8237 width=0)
                                             Index Cond: (median >= 70)
                                 ->  Hash  (cost=152.79..152.79 rows=4679 width=49)
                                       ->  Seq Scan on brandlist  (cost=0.00..152.79 rows=4679 width=49)
(57 行)
pov1つ1つにBitmap Index Scan on povgroups_povが動いています。
->  BitmapOr  (cost=9403.99..9403.99 rows=408734 width=0)
  ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..344.66 rows=18164 width=0)
    Index Cond: (pov = 33)
  ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..391.88 rows=20726 width=0)
    Index Cond: (pov = 10)
以下略

一方、たくさんのPOVをINで指定した場合の実行計画は以下のとおりです。
ap2=# EXPLAIN SELECT '<img src="http://pics.dmm.co.jp/digital/pcgame/' || gamelist.dmm || '/' || gamelist.dmm || 'ps.jpg">' AS 画像 ,'' || gamelist.gamename || '' AS タイトル ,gamelist.sellday as 発売日 ,CASE WHEN brandlist.url IS NOT NULL THEN '' || brandlist.brandname || '' ELSE brandlist.brandname || ' (解散)' END AS ブランド ,gamelist.median as 中央値 ,gamelist.count2 as データ数 ,(CASE WHEN gamelist.dmm_subsc IS NOT NULL THEN '有' END) AS DMMプレミアム ,SUM(CASE WHEN povgroups.pov=33 THEN 1 ELSE 0 END) AS 萌ゲー ,SUM(CASE WHEN povgroups.pov=10 THEN 1 ELSE 0 END) AS コメディ ,SUM(CASE WHEN povgroups.pov=57 THEN 1 ELSE 0 END) AS シナリオいい ,SUM(CASE WHEN povgroups.pov=193 THEN 1 ELSE 0 END) AS 主人公強い ,SUM(CASE WHEN povgroups.pov=50 THEN 1 ELSE 0 END) AS ロリ ,SUM(CASE WHEN povgroups.pov=314 THEN 1 ELSE 0 END) AS 戦う女の子 ,SUM(CASE WHEN povgroups.pov=369 THEN 1 ELSE 0 END) AS 女装 ,SUM(CASE WHEN povgroups.pov=379 THEN 1 ELSE 0 END) AS 主人公モテモテ ,SUM(CASE WHEN povgroups.pov=1 THEN 1 ELSE 0 END) AS おかず ,SUM(CASE WHEN povgroups.pov=13 THEN 1 ELSE 0 END) AS 主人公素敵 ,SUM(CASE WHEN povgroups.pov=216 THEN 1 ELSE 0 END) AS アナルSEX ,SUM(CASE WHEN povgroups.pov=250 THEN 1 ELSE 0 END) AS 陵辱レイプ ,SUM(CASE WHEN povgroups.pov=124 THEN 1 ELSE 0 END) AS 女の子に襲 ,SUM(CASE WHEN povgroups.pov=3 THEN 1 ELSE 0 END) AS ゲーム性有 ,SUM(CASE WHEN povgroups.pov=34 THEN 1 ELSE 0 END) AS キャラ取巻く世界観 ,SUM(CASE WHEN povgroups.pov=235 THEN 1 ELSE 0 END) AS 奇人変人 ,SUM(CASE WHEN povgroups.pov=72 THEN 1 ELSE 0 END) AS 主人公ダメ ,SUM(CASE WHEN povgroups.pov=528 THEN 1 ELSE 0 END) AS 個別ルートダメ FROM (gamelist INNER JOIN brandlist ON gamelist.brandname = brandlist.id) INNER JOIN povgroups ON gamelist.ID=povgroups.game WHERE gamelist.median >= 70 AND ((gamelist.count2 >=80 AND gamelist.sellday >= '2008-01-01') OR (gamelist.count2 >=40 AND gamelist.sellday >= CURRENT_DATE - interval '6 month')) AND (povgroups.pov IN (33,10,57,193,50,314,369,379,1,13,216,250,124,3,34,235,72,528 )) GROUP BY gamelist.sellday,gamelist.count2,gamelist.median,gamelist.dmm,gamelist.id,brandlist.url,brandlist.brandname,gamelist.dmm_subsc ORDER BY gamelist.sellday DESC,gamelist.count2 DESC;
                                                                                    QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------
 Sort  (cost=54480.58..54502.87 rows=8918 width=355)
   Sort Key: gamelist.sellday DESC, gamelist.count2 DESC
   ->  HashAggregate  (cost=53471.84..53895.45 rows=8918 width=355)
         Group Key: gamelist.id, brandlist.url, brandlist.brandname
         ->  Hash Join  (cost=9425.55..52602.34 rows=8918 width=126)
               Hash Cond: (povgroups.game = gamelist.id)
               ->  Bitmap Heap Scan on povgroups  (cost=7403.37..48958.22 rows=408734 width=8)
                     Recheck Cond: (pov = ANY ('{33,10,57,193,50,314,369,379,1,13,216,250,124,3,34,235,72,528}'::integer[]))
                     ->  Bitmap Index Scan on povgroups_pov  (cost=0.00..7301.18 rows=408734 width=0)
                           Index Cond: (pov = ANY ('{33,10,57,193,50,314,369,379,1,13,216,250,124,3,34,235,72,528}'::integer
[]))
               ->  Hash  (cost=2015.70..2015.70 rows=518 width=122)
                     ->  Hash Join  (cost=473.47..2015.70 rows=518 width=122)
                           Hash Cond: (gamelist.brandname = brandlist.id)
                           ->  Bitmap Heap Scan on gamelist  (cost=262.19..1797.30 rows=518 width=81)
                                 Recheck Cond: (median >= 70)
                                 Filter: (((count2 >= 80) AND (sellday >= '2008-01-01'::date)) OR ((count2 >= 40) AND (selld
ay >= (('now'::cstring)::date - '6 mons'::interval))))
                                 ->  Bitmap Index Scan on tokuten_index  (cost=0.00..262.06 rows=8237 width=0)
                                       Index Cond: (median >= 70)
                           ->  Hash  (cost=152.79..152.79 rows=4679 width=49)
                                 ->  Seq Scan on brandlist  (cost=0.00..152.79 rows=4679 width=49)
(20 行)
->  Bitmap Index Scan on povgroups_pov  (cost=0.00..7301.18 rows=408734 width=0)
 Index Cond: (pov = ANY ('{33,10,57,193,50,314,369,379,1,13,216,250,124,3,34,235,72,528}'::integer[]))
と、一回のBitmap Index Scan on povgroups_pov でpovを探しています。

ORでつなぐよりINを使った方が速いです。

クリエイター別感想数(上位10まで表示)

レビューサイトさんの情報を表示する画面に「クリエイター別感想数(上位10まで表示)」という項目があります。
この情報はWindow関数(分析関数)を使って取り出しています。
※いい方法があったら教えて欲しいです。

表示している内容

原画

クリエイター感想数感想リスト
リバ原あき 4 デュアルソウル
魔女狩りの夜に
淫魔制服狩り(PC98)
脅迫
影崎夕那(影崎由那、影山由多) 4 HIGHスク~ルDAYS
BACTA2
霧島診療室の午後
FILE
横田守 4 クローンドール 課外授業
SHANGRLIA2
ここは楽園荘
ここは楽園荘2
吉澤友章 3 Esの方程式
黒の断章 -THE LITERARY FRAGMENT-
つもバカ日誌
蒼月裕忠 3 TAXI幻夢譚 ~ストレンジ・ワールド ACT.2~
PERSONA ~淫虐の仮面~
ストレンジ・ワールド
マーシーラビット(MERCY RABBIT、マーシー兎) 3 VRデート 五月倶楽部
わくわく麻雀パニック!2 -刻・視・夢・想-
わくわく麻雀パニック! -式・神・伝・承-
野々原幹(ぢょんたいらん) 2 リトルモニカ物語
Ricotte ~アルペンブルの歌姫~
新井和崎(新井カズキ) 2 殻の中の小鳥
雛鳥の囀
まさはる 2 Floralia ~フローラリア~
ときどきシュガー
本田直樹 2 つもバカ日誌
らいむいろ戦奇譚 ~明治日本、乙女防人ス。~

シナリオ

クリエイター感想数感想リスト
TAMAMI 2 STARS
Oh! きつねさま
大槻涼樹 2 Esの方程式
黒の断章 -THE LITERARY FRAGMENT-
秋津環 2 Wing&Wind
TALK to TALK
リバ原あき 2 魔女狩りの夜に
脅迫
朝凪軽(GM、夕凪) 2 リングアウト!!
ルージュの伝説
藤原将(藤原たすく) 2 Floralia ~フローラリア~
ときどきシュガー
安部三郎 2 魔性の貌(かお)
BACTA2
茂手木亮平 2 Blind Games
FILE
古我望 1 Wind -a breath of heart-
片岡とも(ぽこつ☆とも、へぽこ☆とも) 1 White ~セツナサのカケラ~
渡部好範 1 忘レナ草 ~Forget-me-Not~

SQLで取り出した内容
 shubetu | creater_id |                creater_name                | count | game_id |                  gamename                   |  sellday   | rank
---------+------------+--------------------------------------------+-------+---------+---------------------------------------------+------------+------
       1 |        534 | リバ原あき                                 |     6 |     456 | デュアルソウル                              | 1995-04-15 |    1
       1 |        534 | リバ原あき                                 |     6 |     721 | 魔女狩りの夜に                              | 1995-10-13 |    1
       1 |        534 | リバ原あき                                 |     6 |   16254 | 淫魔制服狩り(PC98)                          | 1995-07-15 |    1
       1 |        534 | リバ原あき                                 |     6 |    4871 | 脅迫                                        | 1996-04-27 |    1
       1 |        625 | 影崎夕那(影崎由那、影山由多)               |     4 |     549 | HIGHスク~ルDAYS                            | 1996-12-06 |    2
       1 |        625 | 影崎夕那(影崎由那、影山由多)               |     4 |    9185 | BACTA2                                      | 1994-02-05 |    2
       1 |        625 | 影崎夕那(影崎由那、影山由多)               |     4 |    9189 | 霧島診療室の午後                            | 1995-08-01 |    2
       1 |        625 | 影崎夕那(影崎由那、影山由多)               |     4 |    9187 | FILE                                        | 1994-11-01 |    2
       1 |        650 | 横田守                                     |     4 |    1000 | クローンドール 課外授業                     | 1995-12-21 |    3
       1 |        650 | 横田守                                     |     4 |    4617 | SHANGRLIA2                                  | 1993-09-30 |    3
       1 |        650 | 横田守                                     |     4 |    3248 | ここは楽園荘                                | 1994-04-22 |    3
       1 |        650 | 横田守                                     |     4 |    1054 | ここは楽園荘2                               | 1995-10-20 |    3
       1 |        974 | 吉澤友章                                   |     3 |      95 | Esの方程式                                  | 1996-09-13 |    4
       1 |        974 | 吉澤友章                                   |     3 |    3711 | 黒の断章 -THE LITERARY FRAGMENT-            | 1995-07-14 |    4
       1 |        974 | 吉澤友章                                   |     3 |     438 | つもバカ日誌                                | 1998-08-07 |    4
       1 |       1084 | 蒼月裕忠                                   |     3 |    1093 | TAXI幻夢譚 ~ストレンジ・ワールド ACT.2~   | 1998-02-06 |    5
       1 |       1084 | 蒼月裕忠                                   |     3 |    3718 | PERSONA ~淫虐の仮面~                      | 1996-02-09 |    5
       1 |       1084 | 蒼月裕忠                                   |     3 |   14269 | ストレンジ・ワールド                        | 1996-05-31 |    5
       1 |       4563 | マーシーラビット(MERCY RABBIT、マーシー兎) |     3 |     538 | VRデート 五月倶楽部                         | 1996-10-25 |    6
       1 |       4563 | マーシーラビット(MERCY RABBIT、マーシー兎) |     3 |    2004 | わくわく麻雀パニック!2 -刻・視・夢・想-   | 1996-07-12 |    6
       1 |       4563 | マーシーラビット(MERCY RABBIT、マーシー兎) |     3 |    2005 | わくわく麻雀パニック! -式・神・伝・承-    | 1995-01-31 |    6
       1 |         26 | 野々原幹(ぢょんたいらん)                   |     2 |    1598 | リトルモニカ物語                            | 2001-09-21 |    7
       1 |         26 | 野々原幹(ぢょんたいらん)                   |     2 |    3008 | Ricotte ~アルペンブルの歌姫~              | 2003-09-26 |    7
       1 |         80 | 新井和崎(新井カズキ)                       |     2 |     190 | 殻の中の小鳥                                | 1996-02-29 |    8
       1 |         80 | 新井和崎(新井カズキ)                       |     2 |     619 | 雛鳥の囀                                    | 1997-03-28 |    8
       1 |        128 | まさはる                                   |     2 |    2070 | Floralia ~フローラリア~                   | 2002-05-31 |    9
       1 |        128 | まさはる                                   |     2 |    2553 | ときどきシュガー                            | 2003-01-31 |    9
       1 |        310 | 本田直樹                                   |     2 |     438 | つもバカ日誌                                | 1998-08-07 |   10
       1 |        310 | 本田直樹                                   |     2 |    2444 | らいむいろ戦奇譚 ~明治日本、乙女防人ス。~ | 2002-12-13 |   10
       2 |        534 | リバ原あき                                 |     6 |    4871 | 脅迫                                        | 1996-04-27 |    0
       2 |        534 | リバ原あき                                 |     6 |     721 | 魔女狩りの夜に                              | 1995-10-13 |    0
       2 |        164 | TAMAMI                                     |     2 |    1202 | STARS                                       | 1997-03-14 |    1
       2 |        164 | TAMAMI                                     |     2 |    1247 | Oh! きつねさま                              | 1996-09-20 |    1
       2 |        311 | 大槻涼樹                                   |     2 |      95 | Esの方程式                                  | 1996-09-13 |    2
       2 |        311 | 大槻涼樹                                   |     2 |    3711 | 黒の断章 -THE LITERARY FRAGMENT-            | 1995-07-14 |    2
       2 |        408 | 秋津環                                     |     2 |    1127 | Wing&Wind                                   | 2001-03-16 |    3
       2 |        408 | 秋津環                                     |     2 |    1872 | TALK to TALK                                | 2002-02-01 |    3
       2 |        784 | 朝凪軽(GM、夕凪)                           |     2 |    1459 | リングアウト!!                              | 1996-04-12 |    4
       2 |        784 | 朝凪軽(GM、夕凪)                           |     2 |    3993 | ルージュの伝説                              | 1996-05-17 |    4
       2 |       1399 | 藤原将(藤原たすく)                         |     2 |    2070 | Floralia ~フローラリア~                   | 2002-05-31 |    5
この情報は以下のSQLで取り出しています。
SELECT *
  FROM (
         SELECT t.shubetu
              , t.creater_id
              , t.creater_name
              , t.count
              , t.game_id
              , t.gamename
              , t.sellday
              , SUM(t.flag) OVER( PARTITION BY t.shubetu ORDER BY t.count DESC, t.creater_id, t.flag DESC) AS rank
           FROM ( SELECT c.id AS creater_id
                       , c.name AS creater_name
                       , s.shubetu
                       , COUNT(*) OVER( PARTITION BY c.id ) AS count
                       , g.id AS game_id
                       , g.gamename
                       , g.sellday
                       , CASE WHEN LAG(c.id,1) OVER() = c.id
                              THEN 0
                              ELSE 1
                   END AS flag
                    FROM createrlist c
                   INNER JOIN shokushu s
                           ON s.creater = c.id
                   INNER JOIN reviewpagelist r
                           ON r.game = s.game
                   INNER JOIN gamelist g
                           ON g.id = s.game
                   WHERE r.reviewpage = 497
                     AND s.shubetu IN (1,2)
                   GROUP BY c.id
                          , c.name
                          , s.shubetu
                          , g.id
                          , g.gamename
                          , g.sellday
                   ORDER BY s.shubetu
                          , count DESC
                          , c.id
                ) AS t
          ORDER BY shubetu
                 , count DESC
                 , t.creater_id
       ) AS t2
 WHERE rank <= 10
;
最後のrankを簡単に取り出す方法が思いつきませでした。rankを取得するrank関数はあるのですが、今回のケースではおそらく使えないのかな…と思いました。

psqlにおけるタブによるテーブル名等の補完の候補の表示について

psqlではクエリを書いている途中でタブを押すとテーブル名等が補完されます。
例えば以下のとおりです。
ap2=# SELECT * FROM game
game_banner_getchu_widesky         gamegrouplist                      gamelist_masterup_update_id_seq
game_banner_getchu_widesky_id_seq  gamegrouplist_id_seq               gamelist_sellday_update
game_music                         gamelist                           gamelist_sellday_update_id_seq
game_store_sp                      gamelist_id_seq
game_store_sp_id_seq               gamelist_masterup_update
PostgreSQLを以下の設定にすると、タブを押したときにどのように補完候補が抽出されているかのクエリが表示されます。

postgresql.conf
log_statement = 'all'
client_min_messages = log
SELECT * FROM gameli


LOG:  statement: SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(c.relname),1,6)='gameli' AND pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,6)='gameli' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,6) = substring('gameli',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,6)='gameli' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,6) = substring('gameli',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,6) = substring('gameli',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
LIMIT 1000;
このSQLを実行してみると以下の結果が得られます。
           quote_ident
---------------------------------
 gamelist_sellday_update
 gamelist
 gamelist_sellday_update_id_seq
 gamelist_masterup_update_id_seq
 gamelist_id_seq
 gamelist_masterup_update
(6 行)
補完候補ってクエリで抽出していたんだなあ…とちょっと感動したのでメモしておきます。

[解決]FATAL: terminating connection due to administrator command

環境
PostgreSQL 11.4

ここ3日くらいPostgreSQLで意図しない「FATAL:  terminating connection due to administrator command」が発生してpgpoolから切り離されて困っています。

PostgreSQLのログ
2019-07-19 07:50:01.632 JST [27138] FATAL:  terminating connection due to administrator command

PostgreSQLの、ログの出力をdebug3、実行されたSQLをすべて出力している状態で現象が再現したのですが、ログを見ても原因が分かりません…

postgresql.conf
log_min_messages = debug3
log_statement = 'all'

「FATAL:  terminating connection due to administrator command」となる直接の原因は、おそらく「SELECT pg_terminate_backend(pid)」が実行されていることです。
※とても参考にさせて頂いています。とてもすごい資料です。
2019-07-19 07:50:01.615 JST [10453] LOG:  statement: SELECT pg_terminate_backend(pid)
          FROM pg_stat_activity
         WHERE pid IN (
                            SELECT l.pid
                              FROM ( SELECT pid FROM pg_locks GROUP BY pid ) l
                                 , pg_stat_activity a
                             WHERE l.pid = a.pid
                               AND now() - query_start > '5minutes'::INTERVAL
                      )
        ;
このクエリの結果、クエリが実行されてから5分を超えているプロセスにpg_terminate_backendしにいっています。
「FATAL:  terminating connection due to administrator command」を出力しているのは、プロセス番号7813なので、プロセス番号7813を抜き出すと以下の通りでした。

PostgreSQLのログ
2019-07-19 07:50:01.621 JST [7813] LOG:  statement: BEGIN
2019-07-19 07:50:01.626 JST [7813] LOG:  statement:
        SELECT tag
             , uid
                         , game
                         , comment
          FROM userreview_with_tag uwg
         WHERE EXISTS ( SELECT *
                          FROM userreview_tag u
                                                                         WHERE u.uid  = uwg.uid
                                                                           AND u.game = uwg.game
                                                                                 AND timestamp >= ( SELECT timestamp FROM userreview_tag_last_check_timestamp )
                                                                 )
        ;
        2019-07-19 07:50:01.630 JST [7813] LOG:  statement:
        SELECT uid
             , game
                         , tags
         FROM userreview_tag u
        WHERE timestamp >= ( SELECT timestamp FROM userreview_tag_last_check_timestamp )
2019-07-19 07:50:01.632 JST [7813] FATAL:  terminating connection due to administrator command


5minutesな設定でクエリを解放しそうなPostgreSQLの設定が「max_standby_streaming_delay」で、デフォルトは30秒なのですが、当時の自分がなんとなく300秒に設定していました。

userreview_tagテーブル/userreview_tag_last_check_timestampテーブルを長時間ロックしそうな処理はしていない…と思うのですが、してるのかな…


2019/07/19追記

pgpoolのログ
Jul 19 04:20:01 erogamescape13 pgpool[25274]: [2975989-1] 2019-07-19 04:20:01: pid 25274: LOG:  reading and processing packets
Jul 19 04:20:01 erogamescape13 pgpool[25274]: [2975989-2] 2019-07-19 04:20:01: pid 25274: DETAIL:  postmaster on DB node 1 was shutdown by administrative command
Jul 19 04:20:01 erogamescape13 pgpool[25274]: [2975990-1] 2019-07-19 04:20:01: pid 25274: LOG:  received degenerate backend request for node_id: 1 from pid [25274]
Jul 19 04:20:01 erogamescape13 pgpool[10368]: [449-1] 2019-07-19 04:20:01: pid 10368: LOG:  new IPC connection received
Jul 19 04:20:01 erogamescape13 pgpool[10368]: [450-1] 2019-07-19 04:20:01: pid 10368: LOG:  watchdog received the failover command from local pgpool-II on IPC interface
Jul 19 04:20:01 erogamescape13 pgpool[10368]: [451-1] 2019-07-19 04:20:01: pid 10368: LOG:  watchdog is processing the failover command [DEGENERATE_BACKEND_REQUEST] received from local pgpool-II on IPC interface
Jul 19 04:20:01 erogamescape13 pgpool[10368]: [452-1] 2019-07-19 04:20:01: pid 10368: LOG:  we have got the consensus to perform the failover
Jul 19 04:20:01 erogamescape13 pgpool[10368]: [452-2] 2019-07-19 04:20:01: pid 10368: DETAIL:  1 node(s) voted in the favor
Jul 19 04:20:01 erogamescape13 pgpool[10367]: [2976456-1] 2019-07-19 04:20:01: pid 10367: LOG:  Pgpool-II parent process has received failover request
Jul 19 04:20:01 erogamescape13 pgpool[10368]: [453-1] 2019-07-19 04:20:01: pid 10368: LOG:  new IPC connection received
Jul 19 04:20:01 erogamescape13 pgpool[10368]: [454-1] 2019-07-19 04:20:01: pid 10368: LOG:  received the failover indication from Pgpool-II on IPC interface
Jul 19 04:20:01 erogamescape13 pgpool[10368]: [455-1] 2019-07-19 04:20:01: pid 10368: LOG:  watchdog is informed of failover start by the main process
Jul 19 04:20:01 erogamescape13 pgpool[10367]: [2976457-1] 2019-07-19 04:20:01: pid 10367: LOG:  starting degeneration. shutdown host 192.168.0.13(5432)

2019/07/24追記

おそらく解決しました。
2013年9月9日から2013年9月15日に発生した障害について(続き)のときに対策として、以下のクエリをcronで5分おきに実行していました。
SELECT pg_terminate_backend(pid)
          FROM pg_stat_activity
         WHERE pid IN (
                            SELECT l.pid
                              FROM ( SELECT pid FROM pg_locks GROUP BY pid ) l
                                 , pg_stat_activity a
                             WHERE l.pid = a.pid
                               AND now() - query_start > '5minutes'::INTERVAL
                      )
        ;
pg_locksテーブルに存在するクエリで、query_startから5分たったものはpg_terminate_backendで強制的にプロセスを落とすようにしていました。
pg_terminate_backendを実行すると、PostgreSQLのログに「FATAL:  terminating connection due to administrator command」が記録されます。また、pg_terminate_backend()関数の値が定数ではない場合、pgpoolから切り離されます。

5分も実行しているクエリなんかあるのかな…と思っていたら
  • BEGINではじまるトランザクションで5分以上かかる処理が存在する
  • ユーザーさんが5分を超えるクエリを実行することがある。いまいちなSQLの場合は、いつまでも処理が終わらずにCPU使用率が100%にはりつく※ErogameScapeは誰でもクエリを実行できるようにしています。
ことがあることが分かったので、長い間実行しているクエリは殺す必要があります
twitterで「pg_terminate_backend」ではなく「pg_cancel_backend」を使用した方がよいと教わりましたので、「pg_cancel_backend」に置換しました。

あるブランドのゲームにつけられた得点の中央値を求める

SELECT b.id
     , b.brandname
     , b.kind
     , b.lost
     , PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tokuten) AS median
     , cast ( STDDEV(tokuten) AS INTEGER ) AS stddev
     , count(tokuten) AS count
  FROM userreview u
 INNER JOIN gamelist g
         ON u.game = g.id
 INNER JOIN brandlist b
         ON g.brandname = b.id
 WHERE b.id = 1
 GROUP BY b.id
        , b.brandname
        , b.kind
        , b.lost;
 id | brandname |    kind     | lost | median | stddev | count
----+-----------+-------------+------+--------+--------+-------
  1 | âge(age)  | CORPORATION | f    |     81 |     15 | 13807
PERCENTILE_CONT関数について
順序集合集約関数というものが9.4のときに追加されていて、中央値をたった一行で求められるようになっていました…(以前はウインドウ関数を使う必要がありました。それ以前は、とてもいろいろ書く必要がありました)

(28)No space left on device: AH00023: Couldn't create the proxy mutex

ErogameScapeは2019/07/15時点で以下のApacheが動いています。

Server Version: Apache/2.4.34 (Red Hat) OpenSSL/1.0.1e-fips
Server MPM: event

ちゃんとした契機が不明ですが、Apacheの応答がなくなりました。
通常のpsコマンドの状態は
[ap2@erogamescape13 ~]$ ps ax | grep http
 1489 ?        S      0:01 tail -n0 -F /var/log/httpd/error_log
15179 ?        Ss     0:00 /opt/rh/httpd24/root/usr/sbin/httpd
15183 ?        S      0:00 /opt/rh/httpd24/root/usr/sbin/httpd
15184 ?        Sl     0:00 /opt/rh/httpd24/root/usr/sbin/httpd
17530 pts/1    S+     0:00 grep http
なのですが、事象発生時は
[ap2@erogamescape13 ~]$ ps ax | grep http
1489 ?        S      0:01 tail -n0 -F /var/log/httpd/error_log
10096 ?        S      0:00 /opt/rh/httpd24/root/usr/sbin/httpd
26194 pts/1    S+     0:00 grep http
となってhttpdのプロセスが足りませんでした。
Apacheにはcronで定期的にアクセス出来るかを監視していて、アクセス出来なかった場合、具体的にはトップページの80番ポートでアクセスして応答がなかった場合、httpdを再起動することにしています。
そのcronは動いているのですが、httpdの再起動に失敗しているようでした。

試しに手動でhttpdを再起動してもNGでした。
[root@erogamescape13 ~]# /sbin/service httpd24-httpd stop
httpd を停止中:                                            [  OK  ]

[root@erogamescape13 ~]# /sbin/service httpd24-httpd start
httpd を起動中:                                            [  OK  ]

[root@erogamescape13 ~]# ps ax | grep http
1489 ?        S      0:01 tail -n0 -F /var/log/httpd/error_log
10096 ?        S      0:00 /opt/rh/httpd24/root/usr/sbin/httpd
27178 pts/1    S+     0:00 grep http
試しに10096をKILLして再起動すればいいかな…と思ってやってみましたが、httpdのプロセスを殺すことが出来ただけで、httpdの起動はNGでした。
[root@erogamescape13 ~]# kill -KILL 10096

[root@erogamescape13 ~]# ps ax | grep http
1489 ?        S      0:01 tail -n0 -F /var/log/httpd/error_log
27260 pts/1    S+     0:00 grep http

[root@erogamescape13 ~]# /sbin/service httpd24-httpd start
httpd を起動中:                                            [  OK  ]
※OKになっていますが、実際はhttpdは起動していません。

Apacheのerror_logを確認したところ、同じようなメッセージが、ばーっと出ていました。
[root@erogamescape13 ~]# cat /var/log/httpd24/error_log
[Mon Jul 15 08:00:02.469050 2019] [core:emerg] [pid 17663:tid 140413539801056] (28)No space left on device: AH00023: Couldn't create the proxy mutex
[Mon Jul 15 08:00:02.469161 2019] [proxy:crit] [pid 17663:tid 140413539801056] (28)No space left on device: AH02478: failed to create proxy mutex
[Mon Jul 15 08:00:02.469170 2019] [:emerg] [pid 17663:tid 140413539801056] AH00020: Configuration Failed, exiting
[Mon Jul 15 08:05:02.848324 2019] [core:emerg] [pid 19318:tid 140124209342432] (28)No space left on device: AH00023: Couldn't create the proxy mutex
[Mon Jul 15 08:05:02.848454 2019] [proxy:crit] [pid 19318:tid 140124209342432] (28)No space left on device: AH02478: failed to create proxy mutex
[Mon Jul 15 08:05:02.848466 2019] [:emerg] [pid 19318:tid 140124209342432] AH00020: Configuration Failed, exiting
Apacheの状態を監視してNGだったら再起動するスクリプトが起動したタイミング上記メッセージが出ているので、Apacheを起動する際に上記エラーを吐いて失敗しているようです。
エラーメッセージをgoogleで検索すると以下の記事がひっかかりました。


記事によると「セマフォが限界値に達してしまったために新しいセマフォを使ってアプリケーションを起動することができなくなってしまった時にこのエラーが出てくる」そうでした。
ipcs -sでセマフォの確認ができるそうなのでコマンドを実行しました。
[root@erogamescape13 ~]# ipcs -s

------ セマフォ配列 --------
キー     semid      所有者  権限     nsems
0x00000000 0          root       600        1
0x00000000 32769      root       600        1
0x00000000 10944514   apache     600        1
0x00000000 11599875   apache     600        1
0x00000000 11632644   apache     600        1
0x00000000 13205509   apache     600        1
0x00000000 95780870   apache     600        1
0x00000000 95813639   apache     600        1
0x00000000 95846408   apache     600        1
0x00000000 76611593   pgpool     600        6
0x00000000 95944714   apache     600        1
0x00000000 95977483   apache     600        1
以下いっぱい
なんらかの異常でapacheがセマフォを使い切っている模様でした。
apacheが使用しているセマフォを消して、apacheを起動したら、無事起動できました。
[root@erogamescape13 ~]# for i in `ipcs -s | awk '/apache/ {print $2}'`; do (ipcrm -s $i); done
[root@erogamescape13 ~]# ipcs -s

------ セマフォ配列 --------
キー     semid      所有者  権限     nsems
0x00000000 0          root       600        1
0x00000000 32769      root       600        1
0x00000000 76611593   pgpool     600        6

[root@erogamescape13 ~]# /sbin/service httpd24-httpd start
httpd を起動中:                                            [  OK  ]
[root@erogamescape13 ~]# ps ax | grep http
1489 ?        S      0:01 tail -n0 -F /var/log/httpd/error_log
28085 ?        Ss     0:00 /opt/rh/httpd24/root/usr/sbin/httpd
28087 ?        S      0:00 /opt/rh/httpd24/root/usr/sbin/httpd
28088 ?        Sl     0:00 /opt/rh/httpd24/root/usr/sbin/httpd
28360 pts/1    S+     0:00 grep http
この問題が発生したときに備えてApacheを監視するスクリプトにApacheを再起動する前にapacheのセマフォを消すコマンドを追加しました。

Apacheがなぜこのような状態になったのかは分からないです。
契機は、同じサーバーマシンに入っているPostgreSQLを再起動したこと…問題が起こった時に実施したことがそれくらいなのですが、PostgreSQLを再起動したらこの事象が起きるのか、まったくわかりません。








phpだけ404の場合に自分で設定した404用のページが表示されず「File not found. 」とだけ表示される

Apache + php-fpmの環境の話題です。

ErogameScapeではファイルが存在しないURLが指定された場合、ルートディレクトリのindex.phpを表示するように設定しています。
ErrorDocument 404 /~ap2/index.php
PHPをApacheのモジュールとして動かしていた場合には問題が発生していなかったのですが、PHPをCGIとして動作させた場合に以下の問題が発生しました。

phpだけ404の場合に自分で設定した404用のページが表示されず「File not found. 」とだけ表示される。

https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/a
とすると(aは存在しないです)、自分が設定した404用のページが表示されるのに
https://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/a.php
とすると(a.phpも存在しないです)、「File not found. 」とだけ表示されてます。
404
php-fpmを動かすのに、ErogameScapeではunixドメインソケット経由で動かしているので設定ファイルに以下の記述をしています。
<FilesMatch \.php$>
    SetHandler proxy:fcgi://php-fpm
</FilesMatch>
apacheが受けた要求をphp-fpmに渡して、php-fpmが処理した結果をapacheが受けて、apacheがクライアントに返却します。
a.phpの要求を受けたapacheはphp-fpmに渡して、php-fpmはa.phpがないので、「File not found. 」をapacheに返し、apacheはそれを見たそのままクライアントに返却するので、自分が設定した404用のページが表示されません。

php-fpmが返してきた「File not found. 」(404)をapacheが上書きする設定が以下のディレクティブです。

設定ファイルに以下の記述すると自分が設定した404用のページを表示してくれます。
ProxyErrorOverride on
※php-fpmがどうやって「File not found. 」という文字列を返しているのかは分かりませんでした…返す文字列を設定しているっぽいファイルは見つけられなかったのでソースに埋め込まれているのでしょうか…



記事検索