/*
################################################################################
# TCASE NAME : explain_sql.py
# COMPONENT(S) : 测试包含CN轻量化, PBE,PGXC计划, subplan, initplan, nodegroup
# PREREQUISITE :
# PLATFORM : all
# DESCRIPTION : enhance the remote query plain description for explain.
# TAG : explain enhance
# TC LEVEL : Level 1
################################################################################
*/
create schema explain_fqs;
set current_schema=explain_fqs;
create table t1(a int) with (orientation=column);
create table t2(a int) with (orientation=column);
insert into t1 values(generate_series(1,2000));
insert into t2 values(generate_series(1900,3000));
analyze t1;
analyze t2;
-- initplan && subplan --
explain select * from t1,t2 where t1.a < (select * from t2 where t2.a = t1.a);
QUERY PLAN
--------------------------------------------------------------------------------------
--? Row Adapter (cost=.* rows=734367 width=8)
--? -> Vector Nest Loop (cost=.* rows=734367 width=8)
--? -> CStore Scan on t2 (cost=.* rows=1101 width=4)
--? -> Vector Materialize (cost=.* rows=667 width=4)
--? -> CStore Scan on t1 (cost=.* rows=667 width=4)
Filter: (a < (SubPlan 1))
SubPlan 1
--? -> Row Adapter (cost=.* rows=1 width=4)
--? -> CStore Scan on t2 (cost=.* rows=1 width=4)
Filter: (a = t1.a)
(10 rows)
explain verbose select * from t1,t2 where t1.a = (select * from t2 where t2.a = t1.a);
QUERY PLAN
--------------------------------------------------------------------------------------------------
--? Row Adapter (cost=.* rows=11010 width=8)
Output: t1.a, explain_fqs.t2.a
--? -> Vector Nest Loop (cost=.* rows=11010 width=8)
Output: t1.a, explain_fqs.t2.a
--? -> CStore Scan on explain_fqs.t2 (cost=.* rows=1101 width=4)
Output: explain_fqs.t2.a
--? -> Vector Materialize (cost=.* rows=10 width=4)
Output: t1.a
--? -> CStore Scan on explain_fqs.t1 (cost=.* rows=10 width=4)
Output: t1.a
Filter: (t1.a = (SubPlan 1))
SubPlan 1
--? -> Row Adapter (cost=.* rows=1 width=4)
Output: explain_fqs.t2.a
--? -> CStore Scan on explain_fqs.t2 (cost=.* rows=1 width=4)
Output: explain_fqs.t2.a
Filter: (explain_fqs.t2.a = t1.a)
(17 rows)
-- fqs && CN lightproxy --
explain verbose select * from t1 where t1.a < 10;
QUERY PLAN
------------------------------------------------------------------------
--? Row Adapter (cost=.* rows=9 width=4)
Output: a
--? -> CStore Scan on explain_fqs.t1 (cost=.* rows=9 width=4)
Output: a
Filter: (t1.a < 10)
(5 rows)
explain verbose select * from t1 where t1.a = 10;
QUERY PLAN
------------------------------------------------------------------------
--? Row Adapter (cost=.* rows=1 width=4)
Output: a
--? -> CStore Scan on explain_fqs.t1 (cost=.* rows=1 width=4)
Output: a
Filter: (t1.a = 10)
(5 rows)
explain performance select * from t1 where t1.a < 10;
--?
--?
--? Row Adapter (cost=.* rows=9 width=4) (actual time=.* rows=9 loops=1)
Output: a
--? (CPU: ex c/r=.*, ex row=.*, ex cyc=.*, inc cyc=.*)
--? -> CStore Scan on explain_fqs.t1 (cost=.* rows=9 width=4) (actual time=.* rows=9 loops=1)
Output: a
Filter: (t1.a < 10)
Rows Removed by Filter: 1991
--? (Buffers: shared hit=.* dirtied=.*)
--? (CPU: ex c/r=.*, ex row=.*, ex cyc=.*, inc cyc=.*)
--? Total runtime: .* ms
(10 rows)
explain select count(a) from t1 where t1.a < 1000 group by a order by 1;
QUERY PLAN
---------------------------------------------------------------------------------
--? Row Adapter (cost=.* rows=1000 width=12)
--? -> Vector Sort (cost=.* rows=1000 width=12)
Sort Key: (count(a))
--? -> Vector Sonic Hash Aggregate (cost=.* rows=1000 width=12)
Group By Key: a
--? -> CStore Scan on t1 (cost=.* rows=1000 width=4)
Filter: (a < 1000)
(7 rows)
-- nodegroup && subplan && pgxc--
-- create node group ngroup1 with (datanode1, datanode3);
-- create node group ngroup2 with (datanode2, datanode4);
-- create table t3(c1 int, c2 int) ;
-- create table t4(c1 int, c2 int) ;
-- insert into t3 select v,v from generate_series(1,30) as v;
-- insert into t4 select * from t3;
-- explain (verbose on, costs off) select * from t3 join t4 on t3.c1 = t4.c1 order by 1;
-- explain (verbose on, costs off)
-- select count(*) from t1, (select count(s.c1) as sa, s.c2 as sb from t3 s, t2 where s.c1 = t2.a group by sb)tp(a,b) where t1.a = tp.b;
-- explain (verbose on, costs off)
-- with tmp(a,b) as
-- (
-- select count(s.c1) as sa, s.c2 as sb from t3 s, t2 where s.c1 = t2.a group by sb
-- )
-- select count(*) from t1, tmp where t1.a = tmp.b;
-- pbe --
prepare a as select * from t1 where t1.a = $1;
explain insert into t1 values (2001);
QUERY PLAN
------------------------------------------------
--? Insert on t1 (cost=.* rows=1 width=0)
--? -> Result (cost=.* rows=1 width=0)
(2 rows)
explain execute a(1);
QUERY PLAN
------------------------------------------------------------
--? Row Adapter (cost=.* rows=1 width=4)
--? -> CStore Scan on t1 (cost=.* rows=1 width=4)
Filter: (a = $1)
(3 rows)
execute a(1);
a
---
1
(1 row)
explain verbose execute a(1);
QUERY PLAN
------------------------------------------------------------------------
--? Row Adapter (cost=.* rows=1 width=4)
Output: a
--? -> CStore Scan on explain_fqs.t1 (cost=.* rows=1 width=4)
Output: a
Filter: (t1.a = $1)
(5 rows)
execute a(1);
a
---
1
(1 row)
prepare b as select * from t1 where t1.a < (select * from t2 where t2.a= $1);
explain execute b(2);
QUERY PLAN
--------------------------------------------------------------------------
--? Row Adapter (cost=.* rows=667 width=4)
--? -> CStore Scan on t1 (cost=.* rows=667 width=4)
Filter: (a < $0)
InitPlan 1 (returns $0)
--? -> Row Adapter (cost=.* rows=1 width=4)
--? -> CStore Scan on t2 (cost=.* rows=1 width=4)
Filter: (a = $1)
(7 rows)
execute b(2);
a
---
(0 rows)
explain verbose execute b(2);
QUERY PLAN
--------------------------------------------------------------------------------------
--? Row Adapter (cost=.* rows=667 width=4)
Output: t1.a
--? -> CStore Scan on explain_fqs.t1 (cost=.* rows=667 width=4)
Output: t1.a
Filter: (t1.a < $0)
InitPlan 1 (returns $0)
--? -> Row Adapter (cost=.* rows=1 width=4)
Output: t2.a
--? -> CStore Scan on explain_fqs.t2 (cost=.* rows=1 width=4)
Output: t2.a
Filter: (t2.a = $1)
(11 rows)
execute b(2);
a
---
(0 rows)
DEALLOCATE PREPARE a;
DEALLOCATE PREPARE b;
drop schema explain_fqs cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table t1
--?drop cascades to table t2.*