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
pg_get_indexdef
----------------------------------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX customer_address_pkey ON public.customer_address USING
btree (ca_address_sk)
CREATE UNIQUE INDEX customer_demographics_pkey ON public.customer_demographics
USING btree (cd_demo_sk)
CREATE UNIQUE INDEX date_dim_pkey ON public.date_dim USING btree (d_date_sk)
CREATE UNIQUE INDEX ship_mode_pkey ON public.ship_mode USING btree
(sm_ship_mode_sk)
CREATE UNIQUE INDEX time_dim_pkey ON public.time_dim USING btree (t_time_sk)
CREATE UNIQUE INDEX reason_pkey ON public.reason USING btree (r_reason_sk)
CREATE UNIQUE INDEX income_band_pkey ON public.income_band USING btree
(ib_income_band_sk)
CREATE UNIQUE INDEX item_pkey ON public.item USING btree (i_item_sk)
CREATE UNIQUE INDEX store_pkey ON public.store USING btree (s_store_sk)
CREATE INDEX store_s_closed_date_sk_idx ON public.store USING btree
(s_closed_date_sk)
CREATE INDEX call_center_cc_closed_date_sk_idx ON public.call_center USING
btree (cc_closed_date_sk)
CREATE INDEX call_center_cc_open_date_sk_idx ON public.call_center USING btree
(cc_open_date_sk)
CREATE UNIQUE INDEX call_center_pkey ON public.call_center USING btree
(cc_call_center_sk)
CREATE INDEX customer_c_current_cdemo_sk_idx ON public.customer USING btree
(c_current_cdemo_sk)
CREATE UNIQUE INDEX customer_pkey ON public.customer USING btree
(c_customer_sk)
CREATE INDEX customer_c_first_shipto_date_sk_idx ON public.customer USING
btree (c_first_shipto_date_sk)
CREATE INDEX customer_c_first_sales_date_sk_idx ON public.customer USING btree
(c_first_sales_date_sk)
CREATE INDEX customer_c_current_hdemo_sk_idx ON public.customer USING btree
(c_current_hdemo_sk)
CREATE INDEX customer_c_current_addr_sk_idx ON public.customer USING btree
(c_current_addr_sk)
CREATE INDEX store_returns_sr_store_sk_idx ON public.store_returns USING btree
(sr_store_sk)
CREATE INDEX store_returns_sr_return_time_sk_idx ON public.store_returns USING
btree (sr_return_time_sk)
CREATE INDEX store_returns_sr_returned_date_sk_idx ON public.store_returns
USING btree (sr_returned_date_sk)
CREATE INDEX store_returns_sr_reason_sk_idx ON public.store_returns USING
btree (sr_reason_sk)
CREATE INDEX store_returns_sr_item_sk_idx ON public.store_returns USING btree
(sr_item_sk)
CREATE INDEX store_returns_sr_hdemo_sk_idx ON public.store_returns USING btree
(sr_hdemo_sk)
CREATE INDEX store_returns_sr_customer_sk_idx ON public.store_returns USING
btree (sr_customer_sk)
CREATE INDEX store_returns_sr_cdemo_sk_idx ON public.store_returns USING btree
(sr_cdemo_sk)
CREATE INDEX store_returns_sr_addr_sk_idx ON public.store_returns USING btree
(sr_addr_sk)
CREATE UNIQUE INDEX store_returns_pkey ON public.store_returns USING btree
(sr_item_sk, sr_ticket_number)
CREATE UNIQUE INDEX household_demographics_pkey ON
public.household_demographics USING btree (hd_demo_sk)
CREATE INDEX household_demographics_hd_income_band_sk_idx ON
public.household_demographics USING btree (hd_income_band_sk)
CREATE UNIQUE INDEX promotion_pkey ON public.promotion USING btree (p_promo_sk)
CREATE INDEX promotion_p_end_date_sk_idx ON public.promotion USING btree
(p_end_date_sk)
CREATE INDEX promotion_p_start_date_sk_idx ON public.promotion USING btree
(p_start_date_sk)
CREATE INDEX promotion_p_item_sk_idx ON public.promotion USING btree
(p_item_sk)
CREATE UNIQUE INDEX catalog_page_pkey ON public.catalog_page USING btree
(cp_catalog_page_sk)
CREATE INDEX catalog_page_cp_end_date_sk_idx ON public.catalog_page USING
btree (cp_end_date_sk)
CREATE INDEX catalog_page_cp_start_date_sk_idx ON public.catalog_page USING
btree (cp_start_date_sk)
CREATE INDEX inventory_inv_item_sk_idx ON public.inventory USING btree
(inv_item_sk)
CREATE INDEX inventory_inv_date_sk_idx ON public.inventory USING btree
(inv_date_sk)
CREATE INDEX inventory_inv_warehouse_sk_idx ON public.inventory USING btree
(inv_warehouse_sk)
CREATE UNIQUE INDEX inventory_pkey ON public.inventory USING btree
(inv_date_sk, inv_item_sk, inv_warehouse_sk)
CREATE INDEX catalog_returns_cr_returning_cdemo_sk_idx ON
public.catalog_returns USING btree (cr_returning_cdemo_sk)
CREATE INDEX catalog_returns_cr_returning_addr_sk_idx ON
public.catalog_returns USING btree (cr_returning_addr_sk)
CREATE INDEX catalog_returns_cr_returned_time_sk_idx ON public.catalog_returns
USING btree (cr_returned_time_sk)
CREATE INDEX catalog_returns_cr_returned_date_sk_idx ON public.catalog_returns
USING btree (cr_returned_date_sk)
CREATE INDEX catalog_returns_cr_refunded_hdemo_sk_idx ON
public.catalog_returns USING btree (cr_refunded_hdemo_sk)
CREATE UNIQUE INDEX catalog_returns_pkey ON public.catalog_returns USING btree
(cr_item_sk, cr_order_number)
CREATE INDEX catalog_returns_cr_refunded_customer_sk_idx ON
public.catalog_returns USING btree (cr_refunded_customer_sk)
CREATE INDEX catalog_returns_cr_returning_customer_sk_idx ON
public.catalog_returns USING btree (cr_returning_customer_sk)
CREATE INDEX catalog_returns_cr_refunded_cdemo_sk_idx ON
public.catalog_returns USING btree (cr_refunded_cdemo_sk)
CREATE INDEX catalog_returns_cr_refunded_addr_sk_idx ON public.catalog_returns
USING btree (cr_refunded_addr_sk)
CREATE INDEX catalog_returns_cr_reason_sk_idx ON public.catalog_returns USING
btree (cr_reason_sk)
CREATE INDEX catalog_returns_cr_item_sk_idx ON public.catalog_returns USING
btree (cr_item_sk)
CREATE INDEX catalog_returns_cr_catalog_page_sk_idx ON public.catalog_returns
USING btree (cr_catalog_page_sk)
CREATE INDEX catalog_returns_cr_call_center_sk_idx ON public.catalog_returns
USING btree (cr_call_center_sk)
CREATE INDEX catalog_returns_cr_warehouse_sk_idx ON public.catalog_returns
USING btree (cr_warehouse_sk)
CREATE INDEX catalog_returns_cr_ship_mode_sk_idx ON public.catalog_returns
USING btree (cr_ship_mode_sk)
CREATE INDEX catalog_returns_cr_returning_hdemo_sk_idx ON
public.catalog_returns USING btree (cr_returning_hdemo_sk)
CREATE UNIQUE INDEX catalog_sales_pkey ON public.catalog_sales USING btree
(cs_item_sk, cs_order_number)
CREATE INDEX catalog_sales_cs_ship_customer_sk_idx ON public.catalog_sales
USING btree (cs_ship_customer_sk)
CREATE INDEX catalog_sales_cs_bill_cdemo_sk_idx ON public.catalog_sales USING
btree (cs_bill_cdemo_sk)
CREATE INDEX catalog_sales_cs_bill_customer_sk_idx ON public.catalog_sales
USING btree (cs_bill_customer_sk)
CREATE INDEX catalog_sales_cs_bill_hdemo_sk_idx ON public.catalog_sales USING
btree (cs_bill_hdemo_sk)
CREATE INDEX catalog_sales_cs_call_center_sk_idx ON public.catalog_sales USING
btree (cs_call_center_sk)
CREATE INDEX catalog_sales_cs_catalog_page_sk_idx ON public.catalog_sales
USING btree (cs_catalog_page_sk)
CREATE INDEX catalog_sales_cs_item_sk_idx ON public.catalog_sales USING btree
(cs_item_sk)
CREATE INDEX catalog_sales_cs_promo_sk_idx ON public.catalog_sales USING btree
(cs_promo_sk)
CREATE INDEX catalog_sales_cs_ship_addr_sk_idx ON public.catalog_sales USING
btree (cs_ship_addr_sk)
CREATE INDEX catalog_sales_cs_ship_cdemo_sk_idx ON public.catalog_sales USING
btree (cs_ship_cdemo_sk)
CREATE INDEX catalog_sales_cs_bill_addr_sk_idx ON public.catalog_sales USING
btree (cs_bill_addr_sk)
CREATE INDEX catalog_sales_cs_ship_date_sk_idx ON public.catalog_sales USING
btree (cs_ship_date_sk)
CREATE INDEX catalog_sales_cs_ship_hdemo_sk_idx ON public.catalog_sales USING
btree (cs_ship_hdemo_sk)
CREATE INDEX catalog_sales_cs_ship_mode_sk_idx ON public.catalog_sales USING
btree (cs_ship_mode_sk)
CREATE INDEX catalog_sales_cs_sold_date_sk_idx ON public.catalog_sales USING
btree (cs_sold_date_sk)
CREATE INDEX catalog_sales_cs_sold_time_sk_idx ON public.catalog_sales USING
btree (cs_sold_time_sk)
CREATE INDEX catalog_sales_cs_warehouse_sk_idx ON public.catalog_sales USING
btree (cs_warehouse_sk)
CREATE INDEX store_sales_ss_promo_sk_idx ON public.store_sales USING btree
(ss_promo_sk)
CREATE INDEX store_sales_ss_item_sk_idx ON public.store_sales USING btree
(ss_item_sk)
CREATE INDEX store_sales_ss_hdemo_sk_idx ON public.store_sales USING btree
(ss_hdemo_sk)
CREATE INDEX store_sales_ss_customer_sk_idx ON public.store_sales USING btree
(ss_customer_sk)
CREATE INDEX store_sales_ss_cdemo_sk_idx ON public.store_sales USING btree
(ss_cdemo_sk)
CREATE INDEX store_sales_ss_addr_sk_idx ON public.store_sales USING btree
(ss_addr_sk)
CREATE UNIQUE INDEX store_sales_pkey ON public.store_sales USING btree
(ss_item_sk, ss_ticket_number)
CREATE INDEX store_sales_ss_sold_date_sk_idx ON public.store_sales USING btree
(ss_sold_date_sk)
CREATE INDEX store_sales_ss_sold_time_sk_idx ON public.store_sales USING btree
(ss_sold_time_sk)
CREATE INDEX store_sales_ss_store_sk_idx ON public.store_sales USING btree
(ss_store_sk)
(87 rows)
