exists子句在大数据量场景调大work_mem性能更好 exists子句在数据量比较大的情况下,优化器选择走Hash Semi Join,而在调大workmem后,优化器执行走Hash Join,性能会更好。本文为您介绍该场景下的详细测试情况。 以下是关于该场景的测试情况: 1. workmem默认4MB时,SQL执行计划和耗时情况如下: 可以看到,优化器选择走了Hash Semi Join,耗时3.7秒。 teledb explain select count(1) from t1 where exists(select 1 from t2 where t2.t1f1t1.f1); QUERY PLAN Finalize Aggregate (cost242218.32..242218.33 rows1 width8) > Remote Subquery Scan on all (dn001,dn002) (cost242218.30..242218.32 rows1 width0) > Partial Aggregate (cost242118.30..242118.31 rows1 width8) > Hash Semi Join (cost110248.00..242118.30 rows505421 width0) Hash Cond: (t1.f1 t2.t1f1) > Seq Scan on t1 (cost0.00..17420.00 rows1000000 width4) > Hash (cost79340.00..79340.00 rows3000000 width4) > Remote Subquery Scan on all (dn001,dn002) (cost100.00..79340.00 rows3000000 width4) Distribute results by S: t1f1 > Seq Scan on t2 (cost0.00..52240.00 rows3000000 width4) (10 rows) Time: 1.091 ms teledb select count(1) from t1 where exists(select 1 from t2 where t2.t1f1t1.f1); count 500000 (1 row) Time: 3779.401 ms (00:03.779) 2. workmem调整为128MB后,SQL执行计划和耗时情况如下: 可以看到,优化器选择走了Hash Join,耗时1.9秒。 teledb