Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitc238442

Browse files
committed
Micro-optimize some slower queries in the opr_sanity regression test.
Convert the binary_coercible() and physically_coercible() functions fromSQL to plpgsql. It's not that plpgsql is inherently better at doingqueries; if you simply convert the previous single SQL query into oneRETURN expression, it's no faster. The problem with the existing codeis that it fools the plancache into deciding that it's worth re-planningthe query every time, since constant-folding with a concrete value for $2allows elimination of at least one sub-SELECT. In reality that's using theplanner to do the equivalent of a few runtime boolean tests, causing thefunction to run much slower than it should. Splitting the AND/OR logicinto separate plpgsql statements allows each if-expression to acquire astatic plan.Also, get rid of some uses of obj_description() in favor of explicitlyjoining to pg_description, allowing the joins to be optimized better.(Someday we might improve the SQL-function-inlining logic enough thatthis happens automatically, but today is not that day.)Together, these changes reduce the runtime of the opr_sanity regressiontest by about a factor of two on one of my slower machines. They don'tseem to help as much on a fast machine, but this should at least benefitthe buildfarm.
1 parentbc920be commitc238442

File tree

2 files changed

+100
-52
lines changed

2 files changed

+100
-52
lines changed

‎src/test/regress/expected/opr_sanity.out

Lines changed: 50 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -20,31 +20,47 @@
2020
-- allowed.
2121
-- This should match IsBinaryCoercible() in parse_coerce.c.
2222
create function binary_coercible(oid, oid) returns bool as $$
23-
SELECT ($1 = $2) OR
24-
EXISTS(select 1 from pg_catalog.pg_cast where
25-
castsource = $1 and casttarget = $2 and
26-
castmethod = 'b' and castcontext = 'i') OR
27-
($2 = 'pg_catalog.any'::pg_catalog.regtype) OR
28-
($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND
29-
EXISTS(select 1 from pg_catalog.pg_type where
30-
oid = $1 and typelem != 0 and typlen = -1)) OR
31-
($2 = 'pg_catalog.anyrange'::pg_catalog.regtype AND
32-
(select typtype from pg_catalog.pg_type where oid = $1) = 'r')
33-
$$ language sql strict stable;
23+
begin
24+
if $1 = $2 then return true; end if;
25+
if EXISTS(select 1 from pg_catalog.pg_cast where
26+
castsource = $1 and casttarget = $2 and
27+
castmethod = 'b' and castcontext = 'i')
28+
then return true; end if;
29+
if $2 = 'pg_catalog.any'::pg_catalog.regtype then return true; end if;
30+
if $2 = 'pg_catalog.anyarray'::pg_catalog.regtype then
31+
if EXISTS(select 1 from pg_catalog.pg_type where
32+
oid = $1 and typelem != 0 and typlen = -1)
33+
then return true; end if;
34+
end if;
35+
if $2 = 'pg_catalog.anyrange'::pg_catalog.regtype then
36+
if (select typtype from pg_catalog.pg_type where oid = $1) = 'r'
37+
then return true; end if;
38+
end if;
39+
return false;
40+
end
41+
$$ language plpgsql strict stable;
3442
-- This one ignores castcontext, so it considers only physical equivalence
3543
-- and not whether the coercion can be invoked implicitly.
3644
create function physically_coercible(oid, oid) returns bool as $$
37-
SELECT ($1 = $2) OR
38-
EXISTS(select 1 from pg_catalog.pg_cast where
39-
castsource = $1 and casttarget = $2 and
40-
castmethod = 'b') OR
41-
($2 = 'pg_catalog.any'::pg_catalog.regtype) OR
42-
($2 = 'pg_catalog.anyarray'::pg_catalog.regtype AND
43-
EXISTS(select 1 from pg_catalog.pg_type where
44-
oid = $1 and typelem != 0 and typlen = -1)) OR
45-
($2 = 'pg_catalog.anyrange'::pg_catalog.regtype AND
46-
(select typtype from pg_catalog.pg_type where oid = $1) = 'r')
47-
$$ language sql strict stable;
45+
begin
46+
if $1 = $2 then return true; end if;
47+
if EXISTS(select 1 from pg_catalog.pg_cast where
48+
castsource = $1 and casttarget = $2 and
49+
castmethod = 'b')
50+
then return true; end if;
51+
if $2 = 'pg_catalog.any'::pg_catalog.regtype then return true; end if;
52+
if $2 = 'pg_catalog.anyarray'::pg_catalog.regtype then
53+
if EXISTS(select 1 from pg_catalog.pg_type where
54+
oid = $1 and typelem != 0 and typlen = -1)
55+
then return true; end if;
56+
end if;
57+
if $2 = 'pg_catalog.anyrange'::pg_catalog.regtype then
58+
if (select typtype from pg_catalog.pg_type where oid = $1) = 'r'
59+
then return true; end if;
60+
end if;
61+
return false;
62+
end
63+
$$ language plpgsql strict stable;
4864
-- **************** pg_proc ****************
4965
-- Look for illegal values in pg_proc fields.
5066
SELECT p1.oid, p1.proname
@@ -1190,10 +1206,14 @@ WHERE d.classoid IS NULL AND p1.oid <= 9999;
11901206
-- be called directly; those should have comments matching their operator.
11911207
WITH funcdescs AS (
11921208
SELECT p.oid as p_oid, proname, o.oid as o_oid,
1193-
obj_description(p.oid, 'pg_proc') as prodesc,
1209+
pd.description as prodesc,
11941210
'implementation of ' || oprname || ' operator' as expecteddesc,
1195-
obj_description(o.oid, 'pg_operator') as oprdesc
1211+
od.description as oprdesc
11961212
FROM pg_proc p JOIN pg_operator o ON oprcode = p.oid
1213+
LEFT JOIN pg_description pd ON
1214+
(pd.objoid = p.oid and pd.classoid = p.tableoid and pd.objsubid = 0)
1215+
LEFT JOIN pg_description od ON
1216+
(od.objoid = o.oid and od.classoid = o.tableoid and od.objsubid = 0)
11971217
WHERE o.oid <= 9999
11981218
)
11991219
SELECT * FROM funcdescs
@@ -1210,10 +1230,14 @@ SELECT * FROM funcdescs
12101230
-- This should be a pretty short list; it's mostly legacy cases.
12111231
WITH funcdescs AS (
12121232
SELECT p.oid as p_oid, proname, o.oid as o_oid,
1213-
obj_description(p.oid, 'pg_proc') as prodesc,
1233+
pd.description as prodesc,
12141234
'implementation of ' || oprname || ' operator' as expecteddesc,
1215-
obj_description(o.oid, 'pg_operator') as oprdesc
1235+
od.description as oprdesc
12161236
FROM pg_proc p JOIN pg_operator o ON oprcode = p.oid
1237+
LEFT JOIN pg_description pd ON
1238+
(pd.objoid = p.oid and pd.classoid = p.tableoid and pd.objsubid = 0)
1239+
LEFT JOIN pg_description od ON
1240+
(od.objoid = o.oid and od.classoid = o.tableoid and od.objsubid = 0)
12171241
WHERE o.oid <= 9999
12181242
)
12191243
SELECT p_oid, proname, prodesc FROM funcdescs

‎src/test/regress/sql/opr_sanity.sql

Lines changed: 50 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -23,32 +23,48 @@
2323

2424
-- This should match IsBinaryCoercible() in parse_coerce.c.
2525
createfunctionbinary_coercible(oid,oid) returns boolas $$
26-
SELECT ($1= $2)OR
27-
EXISTS(select1frompg_catalog.pg_castwhere
28-
castsource= $1and casttarget= $2and
29-
castmethod='b'and castcontext='i')OR
30-
($2='pg_catalog.any'::pg_catalog.regtype)OR
31-
($2='pg_catalog.anyarray'::pg_catalog.regtypeAND
32-
EXISTS(select1frompg_catalog.pg_typewhere
33-
oid= $1and typelem!=0and typlen=-1))OR
34-
($2='pg_catalog.anyrange'::pg_catalog.regtypeAND
35-
(select typtypefrompg_catalog.pg_typewhereoid= $1)='r')
36-
$$ language sql strict stable;
26+
begin
27+
if $1= $2 then return true; end if;
28+
if EXISTS(select1frompg_catalog.pg_castwhere
29+
castsource= $1and casttarget= $2and
30+
castmethod='b'and castcontext='i')
31+
then return true; end if;
32+
if $2='pg_catalog.any'::pg_catalog.regtype then return true; end if;
33+
if $2='pg_catalog.anyarray'::pg_catalog.regtype then
34+
if EXISTS(select1frompg_catalog.pg_typewhere
35+
oid= $1and typelem!=0and typlen=-1)
36+
then return true; end if;
37+
end if;
38+
if $2='pg_catalog.anyrange'::pg_catalog.regtype then
39+
if (select typtypefrompg_catalog.pg_typewhereoid= $1)='r'
40+
then return true; end if;
41+
end if;
42+
return false;
43+
end
44+
$$ language plpgsql strict stable;
3745

3846
-- This one ignores castcontext, so it considers only physical equivalence
3947
-- and not whether the coercion can be invoked implicitly.
4048
createfunctionphysically_coercible(oid,oid) returns boolas $$
41-
SELECT ($1= $2)OR
42-
EXISTS(select1frompg_catalog.pg_castwhere
43-
castsource= $1and casttarget= $2and
44-
castmethod='b')OR
45-
($2='pg_catalog.any'::pg_catalog.regtype)OR
46-
($2='pg_catalog.anyarray'::pg_catalog.regtypeAND
47-
EXISTS(select1frompg_catalog.pg_typewhere
48-
oid= $1and typelem!=0and typlen=-1))OR
49-
($2='pg_catalog.anyrange'::pg_catalog.regtypeAND
50-
(select typtypefrompg_catalog.pg_typewhereoid= $1)='r')
51-
$$ language sql strict stable;
49+
begin
50+
if $1= $2 then return true; end if;
51+
if EXISTS(select1frompg_catalog.pg_castwhere
52+
castsource= $1and casttarget= $2and
53+
castmethod='b')
54+
then return true; end if;
55+
if $2='pg_catalog.any'::pg_catalog.regtype then return true; end if;
56+
if $2='pg_catalog.anyarray'::pg_catalog.regtype then
57+
if EXISTS(select1frompg_catalog.pg_typewhere
58+
oid= $1and typelem!=0and typlen=-1)
59+
then return true; end if;
60+
end if;
61+
if $2='pg_catalog.anyrange'::pg_catalog.regtype then
62+
if (select typtypefrompg_catalog.pg_typewhereoid= $1)='r'
63+
then return true; end if;
64+
end if;
65+
return false;
66+
end
67+
$$ language plpgsql strict stable;
5268

5369

5470
-- **************** pg_proc ****************
@@ -725,10 +741,14 @@ WHERE d.classoid IS NULL AND p1.oid <= 9999;
725741
-- be called directly; those should have comments matching their operator.
726742
WITH funcdescsAS (
727743
SELECTp.oidas p_oid, proname,o.oidas o_oid,
728-
obj_description(p.oid,'pg_proc')as prodesc,
744+
pd.descriptionas prodesc,
729745
'implementation of'|| oprname||' operator'as expecteddesc,
730-
obj_description(o.oid,'pg_operator')as oprdesc
746+
od.descriptionas oprdesc
731747
FROM pg_proc pJOIN pg_operator oON oprcode=p.oid
748+
LEFT JOIN pg_description pdON
749+
(pd.objoid=p.oidandpd.classoid=p.tableoidandpd.objsubid=0)
750+
LEFT JOIN pg_description odON
751+
(od.objoid=o.oidandod.classoid=o.tableoidandod.objsubid=0)
732752
WHEREo.oid<=9999
733753
)
734754
SELECT*FROM funcdescs
@@ -742,10 +762,14 @@ SELECT * FROM funcdescs
742762
-- This should be a pretty short list; it's mostly legacy cases.
743763
WITH funcdescsAS (
744764
SELECTp.oidas p_oid, proname,o.oidas o_oid,
745-
obj_description(p.oid,'pg_proc')as prodesc,
765+
pd.descriptionas prodesc,
746766
'implementation of'|| oprname||' operator'as expecteddesc,
747-
obj_description(o.oid,'pg_operator')as oprdesc
767+
od.descriptionas oprdesc
748768
FROM pg_proc pJOIN pg_operator oON oprcode=p.oid
769+
LEFT JOIN pg_description pdON
770+
(pd.objoid=p.oidandpd.classoid=p.tableoidandpd.objsubid=0)
771+
LEFT JOIN pg_description odON
772+
(od.objoid=o.oidandod.classoid=o.tableoidandod.objsubid=0)
749773
WHEREo.oid<=9999
750774
)
751775
SELECT p_oid, proname, prodescFROM funcdescs

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp