零除的處理

用NULLIF(col, 0)可以避免復雜的WHEN...CASE判斷, 例如

ROUND(COUNT(view_50.amount_in)::NUMERIC / NULLIF(COUNT(view_50.amount_out)::NUMERIC, 0),2) AS out_divide_in,

使用 COLA / NULLIF(COLB,0) 后, 如果 COLB 為0, 產生的輸出就是 NULL

空陣列決議為默認值

使用jsonb_array_elements_text()可以將一個 JSONB 型別的列決議為多行 -- 這是一個和聚合背道而馳的函式, 在處理陣列時特別有用. 但是在使用中, 往往需要全域統計, 不單單是統計帶內容的陣列, 還需要統計空陣列, 這時候可以區分兩種情況分別處理

空陣列使用 NULL 標識

這時候可以直接使用coalesce()將空值轉為一個包含null的陣列, 對應的SQL為

SELECT
  name,
  jsonb_array_elements_text(coalesce("category", '[null]')) tag
FROM table;

這樣查詢的結果, 如果category為NULL, 也會產生一行記錄

空陣列使用[]標識

這時候需要先用NULLIF()將[]轉為空, 再使用上面的方式, 就能產生空值的記錄

SELECT
	jsonb_array_elements_text( COALESCE( NULLIF(array_field, '[]'), '[null]')) AS "item",
	COALESCE( SUM(amount), 0) AS "total" 
FROM
	some_tbl 
WHERE
	amount > 100
GROUP BY
	jsonb_array_elements_text( COALESCE( NULLIF(array_field, '[]'), '[null]')) 

GENERATED 欄位, GENERATED..STORED

對于讀多寫少的表, 這是一個高效的性能提升方法, 對已知表可以增加Generated欄位, 這些欄位只讀, 自動計算賦值, 可以像普通欄位一樣參與查詢, 不需要在查詢中實時計算, 是一種典型的使用空間換時間的優化方式.

ALTER TABLE "bank_card"
    ADD COLUMN "card_num_in" varchar(255) 	GENERATED ALWAYS AS (CASE WHEN direction = 'IN' THEN card_num ELSE NULL END) STORED,
    ADD COLUMN "card_num_out" varchar(255) 	GENERATED ALWAYS AS (CASE WHEN direction = 'OUT' THEN card_num ELSE NULL END) STORED,
    ADD COLUMN "amount_in" numeric(53,2) 	GENERATED ALWAYS AS (CASE WHEN direction = 'IN' THEN amount ELSE NULL END) STORED,
    ADD COLUMN "amount_out" numeric(53,2) 	GENERATED ALWAYS AS (CASE WHEN direction = 'OUT' THEN amount ELSE NULL END) STORED;

注意

• PostgreSQL 14 只有 STORED 型別的欄位, 還不能使用 VIRTUAL 型別
• 這樣的欄位是只讀的, INSERT 的時候不能往這些欄位寫入
• GENERATED 欄位不帶索引, 如果基于帶索引的欄位創建 GENERATED 欄位, 在 GENERATED 欄位上檢索, 性能可能反而更差, 可以通過給 GENERATED 欄位建索引解決.

COUNT DISTINCT 優化

COUNT DISTINCT 的性能問題

COUNT DISTINCT 的性能是PostgreSQL中長期存在的問題, 在版本14中尚未解決. 在資料量大的時候, 這個查詢會很慢, 千萬級別的表可能需要10秒左右才能回傳結果

SELECT
	COUNT(DISTINCT field_1)
FROM
	table_1

原因鏈接

count(distinct ...) always sorts, rather than using a hash, to do its work. I don't think that there is any fundamental reason that it could not be changed to allow it to use hashing, it just hasn't been done yet. It is complicated by the fact that you can have multiple count() expressions in the same query which demand sorting/grouping on different columns.

PostgreSQL 的 count(distinct ...) 的實作方式是排序而不是使用 hash, 所以速度很慢. 應該要換成 hash 方式, 只是因為各種原因還沒有實作.

規避途徑一: 通過 COUNT 子查詢

使用下面的方式, 查詢時間能縮短一半以上

SELECT
	COUNT(col)
FROM (
	SELECT DISTINCT field_1 AS col FROM table_1
) TEMP

規避途徑二: 通過 COUNT_DISTINCT 擴展

針對這個性能問題的擴展 count_distinct, 安裝之后可以使用COUNT_DISTINCT()函式代替COUNT(DISTINCT ...), 但是缺點是費記憶體, 而且對引數有長度限制.

規避途徑三: 通過 GROUP BY

使用GROUP BY代替DISTINCT, 下面的例子, 對 field_1 和 field_2 建聯合索引, 速度會非常快

SELECT COUNT(field_2), field_1, field_2
FROM table_1
GROUP BY field_1, field_2

對于復雜場景, 可以對 GROUP BY 之后的結果建立視圖, 而后以子查詢的形式取值

優化JOIN性能

JOIN查詢, 需要限定JOIN的范圍, 例如對于一個翻頁查詢, 需要對翻頁的結果通過JOIN掛接大量屬性的, 翻頁結果通過LEFT JOIN連接到多個屬性表, 就應該將翻頁結果限制數量后, 再進行關聯, 這樣性能會好很多, 例如

Preparing : SELECT
"view_46"."id",
"view_46"."name",
"label_view6"."labels" AS "1___label",
"label_view7"."labels" AS "21022___label",
"label_view8"."labels" AS "21023___label",
"label_view9"."labels" AS "50197___label" 
FROM
    -- 這行是關鍵, 因為主體在ID上有索引, 偏移查詢是很快的, 先限制結果集大小, 然后再進行JOIN
	( SELECT * FROM "view_46" ORDER BY ID ASC LIMIT 10 OFFSET 14270 ) AS "view_46"
	LEFT JOIN "label_view" AS "label_view6" ON (
		"label_view6"."item_type" = '1' 
		AND "label_view6"."item_name" = '1' 
		AND "label_view6"."item_attr" = '2' 
		AND "label_view6"."item_id" = "view_46"."id" :: TEXT 
	)
	LEFT JOIN "label_view" AS "label_view7" ON (
		"label_view7"."item_type" = '1' 
		AND "label_view7"."item_name" = '21022' 
		AND "label_view7"."item_attr" = '2' 
		AND "label_view7"."item_id" = "view_46"."id" :: TEXT 
	)
	LEFT JOIN "label_view" AS "label_view8" ON (
		"label_view8"."item_type" = '1' 
		AND "label_view8"."item_name" = '21023' 
		AND "label_view8"."item_attr" = '2' 
		AND "label_view8"."item_id" = "view_46"."id" :: TEXT 
	)
	LEFT JOIN "label_view" AS "label_view9" ON (
		"label_view9"."item_type" = '1' 
		AND "label_view9"."item_name" = '50197' 
		AND "label_view9"."item_attr" = '2' 
		AND "label_view9"."item_id" = "view_46"."id" :: TEXT 
	) 
ORDER BY
ID ASC

LIKE ARRAY的用法

PostgreSQl 的LIKE用法

LIKE

• LIKE
• NOT LIKE
• LIKE ANY(ARRAY[]) 如果需要相似任意一個引數, 需要使用這個語法
• NOT LIKE ALL(ARRAY[]) 如果想達到不相似任意一個引數, 需要用這個語法

ILIKE

ILIKE是不區分大小寫的LIKE

• ILIKE
• NOT ILIKE
• ILIKE ANY(ARRAY[])
• NOT ILIKE ALL(ARRAY[])

標籤：PostgreSQL

上一篇：PostgreSQL 9.1 飛升之路

下一篇：PostgreSQL世界上最先進的開源關系型資料庫