trafodion-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Eric Owhadi <eric.owh...@esgyn.com>
Subject perf optimization in Predicate Push down V2 and MDAM problem
Date Fri, 08 Jan 2016 00:02:40 GMT
OK so now I am seeing a hang on executor/test025

And I have investigated it to be related to the optimization I do to limit
the columns retrieved to strict minimum.

Here is a working scenario, where I removed the optimization code:



Apache Trafodion Conversational Interface 2.0.0

Copyright (c) 2015 Apache Software Foundation

>>cqd hbase_filter_preds '2';



--- SQL operation complete.

>>control query shape groupby(scan('t025m2', MDAM_COLUMNS ALL));



--- SQL operation complete.

>>explain select count(*),sum(s),min(s),max(s),avg(s) from t025m2

      where r > 4 and r < 60;+>



------------------------------------------------------------------ PLAN
SUMMARY

MODULE_NAME .............. DYNAMICALLY COMPILED

STATEMENT_NAME ........... NOT NAMED

PLAN_ID .................. 212318970462810408

ROWS_OUT ................. 1

EST_TOTAL_COST ........... 0.08

STATEMENT ................ select count(*),sum(s),min(s),max(s),avg(s)

                           from t025m2

                           where r > 4 and r < 60;

MUST_MATCH ............... REL_ANY_GROUP(forced scan(T025M2, index T025M2))





------------------------------------------------------------------ NODE
LISTING

ROOT ======================================  SEQ_NO 3        ONLY CHILD 2

REQUESTS_IN .............. 1

ROWS_OUT ................. 1

EST_OPER_COST ............ 0

EST_TOTAL_COST ........... 0.08

DESCRIPTION

  max_card_est ........... 1

  fragment_id ............ 0

  parent_frag ............ (none)

  fragment_type .......... master

  statement_index ........ 0

  affinity_value ......... 0

  max_max_cardinality ... 11

  total_overflow_size .... 0.00 KB

  xn_access_mode ......... read_only

  xn_autoabort_interval    0

  auto_query_retry ....... enabled

  plan_version ....... 2,600

  embedded_arkcmp ........ used

  IS_SQLCI ............... ON

  LDAP_USERNAME

  HBASE_FILTER_PREDS ..... 2

  GENERATE_EXPLAIN ....... ON

  ObjectUIDs ............. 8537785827430889366

  select_list ............ count(1 ), sum(TRAFODION.SCH.T025M2.S),

                             min(TRAFODION.SCH.T025M2.S),

                             max(TRAFODION.SCH.T025M2.S),


cast(cast((cast((cast((cast(sum(TRAFODION.SCH.T025

                             M2.S)) * 10000 ...0)) /

                             cast(count_nonull(TRAFODION.SCH.T025M2.S)))) /

                             10000 ...0)))





SORT_SCALAR_AGGR ==========================  SEQ_NO 2        ONLY CHILD 1

REQUESTS_IN .............. 1

ROWS_OUT ................. 1

EST_OPER_COST ............ 0.01

EST_TOTAL_COST ........... 0.08

DESCRIPTION

  max_card_est ........... 1

  fragment_id ............ 0

  parent_frag ............ (none)

  fragment_type .......... master

  aggregates ............. count(1 ), sum(TRAFODION.SCH.T025M2.S),

                             min(TRAFODION.SCH.T025M2.S),

                             max(TRAFODION.SCH.T025M2.S),

                             count_nonull(TRAFODION.SCH.T025M2.S)





TRAFODION_SCAN ============================  SEQ_NO 1        NO CHILDREN

TABLE_NAME ............... T025M2

REQUESTS_IN .............. 1

ROWS_OUT ................ 11

EST_OPER_COST ............ 0.08

EST_TOTAL_COST ........... 0.08

DESCRIPTION

  max_card_est .......... 11

  fragment_id ............ 0

  parent_frag ............ (none)

  fragment_type .......... master

  scan_type .............. subset scan limited by mdam of table

                             TRAFODION.SCH.T025M2

  object_type ............ Trafodion

  cache_size ........... 100

  rows_accessed ......... 11

  column_retrieved ....... #1:1,#1:2,#1:3

  key_columns ............ Q, R

  mdam_disjunct .......... (R > 4) and (R < 60)



--- SQL operation complete.

>>select count(*),sum(s),min(s),max(s),avg(s)

                           from t025m2

                           where r > 4 and r < 60;

+>+>

(EXPR)                (EXPR)                (EXPR)       (EXPR)       (EXPR)

--------------------  --------------------  -----------  -----------
--------------------



                   3                    65            5
50                    21



--- 1 row(s) selected.

>>



Now if I put my optimization back in, I get for column retrieved

column_retrieved ....... #1:2,#1:3



so I am not returning Q, logically so, since only s and r are involved in
the select statement.



Is there a reason why for the case of MDAM scan, I should always return all
key columns? (so bypass the optimization for MDAM scan)?



Thanks in advance for the help,
Eric

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message