performance of analytical query
Hi folks, we have found that (probably after VACUUM ANALYZE) one analytical query starts to be slow on our production DB. Moreover, more or less the same plan is used on our testing data (how to restore our testing data is described at the end of this email), or better to say the same problem exists in both (production vs testing data) scenarios: nested loop scanning CTE several thousand times is used due to the bad estimates: https://explain.dalibo.com/plan/sER#plan/node/87 (query is included on dalibo). We improved the query guided by some intuitive thoughts about how it works and get a much faster (120x) plan https://explain.dalibo.com/plan/M21#plan/node/68. We continued with further improvement/simplification of the query but we get again a similar plan https://explain.dalibo.com/plan/nLb#plan/node/72 with nested loop and with original inferior performance. I realized that the success of the intermediate plan (M21) is somewhat random as is based on bad estimates as well. Further, I tried version forcing to not materialize CTE https://explain.dalibo.com/plan/0Tp#plan and version using PG default CTE materialization policy https://explain.dalibo.com/plan/g7M#plan/node/68. Both with no success. Do you have any idea how to get HASH JOINS in the CTE w_1p_data instead of NESTED LOOPs? * Add some statistics to not get bad estimates on "lower-level" CTEs? * Some PG configuration (I am currently only disabling JIT [1])? * Rewrite that query into several smaller pieces and use PL/pgSQL to put it together? * In a slightly more complicated function I used temporary tables to be able to narrow statistics [2] but I am afraid of system table bloating because of the huge amount of usage of this function on the production (hundred thousand of calls by day when data are to be analyzed). --- how to restore data === ERD of the schema is also available [3]. testing data as a part of an extension --- It is possible to install [4] the extension https://gitlab.com/nfiesta/nfiesta_pg and run regression tests [5] (make installcheck-all). This will create database contrib_regression_fst_1p (besides other DBs) and populate this DB with the testing data. The regression test fst_1p_data is in fact testing functionality/code, which I am experimenting with. using DB dump (without extension) -- It is also possible to create mentioned testing DB by simply downloading DB dumps from the link https://drive.google.com/drive/folders/1OVJEISpfuvbxPQG1ArDmSQxZByNZN0xG?usp=sharing followed by creating DB with postgis extension and restoring dumps: * perf_test.sql (format plain) to be used with psql \i * perf_test.dump to be used with pg_restore... Thank you for possible suggestions, Jiří. [1] https://gitlab.com/nfiesta/nfiesta_pg/-/blob/master/.gitlab-ci.yml#L10 [2] https://gitlab.com/nfiesta/nfiesta_pg/-/blob/master/functions/extschema/fn_2p_data.sql#L79 [3] https://gitlab.com/nfiesta/nfiesta_pg/-/wikis/Data-Storage#v25x. [4] https://gitlab.com/nfiesta/nfiesta_pg/-/wikis/Installation [5] https://gitlab.com/nfiesta/nfiesta_pg/-/jobs/1762550188
Re: performance of analytical query
On Thu, Nov 11, 2021 at 08:20:57PM +0100, Jiří Fejfar wrote: > Hi folks, > > we have found that (probably after VACUUM ANALYZE) one analytical query > starts to be slow on our production DB. Moreover, more or less the same > plan is used on our testing data (how to restore our testing data is > described at the end of this email), or better to say the same problem > exists in both (production vs testing data) scenarios: nested loop scanning > CTE several thousand times is used due to the bad estimates: > https://explain.dalibo.com/plan/sER#plan/node/87 (query is included on > dalibo). > Do you have any idea how to get HASH JOINS in the CTE w_1p_data instead of > NESTED LOOPs? > * Add some statistics to not get bad estimates on "lower-level" CTEs? Do you know why the estimates are bad ? Index Scan using t_map_plot_cell__cell_gid__idx on cm_plot2cell_mapping cm_plot2cell_mapping (cost=0.29..18.59 rows=381 width=12) (actual time=0.015..2.373 rows=3,898 loops=1) Index Cond: (cm_plot2cell_mapping.estimation_cell = f_a_cell.estimation_cell) Buffers: shared hit=110 I don't know, but is the estimate for this portion of the plan improved by doing: | ALTER TABLE f_a_cell ALTER estimation_cell SET STATISTICS 500; ANALYZE f_a_cell; > * In a slightly more complicated function I used temporary tables to be > able to narrow statistics [2] but I am afraid of system table bloating > because of the huge amount of usage of this function on the production > (hundred thousand of calls by day when data are to be analyzed). I would try this for sure - I think hundreds of calls per day would be no problem. If you're concerned, you could add manual calls to do (for example) VACUUM pg_attribute; after dropping the temp tables. BTW, we disable nested loops for the our analytic report queries. I have never been able to avoid pathological plans any other way.
