Are there any methods to improve the speed of the materialized view data backfilling process?

[artorias1024]

CREATE MATERIALIZED VIEW biods.mamba_allocation_order_detail_mv as
SELECT  detail.cid                                                                    AS cid
       ,main.order_str                                                                AS order_str
       ,main.origin_str                                                               AS origin_str
       ,main.submit_time                                                              AS submit_time
       ,main.approve_time                                                             AS approve_time
       ,main.stock_out_time                                                           AS stock_out_time
       ,main.stock_in_time                                                            AS stock_in_time
       ,main.price_sync_type                                                          AS price_sync_type
       ,detail.goods_id                                                               AS goods_id
       ,sku.name                                                                      AS goods_name
       ,sku.origin_category_id                                                        AS category_id
       ,category.name                                                                 AS category_name
       ,sku.origin_brand_id                                                           AS brand_id
       ,brand.name                                                                    AS brand_name
       ,sku_category_ext.ext                                                          AS category_ext
       ,main.out_warehouse_id                                                         AS out_warehouse_id
       ,warehouse_out.name                                                            AS out_warehouse_name
       ,main.in_warehouse_id                                                          AS in_warehouse_id
       ,warehouse_in.name                                                             AS in_warehouse_name
       ,main.state                                                                    AS order_state
       ,main.note                                                                     AS order_note
       ,detail.note                                                                   AS detail_note
       ,detail.goods_unit_tag                                                         AS goods_unit_tag
       ,detail.stock_out_quantity                                                     AS stock_out_quantity
       ,detail.stock_in_quantity                                                      AS stock_in_quantity
       ,detail.line_num                                                               AS line_num
       ,detail.im_state                                                               AS detail_im_state
       ,sku.im_state																  AS sku_im_state
       ,(sku_ext.sale_ext::jsonb->>'articleNumber')::VARCHAR                          AS goods_code
       ,detail.wholesale_price                                                        AS wholesale_price
       ,detail.wholesale_amount                                                       AS wholesale_amount
       ,detail.production_date                                                        AS expected_production_date
       ,detail.diff_move_price                                                        AS diff_move_price
       ,detail.diff_move_amount                                                       AS diff_move_amount
       ,main.dept_id                                                                  AS dept_id
       ,main.operator_id                                                              AS operator_id
       ,(sku_ext.sale_ext::jsonb->>'unitFactor')::DECIMAL                             AS pkg_unit_factor
       ,(sku_ext.sale_ext::jsonb->>'midUnitFactor')::DECIMAL                          AS mid_unit_factor
       ,(main.ext::jsonb->>'deliveryUserId')::BIGINT                                  AS delivery_user_id
       ,(detail.ext::jsonb->>'referencePrice')::DECIMAL                               AS reference_price
       ,(detail.ext::jsonb->>'referenceAmount')::DECIMAL                              AS reference_amount
FROM mamba_allocation_order_detail_rt detail
INNER JOIN mamba_allocation_order_main_rt main
ON detail.cid = main.cid AND detail.order_str = main.order_str
LEFT JOIN mamba_custom_sku_rt sku
ON main.cid = sku.cid AND sku.goods_id = detail.goods_id 
LEFT JOIN mamba_custom_sku_ext_rt sku_ext
ON detail.cid = sku_ext.cid AND sku.id = sku_ext.sku_id
LEFT JOIN mamba_category_rt category
ON main.cid = category.cid AND sku.origin_category_id = category.origin_category_id
LEFT JOIN mamba_category_ext_rt sku_category_ext
ON main.cid = sku_category_ext.cid AND category.id = sku_category_ext.category_id
LEFT JOIN mamba_doc_brand_rt brand
ON main.cid = brand.cid AND sku.origin_brand_id = brand.id
LEFT JOIN mamba_doc_warehouse_rt warehouse_out
ON main.cid = warehouse_out.cid AND main.out_warehouse_id = warehouse_out.id
LEFT JOIN mamba_doc_warehouse_rt warehouse_in
ON main.cid = warehouse_in.cid AND main.in_warehouse_id = warehouse_in.id;

Above is my code, below are the metrics during the build process.

The resource status of my cluster is as follows：
Compute Node 14core 32G 4
Compactor Node 6core 8G 4

[lmatz]

Do you observe high join amplification on Grafana?

[artorias1024]

yes, The log also exhibits high join amplification record

[lmatz]

#20967 please checkout this, if your data pattern allows, it can avoid high join amplification during backfilling
completely.