摘要：通常跑批加工场景下，都是大数量做关联操作，通常不建议使用索引。有些时候因为计划误判导致使用索引的可能会导致严重的性能问题。本文从一个典型的索引导致性能的场景重发，剖析此类问题的特征，定位方法和解决方法
本文分享自华为云社区《GaussDB(DWS)性能调优：indexscan导致的性能问题识别与优化 #【玩转PB级数仓GaussDB(DWS)】》，作者： 譡里个檔 。
通常跑批加工场景下，都是大数量做关联操作，通常不建议使用索引。有些时候因为计划误判导致使用索引的可能会导致严重的性能问题。本文从一个典型的索引导致性能的场景重发，剖析此类问题的特征，定位方法和解决方法。
1）在某局点POC测试时发现某SQL语句比较慢，原始SQL如下
WITH
/**
etl_116583_7960703_994644
**/
LOADABLE as (select "boq_rel_type_id","to_pu_id","to_version","cycle_id",
"part_offset_flag","to_boq_id","descr","from_contract_id",
"from_version","from_pu_id","ss_id","to_contract_id",
"from_boq_id","enable_flag","last_update_date"
from (SELECT /*+ PARALLEL(4)*/
BOQ_REL.FROM_BOQ_ID,
BOQ_REL.TO_BOQ_ID,
BOQ_REL.FROM_PU_ID,
BOQ_REL.TO_PU_ID,
BOQ_REL.PART_OFFSET_FLAG,
BOQ_REL.DESCR,
BOQ_REL.SS_ID,
BOQ_REL.CYCLE_ID,
NVL(BOQ_REL.FROM_VERSION, 'SNULL') FROM_VERSION,
NVL(BOQ_REL.TO_VERSION, 'SNULL') TO_VERSION,
BOQ_REL.LAST_UPDATE_DATE,
FROM_CON.CONTRACT_ID AS FROM_CONTRACT_ID,
TO_CON.CONTRACT_ID AS TO_CONTRACT_ID,
CLA.CLASS_ID AS BOQ_REL_TYPE_ID,
BOQ_REL.ENABLE_FLAG
FROM (SELECT A.FROM_BOQ_ID,
A.TO_BOQ_ID,
A.FROM_PU_ID,
A.TO_PU_ID,
A.FROM_CONTRACT_NUMBER,
A.TO_CONTRACT_NUMBER,
A.BOQ_REL_TYPE_CODE,
A.PART_OFFSET_FLAG,
A.DESCR,
A.SS_ID,
A.FROM_VERSION,
A.TO_VERSION,
A.LAST_UPDATE_DATE,
A.CYCLE_ID,
A.ENABLE_FLAG,
DECODE(A.SS_ID, 2820, 2600, A.SS_ID) SS_ID_TMP,
ROW_NUMBER() OVER(PARTITION BY FROM_BOQ_ID, TO_BOQ_ID, FROM_PU_ID, TO_PU_ID, FROM_CONTRACT_NUMBER, TO_CONTRACT_NUMBER, BOQ_REL_TYPE_CODE, FROM_VERSION, TO_VERSION
ORDER BY DECODE(A.SS_ID, 2820, 1, 2600, 2, 3)) RN
FROM LDB_MD_BOQ_REL A) BOQ_REL,
(SELECT CONTRACT_ID,
HW_CONTRACT_NUM,
SS_ID
FROM DWI_MD_CONTRACT
WHERE CONTRACT_ID IS NOT NULL
AND END_TIME = TO_DATE('4712-12-31', 'YYYY-MM-DD')) FROM_CON,
(SELECT CONTRACT_ID,
HW_CONTRACT_NUM,
SS_ID
FROM DWI_MD_CONTRACT
WHERE CONTRACT_ID IS NOT NULL
AND END_TIME = TO_DATE('4712-12-31', 'YYYY-MM-DD')) TO_CON,
(SELECT CLASS_ID,
CODE,
CLASS_TYPE_ID,
SS_ID
FROM DWI_MD_CLASS
WHERE CLASS_TYPE_ID = 193) CLA
WHERE BOQ_REL.RN = 1
AND BOQ_REL.FROM_CONTRACT_NUMBER = FROM_CON.HW_CONTRACT_NUM
AND BOQ_REL.SS_ID = FROM_CON.SS_ID
AND BOQ_REL.TO_CONTRACT_NUMBER = TO_CON.HW_CONTRACT_NUM
AND BOQ_REL.SS_ID = TO_CON.SS_ID
AND BOQ_REL.BOQ_REL_TYPE_CODE = CLA.CODE
AND BOQ_REL.SS_ID_TMP = CLA.SS_ID
) t
),
BEFORE_TARGET as (select "from_contract_id","from_pu_id","ss_id","from_boq_id","from_version","to_version",
"crt_cycle_id","to_pu_id","to_boq_id","del_flag","last_upd_cycle_id","last_update_date",
"descr","enable_flag","crt_job_instance_id","dq_improve_flag","upd_job_instance_id",
"to_contract_id","part_offset_flag","boq_rel_type_id"
from (SELECT /*+PARALLEL(4)*/
FROM_BOQ_ID,
TO_BOQ_ID,
FROM_PU_ID,
TO_PU_ID,
FROM_CONTRACT_ID,
TO_CONTRACT_ID,
BOQ_REL_TYPE_ID,
PART_OFFSET_FLAG,
DESCR,
SS_ID,
CRT_CYCLE_ID,
LAST_UPD_CYCLE_ID,
DEL_FLAG,
DQ_IMPROVE_FLAG,
CRT_JOB_INSTANCE_ID,
UPD_JOB_INSTANCE_ID,
NVL(FROM_VERSION, 'SNULL') FROM_VERSION,
NVL(TO_VERSION, 'SNULL') TO_VERSION,
LAST_UPDATE_DATE,
ENABLE_FLAG
FROM DWI_MD_BOQ_REL
) t
),
CDC as (select LOADABLE."ss_id",LOADABLE."from_version",LOADABLE."from_boq_id",
LOADABLE."part_offset_flag",LOADABLE."from_pu_id",
case when BEFORE_TARGET.BOQ_REL_TYPE_ID is null and BEFORE_TARGET.FROM_BOQ_ID is null
and BEFORE_TARGET.FROM_CONTRACT_ID is null and BEFORE_TARGET.FROM_PU_ID is null
and BEFORE_TARGET.FROM_VERSION is null
and BEFORE_TARGET.TO_BOQ_ID is null and BEFORE_TARGET.TO_CONTRACT_ID is null
and BEFORE_TARGET.TO_PU_ID is null and BEFORE_TARGET.TO_VERSION is null
then 1
else 3
end as "change_code",
LOADABLE."to_version",LOADABLE."boq_rel_type_id",
LOADABLE."from_contract_id",LOADABLE."to_contract_id",
LOADABLE."descr",LOADABLE."last_update_date",
LOADABLE."to_pu_id",LOADABLE."enable_flag",LOADABLE."cycle_id",
LOADABLE."to_boq_id"
from LOADABLE
left join BEFORE_TARGET on LOADABLE.BOQ_REL_TYPE_ID = BEFORE_TARGET.BOQ_REL_TYPE_ID
and LOADABLE.FROM_BOQ_ID = BEFORE_TARGET.FROM_BOQ_ID and LOADABLE.FROM_CONTRACT_ID = BEFORE_TARGET.FROM_CONTRACT_ID
and LOADABLE.FROM_PU_ID = BEFORE_TARGET.FROM_PU_ID and LOADABLE.FROM_VERSION = BEFORE_TARGET.FROM_VERSION
and LOADABLE.TO_BOQ_ID = BEFORE_TARGET.TO_BOQ_ID and LOADABLE.TO_CONTRACT_ID = BEFORE_TARGET.TO_CONTRACT_ID
and LOADABLE.TO_PU_ID = BEFORE_TARGET.TO_PU_ID and LOADABLE.TO_VERSION = BEFORE_TARGET.TO_VERSION
),
TFM_FILTER_DATA_TARGET_OUTPUT_U as (select CDC."to_pu_id",CDC."boq_rel_type_id",CDC."ss_id",
Current_Timestamp() as "dw_last_update_date",CDC."to_version",
CDC."from_version",20230104000000 as "last_upd_cycle_id",
CDC."from_contract_id",CDC."last_update_date",CDC."descr",
'N' as "del_flag",CDC."from_boq_id",CDC."to_boq_id",
CDC."enable_flag",CDC."from_pu_id",-1 as "upd_job_instance_id",
'N' as "dq_improve_flag",CDC."to_contract_id",
CDC."part_offset_flag"
from CDC where CDC.change_code=3
)
update DWI_MD_BOQ_REL TARGET_U
set "dq_improve_flag" = TFM_FILTER_DATA_TARGET_OUTPUT_U."dq_improve_flag",
"dw_last_update_date" = TFM_FILTER_DATA_TARGET_OUTPUT_U."dw_last_update_date",
"upd_job_instance_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."upd_job_instance_id",
"descr" = TFM_FILTER_DATA_TARGET_OUTPUT_U."descr",
"part_offset_flag" = TFM_FILTER_DATA_TARGET_OUTPUT_U."part_offset_flag",
"last_update_date" = TFM_FILTER_DATA_TARGET_OUTPUT_U."last_update_date",
"del_flag" = TFM_FILTER_DATA_TARGET_OUTPUT_U."del_flag",
"last_upd_cycle_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."last_upd_cycle_id",
"enable_flag" = TFM_FILTER_DATA_TARGET_OUTPUT_U."enable_flag",
"ss_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."ss_id"
from TFM_FILTER_DATA_TARGET_OUTPUT_U
where TARGET_U."boq_rel_type_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."boq_rel_type_id"
and TARGET_U."to_version" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_version"
and TARGET_U."to_version" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_version"
and TARGET_U."to_pu_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_pu_id"
and TARGET_U."to_pu_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_pu_id"
and TARGET_U."to_contract_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_contract_id"
and TARGET_U."to_contract_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_contract_id"
and TARGET_U."to_boq_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_boq_id"
and TARGET_U."to_boq_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."to_boq_id"
and TARGET_U."from_version" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_version"
and TARGET_U."from_version" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_version"
and TARGET_U."from_pu_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_pu_id"
and TARGET_U."from_pu_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_pu_id"
and TARGET_U."from_contract_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_contract_id"
and TARGET_U."from_contract_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_contract_id"
and TARGET_U."from_boq_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_boq_id"
and TARGET_U."from_boq_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."from_boq_id"
and TARGET_U."boq_rel_type_id" = TFM_FILTER_DATA_TARGET_OUTPUT_U."boq_rel_type_id"
;2）查询此query的topSQL信息的warning字段，发现SQL自诊断信息中有索引相关告警信息。
3）查询此query的topSQL信息(如下图)，分析历史执行信息，发现id=20的CStore Index Scan算子的耗时为90796.980ms，SQL执行总时长137135.658ms。CStore Index Scan算子的耗时占比为 66%
4）找到原始SQL语句，对查询语句中出现的表dwimd.dwi_md_contract 进行hint，强制其走顺序扫描，避免走indexscan（全量语句见附件）
5）对语句进行explain verbose，查看计划，发现计划符合预期（即表dwimd.dwi_md_contract走tablescan，对于列存表计划上显式为CStore Scan）
6）对语句执行EXPLAIN ANALYZE操作(即实际执行语句)，查看实际执行时间如下，发现SQL语句性能提升近10倍。全量的执行信息见附件
我想为Heroku构建一个Rails3应用程序。他们使用Postgres作为他们的数据库，所以我通过MacPorts安装了postgres9.0。现在我需要一个postgresgem并且共识是出于性能原因你想要pggem。但是我对我得到的错误感到非常困惑当我尝试在rvm下通过geminstall安装pg时。我已经非常明确地指定了所有postgres目录的位置可以找到但仍然无法完成安装:$envARCHFLAGS='-archx86_64'geminstallpg--\--with-pg-config=/opt/local/var/db/postgresql90/defaultdb/po
尝试通过RVM将RubyGems升级到版本1.8.10并出现此错误:$rvmrubygemslatestRemovingoldRubygemsfiles...Installingrubygems-1.8.10forruby-1.9.2-p180...ERROR:Errorrunning'GEM_PATH="/Users/foo/.rvm/gems/ruby-1.9.2-p180:/Users/foo/.rvm/gems/ruby-1.9.2-p180@global:/Users/foo/.rvm/gems/ruby-1.9.2-p180:/Users/foo/.rvm/gems/rub
我的最终目标是安装当前版本的RubyonRails。我在OSXMountainLion上运行。到目前为止，这是我的过程:已安装的RVM$\curl-Lhttps://get.rvm.io|bash-sstable检查已知(我假设已批准)安装$rvmlistknown我看到当前的稳定版本可用[ruby-]2.0.0[-p247]输入命令安装$rvminstall2.0.0-p247注意:我也试过这些安装命令$rvminstallruby-2.0.0-p247$rvminstallruby=2.0.0-p247我很快就无处可去了。结果:$rvminstall2.0.0-p247Search
由于fast-stemmer的问题，我很难安装我想要的任何rubygem。我把我得到的错误放在下面。Buildingnativeextensions.Thiscouldtakeawhile...ERROR:Errorinstallingfast-stemmer:ERROR:Failedtobuildgemnativeextension./System/Library/Frameworks/Ruby.framework/Versions/2.0/usr/bin/rubyextconf.rbcreatingMakefilemake"DESTDIR="cleanmake"DESTDIR=
当我尝试安装Ruby时遇到此错误。我试过查看this和this但无济于事➜~brewinstallrubyWarning:YouareusingOSX10.12.Wedonotprovidesupportforthispre-releaseversion.Youmayencounterbuildfailuresorotherbreakages.Pleasecreatepull-requestsinsteadoffilingissues.==>Installingdependenciesforruby:readline,libyaml,makedepend==>Installingrub
我正在尝试使用boilerpipe来自JRuby。我看过guide从JRuby调用Java，并成功地将它与另一个Java包一起使用，但无法弄清楚为什么同样的东西不能用于boilerpipe。我正在尝试基本上从JRuby中执行与此Java等效的操作:URLurl=newURL("http://www.example.com/some-location/index.html");Stringtext=ArticleExtractor.INSTANCE.getText(url);在JRuby中试过这个:require'java'url=java.net.URL.new("http://www
我意识到这可能是一个非常基本的问题，但我现在已经花了几天时间回过头来解决这个问题，但出于某种原因，Google就是没有帮助我。(我认为部分问题在于我是一个初学者，我不知道该问什么......)我也看过O'Reilly的RubyCookbook和RailsAPI，但我仍然停留在这个问题上.我找到了一些关于多态关系的信息，但它似乎不是我需要的(尽管如果我错了请告诉我)。我正在尝试调整MichaelHartl'stutorial创建一个包含用户、文章和评论的博客应用程序(不使用脚手架)。我希望评论既属于用户又属于文章。我的主要问题是:我不知道如何将当前文章的ID放入评论Controller。
导读语言模型给我们的生产生活带来了极大便利，但同时不少人也利用他们从事作弊工作。如何规避这些难辨真伪的文字所产生的负面影响也成为一大难题。在3月9日智源Live第33期活动「DetectGPT：判断文本是否为机器生成的工具」中，主讲人Eric为我们讲解了DetectGPT工作背后的思路——一种基于概率曲率检测的用于检测模型生成文本的工具，它可以帮助我们更好地分辨文章的来源和可信度，对保护信息真实、防止欺诈等方面具有重要意义。本次报告主要围绕其功能，实现和效果等展开。（文末点击“阅读原文”，查看活动回放。）Ericmitchell斯坦福大学计算机系四年级博士生，由ChelseaFinn和Chri
首先回顾一下拉格朗日定理的内容：函数f(x)是在闭区间[a,b]上连续、开区间(a,b)上可导的函数，那么至少存在一个，使得:通过这个表达式我们可以知道，f(x)是函数的主体，a和b可以看作是主体函数f(x)中所取的两个值。那么可以有， 也就意味着我们可以用来替换 这种替换可以用在求某些多项式差的极限中。方法： 外层函数f(x)是一致的，并且h(x)和g(x)是等价无穷小。此时，利用拉格朗日定理，将原式替换为 ，再进行求解，往往会省去复合函数求极限的很多麻烦。使用要注意：1.要先找到主体函数f(x)，即外层函数必须相同。2.f(x)找到后，复合部分是等价无穷小。3.要满足作差的形式。如果是加
之前说过10之后的版本没有3dScan了，所以还是9.8的版本或者之前更早的版本。 3d物体扫描需要先下载扫描的APK进行扫面。首先要在手机上装一个扫描程序，扫描现实中的三维物体，然后上传高通官网，在下载成UnityPackage类型让Unity能够使用这个扫描程序可以从高通官网上进行下载，是一个安卓程序。点到Tools往下滑，找到VuforiaObjectScanner下载后解压数据线连接手机，将apk文件拷入手机安装然后刚才解压文件中的Media文件夹打开，两个PDF图打印第一张A4-ObjectScanningTarget.pdf，主要是用来辅助扫描的。好了，接下来就是扫描三维物体。将瓶