pgAdmin enhancement request.

Hi pgAdmin team,

May I request an enhancement request for pgAdmin tool? We are the support team for Greenplum product. It’s found when using pgAdmin tool to connect Greenplum database, this tool would automatically query the Greenplum catalog tables, like ‘pg_class’. But the query generated from pgAdmin tool have a very high cost. This high cost query would cause customer with resource queue control (max cost limit) unable to use pgAdmin tool.

One of pgAdmin query like below :
SELECT rel.oid, relname, rel.reltablespace AS spcoid, spcname, pg_get_userbyid(relowner) AS relowner, relacl, relhasoids,
relhassubclass, reltuples, description, conname, conkey,
EXISTS(select 1 FROM pg_trigger JOIN pg_proc pt ON pt.oid=tgfoid AND
pt.proname='logtrigger' JOIN
pg_proc pc ON pc.pronamespace=pt.pronamespace
--AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl,
WHERE tgrelid=rel.oid) AS isrepl,
--(select count(*) FROM pg_trigger WHERE tgrelid= rel.oid AND tgisconstraint = FALSE) AS triggercount,
substring(array_to_string(rel.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor, gpd.localoid, gpd.attrnums,
substring(array_to_string(rel.reloptions, ',') from 'appendonly=([a-z]*)') AS appendonly,
substring(array_to_string(rel.reloptions, ',') from 'compresslevel=([0-9]*)') AS compresslevel,
substring(array_to_string(rel.reloptions, ',') from 'compresstype=([a-z0-9]*)') AS compresstype,
substring(array_to_string(rel.reloptions, ',') from 'orientation=([a-z]*)') AS orientation,
substring(array_to_string(reloptions, ',') from 'blocksize=([0-9]*)') AS blocksize,
substring(array_to_string(rel.reloptions, ',') from 'compresstype=([a-z0-9]*)') AS compresstype,
substring(array_to_string(reloptions, ',') from 'blocksize=([0-9]*)') AS blocksize,
substring(array_to_string(reloptions, ',') from 'checksum=([a-z]*)') AS checksum,
rel.oid in (select parrelid from pg_partition) as ispartitioned
FROM pg_class rel
LEFT OUTER JOIN pg_tablespace ta on ta.oid=rel.reltablespace
LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0)
LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0)
LEFT OUTER JOIN pg_constraint c ON c.conrelid=rel.oid AND c.contype='p'
LEFT OUTER JOIN gp_distribution_policy gpd ON gpd.localoid=rel.oid
WHERE relkind IN ('r','s','t') AND relnamespace = 2200::oid AND rel.relstorage <> 'x'
and rel.oid NOT IN (select parchildrelid from pg_partition_rule)ORDER BY relname;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1110150119.39..1110162681.63 rows=5024898 width=358)
Sort Key: rel.relname
Rows out: 1294 rows with 2923 ms to end, start offset by 0.468 ms.
Executor memory: 553K bytes.
Work_mem used: 553K bytes.
-> Hash Left Anti Semi Join (cost=8220415.44..1109590831.59 rows=5024898 width=358)
Hash Cond: rel.oid = "NotIn_SUBQUERY".parchildrelid
Rows out: 1294 rows with 203 ms to first row, 2919 ms to end, start offset by 0.546 ms.
Executor memory: 2K bytes.
Work_mem used: 2K bytes.
Work_mem wanted: 1731K bytes to lessen workfile I/O.
Initial batches 1..2047:
Read 503828107042K bytes from outer workfile: 246129999K avg x 2047 nonempty batches, 246129999K max.
Hash chain length 1.0 avg, 2 max, using 73819 of 268515328 buckets.
-> Hash Left Join (cost=90867.76..1666327.73 rows=5024921 width=358)
Hash Cond: rel.oid = des.objoid
Rows out: 75114 rows with 81 ms to first row, 317 ms to end, start offset by 118 ms.
Executor memory: 117K bytes.
Work_mem used: 117K bytes.
Hash chain length 1.0 avg, 1 max, using 2234 of 65539 buckets.
-> Hash Left Join (cost=88321.58..1639499.25 rows=5024921 width=334)
Hash Cond: rel.oid = c.conrelid
Rows out: 75114 rows with 80 ms to first row, 296 ms to end, start offset by 118 ms.
-> Hash Left Join (cost=85504.62..1623556.67 rows=5024921 width=247)
Hash Cond: rel.oid = gpd.localoid
Rows out: 75114 rows with 38 ms to first row, 236 ms to end, start offset by 118 ms.
Executor memory: 136K bytes.
Work_mem used: 136K bytes.
Work_mem wanted: 4107K bytes to lessen workfile I/O.
Initial batch 0:
Wrote 2976K bytes to inner workfile.
Wrote 6880K bytes to outer workfile.
Initial batches 1..31:
Read 2976K bytes from inner workfile: 96K avg x 31 nonempty batches, 96K max.
Read 7630029956K bytes from outer workfile: 246129999K avg x 31 nonempty batches, 246129999K max.
Hash chain length 1.0 avg, 3 max, using 73462 of 2097248 buckets.
-> Hash Left Join (cost=3.27..1149164.86 rows=5024921 width=218)
Hash Cond: rel.reltablespace = ta.oid
Rows out: 75114 rows with 0.264 ms to first row, 112 ms to end, start offset by 118 ms.
Executor memory: 1K bytes.
Work_mem used: 1K bytes.
Hash chain length 1.0 avg, 1 max, using 2 of 16417 buckets.
-> Seq Scan on pg_class rel (cost=0.00..5433.94 rows=5024921 width=154)
Filter: (relkind = ANY ('{r,s,t}'::"char"[])) AND relnamespace = 2200::oid AND relstorage <> 'x'::"char" AND oid IS NOT NULL
Rows out: 75114 rows with 0.133 ms to first row, 95 ms to end, start offset by 118 ms.
-> Hash (cost=1.02..1.02 rows=2 width=68)
Rows in: 2 rows with 0.038 ms to end, start offset by 118 ms.
-> Seq Scan on pg_tablespace ta (cost=0.00..1.02 rows=180 width=68)
Rows out: 2 rows with 0.033 ms to first row, 0.034 ms to end, start offset by 118 ms.
-> Hash (cost=890.10..890.10 rows=75210 width=29)
Rows in: 75118 rows with 37 ms to end, start offset by 118 ms.
-> Seq Scan on gp_distribution_policy gpd (cost=0.00..890.10 rows=6768900 width=29)
Rows out: 75118 rows with 0.125 ms to first row, 19 ms to end, start offset by 118 ms.
-> Hash (cost=2815.84..2815.84 rows=1 width=91)
Rows in: 0 rows with 42 ms to end, start offset by 156 ms.
-> Seq Scan on pg_constraint c (cost=0.00..2815.84 rows=90 width=91)
Filter: contype = 'p'::"char"
Rows out: 0 rows with 42 ms to end, start offset by 156 ms.
-> Hash (cost=32.92..32.92 rows=2234 width=28)
Rows in: 2234 rows with 0.942 ms to end, start offset by 198 ms.
-> Seq Scan on pg_description des (cost=0.00..32.92 rows=201060 width=28)
Filter: objsubid = 0
Rows out: 2234 rows with 0.041 ms to first row, 0.489 ms to end, start offset by 198 ms.
-> Hash (cost=68287.68..68287.68 rows=6646320 width=4)
Rows in: 73820 rows with 116 ms to end, start offset by 1.468 ms.
-> Subquery Scan "NotIn_SUBQUERY" (cost=0.00..68287.68 rows=598168800 width=4)
Rows out: 73820 rows with 0.029 ms to first row, 37 ms to end, start offset by 1.468 ms.
-> Seq Scan on pg_partition_rule (cost=0.00..1824.48 rows=6646320 width=4)
Rows out: 73820 rows with 0.027 ms to first row, 25 ms to end, start offset by 1.469 ms.
SubPlan 2
-> Materialize for deadlock safety (cost=134.00..1295.00 rows=116100 width=4)
Rows out: 837855 rows with 0.454 ms to first row, 63 ms to end of 1294 scans, start offset by 2922 ms.
-> Seq Scan on pg_partition (cost=0.00..17.90 rows=116100 width=4)
Rows out: 1290 rows with 0.036 ms to first row, 0.296 ms to end, start offset by 203 ms.
SubPlan 1
-> Hash Join (cost=57.77..141.63 rows=354 width=0)
Hash Cond: pt.oid = pg_trigger.tgfoid
Rows out: 0 rows with 0.336 ms to first row, 150 ms to end of 1294 scans, start offset by 2922 ms.
-> Hash Join (cost=55.59..138.64 rows=299 width=4)
Hash Cond: pt.pronamespace = pc.pronamespace
Rows out: 0 rows with 0 ms to end.
-> Seq Scan on pg_proc pt (cost=0.00..54.25 rows=108 width=8)
Filter: proname = 'logtrigger'::name
Rows out: 0 rows with 0 ms to end.
-> Hash (cost=54.25..54.25 rows=2 width=4)
Rows in: 0 rows with 0 ms to end.
-> Seq Scan on pg_proc pc (cost=0.00..54.25 rows=108 width=4)
Filter: proname = 'slonyversion'::name
Rows out: 0 rows with 0 ms to end.
-> Hash (cost=1.05..1.05 rows=1 width=4)
Rows in: 0 rows with 0.013 ms to first row, 7.590 ms to end of 1294 scans, start offset by 2922 ms.
-> Seq Scan on pg_trigger (cost=0.00..1.05 rows=90 width=4)
Filter: tgrelid = $0
Rows out: 0 rows with 0.008 ms to first row, 5.616 ms to end of 1294 scans, start offset by 2922 ms.
Slice statistics:
(slice0) * Executor memory: 7340K bytes. Work_mem: 553K bytes max, 4107K bytes wanted.
Statement statistics:
Memory used: 128000K bytes
Memory wanted: 38754K bytes
Total runtime: 2923.225 ms
(100 rows)

Greenplum Dev team has confirm this high cost is caused by correlated subquery. Using join instead of correlated subquery can avoid this high cost issue. Please let us know if you can rewrite those query for catalog tables. Thank you.
Environment as below :
pgAdmin III 1.16.1 for windows.
Greenplum DB 4.2.3.0 for Linux.

Regards,
Shawn Yan
Technical Support Engineer

Global Services Customer Services VTSG Greenplum
Office Hours: Sunday - Thursday, 19:00 to 04:00 hrs EST
Phone : +1 800-782-4362 ext 7631019 (Global )
: 中文热线 800-819-0009 (分机7631019)
400-670-0009
Email : shawn(dot)yan(at)emc(dot)com<mailto:your(dot)email(at)emc(dot)com>

To escalate your issue or to provide feedback on your Greenplum Support experience:
Please email my Manager Mark Chu mark(dot)chu(at)emc(dot)com<mailto:mark(dot)chu(at)emc(dot)com>

Out of Hours:
Via the Greenplum Manager On Duty
United States +1 800 782 4362
Worldwide +1 508497 7901