[jira] [Created] (TRAFODION-128) LP Bug: 1243877 - Select count(*) on a table with 32 million rows takes 30 minutes to execute on a cluster

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-128:
------------------------------------

             Summary: LP Bug: 1243877 - Select count(*) on a table with 32 million rows takes 30 minutes to execute on a cluster
                 Key: TRAFODION-128
                 URL: https://issues.apache.org/jira/browse/TRAFODION-128
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-general
            Reporter: Weishiun Tsai
            Assignee: Anoop Sharma
            Priority: Critical


On SEASCAPE2/sqtopl7, which has 6 nodes (n009-n014) and runs an open-source build from 2013/10/23, a select count(*) statement takes 30 minutes to execute, as shown by the shell date commands in the following example.  In comparison, the same command on a Seaquest instance from the same machine takes 6 seconds to execute.

On a Trafodion instance:
 
>>obey count.sql;
>>set schema seabase.g_wisc32;

--- SQL operation complete.
>>sh date;
Wed Oct 23 17:51:49 UTC 2013
>>select count(*) from abase;

(EXPR)
--------------------

            32000000

--- 1 row(s) selected.
>>sh date;
Wed Oct 23 18:20:30 UTC 2013
>>prepare xx from select count(*) from abase;

--- SQL command prepared.
>>explain xx;

------------------------------------------------------------------ PLAN SUMMARY
MODULE_NAME .............. DYNAMICALLY COMPILED
STATEMENT_NAME ........... XX
PLAN_ID .................. 212249310714804657
ROWS_OUT ................. 1
EST_TOTAL_COST ........... 0.01
STATEMENT ................ select count(*) from abase;


------------------------------------------------------------------ NODE LISTING
ROOT ======================================  SEQ_NO 3        ONLY CHILD 2
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est ........... 1
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  statement_index ........ 0
  affinity_value ......... 0
  max_max_cardinality .... 0
  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
  MXCI_PROCESS ........... ON
  LDAP_USERNAME
  SCHEMA ................. SEABASE.G_WISC32
  select_list ............ count(1 )


SORT_SCALAR_AGGR ==========================  SEQ_NO 2        ONLY CHILD 1
REQUESTS_IN .............. 1
ROWS_OUT ................. 1
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est ........... 1
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  aggregates ............. count(1 )


SEABASE_SCAN ==============================  SEQ_NO 1        NO CHILDREN
TABLE_NAME ............... ABASE
REQUESTS_IN .............. 1
ROWS_OUT ............... 100
EST_OPER_COST ............ 0.01
EST_TOTAL_COST ........... 0.01
DESCRIPTION
  max_card_est ......... 100
  fragment_id ............ 0
  parent_frag ............ (none)
  fragment_type .......... master
  scan_type .............. subset scan of table SEABASE.G_WISC32.ABASE
  columns ................ all
  begin_keys(incl)
  end_keys(incl)
  key_columns ............ UNIQUE2

--- SQL operation complete.

On a Seaquest instance:
>>obey count.sql;
>>set schema seabase.g_wisc32;

--- SQL operation complete.
>>sh date;
Wed Oct 23 18:45:55 UTC 2013
>>select count(*) from abase;

(EXPR)
--------------------

            32000000

--- 1 row(s) selected.
>>sh date;
Wed Oct 23 18:46:01 UTC 2013



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)