Hello David,
Thank you for your reply.
> 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.
Thanks for your analysis. I understood why HashJoin was not selected
in this query plan.
> 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:
I listed all indexes on my machine by executing your query. I attached
the result to this e-mail. I hope it will help you.
Best regards,
Yuya Watari