On Wed, 14 Apr 2021 at 17:11, Yuya Watari <watari.y...@gmail.com> wrote: > I ran query 62 by "EXPLAIN (ANALYZE, TIMING OFF)" and normally. I > attached these execution results to this e-mail. At this time, I > executed each query only once (not twice). The results are as follows.
Thanks for running that again. I see from the EXPLAIN ANALYZE output that the planner did cost the Result Cache plan slightly more expensive than the Hash Join plan. It's likely that add_path() did not consider the Hash Join plan to be worth keeping because it was not more than 1% better than the Result Cache plan. STD_FUZZ_FACTOR is set so new paths need to be at least 1% better than existing paths for them to be kept. That's pretty unfortunate and that alone does not mean the costs are incorrect. It would be good to know if that's the case for the other queries too. To test that, I've set up TPC-DS locally, however, it would be good if you could send me the list of indexes that you've created. I see the tool from the transaction processing council for TPC-DS only comes with the list of tables. Can you share the output of: select pg_get_indexdef(indexrelid) from pg_index where indrelid::regclass in ( 'call_center', 'catalog_page', 'catalog_returns', 'catalog_sales', 'customer', 'customer_address', 'customer_demographics', 'date_dim', 'dbgen_version', 'household_demographics', 'income_band', 'inventory', 'item', 'promotion', 'reason', 'ship_mode', 'store', 'store_returns', 'store_sales', 'time_dim') order by indrelid; from your TPC-DS database? David
