ERROR 3587: Insufficient resources to execute plan on pool

While executing MERGE (that include 660 COLUMNS) I am getting the above error. (Insufficient resources to execute plan on pool)
I am using 4 Cores 8GB none cluster machine, redhat 6.3
I have created a pool named ETL_BATCH and assign user to work with this pool:

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

can you advice please?


  • Options
    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.

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

    So query require more memory than you provide.

  • Options
    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
  • Options
    Please advice....
  • Options
    and how about "NO STATISTICS" ?

  • Options
    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. 
  • Options
    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   ?

  • Options
    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.


Leave a Comment

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