Блог

[Вопрос, опубликованный пользователем в сообществе YugabyteDB Slack]

У меня есть эта схема,и я не могу использовать pg_hint_plan ее для принудительного соединения(a, b):

 yugabyte=# create table a(id int);
yugabyte=# create table b(id int);
yugabyte=# create table c(id int);

yugabyte=# explain select * from a natural join b natural join c;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Merge Join  (cost=449.49..909.49 rows=25000 width=4)
   Merge Cond: (c.id = a.id)
   ->  Sort  (cost=149.83..152.33 rows=1000 width=4)
         Sort Key: c.id
         ->  Seq Scan on c  (cost=0.00..100.00 rows=1000 width=4)
   ->  Materialize  (cost=299.66..392.16 rows=5000 width=8)
         ->  Merge Join  (cost=299.66..379.66 rows=5000 width=8)
               Merge Cond: (a.id = b.id)
               ->  Sort  (cost=149.83..152.33 rows=1000 width=4)
                     Sort Key: a.id
                     ->  Seq Scan on a  (cost=0.00..100.00 rows=1000 width=4)
               ->  Sort  (cost=149.83..152.33 rows=1000 width=4)
                     Sort Key: b.id
                     ->  Seq Scan on b  (cost=0.00..100.00 rows=1000 width=4)
(14 rows)

 

Обратите внимание, что когда вы хотите указать конкретный план, вам нужно не только определить метод соединения (например, с помощью HashJoin), но также порядок соединения (с ведущим) и направление (в круглых скобках). Примеры:

 yugabyte=# -- probe a, join to hashed b, join the result to hashed c:
yugabyte=# explain /*  Leading(((a b) c)) HashJoin(a b)  HashJoin(a b c) */
yugabyte-#  select * from a natural join b natural join c;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Hash Join  (cost=225.00..1390.00 rows=25000 width=4)
   Hash Cond: (a.id = c.id)
   ->  Hash Join  (cost=112.50..390.00 rows=5000 width=8)
         Hash Cond: (a.id = b.id)
         ->  Seq Scan on a  (cost=0.00..100.00 rows=1000 width=4)
         ->  Hash  (cost=100.00..100.00 rows=1000 width=4)
               ->  Seq Scan on b  (cost=0.00..100.00 rows=1000 width=4)
   ->  Hash  (cost=100.00..100.00 rows=1000 width=4)
         ->  Seq Scan on c  (cost=0.00..100.00 rows=1000 width=4)
(9 rows)



yugabyte=# -- probe b, join to hashed c, probe a and join to the previous result:
yugabyte=# explain /*  Leading((a (b c))) HashJoin(b c) HashJoin(a b c) */
yugabyte-#  select * from a natural join b natural join c;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Hash Join  (cost=452.50..1430.00 rows=25000 width=4)
   Hash Cond: (a.id = b.id)
   ->  Seq Scan on a  (cost=0.00..100.00 rows=1000 width=4)
   ->  Hash  (cost=390.00..390.00 rows=5000 width=8)
         ->  Hash Join  (cost=112.50..390.00 rows=5000 width=8)
               Hash Cond: (b.id = c.id)
               ->  Seq Scan on b  (cost=0.00..100.00 rows=1000 width=4)
               ->  Hash  (cost=100.00..100.00 rows=1000 width=4)
                     ->  Seq Scan on c  (cost=0.00..100.00 rows=1000 width=4)
(9 rows)





yugabyte=# -- probe c, join to hashed b, probe a and join to the previous result:
yugabyte=# explain /*  Leading((a (c b))) HashJoin(c b) HashJoin(a b c) */
yugabyte-#  select * from a natural join b natural join c;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Hash Join  (cost=452.50..1430.00 rows=25000 width=4)
   Hash Cond: (a.id = b.id)
   ->  Seq Scan on a  (cost=0.00..100.00 rows=1000 width=4)
   ->  Hash  (cost=390.00..390.00 rows=5000 width=8)
         ->  Hash Join  (cost=112.50..390.00 rows=5000 width=8)
               Hash Cond: (c.id = b.id)
               ->  Seq Scan on c  (cost=0.00..100.00 rows=1000 width=4)
               ->  Hash  (cost=100.00..100.00 rows=1000 width=4)
                     ->  Seq Scan on b  (cost=0.00..100.00 rows=1000 width=4)
(9 rows)


 

На YugabyteDB 2.9 вы можете анализировать таблицы, и планировщик запросов будет использовать статистику для оценки.