Uploaded image for project: 'Apache Trafodion (Retired)'
  1. Apache Trafodion (Retired)
  2. TRAFODION-128

LP Bug: 1243877 - Select count(*) on a table with 32 million rows takes 30 minutes to execute on a cluster

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • None
    • 1.0 (pre-incubation)
    • sql-general
    • None

    Description

      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

      Attachments

        Activity

          People

            Unassigned Unassigned
            WTsai Weishiun Tsai
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: