小贝子编程

PostgreSQL ： 使用虚拟表优化功能而无需"UNION SELECT"



我想知道是否可以在不创建任何表和索引的情况下优化此功能。

这是函数： 

CREATE OR REPLACE FUNCTION decode_trame_v3(IN tid integer)
  RETURNS TABLE(id integer, card numeric, kilo double precision) AS
$BODY$
DECLARE
    v_weights character varying;
    v_waste_no integer = 0;
    v_number_waste integer;
BEGIN
SELECT weights FROM data WHERE data.id = tid INTO v_weights;
SELECT COALESCE(length(v_weights)/7, 0) INTO v_number_waste;
LOOP
EXIT WHEN v_number_waste = v_waste_no;
RETURN query
WITH convert_table (letter, value) AS (
SELECT '-' AS letter, 63 AS value
UNION SELECT ':', 62
UNION SELECT 'z', 61
UNION SELECT 'y', 60
UNION SELECT 'x', 59
UNION SELECT 'w', 58
UNION SELECT 'v', 57
UNION SELECT 'u', 56
UNION SELECT 't', 55
UNION SELECT 's', 54
UNION SELECT 'r', 53
UNION SELECT 'q', 52
UNION SELECT 'p', 51
UNION SELECT 'o', 50
UNION SELECT 'n', 49
UNION SELECT 'm', 48
UNION SELECT 'l', 47
UNION SELECT 'k', 46
UNION SELECT 'j', 45
UNION SELECT 'i', 44
UNION SELECT 'h', 43
UNION SELECT 'g', 42
UNION SELECT 'f', 41
UNION SELECT 'e', 40
UNION SELECT 'd', 39
UNION SELECT 'c', 38
UNION SELECT 'b', 37
UNION SELECT 'a', 36
UNION SELECT 'Z', 35
UNION SELECT 'Y', 34
UNION SELECT 'X', 33
UNION SELECT 'W', 32
UNION SELECT 'V', 31
UNION SELECT 'U', 30
UNION SELECT 'T', 29
UNION SELECT 'S', 28
UNION SELECT 'R', 27
UNION SELECT 'Q', 26
UNION SELECT 'P', 25
UNION SELECT 'O', 24
UNION SELECT 'N', 23
UNION SELECT 'M', 22
UNION SELECT 'L', 21
UNION SELECT 'K', 20
UNION SELECT 'J', 19
UNION SELECT 'I', 18
UNION SELECT 'H', 17
UNION SELECT 'G', 16
UNION SELECT 'F', 15
UNION SELECT 'E', 14
UNION SELECT 'D', 13
UNION SELECT 'C', 12
UNION SELECT 'B', 11
UNION SELECT 'A', 10
UNION SELECT '9', 9
UNION SELECT '8', 8
UNION SELECT '7', 7
UNION SELECT '6', 6
UNION SELECT '5', 5
UNION SELECT '4', 4
UNION SELECT '3', 3
UNION SELECT '2', 2
UNION SELECT '1', 1
UNION SELECT '0', 0
)
SELECT tid AS id,
(((SELECT value FROM convert_table WHERE letter =substring(v_weights from 4+(v_waste_no*7) for 1)::character varying) % 8)*2 + 
trunc((SELECT value FROM convert_table WHERE letter =substring(v_weights from 5+(v_waste_no*7) for 1)::character varying)/32, 0)
) * 1 + 
(((SELECT value FROM convert_table WHERE letter =substring(v_weights from 3+(v_waste_no*7) for 1)::character varying) % 2)*8 +
trunc((SELECT value FROM convert_table WHERE letter =substring(v_weights from 4+(v_waste_no*7) for 1)::character varying) / 8, 0)
)*16 +
(trunc((SELECT value FROM convert_table WHERE letter =substring(v_weights from 3+(v_waste_no*7) for 1)::character varying) / 2, 0) -
trunc((SELECT value FROM convert_table WHERE letter =substring(v_weights from 3+(v_waste_no*7) for 1)::character varying) /32 ,0)*16
) * 256 +
(((SELECT value FROM convert_table WHERE letter =substring(v_weights from 2+(v_waste_no*7) for 1)::character varying) % 8 ) * 2 +
trunc((SELECT value FROM convert_table WHERE letter =substring(v_weights from 3+(v_waste_no*7) for 1)::character varying) / 32, 0)
) * 4096 +
(trunc((SELECT value FROM convert_table WHERE letter =substring(v_weights from 2+(v_waste_no*7) for 1)::character varying) / 8, 0) +
((SELECT value FROM convert_table WHERE letter = substring(v_weights from 1+(v_waste_no*7) for 1)::character varying) % 2) * 8
) * 65536 +
(trunc((SELECT value FROM convert_table WHERE letter = substring(v_weights from 1+(v_waste_no*7) for 1)::character varying) / 2, 0)
) * 1048576
AS card,
(((SELECT value FROM convert_table WHERE letter =substring(v_weights from 6+(v_waste_no*7) for 1)::character varying) % 16) * 64 +
(SELECT value FROM convert_table WHERE letter =substring(v_weights from 7+(v_waste_no*7) for 1)::character varying)) * 50 /1000::real
AS kilo;

v_waste_no = v_waste_no + 1;
END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

当我使用此查询调用函数时，大约需要 10 秒：

SELECT (decode_trame_v3(id)).* FROM data
LIMIT 1000

然后我用这个新函数简化了代码：

CREATE OR REPLACE FUNCTION decode_trame_v3(IN tid integer)
  RETURNS TABLE(id integer, card numeric, kilo double precision) AS
$BODY$
DECLARE
    v_weights character varying;
    v_waste_no integer = 0;
    v_number_waste integer;
BEGIN
SELECT weights FROM data WHERE data.id = tid INTO v_weights;
SELECT COALESCE(length(v_weights)/7, 0) INTO v_number_waste;
LOOP
EXIT WHEN v_number_waste = v_waste_no;
RETURN query
WITH convert_table (letter, value) AS (
SELECT '-' AS letter, 63 AS value
UNION SELECT ':', 62
UNION SELECT 'z', 61
UNION SELECT 'y', 60
UNION SELECT 'x', 59
UNION SELECT 'w', 58
UNION SELECT 'v', 57
UNION SELECT 'u', 56
UNION SELECT 't', 55
UNION SELECT 's', 54
UNION SELECT 'r', 53
UNION SELECT 'q', 52
UNION SELECT 'p', 51
UNION SELECT 'o', 50
UNION SELECT 'n', 49
UNION SELECT 'm', 48
UNION SELECT 'l', 47
UNION SELECT 'k', 46
UNION SELECT 'j', 45
UNION SELECT 'i', 44
UNION SELECT 'h', 43
UNION SELECT 'g', 42
UNION SELECT 'f', 41
UNION SELECT 'e', 40
UNION SELECT 'd', 39
UNION SELECT 'c', 38
UNION SELECT 'b', 37
UNION SELECT 'a', 36
UNION SELECT 'Z', 35
UNION SELECT 'Y', 34
UNION SELECT 'X', 33
UNION SELECT 'W', 32
UNION SELECT 'V', 31
UNION SELECT 'U', 30
UNION SELECT 'T', 29
UNION SELECT 'S', 28
UNION SELECT 'R', 27
UNION SELECT 'Q', 26
UNION SELECT 'P', 25
UNION SELECT 'O', 24
UNION SELECT 'N', 23
UNION SELECT 'M', 22
UNION SELECT 'L', 21
UNION SELECT 'K', 20
UNION SELECT 'J', 19
UNION SELECT 'I', 18
UNION SELECT 'H', 17
UNION SELECT 'G', 16
UNION SELECT 'F', 15
UNION SELECT 'E', 14
UNION SELECT 'D', 13
UNION SELECT 'C', 12
UNION SELECT 'B', 11
UNION SELECT 'A', 10
UNION SELECT '9', 9
UNION SELECT '8', 8
UNION SELECT '7', 7
UNION SELECT '6', 6
UNION SELECT '5', 5
UNION SELECT '4', 4
UNION SELECT '3', 3
UNION SELECT '2', 2
UNION SELECT '1', 1
UNION SELECT '0', 0
),
separate_weights(coordonate, letter) AS (
SELECT 'K21', (SELECT value FROM convert_table WHERE letter = substring(v_weights from 1 +(v_waste_no*7) for 1)::character varying)
UNION SELECT 'L21', (SELECT value FROM convert_table WHERE letter =substring(v_weights from 2 +(v_waste_no*7) for 1)::character varying)
UNION SELECT 'M21', (SELECT value FROM convert_table WHERE letter =substring(v_weights from 3 +(v_waste_no*7) for 1)::character varying)
UNION SELECT 'N21', (SELECT value FROM convert_table WHERE letter =substring(v_weights from 4 +(v_waste_no*7) for 1)::character varying)
UNION SELECT 'O21', (SELECT value FROM convert_table WHERE letter =substring(v_weights from 5 +(v_waste_no*7) for 1)::character varying)
UNION SELECT 'P21', (SELECT value FROM convert_table WHERE letter =substring(v_weights from 6 +(v_waste_no*7) for 1)::character varying)
UNION SELECT 'Q21', (SELECT value FROM convert_table WHERE letter =substring(v_weights from 7 +(v_waste_no*7) for 1)::character varying)
),
calc_weights(coordonate, value) AS (
SELECT 'S21', trunc(( SELECT letter FROM separate_weights WHERE coordonate = 'K21') / 2, 0)
UNION SELECT 'T21', trunc(( SELECT letter FROM separate_weights WHERE coordonate = 'L21') / 8, 0) + (( SELECT letter FROM separate_weights WHERE coordonate = 'K21') % 2) * 8
UNION SELECT 'U21', (( SELECT letter FROM separate_weights WHERE coordonate = 'L21') % 8) * 2 + trunc(( SELECT letter FROM separate_weights WHERE coordonate = 'M21') / 32, 0)
UNION SELECT 'V21', trunc(( SELECT letter FROM separate_weights WHERE coordonate = 'M21') / 2, 0) - trunc(( SELECT letter FROM separate_weights WHERE coordonate = 'M21') / 32, 0) * 16
UNION SELECT 'W21', (( SELECT letter FROM separate_weights WHERE coordonate = 'M21') % 2) * 8 + trunc(( SELECT letter FROM separate_weights WHERE coordonate = 'N21') / 8, 0)
UNION SELECT 'X21', (( SELECT letter FROM separate_weights WHERE coordonate = 'N21') % 8) * 2 + trunc(( SELECT letter FROM separate_weights WHERE coordonate = 'O21') / 32, 0)
),
card(card) AS (
SELECT (
    (SELECT value FROM calc_weights WHERE coordonate = 'X21') * 1 +
    (SELECT value FROM calc_weights WHERE coordonate = 'W21') * 16 +
    (SELECT value FROM calc_weights WHERE coordonate = 'V21') * 256 +
    (SELECT value FROM calc_weights WHERE coordonate = 'U21') * 4096 +
    (SELECT value FROM calc_weights WHERE coordonate = 'T21') * 65536 +
    (SELECT value FROM calc_weights WHERE coordonate = 'S21') * 1048576
    )
),
kilo(kilo) AS (
SELECT (
((( SELECT letter FROM separate_weights WHERE coordonate = 'P21') % 16) * 64 + ( SELECT letter FROM separate_weights WHERE coordonate = 'Q21')) * 50 /1000::double precision
)
)
SELECT tid AS id, (SELECT card.card FROM card) AS card, (SELECT kilo.kilo FROM kilo) AS kilo;
v_waste_no = v_waste_no + 1;
END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

我以为它会更快，但这个新功能大约需要 12-13 秒（始终使用相同的查询）。

似乎"UNION SELECT"正在减慢查询速度。

我可以做些什么来加快查询速度而不创建任何表或索引？

提前感谢您的帮助！

编辑：

我

发现我的问题太不精确和太大，所以我把我的问题切成一个简单的问题：

我想知道是否可以在没有"UNION SELECT"的情况下创建虚拟表？

我不想创建表或临时表。

现在为了使用数据创建虚拟表，我使用：

WITH convert_table (letter, value) AS (
SELECT '-' AS letter, 63 AS value
UNION SELECT ':', 62
UNION SELECT 'z', 61
UNION SELECT 'y', 60
UNION SELECT 'x', 59
UNION SELECT 'w', 58
UNION SELECT 'v', 57
UNION SELECT 'u', 56
UNION SELECT 't', 55
UNION SELECT 's', 54
UNION SELECT 'r', 53
UNION SELECT 'q', 52
UNION SELECT 'p', 51
...)
SELECT * FROM convert_table

我不觉得这很漂亮而且易于使用。

有没有其他方法可以完成相同的工作？

对不起

，新来的人来了！
使用

values 关键字使用行构造函数：

WITH convert_table (letter, value) AS 
(
   values 
    ('-', 63), 
    (':', 62), 
    ('z', 61), 
    ...
)
SELECT * 
FROM convert_table;

您实际上不需要 CTE，可以直接使用 VALUES 子句：

select *
from (
   values 
    ('-', 63), 
    (':', 62), 
    ('z', 61)
) as convert_table (letter, value);

但是，如果在最终查询中多次使用值列表，则 CTE 会更方便。

但这不会比使用 UNION 更快，只是打字更少。

一个相当激进的清理使它看起来像这样：

CREATE OR REPLACE FUNCTION decode_trame_v3(tid integer)
  RETURNS TABLE(id integer, card numeric, kilo double precision) AS
$BODY$
DECLARE
    v_weights character varying;
    v_waste_no integer := 0;
    v_number_waste integer;
    cvt char[];
    v1 int;
    v2 int;
    v3 int;
    v4 int;
    v5 int;
    v6 int;
    v7 int;
BEGIN
  SELECT weights INTO v_weights FROM data WHERE data.id = tid;
  v_number_waste := coalesce(length(v_weights)/7, 0) * 7;
  cvt := '[0:63]={0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,'
                 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,:,-}';
  id := tid;
  LOOP
    EXIT WHEN v_waste_no = v_number_waste;
    v1 := array_position(cvt, substring(v_weights from 1+v_waste for 1));
    v2 := array_position(cvt, substring(v_weights from 2+v_waste for 1));
    v3 := array_position(cvt, substring(v_weights from 3+v_waste for 1));
    v4 := array_position(cvt, substring(v_weights from 4+v_waste for 1));
    v5 := array_position(cvt, substring(v_weights from 5+v_waste for 1));
    v6 := array_position(cvt, substring(v_weights from 6+v_waste for 1));
    v7 := array_position(cvt, substring(v_weights from 7+v_waste for 1));
    card := (v4 % 8) * 2 + 
            trunc(v5/32, 0) * 1 + 
            (v5 % 2) * 8 +
            trunc(v4 / 8, 0) * 16 +
            trunc(v3 / 2, 0) -
            trunc(v3 /32 ,0) * 16 * 256 +
            (v2 % 8 ) * 2 +
            trunc(v3 / 32, 0) * 4096 +
            trunc(v2 / 8, 0) +
            (v1 % 2) * 8 * 65536 +
            trunc(v1 / 2, 0) * 1048576;
    kilo := (v6 % 16) * 64 + v7 * 50 / 1000.;
    RETURN NEXT;
    v_waste_no = v_waste_no + 7;
  END LOOP;
  RETURN;
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000;

首先，您的整个 CTE 已成为单个数组，它是在循环之外构造的，因此只需创建一次。在循环中，您可以找到"值"，现在是带有array_position()的数组索引。因为您有多个具有相同参数的调用，所以我v1..v7 7 个变量来保存数组索引。SELECT已被简单的赋值所取代，然后循环变得非常简单且可读。变量v_waste_number递增 7，因此您可以忘记所有乘法。

请注意，array_position()是 PG 9.5 中的新功能。如果您有旧版本，则应ctv char(64) := '0123...'，然后使用：

v1 := position(substring(v_weights from 1+v_waste for 1) in cvt) - 1;
...

这可能会慢一些，但很难说有多少。请注意末尾的- 1，使结果从 0 开始。

现在你实际上可以再次"看到"你的算法，最好检查一下，因为我可能删除了太多的括号（尽管它不认为我这样做了）。

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium