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系数据库中，列数据类型转换一定要小心小心再小心