vertica-not fit in memory

ERROR: Join [(web_test.Web_Dim_Domain x web.JSLogRecord) using Web_Dim_Domain_b0 and JSLogRecord_DBD_5_seg_c_stg_web2_b1 (PATH ID: 8)] inner partition did not fit in memory; value [cp.eastmoney.com]

解决方法

explain这个sql

 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [Cost: 815, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
 | Join Cond: (dim.y = ext.z)
 | Materialize at Output: fact.x
 | Execute on: All Nodes
 | +-- Outer -> JOIN MERGEJOIN(inputs presorted) [Cost: 408, Rows: 10K (NO STATISTICS)] (PATH ID: 2)
 | | Join Cond: (fact.x = dim.y)
 | | Execute on: All Nodes
 | | +-- Outer -> STORAGE ACCESS for fact [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 3)
 | | | Projection: public.fact_super
 | | | Materialize: fact.x
 | | | Execute on: All Nodes
 | | +-- Inner -> STORAGE ACCESS for dim [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 4)
 | | | Projection: public.dim_super
 | | | Materialize: dim.y
 | | | Execute on: All Nodes
 | +-- Inner -> STORAGE ACCESS for ext [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 5)
 | | Projection: public.ext_super
 | | Materialize: ext.z
 | | Execute on: All Nodes

注意这里NO STATISTICS是没用收集统计信息

没用收集统计信息导致sql占用内存超过既定内存，从而导致上述报错

 select ANALYZE_STATISTICS('web.jslogrecord')

 select ANALYZE_STATISTICS('web_test.Dim_Ip_Borui')

 select ANALYZE_STATISTICS('web_test.web_dim_domain')

 select ANALYZE_STATISTICS('common.Cmm_Dim_Geography')

 select ANALYZE_STATISTICS('web.Web_Dim_Time')

只需收集全部统计信息即可解决