[YSQL] TServer stuck with 100% CPU load while running EXPLAIN with enabled yb_enable_optimizer_statistics

[qvad]

Jira Link: DB-2837

Description

Tested on local cluster 2.15.1.0-b89

CREATE TABLE t500000 as select a, md5(random()::text) from generate_Series(1,500000) a;
CREATE TABLE t100000 as select a, md5(random()::text) from generate_Series(1,100000) a;
CREATE TABLE t50000 as select a, md5(random()::text) from generate_Series(1,50000) a;

SET yb_enable_optimizer_statistics = true;

EXPLAIN  SELECT * FROM t500000  inner join t100000 on t500000.a = t100000.a inner join t50000 on t500000.a = t50000.a;

[sushantrmishra]

• Plan without yb_enable_optimizer_statistics

EXPLAIN  SELECT * FROM t500000  inner join t100000 on t500000.a = t100000.a inner join t50000 on t5
00000.a = t50000.a;
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Merge Join  (cost=449.49..909.49 rows=25000 width=108)
   Merge Cond: (t50000.a = t500000.a)
   ->  Sort  (cost=149.83..152.33 rows=1000 width=36)
         Sort Key: t50000.a
         ->  Seq Scan on t50000  (cost=0.00..100.00 rows=1000 width=36)
   ->  Materialize  (cost=299.66..392.16 rows=5000 width=72)
         ->  Merge Join  (cost=299.66..379.66 rows=5000 width=72)
               Merge Cond: (t500000.a = t100000.a)                                                                           ->  Sort  (cost=149.83..152.33 rows=1000 width=36)
                     Sort Key: t500000.a
                     ->  Seq Scan on t500000  (cost=0.00..100.00 rows=1000 width=36)
               ->  Sort  (cost=149.83..152.33 rows=1000 width=36)
                     Sort Key: t100000.a
                     ->  Seq Scan on t100000  (cost=0.00..100.00 rows=1000 width=36)
(14 rows)

• Plan with yb_enable_optimizer_statistics set to true .

yugabyte=# analyze t100000
yugabyte-# ;
ANALYZE
yugabyte=# analyze t50000;
ANALYZE
yugabyte=# analyze t500000;

yugabyte=# SET yb_enable_optimizer_statistics = true;
SET
yugabyte=# EXPLAIN  SELECT * FROM t500000  inner join t100000 on t500000.a = t100000.a inner join t50000 on t500000.a = t50000.a;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Hash Join  (cost=18087.46..80907.25 rows=10038 width=108)
   Hash Cond: (t500000.a = t100000.a)
   ->  Hash Join  (cost=6019.83..66593.60 rows=50034 width=72)
         Hash Cond: (t500000.a = t50000.a)
         ->  Seq Scan on t500000  (cost=0.00..49995.60 rows=499956 width=36)
         ->  Hash  (cost=5003.40..5003.40 rows=50034 width=36)
               ->  Seq Scan on t50000  (cost=0.00..5003.40 rows=50034 width=36)
   ->  Hash  (cost=10029.90..10029.90 rows=100299 width=36)
         ->  Seq Scan on t100000  (cost=0.00..10029.90 rows=100299 width=36)
(9 rows)

[sushantrmishra]

Lets execute the query :

• Ran Analyze on the tables and set yb_enable_optimizer_statistics is set to true.

yugabyte=# EXPLAIN analyze SELECT * FROM t500000 inner join t100000 on t500000.a = t100000.a inner join t500 00 on t500000.a = t50000.a; QUERY PLAN

---

---

Hash Join (cost=18087.46..80907.25 rows=10038 width=108) (actual time=536.783..2395.365 rows=50000 loops=1) Hash Cond: (t500000.a = t100000.a) -> Hash Join (cost=6019.83..66593.60 rows=50034 width=72) (actual time=187.120..2009.328 rows=50000 loops =1) Hash Cond: (t500000.a = t50000.a) -> Seq Scan on t500000 (cost=0.00..49995.60 rows=499956 width=36) (actual time=3.898..1679.340 rows =500000 loops=1) -> Hash (cost=5003.40..5003.40 rows=50034 width=36) (actual time=182.996..182.996 rows=50000 loops= 1) Buckets: 65536 Batches: 2 Memory Usage: 2200kB -> Seq Scan on t50000 (cost=0.00..5003.40 rows=50034 width=36) (actual time=5.937..172.624 ro ws=50000 loops=1) -> Hash (cost=10029.90..10029.90 rows=100299 width=36) (actual time=349.404..349.404 rows=100000 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 2198kB -> Seq Scan on t100000 (cost=0.00..10029.90 rows=100299 width=36) (actual time=3.414..329.499 rows= 100000 loops=1) Planning Time: 0.133 ms Execution Time: 2399.116 ms Peak Memory Usage: 4659 kB

[sushantrmishra]

@qvad Did you ran the Analyze on the table first in this test ? analyze updates the statistic for the tables and query planner uses that information to finding the most optimal query plan. Without Analyze , DB ends up using default statistics, which can result in wrong query plans.

[qvad]

@sushantrmishra Tried with ANALYZE and issue is not reproducible anymore. Still not clear why everything works well w/o yb_enable_optimizer_statistics? Not sure that it's expected.