[
https://issues.apache.org/jira/browse/TRAFODION-1468?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14723719#comment-14723719
]
Qifan Chen commented on TRAFODION-1468:
---------------------------------------
The following comparison on MDAM costs when RANGE SPE is on and off confirms
that the costing in the range spec ON case is over-estimated.
===== RANGE SPEC on
MDAM cost object vector
------------------------------
(gdb) p scmCost_->display()
SCM Last Row Cost information
[Thread 0x7fffd6980700 (LWP 1066) exited]
CPUTime=0
IOTime=0
MSGTime=0
idleTime=0
tuple processed=8.81056e+08
tuple produced=2.93697e+08
tuple sent=5.8737e+08
IO rand=8.20867e+06
IO seq=91584
num Probes=1
========= RANGE SPEC OFF
(gdb) p scmCost_->display()
SCM Last Row Cost information
CPUTime=0
IOTime=0
MSGTime=0
idleTime=0
tuple processed=635019
tuple produced=223238
tuple sent=423346
IO rand=231
IO seq=224
num Probes=1
subset Scan vector: (same for both RANGE SPEC on and OFF)
----------------------
SCM Last Row Cost information
CPUTime=0
IOTime=0
MSGTime=0
idleTime=0
tuple processed=5.01317e+08
tuple produced=1.67117e+08
tuple sent=3.34211e+08
IO rand=0
IO seq=91584
num Probes=1
> MDAM plan is not chosen
> ------------------------
>
> Key: TRAFODION-1468
> URL: https://issues.apache.org/jira/browse/TRAFODION-1468
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Reporter: Qifan Chen
> Assignee: Qifan Chen
> Labels: performance
>
> The query is as follows.
> select "cliente_CIC", "tipoMovimiento", sum("monto") from T where
> ("cliente_CIC" = '000003566661') and "fechaOrigen" between cast('2013-12-01'
> as timestamp(6)) and cast('2013-12-31' as timestamp(6)) group by 1, 2 order
> by 1, 2;
> Part of the DDL looks like:
> create table T
> (... ...
> , PRIMARY KEY
> (
> "cliente_CIC"
> , "operacion_identificacionOperacion"
> , "fechaOrigen"
> , "nemonico"
> , "claveUnicaRegistroJNL"
> )
> )
> SALT USING 16 PARTITIONS ON
> (
> "cliente_CIC"
> , "operacion_identificacionOperacion"
> )
> DIVISION BY
> (
> date_part('YEARMONTH', "fechaOrigen")
> )
> And stats are available for all leading key column groups.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
