はじめに
監視対象ホスト数が何十万とある環境でZabbix画面遷移が遅い、あるいはいくら待っても終わらない問題がありました。
本記事では、問題を少しでも改善するために実施したPostgreSQLのチューニング事例を紹介します。Zabbixを利用する上での参考程度のご紹介です。全てのシステムに効果があることを保証するものではありませんのでご了承ください。
環境構成は下記です。
| ＃ | バージョン |
|---|---|
| OS | RHEL8 |
| PostgreSQL | 13 |
| Zabbix | 6.0 |
実施したチューニングの流れ
1.長時間実行されているクエリを確認
Zabbix画面遷移中にpg_stat_activityを参照し、query_startが古い（現在時刻から数時間前など）クエリの有無を確認しました。下記は1時間以上実行されているクエリを表示する例です。
SELECT
state,
query,
query_start,
NOW() - query_start AS duration
FROM
pg_stat_activity
WHERE
state = 'active'
AND NOW() - query_start >= INTERVAL '1 hour';
なお、クエリの文字列長が長く、queryが途中で切れてしまったため、postgresql.confのlog_min_duration_statementを3600に設定し、クエリをPostgreSQLログに出力しました。
（PostgreSQLマニュアル）
PostgreSQL 13文書：pg_stat_activity
PostgreSQL 13文書：log_min_duration_statement
2.クエリの実行計画を確認
EXPLAIN ANALYZEでクエリの実行計画を確認しました。下記はEXPLAN ANALYZEの例です。（WHERE条件内のIDはマスクしたものです）
EXPLAIN ANALYZE SELECT f.functionid,f.itemid,f.name,f.parameter
FROM functions f
WHERE
( f.functionid IN (93790398,93790399,93790400,93790401,93790402,93790403,93790404,93790405,93790409,93790407,93790408,93790409,93790410,93790411,93790412,93790413,93790414,93790415,93790419,93790417,93790418,93790419,93790420,93790421,93790422,93790423,93790424,93790425,93790429,93790427,93790428,93790429,93790430,93790431,93790432,93790433,93790434,93790435,93790439,93790437,93790438,93790439,93790440,93790441,93790442,93790443,93790444,93790445,93790449,93790447,93790448,93790449,93790450,93790451,93790452,93790453,93790454,93790455,93790459,93790457,93790458,93790459,93790490,93790491,93790492,93790493,93790494,93790495,93790499,93790497,93790498,93790499,93790470,93790471,93790472,93790473,93790474,93790475,93790479,93790477,93790478,93790479,93790480,93790481,93790482,93790483,93790484,93790485,93790489,93790487,93790488,93790489,93790490,93790491,93790492,93790493,93790494,93790495,93790499,93790497,93790498,93790499,93790500,93790501,93790502,93790503,93790504,93790505,93790509,93790507,93790508,93790509,93790510,93790511,93790512,93790513,93790514,93790515,93790519,93790517,93790518,93790519,93790520,93790521,93790522,93790523,93790524,93790525,93790529,93790527,93790528,93790529,93790530,93790531,93790532,93790533,93790534,93790535,93790539,93790537,93790538,93790539,93790540,93790541,93790542,93790543,93790544,93790545,93790549,93790547,93790548,93790549,93790550,93790551,93790552,93790553,93790554,93790555,93790559,93790557,93790558,93790559,93790590,93790591,93790592,93790593,93790594,93790595,93790599,93790597,93790598,93790599,93790570,93790571,93790572,93790573,93790574,93790575,93790579,93790577,93790578,93790579,93790580,93790581,93790582,93790583,93790584,93790585,93790589,93790587,93790588,93790589,93790590,93790591,93790592,93790593,93790594,93790595,93790599,93790597,93790598,93790599,93790900,93790901,93790902,93790903,93790904,93790905,93790909,93790907,93790908,93790909,93790910,93790911,93790912,93790913,93790914,93790915,93790919,93790917,93790918,93790919,93790920,93790921,93790922,93790923,93790924,93790925,93790929,93790927,93790928,93790929,93790930,93790931,93790932,93790933,93790934,93790935,93790939,93790937,93790938,93790939,93790940,93790941,93790942,93790943,93790944,93790945,93790949,93790947,93790948,93790949,93790950,93790951,93790952,93790953,93790954,93790955,93790959,93790957,93790958,93790959,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790970,93790971,93790972,93790973,93790974,93790975,93790979,93790977,93790978,93790979,93790980,93790981,93790982,93790983,93790984,93790985,93790989,93790987,93790988,93790989,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790700,93790701,93790702,93790703,93790704,93790705,93790709,93790707,93790708,93790709,93790710,93790711,93790712,93790713,93790714,93790715,93790719,93790717,93790718,93790719,93790720,93790721,93790722,93790723,93790724,93790725,93790729,93790727,93790728,93790729,93790730,93790731,93790732,93790733,93790734,93790735,93790739,93790737,93790738,93790739,93790740,93790741,93790742,93790743,93790744,93790745,93790749,93790747,93790748,93790749,93790750,93790751,93790752,93790753,93790754,93790755,93790759,93790757,93790758,93790759,93790790,93790791,93790792,93790793,93790794,93790795,93790799,93790797,93790798,93790799,93790770,93790771,93790772,93790773,93790774,93790775,93790779,93790777,93790778,93790779,93790780,93790781,93790782,93790783,93790784,93790785,93790789,93790787,93790788,93790789,93790790,93790791,93790792,93790793,93790794,93790795,93790799,93790797,93790798,93790799,93790800,93790801,93790802,93790803,93790804,93790805,93790809,93790807,93790808,93790809,93790810,93790811,93790812,93790813,93790814,93790815,93790819,93790817,93790818,93790819,93790820,93790821,93790822,93790823,93790824,93790825,93790829,93790827,93790828,93790829,93790830,93790831,93790832,93790833,93790834,93790835,93790839,93790837,93790838,93790839,93790840,93790841,93790842,93790843,93790844,93790845,93790849,93790847,93790848,93790849,93790850,93790851,93790852,93790853,93790854,93790855,93790859,93790857,93790858,93790859,93790890,93790891,93790892,93790893,93790894,93790895,93790899,93790897,93790898,93790899,93790870,93790871,93790872,93790873,93790874,93790875,93790879,93790877,93790878,93790879,93790880,93790881,93790882,93790883,93790884,93790885,93790889,93790887,93790888,93790889,93790890,93790891,93790892,93790893,93790894,93790895,93790899,93790897,93790898,93790899,93790900,93790901,93790902,93790903,93790904,93790905,93790909,93790907,93790908,93790909,93790910,93790911,93790912,93790913,93790914,93790915,93790919,93790917,93790918,93790919,93790920,93790921,93790922,93790923,93790924,93790925,93790929,93790927,93790928,93790929,93790930,93790931,93790932,93790933,93790934,93790935,93790939,93790937,93790938,93790939,93790940,93790941,93790942,93790943,93790944,93790945,93790949,93790947,93790948,93790949,93790950,93790951,93790952,93790953,93790954,93790955,93790959,93790957,93790958,93790959,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790970,93790971,93790972,93790973,93790974,93790975,93790979,93790977,93790978,93790979,93790980,93790981,93790982,93790983,93790984,93790985,93790989,93790987,93790988,93790989,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93791000,93791001,93791002,93791003,93791004,93791005,93791009,93791007,93791008,93791009,93791010,93791011,93791012,93791013,93791014,93791015,93791019,93791017,93791018,93791019,93791020,93791021,93791022,93791023,93791024,93791025,93791029,93791027,93791028,93791029,93791030,93791031,93791032,93791033,93791034,93791035,93791039,93791037,93791038,93791039,93791040,93791041,93791042,93791043,93791044,93791045,93791049,93791047,93791048,93791049,93791050,93791051,93791052,93791053,93791054,93791055,93791059,93791057,93791058,93791059,93791090,93791091,93791092,93791093,93791094,93791095,93791099,93791097,93791098,93791099,93791070,93791071,93791072,93791073,93791074,93791075,93791079,93791077,93791078,93791079,93791080,93791081,93791082,93791083,93791084,93791085,93791089,93791087,93791088,93791089,93791090,93791091,93791092,93791093,93791094,93791095,93791099,93791097,93791098,93791099,93791100,93791101,93791102,93791103,93791104,93791105,93791109,93791107,93791108,93791109,93791110,93791111,93791112,93791113,93791114,93791115,93791119,93791117,93791118,93791119,93791120,93791121,93791122,93791123,93791124,93791125,93791129,93791127,93791128,93791129,93791130,93791131,93791132,93791133,93791134,93791135,93791139,93791137,93791138,93791139,93791140,93791141,93791142,93791143,93791144,93791145,93791149,93791147,93791148,93791149,93791150,93791151,93791152,93791153,93791154,93791155,93791159,93791157,93791158,93791159,93791190,93791191,93791192,93791193,93791194,93791195,93791199,93791197,93791198,93791199,93791170,93791171,93791172,93791173,93791174,93791175,93791179,93791177,93791178,93791179,93791180,93791181,93791182,93791183,93791184,93791185,93791189,93791187,93791188,93791189,93791190,93791191,93791192,93791193,93791194,93791195,93791199,93791197,93791198,93791199,93791200,93791201,93791202,93791203,93791204,93791205,93791209,93791207,93791208,93791209,93791210,93791211,93791212,93791213,93791214,93791215,93791219,93791217,93791218,93791219,93791220,93791221,93791222,93791223,93791224,93791225,93791229,93791227,93791228,93791229,93791230,93791231,93791232,93791233,93791234,93791235,93791239,93791237,93791238,93791239,93791240,93791241,93791242,93791243,93791244,93791245,93791249,93791247,93791248,93791249,93791250,93791251,93791252,93791253,93791254,93791255,93791259,93791257,93791258,93791259,93791290,93791291,93791292,93791293,93791294,93791295,93791299,93791297,93791298,93791299,93791270,93791271,93791272,93791273,93791274,93791275,93791279,93791277,93791278,93791279,93791280,93791281,93791282,93791283,93791284,93791285,93791289,93791287,93791288,93791289,93791290,93791291,93791292,93791293,93791294,93791295,93791299,93791297,93791298,93791299,93791300,93791301,93791302,93791303,93791304,93791305,93791309,93791307,93791308,93791309,93791310,93791311,93791312,93791313,93791314,93791315,93791319,93791317,93791318,93791319,93791320,93791321,93791322,93791323,93791324,93791325,93791329,93791327,93791328,93791329,93791330,93791331,93791332,93791333,93791334,93791335,93791339,93791337,93791338,93791339,93791340,93791341,93791342,93791343,93791344,93791345,93791349,93791347)
OR f.functionid IN (93791348,93791349,93791350,93791351,93791352,93791353,93791354,93791355,93791359,93791357,93791358,93791359,93791390,93791391,93791392,93791393,93791394,93791395,93791399,93791397,93791398,93791399,93791370,93791371,93791372,93791373,93791374,93791375,93791379,93791377,93791378,93791379,93791380,93791381,93791382,93791383,93791384,93791385,93791389,93791387,93791388,93791389,93791390,93791391,93791392,93791393,93791394,93791395,93791399,93791397,93791398,93791399,93791400,93791401,93791402,93791403,93791404,93791405,93791409,93791407,93791408,93791409,93791410,93791411,93791412,93791413,93791414,93791415,93791419,93791417,93791418,93791419,93791420,93791421,93791422,93791423,93791424,93791425,93791429,93791427,93791428,93791429,93791430,93791431,93791432,93791433,93791434,93791435,93791439,93791437,93791438,93791439,93791440,93791441,93791442,93791443,93791444,93791445,93791449,93791447,93791448,93791449,93791450,93791451,93791452,93791453,93791454,93791455,93791459,93791457,93791458,93791459,93791490,93791491,93791492,93791493,93791494,93791495,93791499,93791497,93791498,93791499,93791470,93791471,93791472,93791473,93791474,93791475,93791479,93791477,93791478,93791479,93791480,93791481,93791482,93791483,93791484,93791485,93791489,93791487,93791488,93791489,93791490,93791491,93791492,93791493,93791494,93791495,93791499,93791497,93791498,93791499,93791500,93791501,93791502,93791503,93791504,93791505,93791509,93791507,93791508,93791509,93791510,93791511,93791512,93791513,93791514,93791515,93791519,93791517,93791518,93791519,93791520,93791521,93791522,93791523,93791524,93791525,93791529,93791527,93791528,93791529,93791530,93791531,93791532,93791533,93791534,93791535,93791539,93791537,93791538,93791539,93791540,93791541,93791542,93791543,93791544,93791545,93791549,93791547,93791548,93791549,93791550,93791551,93791552,93791553,93791554,93791555,93791559,93791557,93791558,93791559,93791590,93791591,93791592,93791593,93791594,93791595,93791599,93791597,93791598,93791599,93791570,93791571,93791572,93791573,93791574,93791575,93791579,93791577,93791578,93791579,93791580,93791581,93791582,93791583,93791584,93791585,93791589,93791587,93791588,93791589,93791590,93791591,93791592,93791593,93791594,93791595,93791599,93791597,93791598,93791599,93791900,93791901,93791902,93791903,93791904,93791905,93791909,93791907,93791908,93791909,93791910,93791911,93791912,93791913,93791914,93791915,93791919,93791917,93791918,93791919,93791920,93791921,93791922,93791923,93791924,93791925,93791929,93791927,93791928,93791929,93791930,93791931,93791932,93791933,93791934,93791935,93791939,93791937,93791938,93791939,93791940,93791941,93791942,93791943,93791944,93791945,93791949,93791947,93791948,93791949,93791950,93791951,93791952,93791953,93791954,93791955,93791959,93791957,93791958,93791959,93791990,93791991,93791992,93791993,93791994,93791995,93791999,93791997,93791998,93791999,93791970,93791971,93791972,93791973,93791974,93791975,93791979,93791977,93791978,93791979,93791980,93791981,93791982,93791983,93791984,93791985,93791989,93791987,93791988,93791989,93791990,93791991,93791992,93791993,93791994,93791995,93791999,93791997,93791998,93791999,93791700,93791701,93791702,93791703,93791704,93791705,93791709,93791707,93791708,93791709,93791710,93791711,93791712,93791713,93791714,93791715,93791719,93791717,93791718,93791719,93791720,93791721,93791722,93791723,93791724,93791725,93791729,93791727,93791728,93791729,93791730,93791731,93791732,93791733,93791734,93791735,93791739,93791737,93791738,93791739,93791740,93791741,93791742,93791743,93791744,93791745,93791749,93791747,93791748,93791749,93791750,93791751,93791752,93791753,93791754,93791755,93791759,93791757,93791758,93791759,93791790,93791791,93791792,93791793,93791794,93791795,93791799,93791797,93791798,93791799,93791770,93791771,93791772,93791773,93791774,93791775,93791779,93791777,93791778,93791779,93791780,93791781,93791782,93791783,93791784,93791785,93791789,93791787,93791788,93791789,93791790,93791791,93791792,93791793,93791794,93791795,93791799,93791797,93791798,93791799,93791800,93791801,93791802,93791803,93791804,93791805,93791809,93791807,93791808,93791809,93791810,93791811,93791812,93791813,93791814,93791815,93791819,93791817,93791818,93791819,93791820,93791821,93791822,93791823,93791824,93791825,93791829,93791827,93791828,93791829,93791830,93791831,93791832,93791833,93791834,93791835,93791839,93791837,93791838,93791839,93791840,93791841,93791842,93791843,93791844,93791845,93791849,93791847,93791848,93791849,93791850,93791851,93791852,93791853,93791854,93791855,93791859,93791857,93791858,93791859,93791890,93791891,93791892,93791893,93791894,93791895,93791899,93791897,93791898,93791899,93791870,93791871,93791872,93791873,93791874,93791875,93791879,93791877,93791878,93791879,93791880,93791881,93791882,93791883,93791884,93791885,93791889,93791887,93791888,93791889,93791890,93791891,93791892,93791893,93791894,93791895,93791899,93791897,93791898,93791899,93791900,93791901,93791902,93791903,93791904,93791905,93791909,93791907,93791908,93791909,93791910,93791911,93791912,93791913,93791914,93791915,93791919,93791917,93791918,93791919,93791920,93791921,93791922,93791923,93791924,93791925,93791929,93791927,93791928,93791929,93791930,93791931,93791932,93791933,93791934,93791935,93791939,93791937,93791938,93791939,93791940,93791941,93791942,93791943,93791944,93791945,93791949,93791947,93791948,93791949,93791950,93791951,93791952,93791953,93791954,93791955,93791959,93791957,93791958,93791959,93791990,93791991,93791992,93791993,93791994,93791995,93791999,93791997,93791998,93791999,93791970,93791971,93791972,93791973,93791974,93791975,93791979,93791977,93791978,93791979,93791980,93791981,93791982,93791983,93791984,93791985,93791989,93791987,93791988,93791989,93791990,93791991,93791992,93791993,93791994,93791995,93791999,93791997,93791998,93791999,93792000,93792001,93792002,93792003,93792004,93792005,93792009,93792007,93792008,93792009,93792010,93792011,93792012,93792013,93792014,93792015,93792019,93792017,93792018,93792019,93792020,93792021,93792022,93792023,93792024,93792025,93792029,93792027,93792028,93792029,93792030,93792031,93792032,93792033,93792034,93792035,93792039,93792037,93792038,93792039,93792040,93792041,93792042,93792043,93792044,93792045,93792049,93792047,93792048,93792049,93792050,93792051,93792052,93792053,93792054,93792055,93792059,93792057,93792058,93792059,93792090,93792091,93792092,93792093,93792094,93792095,93792099,93792097,93792098,93792099,93792070,93792071,93792072,93792073,93792074,93792075,93792079,93792077,93792078,93792079,93792080,93792081,93792082,93792083,93792084,93792085,93792089,93792087,93792088,93792089,93792090,93792091,93792092,93792093,93792094,93792095,93792099,93792097,93792098,93792099,93792100,93792101,93792102,93792103,93792104,93792105,93792109,93792107,93792108,93792109,93792110,93792111,93792112,93792113,93792114,93792115,93792119,93792117,93792118,93792119,93792120,93792121,93792122,93792123,93792124,93792125,93792129,93792127,93792128,93792129,93792130,93792131,93792132,93792133,93792134,93792135,93792139,93792137,93792138,93792139,93792140,93792141,93792142,93792143,93792144,93792145,93792149,93792147,93792148,93792149,93792150,93792151,93792152,93792153,93792154,93792155,93792159,93792157,93792158,93792159,93792190,93792191,93792192,93792193,93792194,93792195,93792199,93792197,93792198,93792199,93792170,93792171,93792172,93792173,93792174,93792175,93792179,93792177,93792178,93792179,93792180,93792181,93792182,93792183,93792184,93792185,93792189,93792187,93792188,93792189,93792190,93792191,93792192,93792193,93792194,93792195,93792199,93792197,93792198,93792199,93792200,93792201,93792202,93792203,93792204,93792205,93792209,93792207,93792208,93792209,93792210,93792211,93792212,93792213,93792214,93792215,93792219,93792217,93792218,93792219,93792220,93792221,93792222,93792223,93792224,93792225,93792229,93792227,93792228,93792229,93792230,93792231,93792232,93792233,93792234,93792235,93792239,93792237,93792238,93792239,93792240,93792241,93792242,93792243,93792244,93792245,93792249,93792247,93792248,93792249,93792250,93792251,93792252,93792253,93792254,93792255,93792259,93792257,93792258,93792259,93792290,93792291,93792292,93792293,93792294,93792295,93792299,93792297,93792298,93792299,93792270,93792271,93792272,93792273,93792274,93792275,93792279,93792277,93792278,93792279,93792280,93792281,93792282,93792283,93792284,93792285,93792289,93792287,93792288,93792289,93792290,93792291,93792292,93792293,93792294,93792295,93792299,93792297)
OR f.functionid IN (93792298,93792299,93792300,93792301,93792302,93792303,93792304,93792305,93792309,93792307,93792308,93792309,93792310,93792311,93792312,93792313,93792314,93792315,93792319,93792317,93792318,93792319,93792320,93792321,93792322,93792323,93792324,93792325,93792329,93792327,93792328,93792329,93792330,93792331,93792332,93792333,93792334,93792335,93792339,93792337,93792338,93792339,93792340,93792341,93792342,93792343,93792344,93792345,93792349,93792347,93792348,93792349,93792350,93792351,93792352,93792353,93792354,93792355,93792359,93792357,93792358,93792359,93792390,93792391,93792392,93792393,93792394,93792395,93792399,93792397,93792398,93792399,93792370,93792371,93792372,93792373,93792374,93792375,93792379,93792377,93792378,93792379,93792380,93792381,93792382,93792383,93792384,93792385,93792389,93792387,93792388,93792389,93792390,93792391,93792392,93792393,93792394,93792395,93792399,93792397,93792398,93792399,93792400,93792401,93792402,93792403,93792404,93792405,93792409,93792407,93792408,93792409,93792410,93792411,93792412,93792413,93792414,93792415,93792419,93792417,93792418,93792419,93792420,93792421,93792422,93792423,93792424,93792425,93792429,93792427,93792428,93792429,93792430,93792431,93792432,93792433,93792434,93792435,93792439,93792437,93792438,93792439,93792440,93792441,93792442,93792443,93792444,93792445,93792449,93792447,93792448,93792449,93792450,93792451,93792452,93792453,93792454,93792455,93792459,93792457,93792458,93792459,93792490,93792491,93792492,93792493,93792494,93792495,93792499,93792497,93792498,93792499,93792470,93792471,93792472,93792473,93792474,93792475,93792479,93792477,93792478,93792479,93792480,93792481,93792482,93792483,93792484,93792485,93792489,93792487,93792488,93792489,93792490,93792491,93792492,93792493,93792494,93792495,93792499,93792497,93792498,93792499,93792500,93792501,93792502,93792503,93792504,93792505,93792509,93792507,93792508,93792509,93792510,93792511,93792512,93792513,93792514,93792515,93792519,93792517,93792518,93792519,93792520,93792521,93792522,93792523,93792524,93792525,93792529,93792527,93792528,93792529,93792530,93792531,93792532,93792533,93792534,93792535,93792539,93792537,93792538,93792539,93792540,93792541,93792542,93792543,93792544,93792545,93792549,93792547,93792548,93792549,93792550,93792551,93792552,93792553,93792554,93792555,93792559,93792557,93792558,93792559,93792590,93792591,93792592,93792593,93792594,93792595,93792599,93792597,93792598,93792599,93792570,93792571,93792572,93792573,93792574,93792575,93792579,93792577,93792578,93792579,93792580,93792581,93792582,93792583,93792584,93792585,93792589,93792587,93792588,93792589,93792590,93792591,93792592,93792593,93792594,93792595,93792599,93792597,93792598,93792599,93792900,93792901,93792902,93792903,93792904,93792905,93792909,93792907,93792908,93792909,93792910,93792911,93792912,93792913,93792914,93792915,93792919,93792917,93792918,93792919,93792920,93792921,93792922,93792923,93792924,93792925,93792929,93792927,93792928,93792929,93792930,93792931,93792932,93792933,93792934,93792935,93792939,93792937,93792938,93792939,93792940,93792941,93792942,93792943,93792944,93792945,93792949,93792947,93792948,93792949,93792950,93792951,93792952,93792953,93792954,93792955,93792959,93792957,93792958,93792959,93792990,93792991,93792992,93792993,93792994,93792995,93792999,93792997,93792998,93792999,93792970,93792971,93792972,93792973,93792974,93792975,93792979,93792977,93792978,93792979,93792980,93792981,93792982,93792983,93792984,93792985,93792989,93792987,93792988,93792989,93792990,93792991,93792992,93792993,93792994,93792995,93792999,93792997,93792998,93792999,93792700,93792701,93792702,93792703,93792704,93792705,93792709,93792707,93792708,93792709,93792710,93792711,93792712,93792713,93792714,93792715,93792719,93792717,93792718,93792719,93792720,93792721,93792722,93792723,93792724,93792725,93792729,93792727,93792728,93792729,93792730,93792731,93792732,93792733,93792734,93792735,93792739,93792737,93792738,93792739,93792740,93792741,93792742,93792743,93792744,93792745,93792749,93792747,93792748,93792749,93792750,93792751,93792752,93792753,93792754,93792755,93792759,93792757,93792758,93792759,93792790,93792791,93792792,93792793,93792794,93792795,93792799,93792797,93792798,93792799,93792770,93792771,93792772,93792773,93792774,93792775,93792779,93792777,93792778,93792779,93792780,93792781,93792782,93792783,93792784,93792785,93792789,93792787,93792788,93792789,93792790,93792791,93792792,93792793,93792794,93792795,93792799,93792797,93792798,93792799,93792800,93792801,93792802,93792803,93792804,93792805,93792809,93792807,93792808,93792809,93792810,93792811,93792812,93792813,93792814,93792815,93792819,93792817,93792818,93792819,93792820,93792821,93792822,93792823,93792824,93792825,93792829,93792827,93792828,93792829,93792830,93792831,93792832,93792833,93792834,93792835,93792839,93792837,93792838,93792839,93792840,93792841,93792842,93792843,93792844,93792845,93792849,93792847,93792848,93792849,93792850,93792851,93792852,93792853,93792854,93792855,93792859,93792857,93792858,93792859,93792890,93792891,93792892,93792893,93792894,93792895,93792899,93792897,93792898,93792899,93792870,93792871,93792872,93792873,93792874,93792875,93792879,93792877,93792878,93792879,93792880,93792881,93792882,93792883,93792884,93792885,93792889,93792887,93792888,93792889,93792890,93792891,93792892,93792893,93792894,93792895,93792899,93792897,93792898,93792899,93792900,93792901,93792902,93792903,93792904,93792905,93792909,93792907,93792908,93792909,93792910,93792911,93792912,93792913,93792914,93792915,93792919,93792917,93792918,93792919,93792920,93792921,93792922,93792923,93792924,93792925,93792929,93792927,93792928,93792929,93792930,93792931,93792932,93792933,93792934,93792935,93792939,93792937,93792938,93792939,93792940,93792941,93792942,93792943,93792944,93792945,93792949,93792947,93792948,93792949,93792950,93792951,93792952,93792953,93792954,93792955,93792959,93792957,93792958,93792959,93792990,93792991,93792992,93792993,93792994,93792995,93792999,93792997,93792998,93792999,93792970,93792971,93792972,93792973,93792974,93792975,93792979,93792977,93792978,93792979,93792980,93792981,93792982,93792983,93792984,93792985,93792989,93792987,93792988,93792989,93792990,93792991,93792992,93792993,93792994,93792995,93792999,93792997,93792998,93792999,93793000,93793001,93793002,93793003,93793004,93793005,93793009,93793007,93793008,93793009,93793010,93793011,93793012,93793013,93793014,93793015,93793019,93793017,93793018,93793019,93793020,93793021,93793022,93793023,93793024,93793025,93793029,93793027,93793028,93793029,93793030,93793031,93793032,93793033,93793034,93793035,93793039,93793037,93793038,93793039,93793040,93793041,93793042,93793043,93793044,93793045,93793049,93793047,93793048,93793049,93793050,93793051,93793052,93793053,93793054,93793055,93793059,93793057,93793058,93793059,93793090,93793091,93793092,93793093,93793094,93793095,93793099,93793097,93793098,93793099,93793070,93793071,93793072,93793073,93793074,93793075,93793079,93793077,93793078,93793079,93793080,93793081,93793082,93793083,93793084,93793085,93793089,93793087,93793088,93793089,93793090,93793091,93793092,93793093,93793094,93793095,93793099,93793097,93793098,93793099,93793100,93793101,93793102,93793103,93793104,93793105,93793109,93793107,93793108,93793109,93793110,93793111,93793112,93793113,93793114,93793115,93793119,93793117,93793118,93793119,93793120,93793121,93793122,93793123,93793124,93793125,93793129,93793127,93793128,93793129,93793130,93793131,93793132,93793133,93793134,93793135,93793139,93793137,93793138,93793139,93793140,93793141,93793142,93793143,93793144,93793145,93793149,93793147,93793148,93793149,93793150,93793151,93793152,93793153,93793154,93793155,93793159,93793157,93793158,93793159,93793190,93793191,93793192,93793193,93793194,93793195,93793199,93793197,93793198,93793199,93793170,93793171,93793172,93793173,93793174,93793175,93793179,93793177,93793178,93793179,93793180,93793181,93793182,93793183,93793184,93793185,93793189,93793187,93793188,93793189,93793190,93793191,93793192,93793193,93793194,93793195,93793199,93793197,93793198,93793199,93793200,93793201,93793202,93793203,93793204,93793205,93793209,93793207,93793208,93793209,93793210,93793211,93793212,93793213,93793214,93793215,93793219,93793217,93793218,93793219,93793220,93793221,93793222,93793223,93793224,93793225,93793229,93793227,93793228,93793229,93793230,93793231,93793232,93793233,93793234,93793235,93793239,93793237,93793238,93793239,93793240,93793241,93793242,93793243,93793244,93793245,93793249,93793247)
・・・（略）・・・
OR f.functionid IN (95050098,95050099,95050100,95050101,95050102,95050103,95050104,95050105,95050109,95050107,95050108,95050109,95050110,95050111,95050112,95050113,95050114,95050115,95050119,95050117,95050118,95050119,95050120,95050121,95050122,95050123,95050124,95050125,95050129,95050127,95050128,95050129,95050130,95050131,95050132,95050133,95050134,95050135,95050139,95050137,95050138,95050139,95050140,95050141,95050142,95050143,95050144,95050145,95050149,95050147,95050148,95050149,95050150,95050151,95050152,95050153,95050154,95050155,95050159,95050157,95050158,95050159,95050190,95050191,95050192,95050193,95050194,95050195,95050199,95050197,95050198,95050199,95050170,95050171,95050172,95050173,95050174,95050175,95050179,95050177,95050178,95050179,95050180,95050181,95050182,95050183,95050184,95050185,95050189,95050187,95050188,95050189,95050190,95050191,95050192,95050193,95050194,95050195,95050199,95050197,95050198,95050199,95050200,95050201,95050202,95050203,95050204,95050205,95050209,95050207,95050208,95050209,95050210,95050211,95050212,95050213,95050214,95050215,95050219,95050217,95050218,95050219,95050220,95050221,95050222,95050223,95050224,95050225,95050229,95050227,95050228,95050229,95050230,95050231,95050232,95050233,95050234,95050235,95050239,95050237,95050238,95050239,95050240,95050241,95050242,95050243,95050244,95050245,95050249,95050247,95050248,95050249,95050250,95050251,95050252,95050253,95050254,95050255,95050259,95050257,95050258,95050259,95050290,95050291,95050292,95050293,95050294,95050295,95050299,95050297,95050298,95050299,95050270,95050271,95050272,95050273,95050274,95050275,95050279,95050277,95050278,95050279,95050280,95050281,95050282,95050283,95050284,95050285,95050289,95050287,95050288,95050289,95050290,95050291,95050292,95050293,95050294,95050295,95050299,95050297,95050298,95050299,95050300,95050301,95050302,95050303,95050304,95050305,95050309,95050307,95050308,95050309,95050310,95050311,95050312,95050313,95050314,95050315,95050319,95050317,95050318,95050319,95050320,95050321,95050322,95050323,95050324,95050325,95050329,95050327,95050328,95050329,95050330,95050331,95050332,95050333,95050334,95050335,95050339,95050337,95050338,95050339,95050340,95050341,95050342,95050343,95050344,95050345,95050349,95050347,95050348,95050349,95050350,95050351,95050352,95050353,95050354,95050355,95050359,95050357,95050358,95050359,95050390,95050391,95050392,95050393,95050394,95050395,95050399,95050397,95050398,95050399,95050370,95050371,95050372,95050373,95050374,95050375,95050379,95050377,95050378,95050379,95050380,95050381,95050382,95050383,95050384,95050385,95050389,95050387,95050388,95050389,95050390,95050391,95050392,95050393,95050394,95050395,95050399,95050397,95050398,95050399,95050400,95050401,95050402,95050403,95050404,95050405,95050409,95050407,95050408,95050409,95050410,95050411,95050412,95050413,95050414,95050415,95050419,95050417,95050418,95050419,95050420,95050421,95050422,95050423,95050424,95050425,95050429,95050427,95050428,95050429,95050430,95050431,95050432,95050433,95050434,95050435,95050439,95050437,95050438,95050439)
)
;
下記は、上記のEXPLAIN ANALYZEの実行結果です。「Rows Removed by Index Recheck」や「lossy」というキーワードに注目してください。また、クエリの実行時間はなんと、4時間半弱かかっています。
Gather (cost=5995403.92..18943244403.98 rows=1234149 width=74) (actual time=3759.479..15778099.599 rows=1290042 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Bitmap Heap Scan on functions f (cost=5994403.92..18943119988.78 rows=514229 width=74) (actual time=1012.488..15774891.029 rows=420014 loops=3)
Recheck Cond: ((functionid = ANY ('{93790398,93790399,93790400,93790401,93790402,93790403,93790404,93790405,93790409,93790407,93790408,93790409,93790410,93790411,93790412,93790413,93790414,93790415,93790419,93790417,93790418,93790419,93790420,93790421,93790422,93790423,93790424,93790425,93790429,93790427,93790428,93790429,93790430,93790431,93790432,93790433,93790434,93790435,93790439,93790437,93790438,93790439,93790440,93790441,93790442,93790443,93790444,93790445,93790449,93790447,93790448,93790449,93790450,93790451,93790452,93790453,93790454,93790455,93790459,93790457,93790458,93790459,93790490,93790491,93790492,93790493,93790494,93790495,93790499,93790497,93790498,93790499,93790470,93790471,93790472,93790473,93790474,93790475,93790479,93790477,93790478,93790479,93790480,93790481,93790482,93790483,93790484,93790485,93790489,93790487,93790488,93790489,93790490,93790491,93790492,93790493,93790494,93790495,93790499,93790497,93790498,93790499,93790500,93790501,93790502,93790503,93790504,93790505,93790509,93790507,93790508,93790509,93790510,93790511,93790512,93790513,93790514,93790515,93790519,93790517,93790518,93790519,93790520,93790521,93790522,93790523,93790524,93790525,93790529,93790527,93790528,93790529,93790530,93790531,93790532,93790533,93790534,93790535,93790539,93790537,93790538,93790539,93790540,93790541,93790542,93790543,93790544,93790545,93790549,93790547,93790548,93790549,93790550,93790551,93790552,93790553,93790554,93790555,93790559,93790557,93790558,93790559,93790590,93790591,93790592,93790593,93790594,93790595,93790599,93790597,93790598,93790599,93790570,93790571,93790572,93790573,93790574,93790575,93790579,93790577,93790578,93790579,93790580,93790581,93790582,93790583,93790584,93790585,93790589,93790587,93790588,93790589,93790590,93790591,93790592,93790593,93790594,93790595,93790599,93790597,93790598,93790599,93790900,93790901,93790902,93790903,93790904,93790905,93790909,93790907,93790908,93790909,93790910,93790911,93790912,93790913,93790914,93790915,93790919,93790917,93790918,93790919,93790920,93790921,93790922,93790923,93790924,93790925,93790929,93790927,93790928,93790929,93790930,93790931,93790932,93790933,93790934,93790935,93790939,93790937,93790938,93790939,93790940,93790941,93790942,93790943,93790944,93790945,93790949,93790947,93790948,93790949,93790950,93790951,93790952,93790953,93790954,93790955,93790959,93790957,93790958,93790959,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790970,93790971,93790972,93790973,93790974,93790975,93790979,93790977,93790978,93790979,93790980,93790981,93790982,93790983,93790984,93790985,93790989,93790987,93790988,93790989,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790700,93790701,93790702,93790703,93790704,93790705,93790709,93790707,93790708,93790709,93790710,93790711,93790712,93790713,93790714,93790715,93790719,93790717,93790718,93790719,93790720,93790721,93790722,93790723,93790724,93790725,93790729,93790727,93790728,93790729,93790730,93790731,93790732,93790733,93790734,93790735,93790739,93790737,93790738,93790739,93790740,93790741,93790742,93790743,93790744,93790745,93790749,93790747,93790748,93790749,93790750,93790751,93790752,93790753,93790754,93790755,93790759,93790757,93790758,93790759,93790790,93790791,93790792,93790793,93790794,93790795,93790799,93790797,93790798,93790799,93790770,93790771,93790772,93790773,93790774,93790775,93790779,93790777,93790778,93790779,93790780,93790781,93790782,93790783,93790784,93790785,93790789,93790787,93790788,93790789,93790790,93790791,93790792,93790793,93790794,93790795,93790799,93790797,93790798,93790799,93790800,93790801,93790802,93790803,93790804,93790805,93790809,93790807,93790808,93790809,93790810,93790811,93790812,93790813,93790814,93790815,93790819,93790817,93790818,93790819,93790820,93790821,93790822,93790823,93790824,93790825,93790829,93790827,93790828,93790829,93790830,93790831,93790832,93790833,93790834,93790835,93790839,93790837,93790838,93790839,93790840,93790841,93790842,93790843,93790844,93790845,93790849,93790847,93790848,93790849,93790850,93790851,93790852,93790853,93790854,93790855,93790859,93790857,93790858,93790859,93790890,93790891,93790892,93790893,93790894,93790895,93790899,93790897,93790898,93790899,93790870,93790871,93790872,93790873,93790874,93790875,93790879,93790877,93790878,93790879,93790880,93790881,93790882,93790883,93790884,93790885,93790889,93790887,93790888,93790889,93790890,93790891,93790892,93790893,93790894,93790895,93790899,93790897,93790898,93790899,93790900,93790901,93790902,93790903,93790904,93790905,93790909,93790907,93790908,93790909,93790910,93790911,93790912,93790913,93790914,93790915,93790919,93790917,93790918,93790919,93790920,93790921,93790922,93790923,93790924,93790925,93790929,93790927,93790928,93790929,93790930,93790931,93790932,93790933,93790934,93790935,93790939,93790937,93790938,93790939,93790940,93790941,93790942,93790943,93790944,93790945,93790949,93790947,93790948,93790949,93790950,93790951,93790952,93790953,93790954,93790955,93790959,93790957,93790958,93790959,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790970,93790971,93790972,93790973,93790974,93790975,93790979,93790977,93790978,93790979,93790980,93790981,93790982,93790983,93790984,93790985,93790989,93790987,93790988,93790989,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93791000,93791001,93791002,93791003,93791004,93791005,93791009,93791007,93791008,93791009,93791010,93791011,93791012,93791013,93791014,93791015,93791019,93791017,93791018,93791019,93791020,93791021,93791022,93791023,93791024,93791025,93791029,93791027,93791028,93791029,93791030,93791031,93791032,93791033,93791034,93791035,93791039,93791037,93791038,93791039,93791040,93791041,93791042,93791043,93791044,93791045,93791049,93791047,93791048,93791049,93791050,93791051,93791052,93791053,93791054,93791055,93791059,93791057,93791058,93791059,93791090,93791091,93791092,93791093,93791094,93791095,93791099,93791097,93791098,93791099,93791070,93791071,93791072,93791073,93791074,93791075,93791079,93791077,93791078,93791079,93791080,93791081,93791082,93791083,93791084,93791085,93791089,93791087,93791088,93791089,93791090,93791091,93791092,93791093,93791094,93791095,93791099,93791097,93791098,93791099,93791100,93791101,93791102,93791103,93791104,93791105,93791109,93791107,93791108,93791109,93791110,93791111,93791112,93791113,93791114,93791115,93791119,93791117,93791118,93791119,93791120,93791121,93791122,93791123,93791124,93791125,93791129,93791127,93791128,93791129,93791130,93791131,93791132,93791133,93791134,93791135,93791139,93791137,93791138,93791139,93791140,93791141,93791142,93791143,93791144,93791145,93791149,93791147,93791148,93791149,93791150,93791151,93791152,93791153,93791154,93791155,93791159,93791157,93791158,93791159,93791190,93791191,93791192,93791193,93791194,93791195,93791199,93791197,93791198,93791199,93791170,93791171,93791172,93791173,93791174,93791175,93791179,93791177,93791178,93791179,93791180,93791181,93791182,93791183,93791184,93791185,93791189,93791187,93791188,93791189,93791190,93791191,93791192,93791193,93791194,93791195,93791199,93791197,93791198,93791199,93791200,93791201,93791202,93791203,93791204,93791205,93791209,93791207,93791208,93791209,93791210,93791211,93791212,93791213,93791214,93791215,93791219,93791217,93791218,93791219,93791220,93791221,93791222,93791223,93791224,93791225,93791229,93791227,93791228,93791229,93791230,93791231,93791232,93791233,93791234,93791235,93791239,93791237,93791238,93791239,93791240,93791241,93791242,93791243,93791244,93791245,93791249,93791247,93791248,93791249,93791250,93791251,93791252,93791253,93791254,93791255,93791259,93791257,93791258,93791259,93791290,93791291,93791292,93791293,93791294,93791295,93791299,93791297,93791298,93791299,93791270,93791271,93791272,93791273,93791274,93791275,93791279,93791277,93791278,93791279,93791280,93791281
・・・（略）・・・
Rows Removed by Index Recheck: 2008871
Heap Blocks: exact=14005 lossy=32788
-> BitmapOr (cost=5994403.92..5994403.92 rows=1290042 width=0) (actual time=1438.200..1442.051 rows=0 loops=1)
-> Bitmap Index Scan on functions_pkey (cost=0.00..4210.75 rows=950 width=0) (actual time=1.287..1.291 rows=950 loops=1)
Index Cond: (functionid = ANY ('{93790398,93790399,93790400,93790401,93790402,93790403,93790404,93790405,93790409,93790407,93790408,93790409,93790410,93790411,93790412,93790413,93790414,93790415,93790419,93790417,93790418,93790419,93790420,93790421,93790422,93790423,93790424,93790425,93790429,93790427,93790428,93790429,93790430,93790431,93790432,93790433,93790434,93790435,93790439,93790437,93790438,93790439,93790440,93790441,93790442,93790443,93790444,93790445,93790449,93790447,93790448,93790449,93790450,93790451,93790452,93790453,93790454,93790455,93790459,93790457,93790458,93790459,93790490,93790491,93790492,93790493,93790494,93790495,93790499,93790497,93790498,93790499,93790470,93790471,93790472,93790473,93790474,93790475,93790479,93790477,93790478,93790479,93790480,93790481,93790482,93790483,93790484,93790485,93790489,93790487,93790488,93790489,93790490,93790491,93790492,93790493,93790494,93790495,93790499,93790497,93790498,93790499,93790500,93790501,93790502,93790503,93790504,93790505,93790509,93790507,93790508,93790509,93790510,93790511,93790512,93790513,93790514,93790515,93790519,93790517,93790518,93790519,93790520,93790521,93790522,93790523,93790524,93790525,93790529,93790527,93790528,93790529,93790530,93790531,93790532,93790533,93790534,93790535,93790539,93790537,93790538,93790539,93790540,93790541,93790542,93790543,93790544,93790545,93790549,93790547,93790548,93790549,93790550,93790551,93790552,93790553,93790554,93790555,93790559,93790557,93790558,93790559,93790590,93790591,93790592,93790593,93790594,93790595,93790599,93790597,93790598,93790599,93790570,93790571,93790572,93790573,93790574,93790575,93790579,93790577,93790578,93790579,93790580,93790581,93790582,93790583,93790584,93790585,93790589,93790587,93790588,93790589,93790590,93790591,93790592,93790593,93790594,93790595,93790599,93790597,93790598,93790599,93790900,93790901,93790902,93790903,93790904,93790905,93790909,93790907,93790908,93790909,93790910,93790911,93790912,93790913,93790914,93790915,93790919,93790917,93790918,93790919,93790920,93790921,93790922,93790923,93790924,93790925,93790929,93790927,93790928,93790929,93790930,93790931,93790932,93790933,93790934,93790935,93790939,93790937,93790938,93790939,93790940,93790941,93790942,93790943,93790944,93790945,93790949,93790947,93790948,93790949,93790950,93790951,93790952,93790953,93790954,93790955,93790959,93790957,93790958,93790959,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790970,93790971,93790972,93790973,93790974,93790975,93790979,93790977,93790978,93790979,93790980,93790981,93790982,93790983,93790984,93790985,93790989,93790987,93790988,93790989,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790700,93790701,93790702,93790703,93790704,93790705,93790709,93790707,93790708,93790709,93790710,93790711,93790712,93790713,93790714,93790715,93790719,93790717,93790718,93790719,93790720,93790721,93790722,93790723,93790724,93790725,93790729,93790727,93790728,93790729,93790730,93790731,93790732,93790733,93790734,93790735,93790739,93790737,93790738,93790739,93790740,93790741,93790742,93790743,93790744,93790745,93790749,93790747,93790748,93790749,93790750,93790751,93790752,93790753,93790754,93790755,93790759,93790757,93790758,93790759,93790790,93790791,93790792,93790793,93790794,93790795,93790799,93790797,93790798,93790799,93790770,93790771,93790772,93790773,93790774,93790775,93790779,93790777,93790778,93790779,93790780,93790781,93790782,93790783,93790784,93790785,93790789,93790787,93790788,93790789,93790790,93790791,93790792,93790793,93790794,93790795,93790799,93790797,93790798,93790799,93790800,93790801,93790802,93790803,93790804,93790805,93790809,93790807,93790808,93790809,93790810,93790811,93790812,93790813,93790814,93790815,93790819,93790817,93790818,93790819,93790820,93790821,93790822,93790823,93790824,93790825,93790829,93790827,93790828,93790829,93790830,93790831,93790832,93790833,93790834,93790835,93790839,93790837,93790838,93790839,93790840,93790841,93790842,93790843,93790844,93790845,93790849,93790847,93790848,93790849,93790850,93790851,93790852,93790853,93790854,93790855,93790859,93790857,93790858,93790859,93790890,93790891,93790892,93790893,93790894,93790895,93790899,93790897,93790898,93790899,93790870,93790871,93790872,93790873,93790874,93790875,93790879,93790877,93790878,93790879,93790880,93790881,93790882,93790883,93790884,93790885,93790889,93790887,93790888,93790889,93790890,93790891,93790892,93790893,93790894,93790895,93790899,93790897,93790898,93790899,93790900,93790901,93790902,93790903,93790904,93790905,93790909,93790907,93790908,93790909,93790910,93790911,93790912,93790913,93790914,93790915,93790919,93790917,93790918,93790919,93790920,93790921,93790922,93790923,93790924,93790925,93790929,93790927,93790928,93790929,93790930,93790931,93790932,93790933,93790934,93790935,93790939,93790937,93790938,93790939,93790940,93790941,93790942,93790943,93790944,93790945,93790949,93790947,93790948,93790949,93790950,93790951,93790952,93790953,93790954,93790955,93790959,93790957,93790958,93790959,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790970,93790971,93790972,93790973,93790974,93790975,93790979,93790977,93790978,93790979,93790980,93790981,93790982,93790983,93790984,93790985,93790989,93790987,93790988,93790989,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93791000,93791001,93791002,93791003,93791004,93791005,93791009,93791007,93791008,93791009,93791010,93791011,93791012,93791013,93791014,93791015,93791019,93791017,93791018,93791019,93791020,93791021,93791022,93791023,93791024,93791025,93791029,93791027,93791028,93791029,93791030,93791031,93791032,93791033,93791034,93791035,93791039,93791037,93791038,93791039,93791040,93791041,93791042,93791043,93791044,93791045,93791049,93791047,93791048,93791049,93791050,93791051,93791052,93791053,93791054,93791055,93791059,93791057,93791058,93791059,93791090,93791091,93791092,93791093,93791094,93791095,93791099,93791097,93791098,93791099,93791070,93791071,93791072,93791073,93791074,93791075,93791079,93791077,93791078,93791079,93791080,93791081,93791082,93791083,93791084,93791085,93791089,93791087,93791088,93791089,93791090,93791091,93791092,93791093,93791094,93791095,93791099,93791097,93791098,93791099,93791100,93791101,93791102,93791103,93791104,93791105,93791109,93791107,93791108,93791109,93791110,93791111,93791112,93791113,93791114,93791115,93791119,93791117,93791118,93791119,93791120,93791121,93791122,93791123,93791124,93791125,93791129,93791127,93791128,93791129,93791130,93791131,93791132,93791133,93791134,93791135,93791139,93791137,93791138,93791139,93791140,93791141,93791142,93791143,93791144,93791145,93791149,93791147,93791148,93791149,93791150,93791151,93791152,93791153,93791154,93791155,93791159,93791157,93791158,93791159,93791190,93791191,93791192,93791193,93791194,93791195,93791199,93791197,93791198,93791199,93791170,93791171,93791172,93791173,93791174,93791175,93791179,93791177,93791178,93791179,93791180,93791181,93791182,93791183,93791184,93791185,93791189,93791187,93791188,93791189,93791190,93791191,93791192,93791193,93791194,93791195,93791199,93791197,93791198,93791199,93791200,93791201,93791202,93791203,93791204,93791205,93791209,93791207,93791208,93791209,93791210,93791211,93791212,93791213,93791214,93791215,93791219,93791217,93791218,93791219,93791220,93791221,93791222,93791223,93791224,93791225,93791229,93791227,93791228,93791229,93791230,93791231,93791232,93791233,93791234,93791235,93791239,93791237,93791238,93791239,93791240,93791241,93791242,93791243,93791244,93791245,93791249,93791247,93791248,93791249,93791250,93791251,93791252,93791253,93791254,93791255,93791259,93791257,93791258,93791259,93791290,93791291,93791292,93791293,93791294,93791295,93791299,93791297,93791298,93791299,93791270,93791271,93791272,93791273,93791274,93791275,93791279,93791277,93791278,93791279,93791280,93791281,93791282,93791283,93791284,93791285,93791289,93791287,93791288,93791289,93791290,93791291,93791292,93791293,93791294,93791295,93791299,93791297,93791298,93791299,93791300,93791301,93791302,93791303,93791304,93791305,93791309,93791307,93791308,93791309,93791310,93791311,93791312,93791313,93791314,93791315,93791319,93791317,93791318,93791319,93791320,93791321,93791322,93791323,93791324,93791325,93791329,93791327,93791328,93791329,93791330,93791331,93791332,93791333,93791334,93791335,93791339,93791337,93791338,93791339,93791340,93791341,93791342,93791343,93791344,93791345,93791349,93791347}'::bigint[]))
・・・（略）・・・
Planning Time: 1312.190 ms
Execution Time: 15778260.920 ms（約263分）
3.チューニング内容
postgresql.confのwork_memをデフォルト(4MB)から仮置きで1GBに変更しました。
=# set work_mem='1GB';
work_memを変更後は「Rows Removed by Index Recheck」や「lossy」というキーワードがなくなっています。また、クエリの実行時間は5秒程に改善されています。下記は、work_mem変更後のEXPLAN ANALYZEの結果です。
Gather (cost=5995403.92..833579292.91 rows=1234149 width=74) (actual time=3541.591..5429.409 rows=1290042 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Bitmap Heap Scan on functions f (cost=5994403.92..833451877.71 rows=514229 width=74) (actual time=999.918..2979.482 rows=420014 loops=3)
Recheck Cond: ((functionid = ANY ('{93790398,93790399,93790400,93790401,93790402,93790403,93790404,93790405,93790409,93790407,93790408,93790409,93790410,93790411,93790412,93790413,93790414,93790415,93790419,93790417,93790418,93790419,93790420,93790421,93790422,93790423,93790424,93790425,93790429,93790427,93790428,93790429,93790430,93790431,93790432,93790433,93790434,93790435,93790439,93790437,93790438,93790439,93790440,93790441,93790442,93790443,93790444,93790445,93790449,93790447,93790448,93790449,93790450,93790451,93790452,93790453,93790454,93790455,93790459,93790457,93790458,93790459,93790490,93790491,93790492,93790493,93790494,93790495,93790499,93790497,93790498,93790499,93790470,93790471,93790472,93790473,93790474,93790475,93790479,93790477,93790478,93790479,93790480,93790481,93790482,93790483,93790484,93790485,93790489,93790487,93790488,93790489,93790490,93790491,93790492,93790493,93790494,93790495,93790499,93790497,93790498,93790499,93790500,93790501,93790502,93790503,93790504,93790505,93790509,93790507,93790508,93790509,93790510,93790511,93790512,93790513,93790514,93790515,93790519,93790517,93790518,93790519,93790520,93790521,93790522,93790523,93790524,93790525,93790529,93790527,93790528,93790529,93790530,93790531,93790532,93790533,93790534,93790535,93790539,93790537,93790538,93790539,93790540,93790541,93790542,93790543,93790544,93790545,93790549,93790547,93790548,93790549,93790550,93790551,93790552,93790553,93790554,93790555,93790559,93790557,93790558,93790559,93790590,93790591,93790592,93790593,93790594,93790595,93790599,93790597,93790598,93790599,93790570,93790571,93790572,93790573,93790574,93790575,93790579,93790577,93790578,93790579,93790580,93790581,93790582,93790583,93790584,93790585,93790589,93790587,93790588,93790589,93790590,93790591,93790592,93790593,93790594,93790595,93790599,93790597,93790598,93790599,93790900,93790901,93790902,93790903,93790904,93790905,93790909,93790907,93790908,93790909,93790910,93790911,93790912,93790913,93790914,93790915,93790919,93790917,93790918,93790919,93790920,93790921,93790922,93790923,93790924,93790925,93790929,93790927,93790928,93790929,93790930,93790931,93790932,93790933,93790934,93790935,93790939,93790937,93790938,93790939,93790940,93790941,93790942,93790943,93790944,93790945,93790949,93790947,93790948,93790949,93790950,93790951,93790952,93790953,93790954,93790955,93790959,93790957,93790958,93790959,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790970,93790971,93790972,93790973,93790974,93790975,93790979,93790977,93790978,93790979,93790980,93790981,93790982,93790983,93790984,93790985,93790989,93790987,93790988,93790989,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790700,93790701,93790702,93790703,93790704,93790705,93790709,93790707,93790708,93790709,93790710,93790711,93790712,93790713,93790714,93790715,93790719,93790717,93790718,93790719,93790720,93790721,93790722,93790723,93790724,93790725,93790729,93790727,93790728,93790729,93790730,93790731,93790732,93790733,93790734,93790735,93790739,93790737,93790738,93790739,93790740,93790741,93790742,93790743,93790744,93790745,93790749,93790747,93790748,93790749,93790750,93790751,93790752,93790753,93790754,93790755,93790759,93790757,93790758,93790759,93790790,93790791,93790792,93790793,93790794,93790795,93790799,93790797,93790798,93790799,93790770,93790771,93790772,93790773,93790774,93790775,93790779,93790777,93790778,93790779,93790780,93790781,93790782,93790783,93790784,93790785,93790789,93790787,93790788,93790789,93790790,93790791,93790792,93790793,93790794,93790795,93790799,93790797,93790798,93790799,93790800,93790801,93790802,93790803,93790804,93790805,93790809,93790807,93790808,93790809,93790810,93790811,93790812,93790813,93790814,93790815,93790819,93790817,93790818,93790819,93790820,93790821,93790822,93790823,93790824,93790825,93790829,93790827,93790828,93790829,93790830,93790831,93790832,93790833,93790834,93790835,93790839,93790837,93790838,93790839,93790840,93790841,93790842,93790843,93790844,93790845,93790849,93790847,93790848,93790849,93790850,93790851,93790852,93790853,93790854,93790855,93790859,93790857,93790858,93790859,93790890,93790891,93790892,93790893,93790894,93790895,93790899,93790897,93790898,93790899,93790870,93790871,93790872,93790873,93790874,93790875,93790879,93790877,93790878,93790879,93790880,93790881,93790882,93790883,93790884,93790885,93790889,93790887,93790888,93790889,93790890,93790891,93790892,93790893,93790894,93790895,93790899,93790897,93790898,93790899,93790900,93790901,93790902,93790903,93790904,93790905,93790909,93790907,93790908,93790909,93790910,93790911,93790912,93790913,93790914,93790915,93790919,93790917,93790918,93790919,93790920,93790921,93790922,93790923,93790924,93790925,93790929,93790927,93790928,93790929,93790930,93790931,93790932,93790933,93790934,93790935,93790939,93790937,93790938,93790939,93790940,93790941,93790942,93790943,93790944,93790945,93790949,93790947,93790948,93790949,93790950,93790951,93790952,93790953,93790954,93790955,93790959,93790957,93790958,93790959,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790970,93790971,93790972,93790973,93790974,93790975,93790979,93790977,93790978,93790979,93790980,93790981,93790982,93790983,93790984,93790985,93790989,93790987,93790988,93790989,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93791000,93791001,93791002,93791003,93791004,93791005,93791009,93791007,93791008,93791009,93791010,93791011,93791012,93791013,93791014,93791015,93791019,93791017,93791018,93791019,93791020,93791021,93791022,93791023,93791024,93791025,93791029,93791027,93791028,93791029,93791030,93791031,93791032,93791033,93791034,93791035,93791039,93791037,93791038,93791039,93791040,93791041,93791042,93791043,93791044,93791045,93791049,93791047,93791048,93791049,93791050,93791051,93791052,93791053,93791054,93791055,93791059,93791057,93791058,93791059,93791090,93791091,93791092,93791093,93791094,93791095,93791099,93791097,93791098,93791099,93791070,93791071,93791072,93791073,93791074,93791075,93791079,93791077,93791078,93791079,93791080,93791081,93791082,93791083,93791084,93791085,93791089,93791087,93791088,93791089,93791090,93791091,93791092,93791093,93791094,93791095,93791099,93791097,93791098,93791099,93791100,93791101,93791102,93791103,93791104,93791105,93791109,93791107,93791108,93791109,93791110,93791111,93791112,93791113,93791114,93791115,93791119,93791117,93791118,93791119,93791120,93791121,93791122,93791123,93791124,93791125,93791129,93791127,93791128,93791129,93791130,93791131,93791132,93791133,93791134,93791135,93791139,93791137,93791138,93791139,93791140,93791141,93791142,93791143,93791144,93791145,93791149,93791147,93791148,93791149,93791150,93791151,93791152,93791153,93791154,93791155,93791159,93791157,93791158,93791159,93791190,93791191,93791192,93791193,93791194,93791195,93791199,93791197,93791198,93791199,93791170,93791171,93791172,93791173,93791174,93791175,93791179,93791177,93791178,93791179,93791180,93791181,93791182,93791183,93791184,93791185,93791189,93791187,93791188,93791189,93791190,93791191,93791192,93791193,93791194,93791195,93791199,93791197,93791198,93791199,93791200,93791201,93791202,93791203,93791204,93791205,93791209,93791207,93791208,93791209,93791210,93791211,93791212,93791213,93791214,93791215,93791219,93791217,93791218,93791219,93791220,93791221,93791222,93791223,93791224,93791225,93791229,93791227,93791228,93791229,93791230,93791231,93791232,93791233,93791234,93791235,93791239,93791237,93791238,93791239,93791240,93791241,93791242,93791243,93791244,93791245,93791249,93791247,93791248,93791249,93791250,93791251,93791252,93791253,93791254,93791255,93791259,93791257,93791258,93791259,93791290,93791291,93791292,93791293,93791294,93791295,93791299,93791297,93791298,93791299,93791270,93791271,93791272,93791273,93791274,93791275,93791279,93791277,93791278,93791279,93791280,93791281
・・・（略）・・・
Heap Blocks: exact=40992
-> BitmapOr (cost=5994403.92..5994403.92 rows=1290042 width=0) (actual time=1470.953..1471.321 rows=0 loops=1)
-> Bitmap Index Scan on functions_pkey (cost=0.00..4210.75 rows=950 width=0) (actual time=3.998..3.999 rows=950 loops=1)
Index Cond: (functionid = ANY ('{93790398,93790399,93790400,93790401,93790402,93790403,93790404,93790405,93790409,93790407,93790408,93790409,93790410,93790411,93790412,93790413,93790414,93790415,93790419,93790417,93790418,93790419,93790420,93790421,93790422,93790423,93790424,93790425,93790429,93790427,93790428,93790429,93790430,93790431,93790432,93790433,93790434,93790435,93790439,93790437,93790438,93790439,93790440,93790441,93790442,93790443,93790444,93790445,93790449,93790447,93790448,93790449,93790450,93790451,93790452,93790453,93790454,93790455,93790459,93790457,93790458,93790459,93790490,93790491,93790492,93790493,93790494,93790495,93790499,93790497,93790498,93790499,93790470,93790471,93790472,93790473,93790474,93790475,93790479,93790477,93790478,93790479,93790480,93790481,93790482,93790483,93790484,93790485,93790489,93790487,93790488,93790489,93790490,93790491,93790492,93790493,93790494,93790495,93790499,93790497,93790498,93790499,93790500,93790501,93790502,93790503,93790504,93790505,93790509,93790507,93790508,93790509,93790510,93790511,93790512,93790513,93790514,93790515,93790519,93790517,93790518,93790519,93790520,93790521,93790522,93790523,93790524,93790525,93790529,93790527,93790528,93790529,93790530,93790531,93790532,93790533,93790534,93790535,93790539,93790537,93790538,93790539,93790540,93790541,93790542,93790543,93790544,93790545,93790549,93790547,93790548,93790549,93790550,93790551,93790552,93790553,93790554,93790555,93790559,93790557,93790558,93790559,93790590,93790591,93790592,93790593,93790594,93790595,93790599,93790597,93790598,93790599,93790570,93790571,93790572,93790573,93790574,93790575,93790579,93790577,93790578,93790579,93790580,93790581,93790582,93790583,93790584,93790585,93790589,93790587,93790588,93790589,93790590,93790591,93790592,93790593,93790594,93790595,93790599,93790597,93790598,93790599,93790900,93790901,93790902,93790903,93790904,93790905,93790909,93790907,93790908,93790909,93790910,93790911,93790912,93790913,93790914,93790915,93790919,93790917,93790918,93790919,93790920,93790921,93790922,93790923,93790924,93790925,93790929,93790927,93790928,93790929,93790930,93790931,93790932,93790933,93790934,93790935,93790939,93790937,93790938,93790939,93790940,93790941,93790942,93790943,93790944,93790945,93790949,93790947,93790948,93790949,93790950,93790951,93790952,93790953,93790954,93790955,93790959,93790957,93790958,93790959,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790970,93790971,93790972,93790973,93790974,93790975,93790979,93790977,93790978,93790979,93790980,93790981,93790982,93790983,93790984,93790985,93790989,93790987,93790988,93790989,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790700,93790701,93790702,93790703,93790704,93790705,93790709,93790707,93790708,93790709,93790710,93790711,93790712,93790713,93790714,93790715,93790719,93790717,93790718,93790719,93790720,93790721,93790722,93790723,93790724,93790725,93790729,93790727,93790728,93790729,93790730,93790731,93790732,93790733,93790734,93790735,93790739,93790737,93790738,93790739,93790740,93790741,93790742,93790743,93790744,93790745,93790749,93790747,93790748,93790749,93790750,93790751,93790752,93790753,93790754,93790755,93790759,93790757,93790758,93790759,93790790,93790791,93790792,93790793,93790794,93790795,93790799,93790797,93790798,93790799,93790770,93790771,93790772,93790773,93790774,93790775,93790779,93790777,93790778,93790779,93790780,93790781,93790782,93790783,93790784,93790785,93790789,93790787,93790788,93790789,93790790,93790791,93790792,93790793,93790794,93790795,93790799,93790797,93790798,93790799,93790800,93790801,93790802,93790803,93790804,93790805,93790809,93790807,93790808,93790809,93790810,93790811,93790812,93790813,93790814,93790815,93790819,93790817,93790818,93790819,93790820,93790821,93790822,93790823,93790824,93790825,93790829,93790827,93790828,93790829,93790830,93790831,93790832,93790833,93790834,93790835,93790839,93790837,93790838,93790839,93790840,93790841,93790842,93790843,93790844,93790845,93790849,93790847,93790848,93790849,93790850,93790851,93790852,93790853,93790854,93790855,93790859,93790857,93790858,93790859,93790890,93790891,93790892,93790893,93790894,93790895,93790899,93790897,93790898,93790899,93790870,93790871,93790872,93790873,93790874,93790875,93790879,93790877,93790878,93790879,93790880,93790881,93790882,93790883,93790884,93790885,93790889,93790887,93790888,93790889,93790890,93790891,93790892,93790893,93790894,93790895,93790899,93790897,93790898,93790899,93790900,93790901,93790902,93790903,93790904,93790905,93790909,93790907,93790908,93790909,93790910,93790911,93790912,93790913,93790914,93790915,93790919,93790917,93790918,93790919,93790920,93790921,93790922,93790923,93790924,93790925,93790929,93790927,93790928,93790929,93790930,93790931,93790932,93790933,93790934,93790935,93790939,93790937,93790938,93790939,93790940,93790941,93790942,93790943,93790944,93790945,93790949,93790947,93790948,93790949,93790950,93790951,93790952,93790953,93790954,93790955,93790959,93790957,93790958,93790959,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93790970,93790971,93790972,93790973,93790974,93790975,93790979,93790977,93790978,93790979,93790980,93790981,93790982,93790983,93790984,93790985,93790989,93790987,93790988,93790989,93790990,93790991,93790992,93790993,93790994,93790995,93790999,93790997,93790998,93790999,93791000,93791001,93791002,93791003,93791004,93791005,93791009,93791007,93791008,93791009,93791010,93791011,93791012,93791013,93791014,93791015,93791019,93791017,93791018,93791019,93791020,93791021,93791022,93791023,93791024,93791025,93791029,93791027,93791028,93791029,93791030,93791031,93791032,93791033,93791034,93791035,93791039,93791037,93791038,93791039,93791040,93791041,93791042,93791043,93791044,93791045,93791049,93791047,93791048,93791049,93791050,93791051,93791052,93791053,93791054,93791055,93791059,93791057,93791058,93791059,93791090,93791091,93791092,93791093,93791094,93791095,93791099,93791097,93791098,93791099,93791070,93791071,93791072,93791073,93791074,93791075,93791079,93791077,93791078,93791079,93791080,93791081,93791082,93791083,93791084,93791085,93791089,93791087,93791088,93791089,93791090,93791091,93791092,93791093,93791094,93791095,93791099,93791097,93791098,93791099,93791100,93791101,93791102,93791103,93791104,93791105,93791109,93791107,93791108,93791109,93791110,93791111,93791112,93791113,93791114,93791115,93791119,93791117,93791118,93791119,93791120,93791121,93791122,93791123,93791124,93791125,93791129,93791127,93791128,93791129,93791130,93791131,93791132,93791133,93791134,93791135,93791139,93791137,93791138,93791139,93791140,93791141,93791142,93791143,93791144,93791145,93791149,93791147,93791148,93791149,93791150,93791151,93791152,93791153,93791154,93791155,93791159,93791157,93791158,93791159,93791190,93791191,93791192,93791193,93791194,93791195,93791199,93791197,93791198,93791199,93791170,93791171,93791172,93791173,93791174,93791175,93791179,93791177,93791178,93791179,93791180,93791181,93791182,93791183,93791184,93791185,93791189,93791187,93791188,93791189,93791190,93791191,93791192,93791193,93791194,93791195,93791199,93791197,93791198,93791199,93791200,93791201,93791202,93791203,93791204,93791205,93791209,93791207,93791208,93791209,93791210,93791211,93791212,93791213,93791214,93791215,93791219,93791217,93791218,93791219,93791220,93791221,93791222,93791223,93791224,93791225,93791229,93791227,93791228,93791229,93791230,93791231,93791232,93791233,93791234,93791235,93791239,93791237,93791238,93791239,93791240,93791241,93791242,93791243,93791244,93791245,93791249,93791247,93791248,93791249,93791250,93791251,93791252,93791253,93791254,93791255,93791259,93791257,93791258,93791259,93791290,93791291,93791292,93791293,93791294,93791295,93791299,93791297,93791298,93791299,93791270,93791271,93791272,93791273,93791274,93791275,93791279,93791277,93791278,93791279,93791280
・・・（略）・・・
Planning Time: 1404.825 ms
Execution Time: 5526.236 ms
Bitmap Scanの結果は作業メモリ(work_mem)に作成されます。work_memに収まる場合は「exactモード」になり、行の位置を示すTIDをビットマップに格納します。収まらない場合は「lossyモード」になり、該当行が格納されているページ番号をビットマップに格納します。そのため、取得データから条件を満たさないデータを排除するコスト（Recheck処理）が必要になり、性能に影響を及ぼします。
PostgreSQLしくみ分科会の下記資料を参考にしました。
PostgreSQLしくみ分科会 第20回しくみ+アプリケーション勉強会
「Bitmap Scan について」
「work_mem と lossy storag」
まとめ
Zabbix画面遷移はすぐ終わるようになりました。
Zabbixのチューニングポイントは様々ですが、今回はZabbixで使用していたデータベース、PostgreSQLをチューニングした事例紹介でした。今後も事例が出てきた場合は紹介したいと思います。