ERROR 3587: Insufficient resources to execute plan on pool

Increase your memory or modify your query that will use in merge join and not in hash join (if hash join is used)
You can validate what in use - HASH  or MERGE join by executing query EXPLAIN.
http://vertica-forums.com/viewtopic.php?f=3&t=156&p=285

Requested = 7019241    => 7.7GB
Free = 6520059              => 6.2GB (MAX by resource pool 6.5GB)

So query require more memory than you provide.

Thank You Daniel, I'll try the MERGE JOIN first.

O.K, I have tried building the correct projections (using order by) and now the explain plan actually showed that it using MERGE.
however, i am still facing same error, even worst now, it demand 10G of ram:

ERROR 3587:  Insufficient resources to execute plan on pool batch_etl [Request Too Large:Memory(KB) Exceeded: Requested = 9812391, Free = 6520059 (Limit = 6520059, Used = 0)]

here how the MERGE explain plan looks like:
+-DML MERGE [Cost: 0, Rows: 0]|  Target Projection: hpswadmmockTenanttrg.OPPORTUNITY_FACT_p1|  Target Projection: hpswadmmockTenanttrg.OPPORTUNITY_FACT_p1_b1|  Target Projection: hpswadmmockTenanttrg.OPPORTUNITY_FACT_p1_b0|  Target Prep:| +---> JOIN MERGEJOIN(inputs presorted) [RightOuter] [Cost: 749K, Rows: 12K (NO STATISTICS)] (PATH ID: 1)| |      Join Cond: (OPPORTUNITY_FACT.MD_OPPORTUNITY_GL_PK = VAL(2)) AND (OPPORTUNITY_FACT.MD_START_DATE = VAL(2)) AND (OPPORTUNITY_FACT.MD_CP_ID = VAL(2))| | +-- Outer -> STORAGE ACCESS for <No Alias> [Cost: 467K, Rows: 10K (NO STATISTICS)] (PATH ID: 2)| | |      Projection: hpswadmmockTenanttrg.OPPORTUNITY_FACT_p1
| | |      Runtime Filters: (SIP1(MergeJoin): OPPORTUNITY_FACT.MD_OPPORTUNITY_GL_PK), (SIP2(MergeJoin): OPPORTUNITY_FACT.MD_START_DATE), (SIP3(MergeJoin): OPPORTUNITY_FACT.MD_CP_ID), (SIP4(MergeJoin): OPPORTUNITY_FACT.MD_OPPORTUNITY_GL_PK, OPPORTUNITY_FACT.MD_START_DATE, OPPORTUNITY_FACT.MD_CP_ID)| | +-- Inner -> SELECT [Cost: 106K, Rows: 12K] (PATH ID: 3)| | | +---> STORAGE ACCESS for shd [Cost: 106K, Rows: 12K] (PATH ID: 4)| | | |      Projection: hpswadmmockTenantstg.TITAN_OPPORTUNITY_SHD_FACT_4merge_p1

Please advice....

and how about "NO STATISTICS" ?

Done... before every run, for the tables and it's projections.
one more thing I tried to reduce the amount of rows from 12K to 1K it didn't help, same error
Remember the tables have 660 columns each.

Same as https://community.vertica.com/vertica/topics/resource_limit_not_being_enforce_for_analytic_functions_with_order_by

We are waiting for some vertica Eng to explain the behave .

Any one please please  please   ?

Hi all (and hi future readers),

Eli kindly linked to another relevant question in the previous post.  Discussion of this issue has continued on that question, and there is some more information there.  In order to reduce duplication/confusion, please follow that question instead.

If you think this is an unrelated issue, feel free to post some more details and bring it back up here.

Thanks,
Adam