Блог

У меня есть два сервера postgres с одинаковым IP-адресом и разными портами (одна и та же машина). Я использую postgres_fdw для связи баз данных — внешние таблицы из удаленной базы данных видны в схеме «eaps».

Запрос на объединение 5 таблиц: Таблицы: e_connector :

          |eap_id|start_object_id|end_object_id|skompo|ekompo|connector_type| 
 

..
Мне нужно отсканировать таблицу с включенным фильтром (eap_in,skompo,ekompo), а затем объединить таблицы e_object и c_package_tree, чтобы получить из них дополнительные данные для start_object_id и end_object_id.

e_объект :

       |eap_id|object_id|package_id |name|
 

c_package_tree :

       |eap_id|package_id |nametree|
 

Мне нужен результат:

 Select 
  tc.connector_type,skompo as start_kompo,ekompo as end_kompo,
  ts.name as sname, trs.nametree as snametree,
  te.name as ename, tre.nametree as enametree
from 
 e_connector  tc, e_object ts, e_object te, 
 c_package_tree trs , c_package_tree tre 
where 
    tc.eap_id=36 and 'OU' in (skompo,ekompo) and -- scan index
    tc.eap_id=ts.eap_id and tc.start_object_id=ts.object_id and -- with index
    tc.eap_id=te.eap_id and tc.end_object_id=te.object_id and -- with index
    tc.eap_id=trs.eap_id and ts.package_id=trs.package_id and -- with index
    tc.eap_id=tre.eap_id and te.package_id=tre.package_id  -- with index
 

Запрос должен быть изменен для поддержки первого сканирования e_connector, чтобы предотвратить огромное объединение в 4 других таблицах

 with con as (
    Select eap_id,tc.connector_type,skompo as start_kompo,ekompo as end_kompo,
    start_object_id,end_object_id
    from e_connector tc
    where 
    tc.eap_id=36 and 'OU' in (skompo,ekompo) ) 
Select tc.connector_type,start_kompo,end_kompo,
    ts.name as sname, trs.nametree as snametree,
    te.name as ename, tre.nametree as enametree
    from con tc, e_object ts, e_object te, c_package_tree trs , c_package_tree tre 
    where 
    tc.eap_id=ts.eap_id and tc.start_object_id=ts.object_id and 
    tc.eap_id=te.eap_id and tc.end_object_id=te.object_id and
    tc.eap_id=trs.eap_id and ts.package_id=trs.package_id and
    tc.eap_id=tre.eap_id and te.package_id=tre.package_id 
 

Это занимает 229 мсек в базе данных и сначала сканирует индекс e_connector (eap_id,skompo,ekompo), создает растровое изображение ИЛИ и выполняет внутренние соединения вложенного цикла с другими таблицами.
Один и тот же запрос с «eaps.» перед именами таблиц, выполняемый на удаленном сервере в одной и той же базе данных, имеет другой план выполнения для одних и тех же данных.

e_connector содержит 1142600 записей, 10500, в то время как отфильтрованный e_object содержит 1504650 записей, 63000 записей для данного eap_id и ~49800 из них имеют соединения

Индексы:

 Create unique index ie_object on e_object(eap_id,object_id);
Create unique index ic_package_tree on c_package_tree(eap_id,package_id);
Create index ix_connector_kompo on e_connector(eap_id,skompo,ekompo);
Create index ix_connector_start_end on e_connector(eap_id,start_object_id,end_object_id);
 

How can I avoid plan with :

  {
          "Node Type": "Foreign Scan",
          "Operation": "Select",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Startup Cost": 100,
          "Total Cost": 311.73,
          "Plan Rows": 1,
          "Plan Width": 2088,
          "Output": [
            "ts.name",
            "ts.eap_id",
            "ts.object_id",
            "te.name",
            "te.eap_id",
            "te.object_id",
            "trs.nametree",
            "trs.eap_id",
            "tre.nametree",
            "tre.eap_id"
          ],
          "Relations": "(((eaps.e_object ts) 
          INNER JOIN (eaps.e_object te)) 
          INNER JOIN (eaps.c_package_tree trs)) 
          INNER JOIN (eaps.c_package_tree tre)",
          "Remote SQL": 
          "SELECT r2.name, r2.eap_id, r2.object_id, r3.name, r3.eap_id, r3.object_id, r4.nametree, r4.eap_id, r5.nametree, r5.eap_id 
          FROM (((public.e_object r2 
          INNER JOIN public.e_object r3 ON (((r2.eap_id = r3.eap_id)))) 
          INNER JOIN public.c_package_tree r4 ON (((r2.eap_id = r4.eap_id)) AND ((r2.package_id = r4.package_id)))) 
          INNER JOIN public.c_package_tree r5 ON (((r2.eap_id = r5.eap_id)) AND ((r3.package_id = r5.package_id))))"
        }
 

Мне нужно присоединиться к результату на удаленном сервере.

 with con as (
    Select eap_id,tc.ea_guid,tc.connector_type,skompo as start_kompo,ekompo as end_kompo,
    start_object_id,end_object_id
    from e_connector tc
    where 
    tc.eap_id=36 and 'OU' in (skompo,ekompo) ) 
Select tc.ea_guid,tc.connector_type,  end_kompo,
    ts.object_type as stype,ts.name as sname, ts.ea_guid as start_guid,trs.nametree as snametree,
    te.object_type as etype,te.name as ename, te.ea_guid as end_guid,tre.nametree as enametree
    from con  tc 
    join  e_object ts on (tc.eap_id=ts.eap_id and tc.start_object_id=ts.object_id)
    join e_object te on (tc.eap_id=te.eap_id and tc.end_object_id=te.object_id)
    join c_package_tree trs on (tc.eap_id=trs.eap_id and ts.package_id=trs.package_id)
    join c_package_tree tre on (tc.eap_id=tre.eap_id and te.package_id=tre.package_id)```
with join syntax