PostgreSQL的查询技巧: 零除, GENERATED STORED, COUNT DISTINCT, JOIN和数组LIKE

零除的处理

用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[])