i hava below two statement sql:
0. not in subquery
select a.schemaname, pg_size_pretty(pg_total_relation_size(a.schemaname||'.'||a.tablename)) from pg_tables a where a.tablename not in (select b.tablename from t b);
1. in subquery
select a.schemaname, pg_size_pretty(pg_total_relation_size(a.schemaname||'.'||a.tablename)) from pg_tables a where a.tablename in (select b.tablename from t b);
the [0. not in subquery] can't work well, it's occur error:
error: query plan with multiple segworker groups is not supported (cdbdisp.c:500)
hint: likely caused by a function that reads or modifies data in a distributed table
context: sql statement select sum(pg_total_relation_size('information_schema.sql_languages'))::int8 from gp_dist_random('gp_id');
the [1. in subquery] work well.
detailed below test:
gtlions=# select version();version------------------------------------------------------------------------------------------------------------------------------------------------------postgresql 8.2.15 (greenplum database 4.2.7.3 build 1) on x86_64-unknown-linux-gnu, compiled by gcc gcc (gcc) 4.4.2 compiled on may 7 2014 14:31:08(1 row)gtlions=# select a.schemaname, pg_size_pretty(pg_total_relation_size(a.schemaname||'.'||a.tablename)) from pg_tables a where a.tablename not in (select b.tablename from t b);error: query plan with multiple segworker groups is not supported (cdbdisp.c:500)hint: likely caused by a function that reads or modifies data in a distributed tablecontext: sql statement select sum(pg_total_relation_size('information_schema.sql_languages'))::int8 from gp_dist_random('gp_id');gtlions=# explain select a.schemaname, pg_size_pretty(pg_total_relation_size(a.schemaname||'.'||a.tablename)) from pg_tables a where a.tablename not in (select b.tablename from t b);query plan-----------------------------------------------------------------------------------------------hash left anti semi join (cost=568.98..235912.69 rows=676396 width=128)hash cond: c.relname = notin_subquery.tablename::name-> hash left join (cost=395.97..223194.68 rows=676419 width=128)hash cond: c.relnamespace = n.oid-> hash left join (cost=2.62..112777.67 rows=676419 width=68)hash cond: c.reltablespace = t.oid-> seq scan on pg_class c (cost=0.00..2751.39 rows=676419 width=72)filter: relkind = 'r'::char and relname is not null-> hash (cost=1.02..1.02 rows=2 width=4)-> seq scan on pg_tablespace t (cost=0.00..1.02 rows=128 width=4)-> hash (cost=365.35..365.35 rows=35 width=68)-> seq scan on pg_namespace n (cost=0.00..365.35 rows=2240 width=68)-> hash (cost=106.61..106.61 rows=83 width=274)-> gather motion 64:1 (slice1; segments: 64) (cost=0.00..106.61 rows=83 width=274)-> subquery scan notin_subquery (cost=0.00..52.66 rows=2 width=274)-> seq scan on t b (cost=0.00..51.83 rows=2 width=24)(16 rows)gtlions=# select a.schemaname, pg_size_pretty(pg_total_relation_size(a.schemaname||'.'||a.tablename)) from pg_tables a where a.tablename in (select b.tablename from t b);schemaname | size-1-------------+---------public | 32 kbpublic | 32 kb............public | 96 kbgtlions=# explain select a.schemaname, pg_size_pretty(pg_total_relation_size(a.schemaname||'.'||a.tablename)) from pg_tables a where a.tablename in (select b.tablename from t b);query plan---------------------------------------------------------------------------------------------------------------------------------gather motion 64:1 (slice7; segments: 64) (cost=445.41..10096.03 rows=1 width=128)-> hash left join (cost=445.41..10096.03 rows=1 width=128)hash cond: c.reltablespace = t.oid-> redistribute motion 64:64 (slice5; segments: 64) (cost=443.06..10092.81 rows=1 width=132)hash key: c.reltablespace-> hash left join (cost=443.06..10092.22 rows=1 width=132)hash cond: c.relnamespace = n.oid-> redistribute motion 64:64 (slice3; segments: 64) (cost=54.53..9703.24 rows=1 width=72)hash key: c.relnamespace-> hash exists join (cost=54.53..9702.65 rows=1 width=72)hash cond: c.relname = b.tablename::name-> redistribute motion 1:64 (slice1) (cost=0.00..9621.26 rows=10570 width=72)hash key: c.relname-> seq scan on pg_class c (cost=0.00..2751.39 rows=676419 width=72)filter: relkind = 'r'::char-> hash (cost=53.49..53.49 rows=2 width=24)-> redistribute motion 64:64 (slice2; segments: 64) (cost=0.00..53.49 rows=2 width=24)hash key: b.tablename::name-> seq scan on t b (cost=0.00..51.83 rows=2 width=24)-> hash (cost=388.10..388.10 rows=1 width=68)-> redistribute motion 1:64 (slice4) (cost=0.00..388.10 rows=35 width=68)hash key: n.oid-> seq scan on pg_namespace n (cost=0.00..365.35 rows=2240 width=68)-> hash (cost=2.32..2.32 rows=1 width=4)-> redistribute motion 1:64 (slice6) (cost=0.00..2.32 rows=2 width=4)hash key: t.oid-> seq scan on pg_tablespace t (cost=0.00..1.02 rows=128 width=4)(27 rows)
该问题应该是个bug,等待tse给出fix或者没有fix而只能等到下个版本升级了.
-eof-