型キャストの場所のせいで、秒で終わっていたクエリに1時間超かかるようになってしまった話 - SmartHR Tech Blog

SmartHRで届出書類という機能を担当しているプロダクトエンジニアのsato-sと申します。 今日は、以前私が調査にとても苦労したパフォーマンス上の問題の話を紹介したいと思います。 TL;DR PostgreSQLのアップグレードを実施した アップグレード後、今までは問題のなかった特定のクエリの実行に1時間超かかり、DBのCPU使用率がピッタリ100%に張り付くようになった 色々調査した結果、PostgreSQL上の型キャストの場所のせいで、良くないクエリプランが選択されることが原因だった 型キャストの場所には気をつけよう PostgreSQLのアップグレードと挫折 SmartHRでは基本的にWebアプリケーションのデータベースとしてGoogle CloudのCloudSQLによって提供されるPostgreSQLを利用しています。 私の担当している届出書類機能では、利用中のPostgre

[lets_skeptic]

思考の過程が言語化されてていい感じ。 基本的にカラムの値を加工するのが悪手なのは一般的な知識だと思うけど、ORMで隠蔽されたりする現代では、手書きでSQL書いてた時代とは違うつらさがある

[turanukimaru]

Where でカラムをキャストすると index が効かないのでそもそもそんなクエリは滅多に書かない気がするけど…

[hikalin8686]

こういう手順で解決に至れる力こそ技術力だよなぁ

[khtno73]

ORMがクエリを書いてくれる時代、オプティマイザ様がなんでもやってくれる時代だからこそ、この手のトラブルシュートに価値があるよね。transparentになってる部分の技術こそ金になる。

[circled]

「そもそもそんなクエリは滅多に書かない気がするけど…」← ORMが自動生成してたSQLをキャストしないように直したってことなのでは？

[bfoj]

ブコメを

[Windymelt]

おもしろい。やっぱキャストはトリッキーな振舞いすることあるなぁ

[n314]

orm関係なくuuidを=で比較したらtextにキャストされたってことじゃないのかな。textがuuidにキャストできるかどうかはプログラマーしか分からないし。

[Kazumi007]

こういうチューニングは楽しい。

[ahir0ta]

“更に原因の詳細を特定するためクエリプランを確認しました。”

[kiririmode]

キャストによってオプティマイザがフィルタの推定行数を誤ってしまいパフォーマンス劣化に繋がる例。pev2による実行計画の可視化も参考になる

[tmatsuu]

「クエリプランの可視化のためにpev2というOSSを使用」おーこれいいな。最高では

[tmsgkun]

データベース勉強

[tuki0918]

“参考”

[srng]

おもしろかった

[t_f_m]

面白い

[yarumato]

“PostgreSQLのアップグレード後、特定のクエリの実行に1時間、DBのCPU使用率100%。PostgreSQL上の型キャストの場所のせいで、良くないクエリプラン選択されるのが原因。”

[Guro]

オプティマイザこわいなあ。

[nakag0711]

postgresのクエリプランは気難しいからなあ

[koluku]

こういうDBの原因調査の思考過程参考になる

[kibitaki]

はえー。ORMで生成してると、分析に時間かかるって方が実務上は苦しいなぁ。

[taruhachi]

where句内で型のキャストすると存在する全レコードをキャストして付き合わせるしかなさそうなものだが、バージョンアップ関係あるんだ。。。

[PrivateIntMain]

ORMの自動生成って立ち上げの時にはいいけど、長く運用するにつれて思わぬところで足かせになる印象が拭えない。とはいえぜんぶ素組みも時代遅れな気がするのでいい塩梅のところがないだろうか。

[ghostbass]

比較対象列に対して関数とか適用しないほうが良い、って古くからあるノウハウなのだろうがORMの特性でそうなってしまった？

[napsucks]

DBエンジニアかっこいい

[havanap]

arigachiというか両辺でキャストしてるのがあかんかった的な

[at_yasu]

なるほど。

[katsyoshi]

sugoi

[tmtms]

解決するに至るまでの調査と考察がちゃんとしててすごい