How to speed up count query??

HyeontaeJu

How to speed up count query??

QUERY PLAN DESCRIPTION:

explain SELECT count(*) FROM newbiz_product_dev.products a INNER JOIN newbiz_product_dev.products_image b ON a.product_id = b.product_id INNER JOIN newbiz_product_dev.ws_product_brand_map c ON a.product_id = c.product_id INNER JOIN newbiz_product_dev.ws_product_category_map d ON a.product_id = d.product_id INNER JOIN newbiz_product_dev.ws_category e ON e.master_code = d.cate_mst_cd

Access Path:
+-GROUPBY NOTHING [Cost: 85K, Rows: 1] (PATH ID: 1)
| Aggregates: count(*)
| +---> JOIN MERGEJOIN(inputs presorted) [Cost: 82K, Rows: 2M] (PATH ID: 2)
| | Join Cond: (a.product_id = c.product_id)
| | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 61K, Rows: 2M] (PATH ID: 3)
| | | Join Cond: (a.product_id = b.product_id)
| | | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 37K, Rows: 2M] (PATH ID: 4)
| | | | Join Cond: (a.product_id = d.product_id)
| | | | Materialize at Input: d.product_id
| | | | +-- Outer -> JOIN HASH [Cost: 5K, Rows: 2M] (PATH ID: 5)
| | | | | Join Cond: (e.master_code = d.cate_mst_cd)
| | | | | +-- Outer -> STORAGE ACCESS for d [Cost: 4K, Rows: 2M] (PATH ID: 6)
| | | | | | Projection: newbiz_product_dev.ws_product_category_map_super
| | | | | | Materialize: d.cate_mst_cd
| | | | | | Runtime Filters: (SIP4(HashJoin): d.cate_mst_cd), (SIP3(MergeJoin): d.product_id)
| | | | | +-- Inner -> STORAGE ACCESS for e [Cost: 37, Rows: 5K] (PATH ID: 7)
| | | | | | Projection: newbiz_product_dev.ws_category_super
| | | | | | Materialize: e.master_code
| | | | +-- Inner -> STORAGE ACCESS for a [Cost: 16K, Rows: 2M] (PATH ID: 8)
| | | | | Projection: newbiz_product_dev.products_DBD_1_rep_image_search
| | | | | Materialize: a.product_id
| | | | | Runtime Filters: (SIP2(MergeJoin): a.product_id), (SIP1(MergeJoin): a.product_id)
| | | +-- Inner -> STORAGE ACCESS for b [Cost: 19K, Rows: 2M] (PATH ID: 9)
| | | | Projection: newbiz_product_dev.products_image_DBD_1_rep_wshop_prod_srch2
| | | | Materialize: b.product_id
| | +-- Inner -> STORAGE ACCESS for c [Cost: 16K, Rows: 2M] (PATH ID: 10)
| | | Projection: newbiz_product_dev.ws_product_brand_map_DBD_6_rep_live_product_dl
| | | Materialize: c.product_id

Hibiki

Please read the following page. I hope you will find any hints to tune your query.
https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AnalyzingData/Optimizations/OptimizingJOINQueries.htm