用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
对于读多写少的表, 这是一个高效的性能提升方法, 对已知表可以增加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;
注意
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 方式, 只是因为各种原因还没有实现.
使用下面的方式, 查询时间能缩短一半以上
SELECT COUNT(col) FROM ( SELECT DISTINCT field_1 AS col FROM table_1 ) TEMP
针对这个性能问题的扩展 count_distinct, 安装之后可以使用COUNT_DISTINCT()
函数代替COUNT(DISTINCT ...), 但是缺点是费内存, 而且对参数有长度限制.
使用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挂接大量属性的, 翻页结果通过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
PostgreSQl 的LIKE用法
LIKE
NOT LIKE
LIKE ANY(ARRAY[])
如果需要相似任意一个参数, 需要使用这个语法NOT LIKE ALL(ARRAY[])
如果想达到不相似任意一个参数, 需要用这个语法ILIKE是不区分大小写的LIKE
ILIKE
NOT ILIKE
ILIKE ANY(ARRAY[])
NOT ILIKE ALL(ARRAY[])