1、Nested Loop Join

2層循環連接，外層循環逐行檢索內層循環的每一行。最基礎的連接方式，表比較小且被驅動表有索引的情況下，效率很高。無索引or表較大時性能急劇下降。

2、Hash Join

分2個階段：

• 構建：選取表較小者基於連接字段在內存中構建一個哈希表
• 探測：遍歷較大表(探測表)，對其每行的連接字段應用同樣的哈希函數，快速定位到哈希表中對應位置，找到匹配的行。

特徵：適合大表等值連接、無需索引，但需要額外內存來建構哈希表

3、Sort Megre Join

分2個階段：

• 排序：將2個表各自按照連接字段進行排序
• 合併：像合併2個有序鏈表一樣，同時遍歷2個已排序的結果集，一次性找到所有匹配行

特點：當連接條件是非等值連接時，或已預先排序，可能比Hash Join更有效。排序本身可能需要消耗大量資源。

• 布爾類型：支持標準boolean類型.
• 值可以是true/false、true/false、yes/no、t/f、0/1。
• 邏輯操作符：AND、OR、NOT
• 數值類型
• smallint 2字節
• int 4字節
• bigint 8字節
• real 4字節浮點，不精確、變精度
• doulbe precision 8字節浮點數，不精確、變精度
• money 8字節，貨幣類型，保證精度，不通國家精度不一致
• 字符類型
• varchar （character varying）變長，最大1G，存儲空間：4 + 實際字符串長度
• char (character) 定長，不足則補空白，最大1G，存儲空間也是4 + n
• text 變長，無限制長度。
• 二進制類型：bytea類型，適合存儲原始字節數據，例如圖片
• 日期與時間類型
• date：日期，4字節
• time：時間，8字節，帶時區12字節
• timestamp：帶不帶時區都8字節。以2000年1月1日0點之前或之後的秒數存儲。
• interval ：12字節，時間間隔，用於日期時間加減計算等。
• 時區設置：select timestamp with time zone '2025-11-20 06:30:45 PST'
• 其他類型：枚舉、幾何、網絡地址、數組、符合類型、xml、range、偽類等

(補充)JSON類型

• json：數據原封不動存放至數據庫、使用時再解析。保留json串中key的順序，重複key僅保留最後一個。
• jsonb：存儲時將json轉換為二進制格式，使用時無需解析，且支持建索引，使用性能更高。不保留多餘空格、不保留key順序和重複key

json類型操作

select '[1,2,3]'::json->2 out:3

select '{"a":1,"b":2}'::json ->>'a' out：1

select '{"a":{"b":{"c":1}}}'::json #>'{a,b}' out：{"c":1}

->和->>的區別：->返回原類型，->>返回轉換後的text類型

jsonb '[1,2]' = jsonb '[1,2]' 檢測json對象內容一致性

@> 左包含

@< 右包含

? 檢測key是否存在

?| 檢測數組中是否有任意字符串存在於json的key中

?& 檢測數組中是否所有字符串存在於json的key中

jsonb索引

jsonb支持BTree和GIN索引，默認是GIN索引，因為BTree效率相對較低，不關心jsonb內部數據類型，僅簡單按整個jsonb大小方式進行排序。

1、CAST()函數

select cast('5' as int)

2、::雙冒號轉換

select '10'::int

current_date、current_timestamp

當前日期、時間戳

now()

返回當前時間戳（帶時區）

extract()、date_part() //二者等效

從日期/時間中抽取子域，返回double precision類型數據。可返回年份/季度/月份/第幾個星期/第幾天/時/分/秒等。

select extract(dom from now()) //獲取當前時間是星期幾

複合類型添加、新增用法

create type zyl as (  // 創建複合類型
z int,
y double precision,
l timestamp
)


//新增複合類型新增數據，可單獨為某個字段賦值，未標註的默認為NULL
insert into zhouyinglin values(row(10,10.00,now()),'zyl',now())
insert into zhouyinglin (zhou.z,ying,lin) values(100,'zyl3',now())
 


//查詢複合類型，使用括號隔開列名
select * from zhouyinglin where (zhou).z=10
select * from zhouyinglin where (zhouyinglin.zhou).z=10


//修改複合類型
update zhouyinglin set zhou=row(10,10.00,now()) where (zhou).z=10
update zhouyinglin set zhou.z=50.00 where (zhou).z=10

查看執行計劃：explain analyze verbose select .....

• explain：顯示查詢計劃
• 執行計劃節點：計劃由多個操作節點組成，例如Seq Scan、Index Scan、Hash Scan、Sort等。
• 樹形結構：節點以樹的形式組織，數據從葉子結點流向根節點。
• analyze：實際執行該查詢，收集每個步驟詳細的運行時間、返回的行數等
• Actual Time：該節點實際執行時間（毫秒）
• Rows：該節點實際返回的行數
• Loops：該節點被執行的次數（嵌套循環中可能會執行多次）
• verbose：輸出修飾符，展示比explain、analyze更詳細的信息
• 輸出列表：顯示每個節點輸出的列名
• 模式限定：表名以schema.table的形式顯示
• 表達式計算：如果查詢中有表達式，會顯示詳細求值信息

數據庫服務>數據庫>表/索引>行

模式：數據庫的一個命名空間或目錄，不同模式下可以有相同的表、函數等對象，而不會衝突。模式提出主要用於方便管理。在PG庫中，不能同時訪問不同數據庫對象，但可以訪問不同模式間的對象。類似Mysql中的不同的Database。類似Linkdo開發環境中，將linkdo和linkdoapi放在同一個庫的不同模式下。

pg在新建一個新數據庫時，會默認創建一個public模式登錄數據庫後，若沒有指定，則默認訪問public模式內的對象。

新建模式：create schema xxx

模式授權給用户：GRANT USAGE ON SCHEMA {schema_name} TO {username};

TOAST技術主要用於存儲大字段的值，由於PG頁面大小固定且不允許行跨越多個頁面，所以不能直接存儲較大的值，轉而壓縮或者拆分到系統表，即TOAST表。只有變長的數據類型才支持TOAST，變長數據類型中，前4字節（即32bit）成為長度字，長度字的高2bit是標誌位，後30bit是長度值，對應1GB。2bit標誌位分別對應【壓縮標記位】和【是否行外存儲】，不論是否壓縮、是否行外存儲，長度字內的30bit都表示數據的實際尺寸，而不是壓縮後的數據長度。

• 壓縮標記位：設置後，使用前需要先解壓縮
• 行外存儲：設置後，30bit長度位後面只是一個指針

修改字段設置

ALTER TABLE blog ALTER content SET STORAGE EXTERNAL; 

如果表裏有任意字段是支持TOAST的，那麼就會自動為該表創建一個關聯的TOAST表，行位內容就保存在這個表中。

• PLAIN：避免壓縮、行外存儲
• EXTENDED：允許壓縮和行外存儲，優先壓縮、後行外存儲
• EXTERNAL：允許行外存儲、不允許壓縮
• MAIN：允許壓縮、不允許行外存儲

PostgreSQL11之後，可通過參數toast_tuple_target 來控制TOAST觸發時機，單行數據在INSERT或UPDATE時，如果超過指定數值，就會將該行數據改為TOAST存儲。

alter table test01 set (toast_tuple_terget=128); //128單位為字節

toast_tuple_terget屬於單行優化存儲。另外，還可以通過fillfactor、toast.filltactor來控制數據更新時數據塊擴展時機。

fillfactor：當前表的填充因子

toast.fillfactor：當前表對應的toast表的填充因子

填充因子取值：10~100，表示數據塊在當前頁(page)INSERT多少之後就不再繼續填充了，僅保留做UPDATE使用。例如設置60，則剩下40只當做UPDATE使用。

PG在操作UPDATE時，原數據行並不會被覆蓋，而是會插入一條新數據行，由於新數據行仍在當前頁，因此可以通過Heap-Only Tuple在原行和新行之間建立一個鏈表，因此UPDATE後不需要重新建索引，索引仍會指向原行，並通過鏈表找到最新行。

問題：如果UPDATE插入到其他頁，則無法使用HOT，這時需要更新表上全部索引，會產生較大的開銷。因此，對於更新頻繁的表，需要設置一個較小的fillfactor值。

而Linkdo最常更新的linkdo_task_instance表，通過查詢系統目錄信息並沒有查到相關fillfactor配置，而是更加激進的配置：

SELECT reloptions AS table_options
FROM pg_class
WHERE relname = 'your_table_name';


["autovacuum_vacuum_scale_factor=0.02","autocvacuum_analyze_scale_factor=0.01"]

• autovacuum_vacuum_scale_factor=0.02：當表中有 2% 的行被更新或刪除時觸發 vacuum（默認是 20%，即0.2）
• autovacuum_analyze_scale_factor=0.01：當表中有 1% 的行被更改時觸發 analyze（默認是 10%。即0.1）

vacuum是PG庫中的空間清理工，用於：清理死元祖、更新可見性地圖等，analyze負責查詢優化，主要用於收集表的行數、頁數，每個列的數值分佈、最常見值、更新系統目錄。如果沒有這些信息，優化器可能會選擇很差的查詢計劃。

[數據變更] → [產生死元組] → [VACUUM清理] → [空間重用]
     ↓
[統計信息過時] → [ANALYZE更新] → [優化器獲得新信息]

臨時表

PG庫中臨時表有2種，一種是單純的臨時表，另一種是半持久化表。

• 臨時表：
• 會話級臨時表：數據保存在整個會話中
• 事務級臨時表：數據保存在整個事務中
• UNLOGGED表：不產生WAL日誌、數據庫異常則數據丟失，正常重啓不會丟失數據。在使用上與其他表沒有差異

// 創建臨時表如下4種寫法效果一致
create TEMPORARY table tmp_t1(id int primary key, note text);
create TEMP table tmp_t1(id int primary key, notes text)
create GLOBAL TEMPORARY table tmp_t1(id int primary key, note text);
create LOCAL TEMPORARY table tmp_t1(id int primary key, note text);


// 創建unlogged表
CREATE UNLOGGED TABLE unlogged01(id int primary key, t text);

檢查約束/非空約束/唯一約束/主鍵約束/外鍵約束

增刪字段

alter table table_name add column column_name type;
alter table table_name drop column column_nmae;

增刪約束

alter table table_name add check(age > 16)  //設置檢查約束
alter table table_name alter column column_name  set not null //設置非空約束


//刪除約束前需要知道約束名稱是什麼
alter table table_name drop constraint constraint_name 


//非空約束沒有名字，通過如下語法刪除
ALTER TABLE student ALTER COLUMN student_name DROP NOT NULL;

修改/刪除默認值

// 修改隻影響後續INSERT的默認值
alter table table_name alter column column_name set default 15;


// 刪除默認值，即將默認值修改為NULL
alter table table_name alter column column_name drop default;

修改字段數據類型

// 只有字段內所有項都可以隱式轉換為新類型時，才能修改字段類型，無法隱式轉換則會修改失敗
alter table table_name alter column column_name type text;

字段/表重命名

alter table table_name rename column age to age_num; //重命名字段


alter table table_name rename to table_name2; //改表明

pg庫的表是允許繼承的，且允許多繼承，繼承表的特性如下：

• 子表插入/更新數據後對父表可見，父表插入/更新數據後對子表不可見
• 如果只想查詢父表可使用關鍵字【only】
• 父表所有檢查約束、非空約束都會自動繼承給子表，其他約束不會繼承
• 子表繼承父表後，子表擁有所有父表字段總和，類型相同的字段會被融合，融合字段同樣繼承約束。

PG庫在版本10之前均通過表繼承實現分區表，而10之後提供了DDL語句創建聲明式分區表，但原理仍然是表繼承。表分區就是邏輯上把一個大表分割成物理上的幾塊，表繼承的優勢在於：

• 查詢、刪除更新等操作性能提升，避免全表掃描
• 訪問較少的歷史數據可以使用表空間技術移動到便宜的慢速存儲介質上

創建分區表步驟

1、創建父表，配置好字段。父表不插入數據、不定義約束。

2、創建子表，繼承父表字段且不新增字段，自定定義約束，通過關鍵字段來控制分區，併為關鍵字段創建索引。

3、定義一個觸發器，將原本插入主表的數據重定向到合適的分區表，