The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to speed up count query??

HyeontaeJuHyeontaeJu Vertica Customer

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

Answers

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file