openGauss关联列数据类型不一致引起谓词传递失败
今天分享一个比较有意思的案例
注意：因为原始SQL很长，为了方便排版，简化了SQL
下面SQL跑60秒才出结果，客户请求优化
select dtcs.owner, dtcs.table_name, dtcs.column_name, dct.commentsfrom dba_tab_columns dtcsleft outer join dba_col_comments dcton dct.owner = dtcs.ownerand dct.table_name = dtcs.table_nameand dct.column_name = dtcs.column_namewhere 1 = 1and dtcs.table_name = 'psph_t'and coalesce(dct.comments, 'x') <> dtcs.column_nameand exists (select *from gzda_t dawhere da.gzda001 = dtcs.ownerand da.gzda005 = 'Y');拿到SQL不要一来就去看执行计划，应该先分析SQL写法，然后再查看数据量
dba_tab_columns，dba_col_comments是客户模仿Oracle的数据字典自己编写的2个视图，gzda_t是一个表
我们来看一下where条件过滤之后的数据量以及SQL执行时间
topprd=# select count(*) from dba_tab_columns where table_name = 'psph_t';count
-------265
(1 row)Time: 67.131 ms
topprd=# select count(*) from dba_col_comments where table_name = 'psph_t';count
-------300
(1 row)Time: 126.181 ms
topprd=# select count(*) from gzda_t where gzda005 = 'Y';count
-------4
(1 row)Time: 5.896 ms最多才300行，执行速度也都在毫秒级，那原始SQL应该秒杀才对，不应该跑60秒
有些读者可能会有疑问，dba_col_comments没有过滤条件啊，你怎么加个where table_name = 'psph_t'
这是因为dba_tab_columns过滤条件有where table_name = 'psph_t'，并且两个表关联条件是dct.table_name = dtcs.table_name
openGauss支持可传递谓词功能，这个功能在Oracle里面受到隐含参数_optimizer_filter_pushdown控制
也就是说dtcs.table_name = 'psph_t'的过滤条件会根据dct.table_name = dtcs.table_name传递给dct
经过上面的分析，得到结论，SQL应该秒杀，但是跑了60秒，现在来看一下执行计划吧
Nested Loop Left Join (cost=659287.88..6447409.06 rows=4 width=201) (actual time=1522.588..63483.879 rows=106 loops=1)Filter: (COALESCE(d.description, 'x'::text) <> (att.attname)::text)Rows Removed by Filter: 106-> Hash Right Join (cost=659287.88..6447367.76 rows=4 width=198) (actual time=401.930..63482.739 rows=212 loops=1)Hash Cond: (((n.nspname)::text = (nsp.nspname)::text) AND ((c.relname)::text = (cls.relname)::text) AND ((a.attname)::text = (att.attname)::text))-> Hash Join (cost=576610.79..6337810.41 rows=1433613 width=198) (actual time=125.543..62834.698 rows=846168 loops=1)Hash Cond: (c.relnamespace = n.oid)-> Hash Join (cost=574039.01..6332278.71 rows=1075210 width=138) (actual time=119.805..62582.638 rows=1003296 loops=1)Hash Cond: (a.attrelid = c.oid)-> Seq Scan on pg_attribute a (cost=0.00..5755417.10 rows=1075210 width=70) (actual time=0.006..62057.869 rows=1003296 loops=1)-> Hash (cost=573183.21..573183.21 rows=68464 width=72) (actual time=119.007..119.007 rows=60919 loops=1)Buckets: 131072 Batches: 1 Memory Usage: 7212kB-> Index Scan using pg_class_oid_index on pg_class c (cost=0.00..573183.21 rows=68464 width=72) (actual time=0.022..95.303 rows=60919 loops=1)-> Hash (cost=2570.32..2570.32 rows=116 width=68) (actual time=5.540..5.540 rows=117 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 268kB-> Seq Scan on pg_namespace n (cost=0.00..2570.32 rows=116 width=68) (actual time=0.017..5.504 rows=117 loops=1)Filter: (nspname !~~ 'pg_toast%'::text)Rows Removed by Filter: 67-> Hash (cost=82677.03..82677.03 rows=4 width=192) (actual time=49.883..49.883 rows=212 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 303kB-> Hash Semi Join (cost=74287.54..82677.03 rows=4 width=192) (actual time=17.161..49.547 rows=212 loops=1)Hash Cond: (nsp.nspname = (da.gzda001)::name)-> WindowAgg (cost=74286.33..82644.58 rows=2495 width=1791) (actual time=16.924..48.794 rows=265 loops=1)-> Sort (cost=74286.33..74292.57 rows=2495 width=1791) (actual time=15.406..15.491 rows=265 loops=1)Sort Key: nsp.nspname, att.attnumSort Method: quicksort Memory: 326kB-> Hash Left Join (cost=17668.34..74145.55 rows=2495 width=1791) (actual time=11.925..14.773 rows=265 loops=1)Hash Cond: ((cls.relname = c.relname) AND (nsp.nspname = n.nspname) AND (att.attname = a.attname))-> Nested Loop Left Join (cost=12282.79..68732.57 rows=2401 width=1775) (actual time=10.844..13.527 rows=265 loops=1)-> Hash Join (cost=12282.79..48893.73 rows=2401 width=863) (actual time=10.814..12.536 rows=265 loops=1)Hash Cond: (cls.relnamespace = nsp.oid)-> Hash Join (cost=9711.02..46316.99 rows=1801 width=803) (actual time=5.290..6.849 rows=265 loops=1)Hash Cond: (typ.typnamespace = tnsp.oid)-> Nested Loop (cost=7138.83..43618.69 rows=47135 width=743) (actual time=0.188..1.659 rows=265 loops=1)-> Nested Loop (cost=7138.83..14333.30 rows=1801 width=416) (actual time=0.158..0.468 rows=265 loops=1)-> Index Scan using pg_class_relname_nsp_index on pg_class cls (cost=0.00..36.30 rows=1 width=80) (actual time=0.045..0.060 rows=5 loops=1)Index Cond: (relname = 'psph_t'::name)Filter: (relkind = ANY ('{r,v,t,f}'::"char"[]))-> Bitmap Heap Scan on pg_attribute att (cost=7138.83..14274.18 rows=2283 width=340) (actual time=0.203..0.325 rows=265 loops=5)Recheck Cond: ((attrelid = cls.oid) AND (attnum >= 1))Heap Blocks: exact=12-> Bitmap Index Scan on pg_attribute_relid_attnum_index (cost=0.00..7138.26 rows=1801 width=0) (actual time=0.154..0.154 rows=265 loops=5)Index Cond: ((attrelid = cls.oid) AND (attnum >= 1))-> Index Scan using pg_type_oid_index on pg_type typ (cost=0.00..16.25 rows=1 width=335) (actual time=1.016..1.016 rows=265 loops=265)Index Cond: (oid = att.atttypid)-> Hash (cost=2569.86..2569.86 rows=186 width=68) (actual time=4.890..4.890 rows=184 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 274kB-> Seq Scan on pg_namespace tnsp (cost=0.00..2569.86 rows=186 width=68) (actual time=0.007..4.830 rows=184 loops=1)-> Hash (cost=2570.32..2570.32 rows=116 width=68) (actual time=5.306..5.306 rows=117 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 268kB-> Seq Scan on pg_namespace nsp (cost=0.00..2570.32 rows=116 width=68) (actual time=0.015..5.272 rows=117 loops=1)Filter: (nspname !~~ 'pg_toast%'::text)Rows Removed by Filter: 67-> Index Scan using pg_attrdef_adrelid_adnum_index on pg_attrdef ad (cost=0.00..8.26 rows=1 width=918) (actual time=0.783..0.783 rows=44 loops=265)Index Cond: ((att.attrelid = adrelid) AND (att.attnum = adnum))-> Hash (cost=5381.16..5381.16 rows=251 width=208) (actual time=0.879..0.879 rows=53 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 269kB-> Nested Loop (cost=4.26..5381.16 rows=251 width=208) (actual time=0.214..0.841 rows=53 loops=1)Join Filter: (has_column_privilege(c.oid, a.attnum, 'select'::text) AND (c.oid = a.attrelid))-> Nested Loop (cost=0.00..515.92 rows=66 width=154) (actual time=0.111..0.272 rows=53 loops=1)-> Nested Loop (cost=0.00..109.09 rows=2 width=132) (actual time=0.049..0.096 rows=5 loops=1)-> Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.00..36.29 rows=2 width=72) (actual time=0.026..0.045 rows=5 loops=1)Index Cond: (relname = 'psph_t'::name)-> Index Scan using pg_namespace_oid_index on pg_namespace n (cost=0.00..24.27 rows=1 width=68) (actual time=0.040..0.040 rows=5 loops=5)Index Cond: (oid = c.relnamespace)-> Index Scan using pg_statistic_relid_kind_att_inh_index on pg_statistic s (cost=0.00..136.99 rows=6642 width=22) (actual time=0.061..0.160 rows=53 loops=5)Index Cond: ((starelid = c.oid) AND (starelkind = 'c'::"char"))-> Bitmap Heap Scan on pg_attribute a (cost=4.26..72.43 rows=17 width=70) (actual time=0.288..0.288 rows=53 loops=53)Recheck Cond: ((attrelid = s.starelid) AND (attnum = s.staattnum))Filter: (NOT attisdropped)Heap Blocks: exact=53-> Bitmap Index Scan on pg_attribute_relid_attnum_index (cost=0.00..4.26 rows=17 width=0) (actual time=0.200..0.200 rows=53 loops=53)Index Cond: ((attrelid = s.starelid) AND (attnum = s.staattnum))-> Hash (cost=1.16..1.16 rows=4 width=6) (actual time=0.032..0.032 rows=4 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 257kB-> Seq Scan on gzda_t da (cost=0.00..1.16 rows=4 width=6) (actual time=0.011..0.016 rows=4 loops=1)Filter: ((gzda005)::text = 'Y'::text)Rows Removed by Filter: 9-> Index Scan using pg_description_o_c_o_index on pg_description d (cost=0.00..8.25 rows=1 width=17) (actual time=0.812..0.812 rows=106 loops=212)Index Cond: ((a.attrelid = objoid) AND (classoid = 1259::oid) AND (a.attnum = objsubid))
Total runtime: 63488.041 ms执行计划中
-> Hash Right Join (cost=659287.88..6447367.76 rows=4 width=198) (actual time=401.930..63482.739 rows=212 loops=1)Hash Cond: (((n.nspname)::text = (nsp.nspname)::text) AND ((c.relname)::text = (cls.relname)::text) AND ((a.attname)::text = (att.attname)::text))表示 dtcs left join dct on dct.owner = dtcs.owner and dct.table_name = dtcs.table_name and dct.column_name = dtcs.column_name
执行计划中
-> Hash Join (cost=576610.79..6337810.41 rows=1433613 width=198) (actual time=125.543..62834.698 rows=846168 loops=1)Hash Cond: (c.relnamespace = n.oid)-> Hash Join (cost=574039.01..6332278.71 rows=1075210 width=138) (actual time=119.805..62582.638 rows=1003296 loops=1)Hash Cond: (a.attrelid = c.oid)-> Seq Scan on pg_attribute a (cost=0.00..5755417.10 rows=1075210 width=70) (actual time=0.006..62057.869 rows=1003296 loops=1)-> Hash (cost=573183.21..573183.21 rows=68464 width=72) (actual time=119.007..119.007 rows=60919 loops=1)Buckets: 131072 Batches: 1 Memory Usage: 7212kB-> Index Scan using pg_class_oid_index on pg_class c (cost=0.00..573183.21 rows=68464 width=72) (actual time=0.022..95.303 rows=60919 loops=1)-> Hash (cost=2570.32..2570.32 rows=116 width=68) (actual time=5.540..5.540 rows=117 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 268kB-> Seq Scan on pg_namespace n (cost=0.00..2570.32 rows=116 width=68) (actual time=0.017..5.504 rows=117 loops=1)Filter: (nspname !~~ 'pg_toast%'::text)Rows Removed by Filter: 67是dba_col_comments dct，仔细观察执行计划，没有where table_name = 'psph_t'的过滤信息，也就是说没有发生谓词传递
先做个小实验，确认一下openGauss是否支持谓词传递
create table t1(id number,name varchar2(100));
create table t2(id number,comm varchar2(100));
insert into t1 values(1,'CHINA');
insert into t1 values(2,'JAPAN');
insert into t2 values(1,'牛逼');
commit;oracle=> explain select * from t1 left join t2 on t1.id=t2.id where t1.id=1;QUERY PLAN
-----------------------------------------------------------------Hash Left Join (cost=13.66..27.33 rows=1 width=500)Hash Cond: (t1.id = t2.id)-> Seq Scan on t1 (cost=0.00..13.65 rows=1 width=250)Filter: (id = 1::numeric)-> Hash (cost=13.65..13.65 rows=1 width=250)-> Seq Scan on t2 (cost=0.00..13.65 rows=1 width=250)Filter: (id = 1::numeric)
(7 rows)查看上面执行计划，t2表对id列做了过滤 Filter: (id = 1::numeric)，也就是说openGauss支持谓词传递
经过上面分析，我们得到结论，原始SQL没有进行谓词传递，既然数据库没有自动进行谓词传递，那么我们就人工传递
select dtcs.owner, dtcs.table_name, dtcs.column_name, dct.commentsfrom dba_tab_columns dtcsleft outer join dba_col_comments dcton dct.owner = dtcs.ownerand dct.table_name = dtcs.table_nameand dct.column_name = dtcs.column_nameand dct.table_name='psph_t' ---人工传递谓词where 1 = 1and dtcs.table_name = 'psph_t'and coalesce(dct.comments, 'x') <> dtcs.column_nameand exists (select *from gzda_t dawhere da.gzda001 = dtcs.ownerand da.gzda005 = 'Y');上面SQL的注释部分就是人工传递谓词的过滤条件，SQL秒杀，执行计划如下
Nested Loop Left Join (cost=192358.27..6031726.77 rows=4 width=201) (actual time=127.956..212.777 rows=106 loops=1)Filter: (COALESCE(d.description, 'x'::text) <> (att.attname)::text)Rows Removed by Filter: 106-> Hash Right Join (cost=192358.27..6031685.47 rows=4 width=198) (actual time=71.544..211.792 rows=212 loops=1)Hash Cond: (((n.nspname)::text = (nsp.nspname)::text) AND ((c.relname)::text = (cls.relname)::text) AND ((a.attname)::text = (att.attname)::text))-> Hash Join (cost=11197.98..5833555.24 rows=905063 width=198) (actual time=17.358..156.901 rows=300 loops=1)Hash Cond: (c.relnamespace = n.oid)-> Nested Loop (cost=8586.90..5829179.63 rows=669341 width=138) (actual time=11.802..151.253 rows=300 loops=1)-> Index Scan using pg_class_oid_index on pg_class c (cost=0.00..544818.35 rows=307 width=72) (actual time=11.739..150.856 rows=5 loops=1)Filter: ((relname)::text = 'psph_t'::text)Rows Removed by Filter: 60971-> Bitmap Heap Scan on pg_attribute a (cost=8586.90..17191.10 rows=2180 width=70) (actual time=0.188..0.262 rows=300 loops=5)Recheck Cond: (attrelid = c.oid)Heap Blocks: exact=15-> Bitmap Index Scan on pg_attribute_relid_attnum_index (cost=0.00..8586.36 rows=2180 width=0) (actual time=0.124..0.124 rows=300 loops=5)Index Cond: (attrelid = c.oid)-> Hash (cost=2594.32..2594.32 rows=1340 width=68) (actual time=5.354..5.354 rows=117 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 268kB-> Seq Scan on pg_namespace n (cost=0.00..2594.32 rows=1340 width=68) (actual time=0.024..5.312 rows=117 loops=1)Filter: (nspname !~~ 'pg_toast%'::text)Rows Removed by Filter: 67-> Hash (cost=181160.22..181160.22 rows=4 width=192) (actual time=53.709..53.709 rows=212 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 303kB-> Hash Semi Join (cost=151533.19..181160.22 rows=4 width=192) (actual time=18.529..53.296 rows=212 loops=1)Hash Cond: (nsp.nspname = (da.gzda001)::name)-> WindowAgg (cost=151531.98..181048.83 rows=8811 width=2618) (actual time=18.274..52.512 rows=265 loops=1)-> Sort (cost=151531.98..151554.00 rows=8811 width=2618) (actual time=16.753..16.863 rows=265 loops=1)Sort Key: nsp.nspname, att.attnumSort Method: quicksort Memory: 326kB-> Hash Left Join (cost=18995.91..135323.63 rows=8811 width=2618) (actual time=12.843..16.111 rows=265 loops=1)Hash Cond: ((cls.relname = c.relname) AND (nsp.nspname = n.nspname) AND (att.attname = a.attname))-> Nested Loop Left Join (cost=12082.62..128357.38 rows=4697 width=2602) (actual time=10.714..13.806 rows=265 loops=1)-> Hash Join (cost=12082.62..89555.16 rows=4697 width=1690) (actual time=10.684..12.700 rows=265 loops=1)Hash Cond: (cls.relnamespace = nsp.oid)-> Hash Join (cost=9471.55..86934.92 rows=3474 width=1630) (actual time=5.090..6.948 rows=265 loops=1)Hash Cond: (typ.typnamespace = tnsp.oid)-> Nested Loop (cost=6856.16..84053.46 rows=101159 width=1570) (actual time=0.171..1.914 rows=265 loops=1)-> Nested Loop (cost=6856.16..27579.13 rows=3474 width=416) (actual time=0.144..0.570 rows=265 loops=1)-> Index Scan using pg_class_relname_nsp_index on pg_class cls (cost=0.00..56.32 rows=2 width=80) (actual time=0.040..0.065 rows=5 loops=1)Index Cond: (relname = 'psph_t'::name)Filter: (relkind = ANY ('{r,v,t,f}'::"char"[]))-> Bitmap Heap Scan on pg_attribute att (cost=6856.16..13739.61 rows=2180 width=340) (actual time=0.283..0.429 rows=265 loops=5)Recheck Cond: ((attrelid = cls.oid) AND (attnum >= 1))Heap Blocks: exact=12-> Bitmap Index Scan on pg_attribute_relid_attnum_index (cost=0.00..6855.62 rows=1737 width=0) (actual time=0.226..0.226 rows=265 loops=5)Index Cond: ((attrelid = cls.oid) AND (attnum >= 1))-> Index Scan using pg_type_oid_index on pg_type typ (cost=0.00..16.25 rows=1 width=1162) (actual time=1.161..1.161 rows=265 loops=265)Index Cond: (oid = att.atttypid)-> Hash (cost=2589.06..2589.06 rows=2106 width=68) (actual time=4.756..4.756 rows=184 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 274kB-> Seq Scan on pg_namespace tnsp (cost=0.00..2589.06 rows=2106 width=68) (actual time=0.014..4.687 rows=184 loops=1)-> Hash (cost=2594.32..2594.32 rows=1340 width=68) (actual time=5.379..5.379 rows=117 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 268kB-> Seq Scan on pg_namespace nsp (cost=0.00..2594.32 rows=1340 width=68) (actual time=0.024..5.343 rows=117 loops=1)Filter: (nspname !~~ 'pg_toast%'::text)Rows Removed by Filter: 67-> Index Scan using pg_attrdef_adrelid_adnum_index on pg_attrdef ad (cost=0.00..8.26 rows=1 width=918) (actual time=0.858..0.858 rows=44 loops=265)Index Cond: ((att.attrelid = adrelid) AND (att.attnum = adnum))-> Hash (cost=6903.94..6903.94 rows=534 width=208) (actual time=1.965..1.965 rows=53 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 269kB-> Nested Loop (cost=4.26..6903.94 rows=534 width=208) (actual time=0.212..1.919 rows=53 loops=1)Join Filter: (has_column_privilege(c.oid, a.attnum, 'select'::text) AND (c.oid = a.attrelid))-> Nested Loop (cost=0.00..714.96 rows=101 width=154) (actual time=0.104..1.359 rows=53 loops=1)-> Nested Loop (cost=0.00..89.38 rows=3 width=132) (actual time=0.041..0.096 rows=5 loops=1)-> Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.00..56.30 rows=3 width=72) (actual time=0.019..0.039 rows=5 loops=1)Index Cond: (relname = 'psph_t'::name)-> Index Scan using pg_namespace_oid_index on pg_namespace n (cost=0.00..8.27 rows=1 width=68) (actual time=0.046..0.046 rows=5 loops=5)Index Cond: (oid = c.relnamespace)-> Index Scan using pg_statistic_relid_kind_att_inh_index on pg_statistic s (cost=0.00..141.02 rows=6751 width=22) (actual time=0.063..1.242 rows=53 loops=5)Index Cond: ((starelid = c.oid) AND (starelkind = 'c'::"char"))-> Bitmap Heap Scan on pg_attribute a (cost=4.26..60.41 rows=21 width=70) (actual time=0.303..0.303 rows=53 loops=53)Recheck Cond: ((attrelid = s.starelid) AND (attnum = s.staattnum))Filter: (NOT attisdropped)Heap Blocks: exact=53-> Bitmap Index Scan on pg_attribute_relid_attnum_index (cost=0.00..4.26 rows=14 width=0) (actual time=0.206..0.206 rows=53 loops=53)Index Cond: ((attrelid = s.starelid) AND (attnum = s.staattnum))-> Hash (cost=1.16..1.16 rows=4 width=6) (actual time=0.032..0.032 rows=4 loops=1)Buckets: 32768 Batches: 1 Memory Usage: 257kB-> Seq Scan on gzda_t da (cost=0.00..1.16 rows=4 width=6) (actual time=0.010..0.013 rows=4 loops=1)Filter: ((gzda005)::text = 'Y'::text)Rows Removed by Filter: 9-> Index Scan using pg_description_o_c_o_index on pg_description d (cost=0.00..8.25 rows=1 width=17) (actual time=0.728..0.728 rows=106 loops=212)Index Cond: ((a.attrelid = objoid) AND (classoid = 1259::oid) AND (a.attnum = objsubid))
Total runtime: 216.188 ms虽然SQL从60秒优化到秒杀，但是还没完，要分析是什么原因导致谓词传递失败，是BUG还是其他什么原因
如果是BUG，要给内核研发提需求，修复BUG。
视图dba_col_comments的定义如下
SELECT dba_col_comments1.owner, dba_col_comments1.table_name, dba_col_comments1.column_name, dba_col_comments1.comments FROM
(SELECT (n.nspname) ::text AS owner,(c.relname) ::text AS table_name,(a.attname) ::text AS column_name,d.description AS commentsFROM (((pg_attribute a JOIN pg_class c ON((a.attrelid = c.oid))) JOINpg_namespace n ON((c.relnamespace = n.oid))) LEFT JOINpg_description dON((((a.attrelid = d.objoid) AND (a.attnum = d.objsubid)) AND(d.classoid = ('pg_class' ::regclass) ::oid))))WHERE (n.nspname !~~ 'pg_toast%'::text)
) dba_col_comments1注意观察::text，它表示强制类型转换为text，我们想要传递谓词的列是table_name,也就是pg_class.relname，现在来看一下pg_class.relname是什么数据类型
topprd=# \d pg_classTable "pg_catalog.pg_class"Column | Type | Modifiers
------------------+------------------+-----------relname | name | not null
.....省略...pg_class.relname数据类型是name，但是被转成了text
视图dba_tab_columns的定义非常复杂，这里就不贴了，视图dba_tab_columns的table_name没有做类型转换，table_name同样来自pg_class.relname
现在去掉::text类型转换看看SQL速度
select dtcs.owner, dtcs.table_name, dtcs.column_name, dct.commentsfrom dba_tab_columns dtcsleft outer join (SELECT dba_col_comments1.owner,dba_col_comments1.table_name,dba_col_comments1.column_name,dba_col_comments1.commentsFROM (SELECT (n.nspname) AS owner, ---去掉了::text(c.relname) AS table_name, ---去掉了::text(a.attname) AS column_name, ---去掉了::textd.description AS commentsFROM (((pg_attribute a JOIN pg_class cON((a.attrelid = c.oid))) JOINpg_namespace nON((c.relnamespace = n.oid))) LEFT JOINpg_description dON((((a.attrelid = d.objoid) AND(a.attnum = d.objsubid)) AND(d.classoid = ('pg_class' ::regclass)::oid))))WHERE (n.nspname !~~ 'pg_toast%'::text)) dba_col_comments1) dcton dct.owner = dtcs.ownerand dct.table_name = dtcs.table_nameand dct.column_name = dtcs.column_namewhere 1 = 1and dtcs.table_name = 'psph_t'and coalesce(dct.comments, 'x') <> dtcs.column_nameand exists (select *from gzda_t dawhere da.gzda001 = dtcs.ownerand da.gzda005 = 'Y');上面SQL秒杀，执行计划就不贴了
写在最后
在某交易所遇到过n起bpchar，text数据类型不一致导致常量无法推入视图
磐维遇到过几起ROWID与ROWID关联数据类型不明确引起性能问题
利用CTID删除重复数据CTID与CTID数据类型不明确无法走HASH JOIN遇到性能问题
今天遇到列数据类型不一致引起谓词传递功能失效导致性能问题
...........................省略.............................
在OG/PG系数据库中，列数据类型转换一定要小心小心再小心
相关文章：
openGauss关联列数据类型不一致引起谓词传递失败
今天分享一个比较有意思的案例 注意：因为原始SQL很长，为了方便排版，简化了SQL 下面SQL跑60秒才出结果，客户请求优化 select dtcs.owner, dtcs.table_name, dtcs.column_name, dct.commentsfrom dba_tab_columns dtcsleft outer j…...
头歌实践教学平台--【数据库概论】--SQL
一、表结构与完整性约束的修改(ALTER) 1.修改表名 USE TestDb1; alter table your_table rename TO my_table; 2.添加与删除字段 #语句1：删除表orderDetail中的列orderDate alter table orderDetail drop orderDate; #语句2：添加列unitPrice alter t…...
Unity 全栈开发商业级 MMORPG 大型网游：源码与课件助力进阶之路
Unity 全栈开发商业级 MMORPG 大型网游：源码与课件助力进阶之路 在竞争激烈的游戏市场中，大型多人在线角色扮演游戏（MMORPG）凭借其丰富的世界观、庞大的玩家社区以及持续的内容更新，始终占据着重要地位。Unity 作为一…...
软件工程面试题（六）
1、forward及redirect 的区别?有哪些方式实现 <jsp:forward>重定向后url地址栏地址不变还是原来的地址；而response.sendRedirect()重定向后url地址栏地址显示的请求后的新地址。<jsp:forward>重定向的时候可以保存回话信息，因此可以使用re…...
Apache Dubbo 与 ZooKeeper 集成：服务注册与发现的全解析
在分布式系统中，Apache Dubbo 作为一个高性能的 RPC 和微服务框架，广泛用于服务治理，而 ZooKeeper 作为其常用注册中心，提供了服务注册与发现的核心能力。在2025年的技术生态中，理解 Dubbo 与 ZooKeeper 的集成原理和使…...
算法基础——模拟
目录 1 多项式输出 2.蛇形方阵 3.字符串的展开 模拟，顾名思义，就是题⽬让你做什么你就做什么，考察的是将思路转化成代码的代码能⼒。这类题⼀般较为简单，属于竞赛⾥⾯的签到题（但是，万事⽆绝对ÿ…...
【第30节】MFC编程：ListCtrl控件和TreeCtrl控件
目录 引言 一、高级控件ListCtrl 二、高级控件TreeCtrl 三、Shell控件 四、CImageList 五、综合代码示例 引言 在MFC编程里，高级控件能大幅提升应用程序的交互性与功能性。接下来，咱们会详细讲讲ListCtrl和TreeCtrl这两个高级控件。不仅会介绍它们…...
 : inline、noinline、crossinline 关键字对应编译后的代码是怎样的 ?)
kotlin知识体系(四) : inline、noinline、crossinline 关键字对应编译后的代码是怎样的 ?
1. inline、noinline、crossinline 的作用 在 Kotlin 里，inline、noinline 和 crossinline 这几个关键字和高阶函数紧密相关，它们能够对高阶函数的行为进行优化和控制。本文接下来会详细介绍它们的作用和原理。 1.1 inline 关键字 inline 关键字用于修…...
JavaScript 手写 call、apply、bind 和 new
1. 手写 call 方法 核心思路：改变函数的 this 指向并立即执行，通过将函数临时挂载到目标对象上调用。 Function.prototype.myCall function (context, ...args) {// 如果 context 为 null 或 undefined，则默认为 windowcontext context |…...
睡眠健康领域的智能硬件设备未来的发展趋势
随着社会节奏的不断加快，人们的睡眠问题愈发多了起来，主要表现有以下几个方面： 睡眠质量下降 浅睡眠增多：现代生活中，人们面临着各种压力源，如工作压力、生活琐事、经济压力等，这些压力会导致大…...
计算机网络基础：量子通信技术在网络中的应用前景
计算机网络基础：量子通信技术在网络中的应用前景 一、前言二、量子通信技术基础2.1 量子通信的基本概念2.2 量子通信的主要原理2.2.1 量子密钥分发（QKD）原理2.2.2 量子隐形传态原理三、量子通信技术的特点3.1 绝对安全性3.2 超高通信速率潜力3.3 抗干扰能力强四、量子通信技…...
Postman 下载文件指南：如何请求 Excel/PDF 文件？
在 Postman 中进行 Excel/PDF 文件的请求下载和导出，以下是简明的步骤，帮助你轻松完成任务。首先，我们将从新建接口开始，逐步引导你完成整个过程。 Postman 请求下载/导出 excel/pdf 文件教程...
Stereolabs ZED Box Mini：机器人与自动化领域的人工智能视觉新选择
在人工智能视觉技术快速发展的今天，其应用场景正在持续拓宽，从智能安防到工业自动化，从机器人技术到智能交通，各领域都在积极探索如何利用这一先进技术。而 Stereolabs 推出的ZED Box Mini，正是一款专为满足这些多样化…...
arm之s3c2440的I2C的用法
基础概念 IC（Inter-Integrated Circuit）又称I2C，是是IICBus简称，所以中文应该叫集成电路总线。 IIC的总线的使用场景，所有挂载在IIC总线上的设备都有两根信号线，一根是数据线SDA，另一 根是时钟…...
安装node，配置npm, yarn, pnpm, bun
文章目录 安装node, 配置 npm, yarn, pnpm, bun配置node配置 npm, yarn, pnpm, bunnpmyarnpnpmbun 安装node, 配置 npm, yarn, pnpm, bun 配置node 输入网址：Node.js，包含各种安装方式以及多版本管理方式。也可以直接下载安装包。 安装包的安装过程…...
redis部署架构
一.redis多实例 如上图所示，我们经常使用实例的端口号来作为实例的安装目录名称。 1.创建实例安装目录 如上图所示，这是创建实例的安装目录， 2.拷贝实例的配置文件 如上图所示，将redis解压目录下的配置文件拷贝到对应的conf目录…...
深入理解指针（4）（C语言版）
文章目录 前言一、回调函数是什么（一）定义（二）工作原理（三）应用场景 二、qsort举例（一）qsort函数简介（二）比较函数的定义（三）使用示例…...
【HTML】验证与调试工具
个人主页：Guiat 归属专栏：HTML CSS JavaScript 文章目录 1. HTML 验证工具概述1.1 验证的重要性1.2 常见 HTML 错误类型 2. W3C 验证服务2.1 W3C Markup Validation Service2.2 使用 W3C 验证器2.3 验证结果解读 3. 浏览器开发者工具3.1 Chrome DevTools…...
【Mysql】SQL 优化全解析
文章目录 一、理解执行计划1.1 执行计划的作用1.2 查看执行计划 二、查询优化2.1 避免全表扫描2.2 使用覆盖索引2.3 合理使用 JOIN 三、索引优化3.1 索引设计原则3.2 索引维护 在数据驱动的当今时代，MySQL 作为应用广泛的开源关系型数据库&…...
SenseGlove与Aeon Robotics携手推出HEART项目，助力机器人培训迈向新台阶
在自动化和机器人技术快速发展的今天，SenseGlove和Aeon Robotics联合推出了一项创新项目——HEART项目。该项目在欧盟资助的MasterXR框架内展开，旨在通过整合虚拟现实（VR）、力反馈触觉手套（SenseGlove项目Rembrandt&am…...
mapbox进阶，仿照百度，加载marker点位，移入marker点切换图标，点击展示气泡，气泡和marker联动
👨⚕️ 主页： gis分享者 👨⚕️ 感谢各位大佬 点赞👍 收藏⭐ 留言📝 加关注✅! 👨⚕️ 收录于专栏：mapbox 从入门到精通 文章目录 一、🍀前言1.1 ☘️mapboxgl.Map 地图对象1.2 ☘️mapboxgl.Map style属性1.3 ☘️marker 标注点位 api1.3.1 ☘️构造函数…...
使用HTML5和CSS3实现3D旋转相册效果
使用HTML5和CSS3实现3D旋转相册效果 这里写目录标题 使用HTML5和CSS3实现3D旋转相册效果项目介绍技术栈核心功能实现思路1. HTML结构2. CSS样式解析2.1 基础样式设置2.2 3D效果核心样式2.3 卡片样式 3. JavaScript交互实现3.1 旋转控制3.2 自动播放功能 技术要点总结项目亮点总…...
HTML5 新的 Input 类型学习笔记
HTML5 引入了多种新的表单输入类型，这些新特性不仅增强了输入控制，还提供了更强大的验证功能，使表单设计更加灵活和便捷。以下是 HTML5 新的 Input 类型的详细学习笔记。 一、color 类型 功能：用于选取颜色。 使用场景ÿ…...
游戏引擎学习第186天
回顾并规划今天的任务 现在，我们站在了一个关键的时刻，准备突破，拥有一些优秀的性能分析代码。从目前来看，我们已经能够看到时间的消耗情况，我对这一点感到非常兴奋。昨天的直播中我们勉强让一些东西工作了࿰…...
NDK CMake工程中引入其他C++三方库
在Android NDK CMake工程中引入其他C三方库时，有以下几种常见的依赖方式： 1. 源码依赖 如果三方库的源代码包含在你的项目目录中，并且它有自己的CMake配置，可以使用add_subdirectory将三方库的构建过程集成到你的项目中。 示例…...
【redis】持久化之RDB与AOF
在数字世界的脉搏中，数据是流淌的血液，而持久化则是保障系统生命力的核心机制。作为内存数据库的标杆，Redis凭借其高性能特性成为互联网架构的基石，但其「易失性」的天然属性也催生了关键命题：如何在服务重启或故障时保…...
Brainstorm绘制功能连接图（matlab）
上篇笔记简单介绍了Brainstorm，本次使用Brainstorm绘制功能连接图。而对于连接矩阵，软件中有几种方法：相关、相干、双变量格兰杰因果关系、相位锁相值、包络相关、相位转移熵。 首先，对数据进行预处理，保存为.set&…...
华为HG532路由器RCE漏洞 CVE-2017-17215 复现
华为HG532路由器RCE漏洞 CVE-2017-17215 CVE-Description Huawei HG532 with some customized versions has a remote code execution vulnerability. An authenticated attacker could send malicious packets to port 37215 to launch attacks. Successful exploit could l…...
布局全面指南（20）)
CSS3学习教程，从入门到精通，CSS3 弹性盒子(Flexbox)布局全面指南（20）
CSS3 弹性盒子(Flexbox)布局全面指南 一、Flexbox 概述 Flexbox（弹性盒子）是 CSS3 提供的一种一维布局模型，可以轻松实现各种复杂的页面布局。它特别适合处理不同屏幕尺寸下的元素排列和对齐问题。 主要优势： 简单实现垂直居中…...
Redis 性能数据解读与问题排查优化版
目录标题 Redis 性能数据解读与问题排查优化版一、Redis 性能数据解读二、常见问题排查与解决（一）CPU 使用率高（二）内存使用异常（三）集群状态异常（四）数据库状态问题 三、综合优化建…...