user2156353
Reputation: 45
PostgreSQL : Optimizing function with virtuals tables without "UNION SELECT"
I'm wondering if it possible to optimize this function without creating any table and indexes.
This is the function :
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;
When i invoke the function with this query, it takes around 10 seconds :
SELECT (decode_trame_v3(id)).* FROM data
LIMIT 1000
And then i have simplified the code with this new function :
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;
I was thinking it will be faster but this new function takes around 12-13 seconds (always with the same query).
It seems that the "UNION SELECT" is slowing the query.
What could i do to speed up the query without creating any tables or indexes ?
Many thanks in advance for your help !
EDIT :
I find my question too imprecise and too large, so i have cut my question to a simple question :
I'm wondering if it is possible to create virtual tables without "UNION SELECT" ?
I don't want to create tables or temp tables.
Now for creating virtual tables with data, i use :
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
I dont' find this very beautiful and easy to use.
Is there other methods to to the same job ?
Sorry for new people who cames after !
Upvotes: 2
Views: 71
Answers (2)
Patrick
Reputation: 32179
A rather radical cleanup makes it look like this:
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;
First of all, your entire CTE has become a single array and it is constructed outside of the loop, so it gets created just once. Inside the loop you find the "value", which is now the array index with the array_position(). Because you have multiple calls with the same arguments I have made 7 variables v1..v7 to hold the array indexes. The SELECTs have been replaced by simple assignment and then the loop becomes very simple and readable. The variable v_waste_number gets incremented by 7 so you can forget about all the multiplications.
Note that array_position() is new in PG 9.5. If you have an older version, you should make ctv char(64) := '0123...' and then use:
v1 := position(substring(v_weights from 1+v_waste for 1) in cvt) - 1;
...
This is probably going to be somewhat slower, but hard to say by how much. Note the - 1 at the end to make the result 0-based.
Now you can actually "see" your algorithm again and better you check it because I may have removed a few too many parentheses (although it don't think I did).
Upvotes: 1
user330315
Reputation:
Use a row constructor using the values keyword:
WITH convert_table (letter, value) AS
(
values
('-', 63),
(':', 62),
('z', 61),
...
)
SELECT *
FROM convert_table;
You actually don't need the CTE, you can use a VALUES clause directly:
select *
from (
values
('-', 63),
(':', 62),
('z', 61)
) as convert_table (letter, value);
The CTE is however more convenient if you are using the list of values more than once in the final query.
But this won't be faster than using a UNION, just less typing.
Upvotes: 2
Related Questions
- postgresql list and order tables by size
- Find all tables containing column with specified name
- How can I drop all the tables in a PostgreSQL database?
- Best way to select random rows PostgreSQL
- PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL
- How to show tables in PostgreSQL?
- How can I list the tables in a SQLite database file that was opened with ATTACH?
- How to list the tables of schemas in PostgreSQL?
- Insert text with single quotes in PostgreSQL