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系數據庫中，列數據類型轉換一定要小心小心再小心

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/899280.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/899280.shtml
英文地址，請注明出處：http://en.pswp.cn/news/899280.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

01 相機標定與相機模型介紹

01 相機標定與相機模型介紹

學完本文，您將了解不同相機模型分類、內參意義，及對應的應用代碼模型 標定的意義 建模三維世界點投影到二維圖像平面的過程。標定輸出的是相機模型。 相機模型 相機模型可以解理解為投影模型 +
閱讀更多...
Hyperlane：Rust Web開發的未來，釋放極致性能與簡潔之美

Hyperlane：Rust Web開發的未來，釋放極致性能與簡潔之美

Hyperlane&#xff1a;Rust Web開發的未來&#xff0c;釋放極致性能與簡潔之美 你是否厭倦了復雜的Web框架&#xff0c;想要一個既高效又易用的工具來構建現代Web應用&#xff1f;Hyperlane正是你需要的答案&#xff01;作為專為Rust打造的輕量級、高性能HTTP服務器庫&#xf…
閱讀更多...
STM32學習筆記之振蕩器（原理篇）

STM32學習筆記之振蕩器（原理篇）

&#x1f4e2;&#xff1a;如果你也對機器人、人工智能感興趣&#xff0c;看來我們志同道合? &#x1f4e2;&#xff1a;不妨瀏覽一下我的博客主頁【https://blog.csdn.net/weixin_51244852】 &#x1f4e2;&#xff1a;文章若有幸對你有幫助&#xff0c;可點贊 &#x1f44d;…
閱讀更多...
Stereolabs ZED Box Mini：機器人與自動化領域的人工智能視覺新選擇

Stereolabs ZED Box Mini：機器人與自動化領域的人工智能視覺新選擇

在人工智能視覺技術快速發展的今天&#xff0c;其應用場景正在持續拓寬&#xff0c;從智能安防到工業自動化&#xff0c;從機器人技術到智能交通&#xff0c;各領域都在積極探索如何利用這一先進技術。而 Stereolabs 推出的ZED Box Mini&#xff0c;正是一款專為滿足這些多樣化…
閱讀更多...
K8S學習之基礎五十九：部署gitlab服務

K8S學習之基礎五十九：部署gitlab服務

部署gitlab docker pull gitlab/gitlab-ce:latest docker tag gitlab/gitlab-ce:latest 172.16.80.140/gitlab/gitlab-ce:latest docker push 172.16.80.140/gitlab/gitlab-ce:latest docker run -d -p 443:443 -p 80:80 -p 222:22 --name gitlab --restart always -v /home/…
閱讀更多...
多線程 --- 多線程編程

多線程 --- 多線程編程

在寫代碼的時候&#xff0c;可以使用多進程進行并發編程&#xff08;在Java中&#xff0c;不太推薦&#xff0c;很多很多關于進程相關的API&#xff0c;在Java標準庫中&#xff0c;都沒有提供&#xff09;&#xff0c;也可以使用多線程進行并發編程&#xff08;系統提供了多線程…
閱讀更多...
HTML~視頻音頻在網頁中不能自動播放

HTML~視頻音頻在網頁中不能自動播放

問題&#xff1a; autoplay是打開自動播放&#xff0c;但是發現加了關鍵詞還是沒有反應 原因&#xff1a; 現在瀏覽器禁止自動播放&#xff08;特別是帶聲音的&#xff09; 解決辦法&#xff1a; 可以添加muted 進行沒有聲音的自動播放
閱讀更多...
vue中上傳接口file表單提交二進制文件流

vue中上傳接口file表單提交二進制文件流

1.使用elementui上傳組件 要做一個選擇文件后&#xff0c;先不上傳&#xff0c;等最后點擊確定后&#xff0c;把file二進制流及附加參數一起提交上去。 首先使用elementui中的上傳組件&#xff0c;設置auto-uploadfalse&#xff0c;也就是選擇文件后不立刻上傳。 <el-uplo…
閱讀更多...
Flutter環境配置

Flutter環境配置

配置環境變量 PUB_HOSTED_URLhttps://pub.flutter-io.cnFLUTTER_STORAGE_BASE_URLhttps://storage.flutter-io.cn 這個命令是用來配置 Flutter 的鏡像源地址&#xff0c;主要是為了解決在中國大陸地區訪問 Flutter 官方資源較慢的問題。 具體的操作如下&#xff1a; 右鍵點…
閱讀更多...
ngx_http_index_set_index

ngx_http_index_set_index

定義在 src\http\modules\ngx_http_index_module.c static char * ngx_http_index_set_index(ngx_conf_t *cf, ngx_command_t *cmd, void *conf) {ngx_http_index_loc_conf_t *ilcf conf;ngx_str_t *value;ngx_uint_t i, n;ngx_http_inde…
閱讀更多...
數據庫的視圖有什么用？

數據庫的視圖有什么用？

數據庫的視圖&#xff08;View&#xff09;是一種虛擬表&#xff0c;它的內容由查詢定義&#xff0c;并不實際存儲數據&#xff0c;而是動態生成。視圖的主要作用如下&#xff1a; 1. 簡化復雜查詢 場景&#xff1a;當查詢涉及多表連接、復雜過濾或聚合操作時&#xff0c;SQL …
閱讀更多...
Ubuntu 24.04 安裝 Docker 詳細教程

Ubuntu 24.04 安裝 Docker 詳細教程

前言 Docker 是目前最流行的容器化技術&#xff0c;它可以幫助開發者快速部署和運行應用程序。本文將詳細介紹在 Ubuntu 24.04 (Noble Numbat) 上安裝 Docker 的完整步驟&#xff0c;包括配置鏡像加速等實用技巧。 一、準備工作 1.1 系統要求 Ubuntu 24.04 LTS 具有 sudo 權…
閱讀更多...
鴻蒙進行視頻上傳，使用 request.uploadFile方法

鴻蒙進行視頻上傳，使用 request.uploadFile方法

一.拉起選擇器進行視頻選擇&#xff0c;并且創建文件名稱 async getPictureFromAlbum() {// 拉起相冊&#xff0c;選擇圖片let PhotoSelectOptions new photoAccessHelper.PhotoSelectOptions();PhotoSelectOptions.MIMEType photoAccessHelper.PhotoViewMIMETypes.VIDEO_TY…
閱讀更多...
C++ map容器總結

C++ map容器總結

map基本概念 簡介&#xff1a; map中所有元素都是pair pair中第一個元素為key&#xff08;鍵值&#xff09;&#xff0c;起到索引作用&#xff0c;第二個元素為value&#xff08;實值&#xff09; 所有元素都會根據元素的鍵值自動排序 本質&#xff1a; map/multimap屬于關…
閱讀更多...
【Zookeeper搭建(跟練版)】Zookeeper分布式集群搭建

【Zookeeper搭建(跟練版)】Zookeeper分布式集群搭建

&#xff08;一&#xff09;克隆前的準備 1. 用 xftp 發送文件 2. 時間同步&#xff1a; sudo cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime 3. zookpeeper 安裝 部署 呼應開頭發送的壓縮包&#xff0c;解壓&#xff1a; cd ~ tar -zxvf zookeeper-3.4.6.tar.g…
閱讀更多...
Flutter項目之頁面實現以及路由fluro

Flutter項目之頁面實現以及路由fluro

目錄&#xff1a; 1、項目代碼結構2、頁面編寫以及路由配置main.dart(入口文件)page_content.dartindex.dart&#xff08;首頁&#xff09;application.dart&#xff08;啟動加載類&#xff09;pubspec.yaml&#xff08;依賴配置文件&#xff09;login.dart&#xff08;登錄頁&…
閱讀更多...
記錄Jmeter 利用BeanShell 腳本解析JSON字符串

記錄Jmeter 利用BeanShell 腳本解析JSON字符串

下載org.json包(文檔說明) #下載地址 https://www.json.org/ # github 地址 https://github.com/stleary/JSON-java # api 文檔說明 https://resources.arcgis.com/en/help/arcobjects-java/api/arcobjects/com/esri/arcgis/server/json/JSONObject.htmlBeanShell腳本 import…
閱讀更多...
uniapp動態循環表單校驗失敗：初始值校驗

uniapp動態循環表單校驗失敗：初始值校驗

問題現象 &#x1f4a5; 在實現動態增減的單價輸入表單時&#xff08;基于uv-form組件&#xff09;&#xff0c;遇到以下詭異現象&#xff1a; <uv-input>的v-model綁定初始值為數字類型時&#xff0c;required規則失效 ?數字類型與字符串類型校驗表現不一致 &#x1…
閱讀更多...
UML 圖六種箭頭含義詳解：泛化、實現、依賴、關聯、聚合、組合

UML 圖六種箭頭含義詳解：泛化、實現、依賴、關聯、聚合、組合

目錄 一、泛化&#xff08;Generalization&#xff09; 概念 表示方法 二、實現&#xff08;Realization&#xff09; 概念 表示方法 三、依賴&#xff08;Dependency&#xff09; 概念 表示方法 四、關聯&#xff08;Association&#xff09; 概念 表示方法 五、…
閱讀更多...
Android Logcat總結

Android Logcat總結

文章目錄 Android Logcat總結日志格式過濾日志正向過濾反向過濾正則過濾日志等級 Android Logcat總結 日志格式 用法&#xff1a; Log.e("TAG", "hello") Log.i("TAG", "hello") Log.d("TAG", "hello")依次為&…
閱讀更多...
最新文章

Copyright @ 2022~2023