mogdb/opengauss数据库执行计划缓存/失效机制的测试 -m6米乐安卓版下载

1.强制对临时对象使用commit而不是2pc

postgres=# set enforce_two_phase_commit to off;
set

2.创建测试表并插入测试数据

postgres=# create temp table tab_test_plancache(q1 int8, q2 int8);
create table
postgres=# insert into tab_test_plancache values('  123   ','  456');
insert 0 1
postgres=# insert into tab_test_plancache values('123   ','4567890123456789');
insert 0 1
postgres=# insert into tab_test_plancache values('4567890123456789','123');
insert 0 1
postgres=# insert into tab_test_plancache values( 4567890123456789,'4567890123456789');
insert 0 1
postgres=# insert into tab_test_plancache values(' 4567890123456789','-4567890123456789');
insert 0 1

3.创建并使用缓存的计划

postgres=# prepare prepstmt as select * from tab_test_plancache order by q1, q2;
prepare
postgres=# execute prepstmt;
        q1        |        q2
------------------ -------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)

并且包含一个带有绑定变量的

postgres=# prepare prepstmt2(bigint) as select * from tab_test_plancache where q1 = $1 order by q1, q2;
prepare
postgres=# execute prepstmt2(123);
 q1  |        q2
----- ------------------
 123 |              456
 123 | 4567890123456789
(2 rows)

4.删除临时表，查看现象

postgres=# drop table tab_test_plancache;
drop table
postgres=# execute prepstmt;
error:  relation "tab_test_plancache" does not exist on dn_6001_6002
postgres=# execute prepstmt2(123);
error:  relation "tab_test_plancache" does not exist on dn_6001_6002

重建临时表

postgres=# select * from tab_test_plancache;
        q1        |        q2
------------------ -------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)
postgres=# execute prepstmt;
        q1        |        q2
------------------ -------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)
postgres=# execute prepstmt2(123);
 q1  |        q2
----- ------------------
 123 |              456
 123 | 4567890123456789
(2 rows)

这表明原始计划是纯文本的，不依赖于oid

5.prepared statements应该防止在输出的tupdesc中更改， 因为clients可能不希望这种情况瞬间改变

postgres=# alter table tab_test_plancache add column q3 bigint;
alter table
postgres=# execute prepstmt;
error:  cached plan must not change result type
postgres=# execute prepstmt2(123);
error:  cached plan must not change result type

例子里增加了一列，但是报出了缓存的计划不能更改结果类型，可以通过还原原来表的结构解决

postgres=# alter table tab_test_plancache add column q3 bigint;
alter table
postgres=# select * from tab_test_plancache;
        q1        |        q2         | q3
------------------ ------------------- ----
              123 |               456 |
              123 |  4567890123456789 |
 4567890123456789 | -4567890123456789 |
 4567890123456789 |               123 |
 4567890123456789 |  4567890123456789 |
(5 rows)
postgres=# alter table tab_test_plancache drop column q3;
alter table
postgres=# execute prepstmt;
        q1        |        q2
------------------ -------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)
postgres=# execute prepstmt2(123);
 q1  |        q2
----- ------------------
 123 |              456
 123 | 4567890123456789
(2 rows)

6.检查使用视图的有效性

如果尝试使用一个视图的话，这个视图不会直接用于生成的计划中，但也是有效的

postgres=# create temp view pcacheview as
postgres-#   select * from tab_test_plancache;
create view
postgres=# prepare vprep as select * from pcacheview order by q1, q2;
prepare
postgres=# execute vprep;
        q1        |        q2
------------------ -------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 | -4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
(5 rows)
postgres=# create or replace temp view pcacheview as
  select q1, q2 1 as q2 from tab_test_plancache order by q1, q2;
create view
postgres=# execute vprep;
        q1        |        q2
------------------ -------------------
              123 |               457
              123 |  4567890123456790
 4567890123456789 | -4567890123456788
 4567890123456789 |               124
 4567890123456789 |  4567890123456790
(5 rows)

7.检查基本 spi plan 是否有效

postgres=# create function cache_test(int) returns int as $$
postgres$# declare total int;
postgres$# begin
postgres$# create table t1_plancache(f1 int);
postgres$# insert into t1_plancache values($1);
postgres$# insert into t1_plancache values(11);
postgres$# insert into t1_plancache values(12);
postgres$# insert into t1_plancache values(13);
postgres$# select sum(f1) into total from t1_plancache;
postgres$# drop table t1_plancache;
postgres$# return total;
postgres$# end
postgres$# $$ language plpgsql;
create function
postgres=# select cache_test(1);
 cache_test
------------
         37
(1 row)
postgres=# select cache_test(2);
 cache_test
------------
         38
(1 row)
postgres=# select cache_test(3);
 cache_test
------------
         39
(1 row)

8.检查plpgsql“简单表达式”的有效性

postgres=# create temp view v1 as
postgres-#   select 2 2 as f1;
create view
postgres=# create function cache_test_2() returns int as $$
postgres$# begin
postgres$# return f1 from v1;
postgres$# end$$ language plpgsql;
create function
postgres=# select cache_test_2();
 cache_test_2
--------------
            4
(1 row)
postgres=# create or replace temp view v1 as
postgres-#   select 2 2 4 as f1;
create view
postgres=# select cache_test_2();
 cache_test_2
--------------
            8
(1 row)

9.检查缓存执行计划使用与search_path影响

可以看到，两个schema下都有同一张表，修改了search_path后，缓存执行计划执行的是search_path下的表，所以缓存执行计划会受search_path影响。

postgres=# create schema s1
postgres-#   create table abc (f1 int);
create schema
postgres=# create schema s2
postgres-#   create table abc (f1 int);
create schema
postgres=# insert into s1.abc values(123);
insert 0 1
postgres=# insert into s2.abc values(456);
insert 0 1
postgres=# set search_path = s1;
set
postgres=# prepare p1 as select f1 from abc;
prepare
postgres=# execute p1;
 f1
-----
 123
(1 row)
postgres=# set search_path = s2;
set
postgres=# select f1 from abc;
 f1
-----
 456
(1 row)
postgres=# execute p1;
 f1
-----
 456
(1 row)
postgres=# alter table s1.abc add column f2 float8;
alter table
postgres=# execute p1;
 f1
-----
 456
(1 row)
postgres=# drop schema s1 cascade;
notice:  drop cascades to table s1.abc
drop schema
postgres=# drop schema s2 cascade;
notice:  drop cascades to table abc
drop schema
postgres=# reset search_path;
reset

10.检查regclass常量是否有效

postgres=# create  sequence seq;
create sequence
postgres=# prepare p2 as select nextval('seq');
prepare
postgres=# execute p2;
##  nextval
```
   1
```
(1 row)
postgres=# drop sequence seq;
drop sequence
postgres=# create  sequence seq;
create sequence
postgres=# execute p2;
##  nextval
```
   1
```
(1 row)

11.检查ddl，然后立即重新使用spi plan

postgres=# create function cachebug() returns void as $$
postgres$# declare r int;
postgres$# begin
postgres$#   drop table if exists temptable cascade;
postgres$#   create temp table temptable as select * from generate_series(1,3) as f1;
postgres$#   create temp view vv as select * from temptable;
postgres$#   for r in select * from vv order by 1 loop
postgres$#     raise notice '%', r;
postgres$#   end loop;
postgres$# end$$ language plpgsql;
create function
postgres=# select cachebug();
notice:  table "temptable" does not exist, skipping
context:  sql statement "drop table if exists temptable cascade"
pl/pgsql function cachebug() line 4 at sql statement
referenced column: cachebug
notice:  1
context:  referenced column: cachebug
notice:  2
context:  referenced column: cachebug
notice:  3
context:  referenced column: cachebug
 cachebug
----------
(1 row)
postgres=# select cachebug();
notice:  drop cascades to view vv
context:  sql statement "drop table if exists temptable cascade"
pl/pgsql function cachebug() line 4 at sql statement
referenced column: cachebug
notice:  1
context:  referenced column: cachebug
notice:  2
context:  referenced column: cachebug
notice:  3
context:  referenced column: cachebug
 cachebug
----------
(1 row)