Re: Performance Evaluation of Result Cache by using TPC-DS

On Wed, 14 Apr 2021 at 17:11, Yuya Watari <watari(dot)yuya(at)gmail(dot)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