ReSQL.ru
     
powered by simpleCommunicator - 2.0.44     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
45 сообщений из 45, показаны все 2 страниц
  все  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40139989
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
mssql2005
Всем привет!
готовим udf mssql базы для переезда на postgresql (санкции да и вообще все морально устарело, win11 отрубила поддержку tls1.x, 'старого' sql драйвера и т.д. - 20ти летний серверный софт 'умирает' понемногу... денег на обновление не выделяют...)

вопрос по оптимизации select - одним запросом выбираем id объектов + 20 (однотипных) left join подгоняем к ним значения атрибутов
mssql справляется а postgresql (на той же базе/миграция на том же запросе) 'умирает'(резко тормозит) если left join более 8~10
оставив пока postgresql в стороне просто хочу попытаться ускорить выборку на mssql например через cte (поскольку left join берет инфу из одних и тех же 4х таблиц многократно)
...
структура бд от вендора (не можем менять), используем базу лет 20ть проблем нет - работает, так что вопрос чисто по ускорению запроса
1. есть 3 таблицы значений атрибутов (id атрибута, значение атрибута и т.д.) для хранения строк, чисел и дат
2. есть одна таблица связи атрибутов с объектами (id объекта, id атрибута и т.д.)
в left join я просто многократно обращаюсь к этим таблицам чтобы по id объекта (из основного запроса по выборке объектов) и id атрибута получить значение атрибута
вот что мы использовали последние 20 лет
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
select
  rw.id, vv14.value,  vv1.value, vv3.value, vv4.value, vv15.value, vv5.value,vv12.value, vv10.value, vv11.value,vv16.value,vv6.value, vv7.value,vv13.value, vv18.value
-- vv14.value, rw.cd, vv.value, rw.description, vv2.value, vv1.value, vv3.value+' ', vv4.value, isnull(vv15.value,0), isnull(vv5.value,0) AS pPrice, isnull(vv12.value,0), isnull(vv10.value,0) as pDiscount01, isnull(vv11.value,0),isnull(vv16.value,0),vv6.value AS pCurrency, vv7.value AS pStatus,isnull(vv13.value, vv17.value),(select LSDBO.Ric_Get_ShortFIO(vv9.value)), vv18.value
From lsdbo.Ric_Get_Select(2,100000038900005,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw left join
--select * From lsdbo.Ric_Get_Select(2,4000000015666,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw left join
        LSDBO.attrib_value AS av ON rw.id = av.Object_ID AND av.Attrib_ID = 100004068400000 LEFT JOIN
        LSDBO.value_string AS vv ON av.Value_ID = vv.id AND av.Attrib_ID = 100004068400000 left join
        LSDBO.attrib_value AS av1 ON rw.id = av1.Object_ID AND av1.Attrib_ID = 3000000000101 LEFT JOIN
        LSDBO.value_string AS vv1 ON av1.Value_ID = vv1.id AND av1.Attrib_ID = 3000000000101 LEFT JOIN
        LSDBO.attrib_value AS av2 ON rw.id = av2.Object_ID AND av2.Attrib_ID = 100003121500000 LEFT JOIN
        LSDBO.value_string AS vv2 ON av2.Value_ID = vv2.id AND av2.Attrib_ID = 100003121500000 LEFT JOIN
        LSDBO.attrib_value AS av3 ON rw.id = av3.Object_ID AND av3.Attrib_ID = 100004086600000 LEFT JOIN
        LSDBO.value_string AS vv3 ON av3.Value_ID = vv3.id AND av3.Attrib_ID = 100004086600000 left join
        LSDBO.attrib_value AS av4 ON rw.id = av4.Object_ID AND av4.Attrib_ID = 3000000000106 LEFT JOIN
        LSDBO.value_datetime AS vv4 ON av4.Value_ID = vv4.id AND av4.Attrib_ID = 3000000000106 LEFT JOIN
        LSDBO.attrib_value AS av5 ON rw.id = av5.Object_ID AND av5.Attrib_ID = 100033995800000 LEFT JOIN
        LSDBO.value_numeric AS vv5 ON av5.Value_ID = vv5.id AND av5.Attrib_ID = 100033995800000 left join
        LSDBO.attrib_value AS av6 ON rw.id = av6.Object_ID AND av6.Attrib_ID = 100005428000000 LEFT JOIN
        LSDBO.value_string AS vv6 ON av6.Value_ID = vv6.id AND av6.Attrib_ID = 100005428000000 LEFT JOIN
        LSDBO.attrib_value AS av7 ON rw.id = av7.Object_ID AND av7.Attrib_ID = 100007986500005 LEFT JOIN
        LSDBO.value_string AS vv7 ON av7.Value_ID = vv7.id AND av7.Attrib_ID = 100007986500005 left join
        LSDBO.attrib_value AS av8 ON rw.id = av8.Object_ID AND av8.Attrib_ID = 100003121500000 LEFT JOIN
        LSDBO.value_string AS vv8 ON av8.Value_ID = vv8.id AND av8.Attrib_ID = 100003121500000 left join
        LSDBO.attrib_value AS av9 ON rw.id = av9.Object_ID AND av9.Attrib_ID = 3000000000122 LEFT JOIN
        LSDBO.value_string AS vv9 ON av9.Value_ID = vv9.id AND av9.Attrib_ID = 3000000000122 LEFT JOIN
        LSDBO.attrib_value AS av10 ON rw.id = av10.Object_ID AND av10.Attrib_ID = 100007971500005 LEFT JOIN
        LSDBO.value_numeric AS vv10 ON av10.Value_ID = vv10.id AND av10.Attrib_ID = 100007971500005 LEFT JOIN
        LSDBO.attrib_value AS av11 ON rw.id = av11.Object_ID AND av11.Attrib_ID = 100038030700000 LEFT JOIN
        LSDBO.value_numeric AS vv11 ON av11.Value_ID = vv11.id AND av11.Attrib_ID = 100038030700000 LEFT JOIN
        LSDBO.attrib_value AS av12 ON rw.id = av12.Object_ID AND av12.Attrib_ID = 3000000000103 LEFT JOIN
        LSDBO.value_numeric AS vv12 ON av12.Value_ID = vv12.id AND av12.Attrib_ID = 3000000000103  LEFT JOIN
        lsdbo.attrib_value av13 on rw.id = av13.object_id and av13.attrib_id = 3000000000108 left join
        lsdbo.value_datetime vv13 on av13.value_id = vv13.id and av13.attrib_id = 3000000000108 left join
        LSDBO.attrib_value AS av14 ON rw.id = av14.Object_ID AND av14.Attrib_ID = 100000648000000 LEFT JOIN
        LSDBO.value_numeric AS vv14 ON av14.Value_ID = vv14.id AND av14.Attrib_ID = 100000648000000   LEFT JOIN
        LSDBO.attrib_value AS av15 ON rw.id = av15.Object_ID AND av15.Attrib_ID = 100004081600000 LEFT JOIN
        LSDBO.value_numeric AS vv15 ON av15.Value_ID = vv15.id AND av15.Attrib_ID = 100004081600000 LEFT JOIN
        LSDBO.attrib_value AS av16 ON rw.id = av16.Object_ID AND av16.Attrib_ID = 100004087100000 LEFT JOIN
        LSDBO.value_numeric AS vv16 ON av16.Value_ID = vv16.id AND av16.Attrib_ID = 100004087100000 LEFT JOIN
        LSDBO.attrib_value AS av17 ON rw.id = av17.Object_ID AND av17.Attrib_ID = 9 LEFT JOIN
        LSDBO.value_datetime AS vv17 ON av17.Value_ID = vv17.id AND av17.Attrib_ID = 9 LEFT JOIN
        LSDBO.attrib_value AS av18 ON rw.id = av18.Object_ID AND av18.Attrib_ID = 100004060300000 LEFT JOIN
        LSDBO.value_numeric AS vv18 ON av18.Value_ID = vv18.id AND av18.Attrib_ID = 100004060300000
вот тоже самое через cte
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
with
_string_value as (--NOT MATERIALIZED(
select av.Object_id as id,
     av.Attrib_ID as IdTypeAttr,
       coalesce(sv.value,'')+coalesce(sv.value1,'') as sValue
 from  LSDBO.attrib_value as av left join
       LSDBO.value_string as sv on sv.id=av.Value_ID
),
_numeric_value as (--NOT MATERIALIZED(
select av.Object_id as id,
     av.Attrib_ID as IdTypeAttr,
       nv.value as nValue
 from  LSDBO.attrib_value as av left join
       LSDBO.value_numeric as nv on nv.id=av.Value_id
),
_datetime_value as (--NOT MATERIALIZED(
select av.Object_id as id,
     av.Attrib_ID as IdTypeAttr,
       dv.value as dValue
 from  LSDBO.attrib_value as av left join
       LSDBO.value_datetime as dv on dv.id=av.Value_ID
)
select
  rw.id, av14.nValue, av1.sValue, av3.sValue, av4.dValue, av15.nValue, av5.nValue, av12.nValue, av10.nValue, av11.nValue,av16.nValue,av6.sValue, av7.sValue,av13.dValue, av18.nValue
--  av14.nValue, rw.cd, av.sValue, rw.description, av2.sValue, av1.sValue, av3.sValue+' ', av4.dValue, isnull(av15.nValue,0), isnull(av5.nValue,0), isnull(av12.nValue,0), isnull(av10.nValue,0), isnull(av11.nValue,0),isnull(av16.nValue,0),av6.sValue, av7.sValue AS pStatus,isnull(av13.dValue, av17.dValue),(select LSDBO.Ric_Get_ShortFIO(av9.sValue)), av18.nValue
 From lsdbo.Ric_Get_Select(2,100000038900005,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw left join
--select * From lsdbo.Ric_Get_Select(2,4000000015666,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw left join
_string_value AS av ON av.id= rw.id and av.idTypeAttr= 100004068400000 left join
_string_value AS av1 ON av1.id= rw.id and av1.idTypeAttr= 3000000000101 LEFT JOIN
_string_value AS av2 ON av2.id= rw.id and av2.idTypeAttr= 100003121500000 LEFT JOIN
_string_value AS av3 ON av3.id= rw.id and av3.idTypeAttr= 100004086600000 left join
_datetime_value AS av4 ON av4.id= rw.id and av4.idTypeAttr= 3000000000106 LEFT JOIN
_numeric_value AS av5 ON av5.id= rw.id and av5.idTypeAttr= 100033995800000 left join
_string_value AS av6 ON av6.id= rw.id and av6.idTypeAttr= 100005428000000 LEFT JOIN
_string_value AS av7 ON av7.id= rw.id and av7.idTypeAttr= 100007986500005 left join
_string_value AS av8 ON av8.id= rw.id and av8.idTypeAttr= 100003121500000 left join
_string_value AS av9 ON av9.id= rw.id and av9.idTypeAttr= 3000000000122 LEFT JOIN
_numeric_value AS av10 ON av10.id= rw.id and av10.idTypeAttr= 100007971500005 LEFT JOIN
_numeric_value AS av11 ON av11.id= rw.id and av11.idTypeAttr= 100038030700000 LEFT JOIN
_numeric_value AS av12 ON av12.id= rw.id and av12.idTypeAttr= 3000000000103  LEFT JOIN
_datetime_value AS av13 ON av13.id= rw.id and av13.idTypeAttr= 3000000000108 left join
_numeric_value AS av14 ON av14.id= rw.id and av14.idTypeAttr= 100000648000000   LEFT JOIN
_numeric_value AS av15 ON av15.id= rw.id and av15.idTypeAttr= 100004081600000 LEFT JOIN
_numeric_value AS av16 ON av16.id= rw.id and av16.idTypeAttr= 100004087100000 LEFT JOIN
_datetime_value AS av17 ON av17.id= rw.id and av17.idTypeAttr= 9 LEFT JOIN
_numeric_value AS av18 ON av18.id= rw.id and av18.idTypeAttr= 100004060300000
ответ один и тот же, выглядит примерно так...
test1.png
статистика без cte
without_cte.png
статистика с cte
with_cte.png
здесь на выборке ~7200 строк cte 'визуально по ощущениям' быстрее, по цифрам примерно одинаково, но на меньшем количестве left join и глубине выборки cte однозначно быстрее...

вопрос чисто эмпирический - можно ли как то ускорить запрос многократно использующий в left join одни и те-же таблицы (в данном случае) каким либо другим подходом к выборке? или это все упирается в железо сервера?

ps
на mssql сейчас отдельная виртуалка winserver2003+mssql2005 (здесь по скорости все более менее, количество left join глотает нормально), на postgres просто виртуалка win8.1+posgresql16 (здесь все это вообще выглядит 'мертвым' при большом количестве left join)
...
Рейтинг: 0 / 0
12.02.2025, 12:19
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40139993
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
ef1 [игнорируется] 

структуру покажите с индексами, планы, которые у вас получились
...
Рейтинг: 0 / 0
12.02.2025, 14:01
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140001
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
Shakill [игнорируется] 
здесь структура таблиц для left join
struct.png
и два вложения с планами без CTE и с ним
plan_with_cte.sqlplan
plan_with_cte.sqlplan
plan_without_cte.sqlplan
plan_without_cte.sqlplan
...
Рейтинг: 0 / 0
12.02.2025, 15:41
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140052
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
неожиданно на postgresql стрельнула вот такая конструкция
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
with
_value_attr as NOT MATERIALIZED(
select
     av.Object_id as id,
     av.Attrib_ID as IdTypeAttr,
       coalesce(sv.value,'')+coalesce(sv.value1,'') as sValue,
       nv.value as nValue,
       dv.value as dValue
 from  LSDBO.attrib_value as av left join
       LSDBO.value_string as sv on sv.id=av.Value_ID left join
       LSDBO.value_datetime as dv on dv.id=av.Value_ID left join
       LSDBO.value_numeric as nv on nv.id=av.Value_id
 where av.Attrib_ID in(
   100000016000000,100005427200000,100004974000000,100004087100000,3000000000004,
   3000000000021,3000000000024,3000000000023,3000000000025,3000000000041,3000000000042,
   3000000000043,3000000000062,100000019000005,100004086100000,3000000000022,3000000000002,
   3000000000122,100040102100000,100040102000000,100004986100000,3000000000005,100001318200016
   )
 )
  SELECT
    rw.id as isobject_id,
        rw.cd as cDate,
        vv3.sValue + coalesce(' (' + vv13.sValue + ')','') as cIst,
        vv.sValue as cStatus,
        vv15.sValue as cForm,
        rw.description as cName,
        vv0.sValue as cAbr,
        vv17.sValue as cTwoName,
        vv1.sValue as cManager,
        vv14.sValue as cIndex,
        vv4.sValue as cStrana,
        vv5.sValue as cOkrug,
        vv6.sValue as cSity,
        vv7.sValue as cStreet,
        vv8.sValue as cKod,
        vv9.sValue as cFax,
        vv10.sValue as cTel,
        vv11.sValue as cEmail,
        vv12.nValue as cBy,
        coalesce(vv2.nValue,0) as cWarning,
        vv16.sValue as cCurator,
        vv18.sValue as Req,
        vv19.sValue as xmlName,
        vv20.sValue as xmlInfo,
        coalesce(vv21.sValue,'0') as idB24
  FROM
    lsdbo.Ric_Get_Select(1,0, 'Dvl','%%','',0,'') rw left join
    _value_attr AS vv ON vv.id= rw.id and vv.idTypeAttr= 100000016000000 left join
    _value_attr AS vv0 ON vv0.id= rw.id and vv0.idTypeAttr= 100005427200000 left join
    _value_attr AS vv1 ON vv1.id= rw.id and vv1.idTypeAttr= 100004974000000 left join
    _value_attr AS vv2 ON vv2.id= rw.id and vv2.idTypeAttr= 100004087100000 left join
    _value_attr AS vv3 ON vv3.id= rw.id and vv3.idTypeAttr= 3000000000004 left join
    _value_attr AS vv4 ON vv4.id= rw.id and vv4.idTypeAttr= 3000000000021 left join
    _value_attr AS vv5 ON vv5.id= rw.id and vv5.idTypeAttr= 3000000000024 left join
    _value_attr AS vv6 ON vv6.id= rw.id and vv6.idTypeAttr= 3000000000023 left join
    _value_attr AS vv7 ON vv7.id= rw.id and vv7.idTypeAttr= 3000000000025 left join
    _value_attr AS vv8 ON vv8.id= rw.id and vv8.idTypeAttr= 3000000000041 left join
    _value_attr AS vv9 ON vv9.id= rw.id and vv9.idTypeAttr= 3000000000042 left join
    _value_attr AS vv10 ON vv10.id= rw.id and vv10.idTypeAttr= 3000000000043 left join
    _value_attr AS vv11 ON vv11.id= rw.id and vv11.idTypeAttr= 3000000000062 left join
    _value_attr AS vv12 ON vv12.id= rw.id and vv12.idTypeAttr= 100000019000005 left join
    _value_attr AS vv13 ON vv13.id= rw.id and vv13.idTypeAttr= 100004086100000 left join
    _value_attr AS vv14 ON vv14.id= rw.id and vv14.idTypeAttr= 3000000000022 left join
    _value_attr AS vv15 ON vv15.id= rw.id and vv15.idTypeAttr= 3000000000002 left join
    _value_attr AS vv16 ON vv16.id= rw.id and vv16.idTypeAttr= 3000000000122 left join
    _value_attr AS vv17 ON vv17.id= rw.id and vv17.idTypeAttr= 100040102100000 left join
    _value_attr AS vv18 ON vv18.id= rw.id and vv18.idTypeAttr= 100040102000000 left join
    _value_attr AS vv19 ON vv19.id= rw.id and vv19.idTypeAttr= 100004986100000 left join
    _value_attr AS vv20 ON vv20.id= rw.id and vv20.idTypeAttr= 3000000000005 left join
    _value_attr AS vv21 ON vv21.id= rw.id and vv21.idTypeAttr= 100001318200016
общий смысл = в cte формируем полную таблицу значений атрибутов и связей с объектами
(самая длинная таблица 9 562 038 строк - подрезаем ее через in = только нужные атрибуты = получается где то 2 700 099 - неважно...)
говорим что материализация не нужна as NOT MATERIALIZED( типа в память упаковываем (как я понял)

и дальше работаем с ней в запросе - выигрыш на коротких выборках = до 200 - на порядок, на длинных минимум в два раза быстрее
в mssql нет эффекта - не нашел флага не материализовать - наверно аналог табличная переменная

короче в памяти формируем блок и многократно его используем...
...
Рейтинг: 0 / 0
15.02.2025, 17:41
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140053
чебуран
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
чебуран
Участник
В статье по EAV в википедии есть вариант конкретно для постгре (и для мс)

https://en.wikipedia.org/wiki/Entity–attribute–value_model#PostgreSQL:_JSONB_columns

Это переход на JSON хранение. Но он подразумевает изменение структуры, поэтому, возможно, не подойдет.

Сам не пользовался, но судя по гуглу такой подход вполне используют.
...
Рейтинг: 0 / 0
15.02.2025, 20:21
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140054
чебуран
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
чебуран
Участник
Да, в той же статье есть еще всякие разные подходы оптимизации EAV. Отдельно описаны.
...
Рейтинг: 0 / 0
15.02.2025, 20:24
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140278
Тяпа-ляпа
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тяпа-ляпа
Участник
ef1 [игнорируется] 

Странно, что помогло NOT MATERIALIZED, я бы понял, если бы с MATERIALIZED быстро работало.

cte c NOT MATERIALIZED не делает этого: "формируем блок", данные читаются из исходных таблиц.
...
Рейтинг: 0 / 0
06.03.2025, 14:50
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140291
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
Тяпа-ляпа [игнорируется] 

ну не знаю... по факту так = в памяти получается быстрее - так оптимизатор их строит, в конечном счете убив неделю на разборки (вылавливая микросекунды на конструирование запроса), отправив все планы на анализ в https://explain.tensor.ru/ - получил только одну рекомендацию = добавить индексов

ну и настроить железо под postgresql, и сам сервер постгрис
в общем пока все печально

ps
пойду в их тему - postgres может там найду что

Спасибо всем!
...
Изменено: 10.03.2025, 09:35 - ef1
Рейтинг: 0 / 0
10.03.2025, 09:31
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140301
Тяпа-ляпа
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тяпа-ляпа
Участник
ef1 [игнорируется] 

Все джойны выполняются в памяти, если уж быть точным, даже если данные не в кэше, а на диске на начало выполнения запроса.
...
Рейтинг: 0 / 0
10.03.2025, 12:15
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140316
ArtToms
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ArtToms
Участник
Добрый день.

Есть варианты оптимизации запроса, желательно пробовать на данных похожие на реальные.
Можно ли получить скрипты на создания таблиц и обезличенные тестовые данные?
Если не позволяет размер файлов прикрепить к форуму, можно ссылку на облако.
Спасибо.
...
Рейтинг: 0 / 0
11.03.2025, 11:14
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140330
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ArtToms [игнорируется] 

скрипты таблиц не проблема - а вот обезличенные данные - проблема - база коммерческая...
в принципе запросы идут по 6 таблицам объекты + типы объектов + атрибуты(строковые + числа + дата/время) + связь объектов с атрибутами... да и в mssql все летает, основной трабл в postgresql (https://resql.ru/forum/topic.php?fid=53&tid=2187215) - цель именно там скорость получить в их оптимизаторе..

но за предложение спасибо))
...
Рейтинг: 0 / 0
12.03.2025, 09:23
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140331
ArtToms
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ArtToms
Участник
ef1 [игнорируется] 
Добрый день.
Попробовать не могу, нет таблиц, хотя бы покажу, что я хотел попробовать.
1. Уменьшить число внешних соединений, выделил полужирным, можно попробовать заменить на внутреннее соединение (inner).
Внешнее оставить только к rw, и так в других местах, уменьшаем в два раза. Должно повлиять на скорость выполнения.
2. В MSSQL внешние соединения удавалось сделать внутренними с помощью cross apply.
Вот это я хотел попробовать.

From lsdbo.Ric_Get_Select(2,100000038900005,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw left join
--select * From lsdbo.Ric_Get_Select(2,4000000015666,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw left join
LSDBO.attrib_value AS av ON rw.id = av.Object_ID AND av.Attrib_ID = 100004068400000 LEFT JOIN
LSDBO.value_string AS vv ON av.Value_ID = vv.id AND av.Attrib_ID = 100004068400000 left join
LSDBO.attrib_value AS av1 ON rw.id = av1.Object_ID AND av1.Attrib_ID = 3000000000101 LEFT JOIN
LSDBO.value_string AS vv1 ON av1.Value_ID = vv1.id AND av1.Attrib_ID = 3000000000101 LEFT JOIN
...
Рейтинг: 0 / 0
12.03.2025, 09:53
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140333
ArtToms
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ArtToms
Участник
ef1 [игнорируется] 

А можно хотя бы скрипты таблиц?
Можно оставить только поля указанные в запросе, попробую накидать тестовые данные.
Есть еще один вариант изменения запроса, хочу попробовать.
...
Рейтинг: 0 / 0
12.03.2025, 13:21
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140345
ShIgor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ShIgor
Участник
ef1 [игнорируется] 

что мешает вытянуть сначала все объекты и все их атрибуты и поверх PIVOT (для PG crosstab)?
...
Рейтинг: 0 / 0
13.03.2025, 01:24
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140346
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ShIgor [игнорируется] 

в общем то ничего за исключением переменной длины столбцов и динамического crosstab в postgresql - изучаю как раз
не подскажите синтаксис pivot для поворота на примере t-sql
запроса cte объект и его атрибуты
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
with
_obj_and_attr as (--NOT MATERIALIZED(
  select
    rw.id,
    tw.mnemo,
    rw.cd,
    rw.description,
    av.Attrib_ID as IdTypeAttr,
    coalesce(sv.value,'')+coalesce(sv.value1,'') as sValue,
    nv.value as nValue,
    dv.value as dValue
  from
    LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id left join
    LSDBO.attrib_value as av on rw.id=av.Object_id left join
    LSDBO.value_string as sv on sv.id=av.Value_ID left join
    LSDBO.value_datetime as dv on dv.id=av.Value_ID left join
    LSDBO.value_numeric as nv on nv.id=av.Value_id
  where
    tw.mnemo in (
      'Req','tnd','PSft', 'PHrd', 'PExp', 'PSrv', 'pPls','Del'
    )
    and
    av.Attrib_ID in(
      3000000000105,100004086600000,3000000000106,100037488300000,3000000000122,
      100000019000005,100000648000000,3000000000101,100005428000000,
      100004087100000, -33,100004081200000
    )
)

select * from _obj_and_attr
 where id=100000000000045 --для одного объекта
как повернуть результат запроса по колонке IdTypeAttr чтобы получить одну строку со всеми колонками атрибутов (количество атрибутов может быть разным)(в данном случае для одного объекта)?
pivot.png
...
Изменено: 13.03.2025, 10:09 - ef1
Рейтинг: 0 / 0
13.03.2025, 10:07
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140347
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ArtToms [игнорируется] 
предлагаю через удаленный рабочий стол (например anydesk) посмотреть в MS студии - так будет проще всего - напишите zaytsev@cad.ru - потестим
?
...
Рейтинг: 0 / 0
13.03.2025, 10:23
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140348
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ef1 [игнорируется] 
упс...
к сожалению pivot попробовать здесь не могу = база старая sql compatibility level = server 2000(80) - удалось переехать только на 2005, уровень совместимости поднять не могу (( нет лицензий хотя-бы на 2008 так что мы в пролете )) - это одна из причин 'бегства' на PostgreSQL....
...
Рейтинг: 0 / 0
13.03.2025, 10:46
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140349
ShIgor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ShIgor
Участник
ef1 [игнорируется] 

наверное переменной не длины, а переменного количества столбцов атрибутов у разных объектов?
это решается перечислением всех возможных вариантов для каждого объекта, т.е. в плоской таблице у каждого объекта должно быть то количество строк атрибутов, сколько всего столбцов планируется в Pivot вне зависимости, есть они физически у объекта или нет, даже в том случае если объект вообще не имеет атрибутов,
например при необходимости вывести 15 атрибутов в столбцы, каждый объект должен иметь 15 строк атрибутов.

пример pivot? в интернете вариантов масса, он каждый получается уникальный..
могу набросать с вашими метаданными.. сейчас только времени нет, на досуге..

а, ну и в PG не силен, только знаю что он (PG) есть :)
...
Изменено: 13.03.2025, 11:17 - ShIgor
Рейтинг: 0 / 0
13.03.2025, 11:16
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140350
ShIgor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ShIgor
Участник
ef1  13.03.2025, 10:46
[игнорируется]
40140348
нет лицензий хотя-бы на 2008
это когда кому мешало?
...
Рейтинг: 0 / 0
13.03.2025, 11:21
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140351
ShIgor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ShIgor
Участник
ef1  13.03.2025, 10:46
[игнорируется]
40140348
к сожалению pivot попробовать здесь не могу
pivot - это "синтаксический сахар" - все можно сделать скриптом не выходя за рамки простейшего SQL
...
Рейтинг: 0 / 0
13.03.2025, 11:23
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140352
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ShIgor [игнорируется] 

спасибо!!! в ожидании досуга, пробую разобраться (смущает только требование функции агрегации, а в моем случае агрегировать в общем то нечего) -
ps
примеров да - навалом, да и 2005 оказалось поддерживает pivot, ок... до связи!
...
Рейтинг: 0 / 0
13.03.2025, 12:13
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140359
ArtToms
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ArtToms
Участник
На postgresql создал тестовые таблицы для выполнения запросов, теперь есть на чем пробовать запросы.
Привожу вариант преобразования внешнего соединения во внутреннее.
На MSSQL, такая замена значительно увеличивала скорость выполнения запросов.
Привожу запросы на postgresql.
Переделал фрагмент из вашего запроса и новый вариант.
Непонятно зачем вот это условие AND av.Attrib_ID = 100004068400000 в left join value_string AS vv?
Отметил полужирным, поставил комментарий, у меня на результаты не повлияло.
Интересно на боевой базе данные изменятся?
В вашем запросе можно переделать подобным образом все аналогичные блоки.
Попробуйте переделайте весь запрос и попробуйте на боевой базе.
Интересно как изменится скорость на postgresql?
Цитата 
[игнорируется]
select rw.id, vv.value From Ric_Get_Select (2,100000038900005,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw left join attrib_value AS av ON rw.id = av.Object_ID AND av.Attrib_ID = 100004068400000 left join value_string AS vv ON av.Value_ID = vv.id --AND av.Attrib_ID = 100004068400000 order by rw.id
Цитата 
[игнорируется]
select rw.id, vv.value From Ric_Get_Select (2,100000038900005,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw cross join lateral (select max(vvv.value) as value from attrib_value av inner join value_string vvv on av.Value_ID = vvv.id where rw.id = av.Object_ID and av.Attrib_ID = 100004068400000) vv order by rw.id
...
Изменено: 13.03.2025, 20:56 - ArtToms
Рейтинг: 0 / 0
13.03.2025, 20:53
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140360
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ArtToms [игнорируется] 

!!! щас буду пробовать
вот скрипты таблиц (но лучше конечно на удаленном столе подключится к реальной базе и MSSQL и PostgreSQL - базы идентичны = была миграция средствами разработчика = владельца структуры данных БД )

MSSQL
object_reference - таблица объектов
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
USE [crm_440]
GO

/****** Object:  Table [LSDBO].[object_reference]    Script Date: 14.03.2025 8:43:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [LSDBO].[object_reference](
  [id] [numeric](18, 0) NOT NULL,
  [description] [varchar](255) NOT NULL,
  [author_id] [int] NOT NULL,
  [filial_id] [int] NOT NULL,
  [cd] [datetime] NOT NULL,
  [user_id] [int] NOT NULL,
  [ws_id] [int] NOT NULL,
  [type_id] [numeric](18, 0) NOT NULL,
  [common_type] [char](1) NOT NULL,
  [parent_id] [numeric](18, 0) NULL,
  [parent_filial_id] [int] NULL,
  [Unit_Last_No] [int] NOT NULL,
  [Created] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [LSDBO].[object_reference] ADD  DEFAULT (0) FOR [filial_id]
GO

ALTER TABLE [LSDBO].[object_reference] ADD  DEFAULT (getdate()) FOR [cd]
GO

ALTER TABLE [LSDBO].[object_reference] ADD  DEFAULT (0) FOR [Unit_Last_No]
GO

ALTER TABLE [LSDBO].[object_reference]  WITH CHECK ADD  CONSTRAINT [object_type_key1] FOREIGN KEY([type_id])
REFERENCES [LSDBO].[object_type] ([id])
GO

ALTER TABLE [LSDBO].[object_reference] CHECK CONSTRAINT [object_type_key1]
GO

ALTER TABLE [LSDBO].[object_reference]  WITH CHECK ADD CHECK  (([common_type] = 'Y' or ([common_type] = 'P' or [common_type] = 'R')))
GO
object_type- таблица типов объектов
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
USE [crm_440]
GO

/****** Object:  Table [LSDBO].[object_type]    Script Date: 14.03.2025 8:45:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [LSDBO].[object_type](
  [id] [numeric](18, 0) NOT NULL,
  [Description] [varchar](50) NOT NULL,
  [icon_path] [varchar](50) NOT NULL,
  [object_type] [char](1) NOT NULL,
  [sort_key] [int] NOT NULL,
  [link_type] [int] NULL,
  [mnemo] [varchar](4) NOT NULL,
  [Rep_Id] [numeric](18, 0) NULL,
  [Rep_Filial_Id] [int] NULL,
  [common_type] [char](1) NOT NULL,
  [author_id] [int] NOT NULL,
  [user_id] [int] NOT NULL,
  [cd] [datetime] NOT NULL,
  [ws_id] [smallint] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [LSDBO].[object_type] ADD  DEFAULT (0) FOR [Rep_Id]
GO

ALTER TABLE [LSDBO].[object_type] ADD  DEFAULT (0) FOR [Rep_Filial_Id]
GO

ALTER TABLE [LSDBO].[object_type] ADD  DEFAULT ('Y') FOR [common_type]
GO
attrib_value - таблица связей объектов и атрибутов
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
USE [crm_440]
GO

/****** Object:  Table [LSDBO].[attrib_value]    Script Date: 14.03.2025 8:47:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [LSDBO].[attrib_value](
  [ID] [numeric](18, 0) NOT NULL,
  [Object_ID] [numeric](18, 0) NOT NULL,
  [TreeLink_ID] [numeric](18, 0) NOT NULL,
  [Attrib_ID] [numeric](18, 0) NOT NULL,
  [Value_ID] [numeric](18, 0) NOT NULL,
  [author_id] [int] NOT NULL,
  [user_id] [int] NOT NULL,
  [cd] [datetime] NOT NULL,
  [ws_id] [int] NOT NULL,
  [filial_id] [int] NOT NULL,
  [Parent_ID] [numeric](18, 0) NOT NULL,
 CONSTRAINT [attrib_value_PK] PRIMARY KEY NONCLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [LSDBO].[attrib_value] ADD  DEFAULT (0) FOR [Parent_ID]
GO

ALTER TABLE [LSDBO].[attrib_value]  WITH CHECK ADD  CONSTRAINT [attrib_key1] FOREIGN KEY([Attrib_ID])
REFERENCES [LSDBO].[attrib] ([id])
GO

ALTER TABLE [LSDBO].[attrib_value] CHECK CONSTRAINT [attrib_key1]
GO

ALTER TABLE [LSDBO].[attrib_value]  WITH CHECK ADD  CONSTRAINT [object_reference_key2] FOREIGN KEY([Object_ID])
REFERENCES [LSDBO].[object_reference] ([id])
GO

ALTER TABLE [LSDBO].[attrib_value] CHECK CONSTRAINT [object_reference_key2]
GO
value_string - таблица строковых значений атрибутов
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
USE [crm_440]
GO

/****** Object:  Table [LSDBO].[value_string]    Script Date: 14.03.2025 8:49:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [LSDBO].[value_string](
  [id] [numeric](18, 0) NOT NULL,
  [filial_id] [int] NOT NULL,
  [value] [varchar](255) NOT NULL,
  [author_id] [int] NOT NULL,
  [attrib_id] [numeric](18, 0) NOT NULL,
  [Value1] [varchar](1745) NULL,
PRIMARY KEY CLUSTERED
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [LSDBO].[value_string] ADD  DEFAULT (0) FOR [filial_id]
GO

ALTER TABLE [LSDBO].[value_string]  WITH CHECK ADD  CONSTRAINT [attrib_key3] FOREIGN KEY([attrib_id])
REFERENCES [LSDBO].[attrib] ([id])
GO

ALTER TABLE [LSDBO].[value_string] CHECK CONSTRAINT [attrib_key3]
GO
value_numeric - таблица числовых значений атрибутов
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
USE [crm_440]
GO

/****** Object:  Table [LSDBO].[value_numeric]    Script Date: 14.03.2025 8:50:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [LSDBO].[value_numeric](
  [id] [numeric](18, 0) NOT NULL,
  [filial_id] [int] NOT NULL,
  [value] [float] NOT NULL,
  [author_id] [int] NOT NULL,
  [attrib_id] [numeric](18, 0) NOT NULL,
PRIMARY KEY CLUSTERED
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [LSDBO].[value_numeric] ADD  DEFAULT (0) FOR [filial_id]
GO

ALTER TABLE [LSDBO].[value_numeric]  WITH CHECK ADD  CONSTRAINT [attrib_key7] FOREIGN KEY([attrib_id])
REFERENCES [LSDBO].[attrib] ([id])
GO

ALTER TABLE [LSDBO].[value_numeric] CHECK CONSTRAINT [attrib_key7]
GO
value_datetime- таблица дата время значений атрибутов
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
USE [crm_440]
GO

/****** Object:  Table [LSDBO].[value_datetime]    Script Date: 14.03.2025 8:51:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [LSDBO].[value_datetime](
  [id] [numeric](18, 0) NOT NULL,
  [filial_id] [int] NOT NULL,
  [value] [datetime] NOT NULL,
  [author_id] [int] NOT NULL,
  [attrib_id] [numeric](18, 0) NOT NULL,
PRIMARY KEY CLUSTERED
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [LSDBO].[value_datetime] ADD  DEFAULT (0) FOR [filial_id]
GO

ALTER TABLE [LSDBO].[value_datetime]  WITH CHECK ADD  CONSTRAINT [attrib_key4] FOREIGN KEY([attrib_id])
REFERENCES [LSDBO].[attrib] ([id])
GO

ALTER TABLE [LSDBO].[value_datetime] CHECK CONSTRAINT [attrib_key4]
GO
PostgreSQL
object_reference - таблица объектов
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
-- Table: lsdbo.object_reference

-- DROP TABLE IF EXISTS lsdbo.object_reference;

CREATE TABLE IF NOT EXISTS lsdbo.object_reference
(
    id numeric(15,0) NOT NULL,
    description character varying(255) COLLATE pg_catalog."default" NOT NULL,
    author_id integer NOT NULL,
    filial_id integer NOT NULL DEFAULT 0,
    cd timestamp without time zone NOT NULL DEFAULT clock_timestamp(),
    user_id integer NOT NULL,
    ws_id integer NOT NULL,
    type_id numeric(15,0) NOT NULL,
    common_type character(1) COLLATE pg_catalog."default" NOT NULL,
    parent_id numeric(15,0),
    parent_filial_id integer,
    unit_last_no integer NOT NULL DEFAULT 0,
    created timestamp without time zone NOT NULL,
    CONSTRAINT object_reference_pkey PRIMARY KEY (id),
    CONSTRAINT object_type_key1 FOREIGN KEY (type_id)
        REFERENCES lsdbo.object_type (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT object_reference_common_type_check CHECK (common_type = ANY (ARRAY['R'::bpchar, 'P'::bpchar, 'Y'::bpchar]))
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS lsdbo.object_reference
    OWNER to lsdbo;
-- Index: object_reference_cd_ndx

-- DROP INDEX IF EXISTS lsdbo.object_reference_cd_ndx;

CREATE INDEX IF NOT EXISTS object_reference_cd_ndx
    ON lsdbo.object_reference USING btree
    (cd ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: object_reference_desc_ndx

-- DROP INDEX IF EXISTS lsdbo.object_reference_desc_ndx;

CREATE INDEX IF NOT EXISTS object_reference_desc_ndx
    ON lsdbo.object_reference USING btree
    (description COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: ric-object_reference-ccd76827

-- DROP INDEX IF EXISTS lsdbo."ric-object_reference-ccd76827";

CREATE INDEX IF NOT EXISTS "ric-object_reference-ccd76827"
    ON lsdbo.object_reference USING btree
    (type_id ASC NULLS LAST)
    TABLESPACE pg_default;

-- Trigger: p_objectreference_right_d

-- DROP TRIGGER IF EXISTS p_objectreference_right_d ON lsdbo.object_reference;

CREATE OR REPLACE TRIGGER p_objectreference_right_d
    BEFORE DELETE
    ON lsdbo.object_reference
    FOR EACH ROW
    EXECUTE FUNCTION lsdbo.p_objectreference_right_d_pg();
object_type- таблица типов объектов
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
-- Table: lsdbo.object_type

-- DROP TABLE IF EXISTS lsdbo.object_type;

CREATE TABLE IF NOT EXISTS lsdbo.object_type
(
    id numeric(15,0) NOT NULL,
    description character varying(50) COLLATE pg_catalog."default" NOT NULL,
    icon_path character varying(50) COLLATE pg_catalog."default" NOT NULL,
    object_type character(1) COLLATE pg_catalog."default" NOT NULL,
    sort_key integer NOT NULL,
    link_type integer,
    mnemo character varying(4) COLLATE pg_catalog."default" NOT NULL,
    rep_id numeric(15,0) DEFAULT 0,
    rep_filial_id integer DEFAULT 0,
    common_type character(1) COLLATE pg_catalog."default" NOT NULL DEFAULT 'Y'::bpchar,
    author_id integer NOT NULL,
    user_id integer NOT NULL,
    cd timestamp without time zone NOT NULL,
    ws_id smallint NOT NULL,
    CONSTRAINT object_type_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS lsdbo.object_type
    OWNER to lsdbo;
-- Index: ric-object_type-d99d83df

-- DROP INDEX IF EXISTS lsdbo."ric-object_type-d99d83df";

CREATE INDEX IF NOT EXISTS "ric-object_type-d99d83df"
    ON lsdbo.object_type USING btree
    ((mnemo::text) COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

-- Trigger: p_object_type_right_d

-- DROP TRIGGER IF EXISTS p_object_type_right_d ON lsdbo.object_type;

CREATE OR REPLACE TRIGGER p_object_type_right_d
    BEFORE DELETE
    ON lsdbo.object_type
    FOR EACH ROW
    EXECUTE FUNCTION lsdbo.p_object_type_right_d_pg();
attrib_value - таблица связей объектов и атрибутов
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
-- Table: lsdbo.attrib_value

-- DROP TABLE IF EXISTS lsdbo.attrib_value;

CREATE TABLE IF NOT EXISTS lsdbo.attrib_value
(
    id numeric(15,0) NOT NULL,
    object_id numeric(15,0) NOT NULL,
    treelink_id numeric(15,0) NOT NULL,
    attrib_id numeric(15,0) NOT NULL,
    value_id numeric(15,0) NOT NULL,
    author_id integer NOT NULL,
    user_id integer NOT NULL,
    cd timestamp without time zone NOT NULL,
    ws_id integer NOT NULL,
    filial_id integer NOT NULL,
    parent_id numeric(15,0) NOT NULL DEFAULT 0,
    CONSTRAINT attrib_value_pk PRIMARY KEY (id),
    CONSTRAINT attrib_key1 FOREIGN KEY (attrib_id)
        REFERENCES lsdbo.attrib (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT object_reference_key2 FOREIGN KEY (object_id)
        REFERENCES lsdbo.object_reference (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS lsdbo.attrib_value
    OWNER to lsdbo;
-- Index: attrib_value_av_ndx

-- DROP INDEX IF EXISTS lsdbo.attrib_value_av_ndx;

CREATE INDEX IF NOT EXISTS attrib_value_av_ndx
    ON lsdbo.attrib_value USING btree
    (attrib_id ASC NULLS LAST, value_id ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: attrib_value_cd_ndx

-- DROP INDEX IF EXISTS lsdbo.attrib_value_cd_ndx;

CREATE INDEX IF NOT EXISTS attrib_value_cd_ndx
    ON lsdbo.attrib_value USING btree
    (cd ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: object_reference_ndx2

-- DROP INDEX IF EXISTS lsdbo.object_reference_ndx2;

CREATE INDEX IF NOT EXISTS object_reference_ndx2
    ON lsdbo.attrib_value USING btree
    (object_id ASC NULLS LAST, treelink_id ASC NULLS LAST, attrib_id ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: treelinkid_ndx

-- DROP INDEX IF EXISTS lsdbo.treelinkid_ndx;

CREATE INDEX IF NOT EXISTS treelinkid_ndx
    ON lsdbo.attrib_value USING btree
    (treelink_id ASC NULLS LAST, attrib_id ASC NULLS LAST)
    TABLESPACE pg_default;

-- Trigger: p_attribvalue_i

-- DROP TRIGGER IF EXISTS p_attribvalue_i ON lsdbo.attrib_value;

CREATE OR REPLACE TRIGGER p_attribvalue_i
    BEFORE INSERT OR UPDATE
    ON lsdbo.attrib_value
    FOR EACH ROW
    EXECUTE FUNCTION lsdbo.p_attribvalue_i_pg();
value_string - таблица строковых значений атрибутов
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
-- Table: lsdbo.value_string

-- DROP TABLE IF EXISTS lsdbo.value_string;

CREATE TABLE IF NOT EXISTS lsdbo.value_string
(
    id numeric(15,0) NOT NULL,
    filial_id integer NOT NULL DEFAULT 0,
    value character varying(255) COLLATE pg_catalog."default" NOT NULL,
    author_id integer NOT NULL,
    attrib_id numeric(15,0) NOT NULL,
    value1 character varying(1745) COLLATE pg_catalog."default",
    CONSTRAINT value_string_pkey PRIMARY KEY (id),
    CONSTRAINT attrib_key3 FOREIGN KEY (attrib_id)
        REFERENCES lsdbo.attrib (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS lsdbo.value_string
    OWNER to lsdbo;
-- Index: attrib_ndx3

-- DROP INDEX IF EXISTS lsdbo.attrib_ndx3;

CREATE INDEX IF NOT EXISTS attrib_ndx3
    ON lsdbo.value_string USING btree
    (attrib_id ASC NULLS LAST, value COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

-- Trigger: p_values_i

-- DROP TRIGGER IF EXISTS p_values_i ON lsdbo.value_string;

CREATE OR REPLACE TRIGGER p_values_i
    BEFORE DELETE
    ON lsdbo.value_string
    FOR EACH ROW
    EXECUTE FUNCTION lsdbo.p_values_i_pg();
value_numeric - таблица числовых значений атрибутов
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
-- Table: lsdbo.value_numeric

-- DROP TABLE IF EXISTS lsdbo.value_numeric;

CREATE TABLE IF NOT EXISTS lsdbo.value_numeric
(
    id numeric(15,0) NOT NULL,
    filial_id integer NOT NULL DEFAULT 0,
    value double precision NOT NULL,
    author_id integer NOT NULL,
    attrib_id numeric(15,0) NOT NULL,
    CONSTRAINT value_numeric_pkey PRIMARY KEY (id),
    CONSTRAINT attrib_key7 FOREIGN KEY (attrib_id)
        REFERENCES lsdbo.attrib (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS lsdbo.value_numeric
    OWNER to lsdbo;
-- Index: attrib_ndx7

-- DROP INDEX IF EXISTS lsdbo.attrib_ndx7;

CREATE UNIQUE INDEX IF NOT EXISTS attrib_ndx7
    ON lsdbo.value_numeric USING btree
    (attrib_id ASC NULLS LAST, value ASC NULLS LAST)
    TABLESPACE pg_default;

-- Trigger: p_valuen_i

-- DROP TRIGGER IF EXISTS p_valuen_i ON lsdbo.value_numeric;

CREATE OR REPLACE TRIGGER p_valuen_i
    BEFORE DELETE
    ON lsdbo.value_numeric
    FOR EACH ROW
    EXECUTE FUNCTION lsdbo.p_valuen_i_pg();
value_datetime- таблица дата время значений атрибутов
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
-- Table: lsdbo.value_datetime

-- DROP TABLE IF EXISTS lsdbo.value_datetime;

CREATE TABLE IF NOT EXISTS lsdbo.value_datetime
(
    id numeric(15,0) NOT NULL,
    filial_id integer NOT NULL DEFAULT 0,
    value timestamp without time zone NOT NULL,
    author_id integer NOT NULL,
    attrib_id numeric(15,0) NOT NULL,
    CONSTRAINT value_datetime_pkey PRIMARY KEY (id),
    CONSTRAINT attrib_key4 FOREIGN KEY (attrib_id)
        REFERENCES lsdbo.attrib (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS lsdbo.value_datetime
    OWNER to lsdbo;
-- Index: attrib_ndx4

-- DROP INDEX IF EXISTS lsdbo.attrib_ndx4;

CREATE UNIQUE INDEX IF NOT EXISTS attrib_ndx4
    ON lsdbo.value_datetime USING btree
    (attrib_id ASC NULLS LAST, value ASC NULLS LAST)
    TABLESPACE pg_default;
-- Index: ric-value_datetime-09d9596e

-- DROP INDEX IF EXISTS lsdbo."ric-value_datetime-09d9596e";

CREATE INDEX IF NOT EXISTS "ric-value_datetime-09d9596e"
    ON lsdbo.value_datetime USING btree
    (id ASC NULLS LAST, value ASC NULLS LAST)
    TABLESPACE pg_default;

-- Trigger: p_valuet_i

-- DROP TRIGGER IF EXISTS p_valuet_i ON lsdbo.value_datetime;

CREATE OR REPLACE TRIGGER p_valuet_i
    BEFORE DELETE
    ON lsdbo.value_datetime
    FOR EACH ROW
    EXECUTE FUNCTION lsdbo.p_valuet_i_pg();
ps
индексы ric- в postgresql - мои по рекомендациям от https://explain.tensor.ru/ - типа к делу не относятся
...
Изменено: 14.03.2025, 09:18 - ef1
Рейтинг: 0 / 0
14.03.2025, 09:15
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140361
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ArtToms [игнорируется] 

тесты по запросу из начала топика - по первому предложению (в общем то и там и там одно и тоже - наиболее 'полный по ограничениям' запрос чуть быстрее)

mssql 2005 (10 выборок, база рабочая кешированная)
test_1.png
postgres16 (~5 выборок, тестовая база - практически не кешированная)
test_2.png
щас второе подготовлю внутреннее соединение...
...
Рейтинг: 0 / 0
14.03.2025, 10:02
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140362
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ArtToms [игнорируется] 

во по планам по запросам по второму без --AND av.Attrib_ID = 100004068400000

mssql
mssql_test_01.sqlplan
mssql_test_01.sqlplan
postgresql
postgres_test_01.csv
postgres_test_01.csv
explain analyze ->https://explain.tensor.ru/
название pg_test_zaytsev_01 - не знаю откроется по ссылке или нет (публичный архив simple plan)
https://explain.tensor.ru/archive/explain/bc5285eb94ce30b11d55bd4105a1f75a:0:2025-03-14#explain
...
Рейтинг: 0 / 0
14.03.2025, 10:22
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140364
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ArtToms [игнорируется] 

НЕВЕРОЯТНО!!!!!! на PostgreSQL ускоренный вариант ->>> на порядок быстрее!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
postgresql cross
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
--explain analyze
select
  rw.id, vv14.value,  vv1.value, vv3.value, vv4.value, vv15.value, vv5.value,vv12.value, vv10.value, vv11.value,vv16.value,vv6.value, vv7.value,vv13.value, vv18.value
--, vv14.value, rw.cd, vv.value, rw.description, vv2.value, vv1.value, vv3.value+' ', vv4.value, isnull(vv15.value,0), isnull(vv5.value,0) AS pPrice, isnull(vv12.value,0), isnull(vv10.value,0) as pDiscount01, isnull(vv11.value,0),isnull(vv16.value,0),vv6.value AS pCurrency, vv7.value AS pStatus,isnull(vv13.value, vv17.value),(select LSDBO.Ric_Get_ShortFIO(vv9.value)), vv18.value
From lsdbo.Ric_Get_Select(2,100000038900005,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw

       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_string vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100004068400000
      ) vv
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_string vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 3000000000101
      ) vv1
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_string vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100003121500000
      ) vv2
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_string vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100004086600000
      ) vv3
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_datetime vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 3000000000106
      ) vv4
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_numeric vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100033995800000
      ) vv5
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_string vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100005428000000
      ) vv6
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_string vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100007986500005
      ) vv7
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_string vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100003121500000
      ) vv8
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_string vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 3000000000122
      ) vv9
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_numeric vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100007971500005
      ) vv10
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_numeric vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100038030700000
      ) vv11
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_numeric vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 3000000000103
      ) vv12
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_datetime vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 3000000000108
      ) vv13
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_numeric vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100000648000000
      ) vv14
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_numeric vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100004081600000
      ) vv15
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_numeric vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100004087100000
      ) vv16
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_datetime vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 9
      ) vv17
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_numeric vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100004060300000
      ) vv18

order by rw.id
4-5 сек как в MSSQL!!!
test_3.png
план
postgres_test_02.csv
postgres_test_02.csv
pg_test_zaytsev_02_2025_03_14
https://explain.tensor.ru/archive/explain/14275a45d875065152fa731c2f315dae:0:2025-03-14#explain

и план какой красивый!!!
test_4.png
-------------------------------------------------------------------------

в двух словах
- какая основная идея этого варианта в PostgreSQL? как получилось такое шикарное ускорение
- нужно ли добавлять 'синтаксический сахар' CTE (с материализацией или без) или какой другой для красоты
- нужно ли для данной структуры данных донастроить config posygresql - сейчас такой (немного памяти добавил по сравнению с коробочной настройкой)
postgresql.conf
postgresql.conf
...
Рейтинг: 0 / 0
14.03.2025, 11:29
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140365
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ef1 [игнорируется] 

еще картинка
test_5.png
...
Рейтинг: 0 / 0
14.03.2025, 12:06
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140366
ArtToms
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ArtToms
Участник
Добрый день.

Для меня такой результат ожидаемый, я таким способом в mssql часто пользуюсь, мне было интересно как это будет на PostgreSQL.
Собственно, идея простая, заменить внешние объединения на внутренние.
С внутренними проще сделать более оптимальный запрос.
Это хорошо для любого sql сервера, так что и на других должно работать быстрее.
В mssql аналог: cross apply.
Есть еще способ перевода на внутреннее и без cross apply, но так проще и красивее.
Если интересно, попробую рассказать, да и не у всех есть cross apply или такой аналог.

Спасибо за подробные результаты тестов.
...
Изменено: 14.03.2025, 16:09 - ArtToms
Рейтинг: 0 / 0
14.03.2025, 16:07
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140367
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ArtToms [игнорируется] 

Вам СПАСИБО!. Уже переписываю свое ядро/конфигурацию (~80 запросов)
...
Рейтинг: 0 / 0
14.03.2025, 16:45
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140368
ArtToms
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ArtToms
Участник
ef1 [игнорируется] 

Если будут еще интересные места, попробую помочь.
Мне возможно светит переход с mssql на PostgreSQL, потому такие задачки еще и полезны.
А на PostgreSQL точно переходите или если mssql поправите проблемы, то откажитесь от перехода?
К примеру, если база и нагрузка позволяет, можно использовать более поздние редакции бесплатной ExpressEdition.
Тогда получите новые фишки, сможете поднять уровень совместимости базы и будет меньше хлопот .

Удачи.
...
Изменено: 14.03.2025, 18:47 - ArtToms
Рейтинг: 0 / 0
14.03.2025, 18:46
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140374
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ArtToms [игнорируется] 

1. переходить будем точно, еще 2 года назад планировали, последней каплей стало то что наши win клиенты с выходом win11 отрубились от базы, пришлось остаться на win10 (microsoft планомерно отключает старые tls, драйвера odbs mssql и т.д.)(к счастью мы пофиксили и то и другое - через реестр и удаление всех sql драйверов win11 и установку нужного от 2005 mssql... но ведь они обязательно еще что нибудь подкинут)
2. на счет ms express - сразу как они включили поддержку linux - поставили 12й(или 14й - не помню) - но ограничения по железу в экспрессе = низкая скорость = отказались - но используем в качестве резерва - каждую ночь туда основной бекап восстанавливаем - типа на всякий случай
--------------
в итоге можно было бы успокоиться еще на пару лет но
3. политические разборки.. практически все уходят на открытые системы, и вендоры и юзеры (для меня например показатель переход ржд с oracle)
--------------
к счастью руководство понимает что в конторе должен быть свой бэк/локальная база в противовес облачным монстрам Битрикс24 (которые даже бекапы не отдают)) ), а раз так и денег на серверный софт нет = дорога одна = на postgresql на llinux - к сожалению в один конец - в общем нужно остаться на волне и желательно бесплатно - поэтому только вперед))
...
Изменено: 15.03.2025, 09:52 - ef1
Рейтинг: 0 / 0
15.03.2025, 09:48
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140378
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ArtToms [игнорируется] 
нет предела совершенству ))
пытался сократить текст запроса
Код: SQL
1.
2.
3.
4.
5.
6.
     cross join lateral (
      select max(vvv.value) as value
        from LSDBO.attrib_value av inner join LSDBO.value_numeric vvv on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100004087100000
      ) vv16
1. вынес чтение значения в отдельную функцию
Код: SQL
1.
  cross join lateral (select lsdbo.ric_get_string_atr(rw.id, 100004068400000) sValue) vv
Код: SQL
1.
2.
3.
4.
 SELECT coalesce(vv.value,'') || coalesce(vv.value1,'')
  FROM lsdbo.value_string vv left join lsdbo.attrib_value av on vv.id = av.Value_ID --and vv.attrib_id = p_idattr
where vv.attrib_id = p_idattr and
      av.object_id=  p_objid
план простой но запрос тормозной

2. делал через cte нематериализованный
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
 with
_value_attr as NOT MATERIALIZED(
  select
    av.Object_id as id,
       av.Attrib_ID as IdTypeAttr,
         coalesce(sv.value,'')||coalesce(sv.value1,'') as sValue,
         nv.value as nValue,
         dv.value as dValue
  from
    LSDBO.attrib_value as av left join
         LSDBO.value_string as sv on sv.id=av.Value_ID left join
         LSDBO.value_datetime as dv on dv.id=av.Value_ID left join
         LSDBO.value_numeric as nv on nv.id=av.Value_id

where
    av.Attrib_ID in(
      100004068400000,3000000000101,100003121500000,100004086600000,3000000000106,100033995800000
    )
)
Код: SQL
1.
2.
INNER JOIN  _value_attr va on va.IdTypeAttr=100004068400000 and va.id=rw.id
INNER JOIN  _value_attr va1 on va1.IdTypeAttr=3000000000101 and va1.id=rw.id
тоже тормоза и план кривой
3. в итоге поменял внутри inner на left
Код: SQL
1.
2.
3.
4.
5.
6.
       cross join lateral (
      select max(vvv.value) as value
        from LSDBO.value_string vvv left join LSDBO.attrib_value av on av.Value_ID = vvv.id
           where rw.id = av.Object_ID
             and av.Attrib_ID = 100004068400000
      ) vv
на тестовом запросе выиграл 30мс и план тот же красивый

единственное что не понял зачем max(vvv.value)... но без него результат короче получается по строкам

короче самый красивый план и скорость при запросе в лоб!)) без извращений
...
Рейтинг: 0 / 0
15.03.2025, 16:10
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140379
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ef1 [игнорируется] 

а вот еще интересный момент
идем по двум объектам = к первому подгоняем связанный
синтаксис mssql
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
explain analyze
 Select isobject_id,
         Clt_Create,
         Clt_Status,
     Clt_Form,
         Clt_Name,
         Clt_Curator,
         Clt_Manager
    From
    (
     Select distinct vv2.value as IdLstClt
       From lsdbo.object_reference rw left join lsdbo.object_type tw on rw.type_id = tw.id left join
            lsdbo.attrib_value av8 on rw.id = av8.object_id and av8.attrib_id = 3000000000082 left join
        lsdbo.value_datetime vv8 on av8.value_id = vv8.id and av8.attrib_id = 3000000000082 left join
        lsdbo.attrib_value av6 on rw.id = av6.object_id and av6.attrib_id = 100000019000005 left join
        lsdbo.value_numeric vv6 on av6.value_id = vv6.id and av6.attrib_id = 100000019000005 left join
        lsdbo.attrib_value av7 on rw.id = av7.object_id and av7.attrib_id = 100004060500000 left join
        lsdbo.value_numeric vv7 on av7.value_id = vv7.id and av7.attrib_id = 100004060500000 left join
        lsdbo.attrib_value av2 on rw.id = av2.object_id and av2.attrib_id = 100004060300000 left join
        lsdbo.value_numeric vv2 on av2.value_id = vv2.id and av2.attrib_id = 100004060300000
      Where (tw.mnemo = 'Lst')
                and (abs(vv6.value)=50)
                and (abs(vv7.value)>=28673)
                and (vv8.Value >='01.09.2023')
                and (vv8.Value <='01.01.2024')
 ) as tLstClt
left join
    (    --Получим атрибутивную информацию по найденным компаниям
         Select Coalesce(vv0.value,'Частное лицо') AS Clt_Status,
                Coalesce(vv1.value || ' ','') as Clt_Form,
        rw.description as Clt_Name,
        vv2.value AS Clt_Curator,
                coalesce(vv3.value,'') || coalesce(vv3.value1,'') as Clt_Manager,
                coalesce(vv4.value,'01.01.2006') AS Clt_Create,
        rw.id AS isobject_id
       From LSDBO.object_reference AS rw LEFT JOIN LSDBO.object_type AS tw ON rw.type_id = tw.id LEFT JOIN
        LSDBO.attrib_value AS av0 ON rw.id = av0.Object_ID AND av0.Attrib_ID = 100000016000000 LEFT JOIN
        LSDBO.value_string AS vv0 ON av0.Value_ID = vv0.id AND av0.Attrib_ID = 100000016000000 LEFT JOIN
        LSDBO.attrib_value AS av1 ON rw.id = av1.Object_ID AND av1.Attrib_ID = 3000000000002 LEFT JOIN
        LSDBO.value_string AS vv1 ON av1.Value_ID = vv1.id AND av1.Attrib_ID = 3000000000002 LEFT JOIN
            lsdbo.attrib_value av3 on rw.id=av3.object_id and av3.attrib_id=100004974000000 left join
            lsdbo.value_string vv3 on av3.value_id=vv3.id and av3.attrib_id=100004974000000 left join
        LSDBO.attrib_value AS av4 ON rw.id = av4.Object_ID AND av4.Attrib_ID = 8 LEFT JOIN
        LSDBO.value_datetime AS vv4 ON av4.Value_ID = vv4.id AND av4.Attrib_ID = 8 left join
        LSDBO.attrib_value AS av2 ON rw.id = av2.Object_ID AND av2.Attrib_ID = 3000000000122 LEFT JOIN
        LSDBO.value_string AS vv2 ON av2.Value_ID = vv2.id AND av2.Attrib_ID = 3000000000122
      Where tw.mnemo in('Dvl','Clt','Cnt')
         ) as tClt
on tLstClt.IdLstClt=tClt.isobject_id
cross inner join
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
explain analyze
  Select
    isobject_id,
      Clt_Create,
        Clt_Status,
    Clt_Form,
        Clt_Name,
        Clt_Curator,
        Clt_Manager
  From
    (
    Select
      distinct vv2.nValue as IdLstClt
    From
      lsdbo.object_reference rw left join lsdbo.object_type tw on rw.type_id = tw.id --left join
      cross join lateral (
        select max(dv.value) as dValue
            from LSDBO.value_datetime dv left join LSDBO.attrib_value av on av.Value_ID = dv.id
               where rw.id = av.Object_ID and av.Attrib_ID = 3000000000082
          ) vv8
      cross join lateral (
        select max(nv.value) as nValue
            from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
               where rw.id = av.Object_ID and av.Attrib_ID = 100000019000005
          ) vv6
      cross join lateral (
        select max(nv.value) as nValue
            from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
               where rw.id = av.Object_ID and av.Attrib_ID = 100004060500000
          ) vv7
      cross join lateral (
        select max(nv.value) as nValue
            from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
               where rw.id = av.Object_ID and av.Attrib_ID = 100004060300000
          ) vv2
    Where
      tw.mnemo = 'Lst'
      and (abs(vv6.nValue)=50)
            and (abs(vv7.nValue)>=28673)
      and vv8.dValue BETWEEN '01.09.2023' and '01.01.2024'
        ) as tLstClt
    left join
    (    --Получим атрибутивную информацию по найденным компаниям
    Select
      coalesce(vv0.sValue,'Частное лицо') AS Clt_Status,
      Coalesce(vv1.sValue || ' ','') as Clt_Form,
      rw.description as Clt_Name,
      vv2.sValue AS Clt_Curator,
            vv3.sValue as Clt_Manager,
            coalesce(vv4.dValue,'01.01.2006') AS Clt_Create,
      rw.id AS isobject_id
    From
      LSDBO.object_reference AS rw LEFT JOIN LSDBO.object_type AS tw ON rw.type_id = tw.id --LEFT JOIN
      cross join lateral (
        select max(sv.value) as sValue
            from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
               where rw.id = av.Object_ID and av.Attrib_ID = 100000016000000
          ) vv0
      cross join lateral (
        select max(sv.value) as sValue
            from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
               where rw.id = av.Object_ID and av.Attrib_ID = 3000000000002
          ) vv1
      cross join lateral (
        select max(sv.value) as sValue
            from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
               where rw.id = av.Object_ID and av.Attrib_ID = 100004974000000
          ) vv3
      cross join lateral (
        select max(dv.value) as dValue
            from LSDBO.value_datetime dv left join LSDBO.attrib_value av on av.Value_ID = dv.id
               where rw.id = av.Object_ID and av.Attrib_ID = 8
          ) vv4
      cross join lateral (
        select max(sv.value) as sValue
            from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
               where rw.id = av.Object_ID and av.Attrib_ID = 3000000000122
          ) vv2
    Where
      tw.mnemo in('Dvl','Clt','Cnt')
    ) as tClt
    on tLstClt.IdLstClt=tClt.isobject_id
первый - время планирования 30,8 мс + выполняется 7 сек и план и диаграмма нормальные https://explain.tensor.ru/archive/explain/3dc257a9cdbbd72b267e13d0ad574007:0:2025-03-15#schema
второй - время планирования 18 мс + выполнения 13 сек план и диаграмма тоже в порядке https://explain.tensor.ru/archive/explain/787fe39e71ec68a7709aaae6b4275c92:0:2025-03-15#schema

т.е когда к одному объекту подгоняем атрибуты - все ок, а к нескольким... не получается ускорится...
...
Рейтинг: 0 / 0
15.03.2025, 19:02
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140380
ArtToms
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ArtToms
Участник
Добрый день.

С max, cross join lateral гарантировано возвратит одну запись, даже если результаты не найдутся (подобно left).
Без него может сократиться число возвращаемых записей, похоже пробовали.
В 'том то и есть изюминка.
Пример, в таблице Table1 в поле id есть только положительные значения.
Запрос select id from Table1 where id < 0 не вернет ни одной записи
А select max(id) as id from Table1 where id < 0 вернет одну ЗАПИСЬ с пустым значением.
Применять такой способ, можно если ожидаем только одну запись для каждого id.
...
Изменено: 15.03.2025, 19:17 - ArtToms
Рейтинг: 0 / 0
15.03.2025, 19:07
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140393
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ArtToms [игнорируется] 

тему наверно закроем - каждый postgresql запрос (или его часть) нужно трассировать отдельно, хорошо у нас в базе аналитики нет ))) халява mssql кончилась))
...
Рейтинг: 0 / 0
17.03.2025, 11:16
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140394
ShIgor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ShIgor
Участник
ef1  17.03.2025, 11:16
[игнорируется]
40140393
халява mssql кончилась
никуда она не девалась и не денется. :)
остальное - страхи параноиков среди менеджеров, безопасников и иже с ними.
...
Рейтинг: 0 / 0
17.03.2025, 12:11
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140434
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ArtToms [игнорируется] 

вопросик, может подскажешь
вот три одинаковых выборки postgresql
вариант_1 каждый атрибут читается отдельно - по твоему варианту
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
explain analyze
  Select *
  From
    lsdbo.Ric_Get_Select(1,0,'PSft','%%','',0,'') rw
    cross join lateral (select max(nv.value) as nValue
             from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
             where rw.id = av.Object_ID and av.Attrib_ID = 100000019000005) vv
      cross join lateral (select max(nv.value) as nValue
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 100000648000000) vv5
      cross join lateral (select max(nv.value) as nValue
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 100004060300000) vv6
      cross join lateral (select max(nv.value) as nValue
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 100004081100000) vv8
    cross join lateral (select max(nv.value) as nValue
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 100004081200000) vv2
    cross join lateral (select max(nv.value) as nValue
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 100004081600000) vv0

      cross join lateral (select max(coalesce(sv.value,'') || coalesce(sv.value1,'')) as sValue
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 3000000000101) vv12
      cross join lateral (select max(sv.value) as sValue
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 3000000000122) vv3
      cross join lateral (select max(sv.value) as sValue
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 100004091100000) vv13
      cross join lateral (select max(sv.value) as sValue
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 100007986500005) vv4
      cross join lateral (select max(coalesce(sv.value,'') || coalesce(sv.value1,'')) as sValue
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 100037488300000) vv9

    cross join lateral (select max(dv.value) as dValue
          from LSDBO.value_datetime dv left join LSDBO.attrib_value av on av.Value_ID = dv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 8) vv1
    cross join lateral (select max(dv.value) as dValue
          from LSDBO.value_datetime dv left join LSDBO.attrib_value av on av.Value_ID = dv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 9) vv11
    cross join lateral (select max(dv.value) as dValue
          from LSDBO.value_datetime dv left join LSDBO.attrib_value av on av.Value_ID = dv.id
            where rw.id = av.Object_ID and av.Attrib_ID = 3000000000108) vv7
  where vv0.nvalue>0
этот самый быстрый но
много повторов
>> устраните повторное выполнение одинакового кода с помощью сохранения результата или совместного вычисления значений
>>сильно расходится плановая и фактическая статистика по таблице, стоит выполнить ее ANALYZE
"Planning Time: 12.753 ms"
"Execution Time: 15175.654 ms"
https://explain.tensor.ru/archive/explain/df6925c777c7935812897d4ee62218fa:0:2025-03-23#explain

по рекомендации тензора https://habr.com/ru/companies/tensor/articles/574330/ - убрал повторные чтения
вариант_2 left join все атрибуты читаются кучей и разворачиваются pivot
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
explain analyze
  select *
  From
    lsdbo.Ric_Get_Select(1,0,'PSft','%%','',0,'') rw
    left join lateral
    (
      select
        min(nv.value) FILTER(WHERE nv.Attrib_ID =  100000019000005) vv
           ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100000648000000) vv5
           ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004060300000) vv6
           ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081100000) vv8
           ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081200000) vv2
          ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081600000) vv0

        ,min(sv.value || coalesce(sv.value1,'')) FILTER(WHERE sv.Attrib_ID = 3000000000101) vv12
        ,min(sv.value) FILTER(WHERE sv.Attrib_ID = 3000000000122) vv3
        ,min(sv.value) FILTER(WHERE sv.Attrib_ID = 100004091100000) vv13
        ,min(sv.value) FILTER(WHERE sv.Attrib_ID = 100007986500005) vv4
        ,min(sv.value || coalesce(sv.value1,'')) FILTER(WHERE sv.Attrib_ID = 100037488300000) vv9

        ,min(dv.value) FILTER(WHERE dv.Attrib_ID = 8) vv1
        ,min(dv.value) FILTER(WHERE dv.Attrib_ID = 9) vv11
        ,min(dv.value) FILTER(WHERE dv.Attrib_ID = 3000000000108) vv7
      from
        LSDBO.attrib_value av left join
        LSDBO.value_numeric nv on av.Value_ID = nv.id left join
        LSDBO.value_string sv on av.Value_ID = sv.id left join
        LSDBO.value_datetime dv on av.Value_ID = dv.id
      where
        rw.id = av.Object_ID
        and av.Attrib_ID =any( values
          (100000019000005),(100004081600000),(8),(100004081200000),(3000000000122),(100007986500005),
          (100000648000000),(100004060300000),(3000000000108),(100004081100000),(100037488300000),(9),
          (3000000000101),(100004091100000)
          )
      --  LIMIT 1
      ) nv
        on true
  where vv0>0
этот медленней но
ни одного повтора но
>>сильно расходится плановая и фактическая статистика по таблице, стоит выполнить ее ANALYZE
"Planning Time: 1.804 ms"
"Execution Time: 19964.939 ms"
https://explain.tensor.ru/archive/explain/343bc8c6b72b3e6fba8077e240e3ff89:0:2025-03-23#explain
вариант_3 cross join все атрибуты читаются кучей и разворачиваются pivot
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
explain analyze
  select *
  From
    lsdbo.Ric_Get_Select(1,0,'PSft','%%','',0,'') rw
    cross join lateral
    (
      select
        min(nv.value) FILTER(WHERE nv.Attrib_ID =  100000019000005) vv
           ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100000648000000) vv5
           ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004060300000) vv6
           ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081100000) vv8
           ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081200000) vv2
          ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081600000) vv0

        ,min(sv.value || coalesce(sv.value1,'')) FILTER(WHERE sv.Attrib_ID = 3000000000101) vv12
        ,min(sv.value) FILTER(WHERE sv.Attrib_ID = 3000000000122) vv3
        ,min(sv.value) FILTER(WHERE sv.Attrib_ID = 100004091100000) vv13
        ,min(sv.value) FILTER(WHERE sv.Attrib_ID = 100007986500005) vv4
        ,min(sv.value || coalesce(sv.value1,'')) FILTER(WHERE sv.Attrib_ID = 100037488300000) vv9

        ,min(dv.value) FILTER(WHERE dv.Attrib_ID = 8) vv1
        ,min(dv.value) FILTER(WHERE dv.Attrib_ID = 9) vv11
        ,min(dv.value) FILTER(WHERE dv.Attrib_ID = 3000000000108) vv7
      from
        LSDBO.attrib_value av left join
        LSDBO.value_numeric nv on av.Value_ID = nv.id left join
        LSDBO.value_string sv on av.Value_ID = sv.id left join
        LSDBO.value_datetime dv on av.Value_ID = dv.id
      where
        rw.id = av.Object_ID
        and av.Attrib_ID =any( values
          (100000019000005),(100004081600000),(8),(100004081200000),(3000000000122),(100007986500005),
          (100000648000000),(100004060300000),(3000000000108),(100004081100000),(100037488300000),(9),
          (3000000000101),(100004091100000)
          )
      --  LIMIT 1
      ) nv
       -- on true
  where vv0>0
этот примерно такой же как предыдущий
ни одного повтора но
>>сильно расходится плановая и фактическая статистика по таблице, стоит выполнить ее ANALYZE
"Planning Time: 1.906 ms"
"Execution Time: 20397.222 ms"
https://explain.tensor.ru/archive/explain/7a09702799792190105d94db06ddbd38:0:2025-03-23#explain

вопрос - вариант 1 самый быстрый потому что планировщик ошибся на ~1000, остальные тормозят т.к. ошибка уже на ~10000 - но при этом они чище...
vaсuum и analyze таблиц из выборки не помогли (https://habr.com/ru/companies/tensor/articles/479656/)

типа тупик? в данном конкретном случае - варианты 2 и 3 проще и планируются на порядок быстрее но выполняются... - не хватает железа? или настройки какой то
вариант 1 грязноват и план длиннее - но уже пофиг на ресурсы....
не знаю в какую сторону и смотреть
...
типа главное план сделать красивым и смотреть конфиг сервера
или планом не заморачиваться и в конфиг не закапываться
...
...
Изменено: 23.03.2025, 18:35 - ef1
Рейтинг: 0 / 0
23.03.2025, 18:26
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140442
Дет-Пердет
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дет-Пердет
Участник
ef1  12.02.2025, 12:19
[игнорируется]
40139989
mssql2005
Всем привет!
готовим udf mssql базы для переезда на postgresql (санкции да и вообще все морально устарело, win11 отрубила поддержку tls1.x, 'старого' sql драйвера и т.д. - 20ти летний серверный софт 'умирает' понемногу... денег на обновление не выделяют...)

вопрос по оптимизации select - одним запросом выбираем id объектов + 20 (однотипных) left join подгоняем к ним значения атрибутов
mssql справляется а postgresql (на той же базе/миграция на том же запросе) 'умирает'(резко тормозит) если left join более 8~10
оставив пока postgresql в стороне просто хочу попытаться ускорить выборку на mssql например через cte (поскольку left join берет инфу из одних и тех же 4х таблиц многократно)
...
структура бд от вендора (не можем менять), используем базу лет 20ть проблем нет - работает, так что вопрос чисто по ускорению запроса
1. есть 3 таблицы значений атрибутов (id атрибута, значение атрибута и т.д.) для хранения строк, чисел и дат
2. есть одна таблица связи атрибутов с объектами (id объекта, id атрибута и т.д.)
в left join я просто многократно обращаюсь к этим таблицам чтобы по id объекта (из основного запроса по выборке объектов) и id атрибута получить значение атрибута
вот что мы использовали последние 20 лет
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
select
  rw.id, vv14.value,  vv1.value, vv3.value, vv4.value, vv15.value, vv5.value,vv12.value, vv10.value, vv11.value,vv16.value,vv6.value, vv7.value,vv13.value, vv18.value
-- vv14.value, rw.cd, vv.value, rw.description, vv2.value, vv1.value, vv3.value+' ', vv4.value, isnull(vv15.value,0), isnull(vv5.value,0) AS pPrice, isnull(vv12.value,0), isnull(vv10.value,0) as pDiscount01, isnull(vv11.value,0),isnull(vv16.value,0),vv6.value AS pCurrency, vv7.value AS pStatus,isnull(vv13.value, vv17.value),(select LSDBO.Ric_Get_ShortFIO(vv9.value)), vv18.value
From lsdbo.Ric_Get_Select(2,100000038900005,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw left join
--select * From lsdbo.Ric_Get_Select(2,4000000015666,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw left join
        LSDBO.attrib_value AS av ON rw.id = av.Object_ID AND av.Attrib_ID = 100004068400000 LEFT JOIN
        LSDBO.value_string AS vv ON av.Value_ID = vv.id AND av.Attrib_ID = 100004068400000 left join
        LSDBO.attrib_value AS av1 ON rw.id = av1.Object_ID AND av1.Attrib_ID = 3000000000101 LEFT JOIN
        LSDBO.value_string AS vv1 ON av1.Value_ID = vv1.id AND av1.Attrib_ID = 3000000000101 LEFT JOIN
        LSDBO.attrib_value AS av2 ON rw.id = av2.Object_ID AND av2.Attrib_ID = 100003121500000 LEFT JOIN
        LSDBO.value_string AS vv2 ON av2.Value_ID = vv2.id AND av2.Attrib_ID = 100003121500000 LEFT JOIN
        LSDBO.attrib_value AS av3 ON rw.id = av3.Object_ID AND av3.Attrib_ID = 100004086600000 LEFT JOIN
        LSDBO.value_string AS vv3 ON av3.Value_ID = vv3.id AND av3.Attrib_ID = 100004086600000 left join
        LSDBO.attrib_value AS av4 ON rw.id = av4.Object_ID AND av4.Attrib_ID = 3000000000106 LEFT JOIN
        LSDBO.value_datetime AS vv4 ON av4.Value_ID = vv4.id AND av4.Attrib_ID = 3000000000106 LEFT JOIN
        LSDBO.attrib_value AS av5 ON rw.id = av5.Object_ID AND av5.Attrib_ID = 100033995800000 LEFT JOIN
        LSDBO.value_numeric AS vv5 ON av5.Value_ID = vv5.id AND av5.Attrib_ID = 100033995800000 left join
        LSDBO.attrib_value AS av6 ON rw.id = av6.Object_ID AND av6.Attrib_ID = 100005428000000 LEFT JOIN
        LSDBO.value_string AS vv6 ON av6.Value_ID = vv6.id AND av6.Attrib_ID = 100005428000000 LEFT JOIN
        LSDBO.attrib_value AS av7 ON rw.id = av7.Object_ID AND av7.Attrib_ID = 100007986500005 LEFT JOIN
        LSDBO.value_string AS vv7 ON av7.Value_ID = vv7.id AND av7.Attrib_ID = 100007986500005 left join
        LSDBO.attrib_value AS av8 ON rw.id = av8.Object_ID AND av8.Attrib_ID = 100003121500000 LEFT JOIN
        LSDBO.value_string AS vv8 ON av8.Value_ID = vv8.id AND av8.Attrib_ID = 100003121500000 left join
        LSDBO.attrib_value AS av9 ON rw.id = av9.Object_ID AND av9.Attrib_ID = 3000000000122 LEFT JOIN
        LSDBO.value_string AS vv9 ON av9.Value_ID = vv9.id AND av9.Attrib_ID = 3000000000122 LEFT JOIN
        LSDBO.attrib_value AS av10 ON rw.id = av10.Object_ID AND av10.Attrib_ID = 100007971500005 LEFT JOIN
        LSDBO.value_numeric AS vv10 ON av10.Value_ID = vv10.id AND av10.Attrib_ID = 100007971500005 LEFT JOIN
        LSDBO.attrib_value AS av11 ON rw.id = av11.Object_ID AND av11.Attrib_ID = 100038030700000 LEFT JOIN
        LSDBO.value_numeric AS vv11 ON av11.Value_ID = vv11.id AND av11.Attrib_ID = 100038030700000 LEFT JOIN
        LSDBO.attrib_value AS av12 ON rw.id = av12.Object_ID AND av12.Attrib_ID = 3000000000103 LEFT JOIN
        LSDBO.value_numeric AS vv12 ON av12.Value_ID = vv12.id AND av12.Attrib_ID = 3000000000103  LEFT JOIN
        lsdbo.attrib_value av13 on rw.id = av13.object_id and av13.attrib_id = 3000000000108 left join
        lsdbo.value_datetime vv13 on av13.value_id = vv13.id and av13.attrib_id = 3000000000108 left join
        LSDBO.attrib_value AS av14 ON rw.id = av14.Object_ID AND av14.Attrib_ID = 100000648000000 LEFT JOIN
        LSDBO.value_numeric AS vv14 ON av14.Value_ID = vv14.id AND av14.Attrib_ID = 100000648000000   LEFT JOIN
        LSDBO.attrib_value AS av15 ON rw.id = av15.Object_ID AND av15.Attrib_ID = 100004081600000 LEFT JOIN
        LSDBO.value_numeric AS vv15 ON av15.Value_ID = vv15.id AND av15.Attrib_ID = 100004081600000 LEFT JOIN
        LSDBO.attrib_value AS av16 ON rw.id = av16.Object_ID AND av16.Attrib_ID = 100004087100000 LEFT JOIN
        LSDBO.value_numeric AS vv16 ON av16.Value_ID = vv16.id AND av16.Attrib_ID = 100004087100000 LEFT JOIN
        LSDBO.attrib_value AS av17 ON rw.id = av17.Object_ID AND av17.Attrib_ID = 9 LEFT JOIN
        LSDBO.value_datetime AS vv17 ON av17.Value_ID = vv17.id AND av17.Attrib_ID = 9 LEFT JOIN
        LSDBO.attrib_value AS av18 ON rw.id = av18.Object_ID AND av18.Attrib_ID = 100004060300000 LEFT JOIN
        LSDBO.value_numeric AS vv18 ON av18.Value_ID = vv18.id AND av18.Attrib_ID = 100004060300000
вот тоже самое через cte
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
with
_string_value as (--NOT MATERIALIZED(
select av.Object_id as id,
     av.Attrib_ID as IdTypeAttr,
       coalesce(sv.value,'')+coalesce(sv.value1,'') as sValue
 from  LSDBO.attrib_value as av left join
       LSDBO.value_string as sv on sv.id=av.Value_ID
),
_numeric_value as (--NOT MATERIALIZED(
select av.Object_id as id,
     av.Attrib_ID as IdTypeAttr,
       nv.value as nValue
 from  LSDBO.attrib_value as av left join
       LSDBO.value_numeric as nv on nv.id=av.Value_id
),
_datetime_value as (--NOT MATERIALIZED(
select av.Object_id as id,
     av.Attrib_ID as IdTypeAttr,
       dv.value as dValue
 from  LSDBO.attrib_value as av left join
       LSDBO.value_datetime as dv on dv.id=av.Value_ID
)
select
  rw.id, av14.nValue, av1.sValue, av3.sValue, av4.dValue, av15.nValue, av5.nValue, av12.nValue, av10.nValue, av11.nValue,av16.nValue,av6.sValue, av7.sValue,av13.dValue, av18.nValue
--  av14.nValue, rw.cd, av.sValue, rw.description, av2.sValue, av1.sValue, av3.sValue+' ', av4.dValue, isnull(av15.nValue,0), isnull(av5.nValue,0), isnull(av12.nValue,0), isnull(av10.nValue,0), isnull(av11.nValue,0),isnull(av16.nValue,0),av6.sValue, av7.sValue AS pStatus,isnull(av13.dValue, av17.dValue),(select LSDBO.Ric_Get_ShortFIO(av9.sValue)), av18.nValue
 From lsdbo.Ric_Get_Select(2,100000038900005,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw left join
--select * From lsdbo.Ric_Get_Select(2,4000000015666,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw left join
_string_value AS av ON av.id= rw.id and av.idTypeAttr= 100004068400000 left join
_string_value AS av1 ON av1.id= rw.id and av1.idTypeAttr= 3000000000101 LEFT JOIN
_string_value AS av2 ON av2.id= rw.id and av2.idTypeAttr= 100003121500000 LEFT JOIN
_string_value AS av3 ON av3.id= rw.id and av3.idTypeAttr= 100004086600000 left join
_datetime_value AS av4 ON av4.id= rw.id and av4.idTypeAttr= 3000000000106 LEFT JOIN
_numeric_value AS av5 ON av5.id= rw.id and av5.idTypeAttr= 100033995800000 left join
_string_value AS av6 ON av6.id= rw.id and av6.idTypeAttr= 100005428000000 LEFT JOIN
_string_value AS av7 ON av7.id= rw.id and av7.idTypeAttr= 100007986500005 left join
_string_value AS av8 ON av8.id= rw.id and av8.idTypeAttr= 100003121500000 left join
_string_value AS av9 ON av9.id= rw.id and av9.idTypeAttr= 3000000000122 LEFT JOIN
_numeric_value AS av10 ON av10.id= rw.id and av10.idTypeAttr= 100007971500005 LEFT JOIN
_numeric_value AS av11 ON av11.id= rw.id and av11.idTypeAttr= 100038030700000 LEFT JOIN
_numeric_value AS av12 ON av12.id= rw.id and av12.idTypeAttr= 3000000000103  LEFT JOIN
_datetime_value AS av13 ON av13.id= rw.id and av13.idTypeAttr= 3000000000108 left join
_numeric_value AS av14 ON av14.id= rw.id and av14.idTypeAttr= 100000648000000   LEFT JOIN
_numeric_value AS av15 ON av15.id= rw.id and av15.idTypeAttr= 100004081600000 LEFT JOIN
_numeric_value AS av16 ON av16.id= rw.id and av16.idTypeAttr= 100004087100000 LEFT JOIN
_datetime_value AS av17 ON av17.id= rw.id and av17.idTypeAttr= 9 LEFT JOIN
_numeric_value AS av18 ON av18.id= rw.id and av18.idTypeAttr= 100004060300000
ответ один и тот же, выглядит примерно так...
test1.png
статистика без cte
without_cte.png
статистика с cte
with_cte.png
здесь на выборке ~7200 строк cte 'визуально по ощущениям' быстрее, по цифрам примерно одинаково, но на меньшем количестве left join и глубине выборки cte однозначно быстрее...

вопрос чисто эмпирический - можно ли как то ускорить запрос многократно использующий в left join одни и те-же таблицы (в данном случае) каким либо другим подходом к выборке? или это все упирается в железо сервера?

ps
на mssql сейчас отдельная виртуалка winserver2003+mssql2005 (здесь по скорости все более менее, количество left join глотает нормально), на postgres просто виртуалка win8.1+posgresql16 (здесь все это вообще выглядит 'мертвым' при большом количестве left join)
Pivot или group by спасут отца русской демократии
...
Рейтинг: 0 / 0
25.03.2025, 09:19
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140457
Alex_Toms
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Toms
Участник
ef1 [игнорируется] 

Восстановил прежний логин, ArtToms был временный.

Пытаюсь понять зачем в первом запросе внутреннее соединение заменили на left join LSDBO.attrib_value?
Я специально обернул внешние соединения в cross join lateral, заменив на внутренние. Так возросла скорость и "выправился" план запросов.

По поводу повторов. Проанализировал исходный запрос, можно попробовать вариант с временными таблицами.

Первый запрос во временную таблицу. Отбираем записи из attrib_value, с av.attrib_id перечисленными в исходном запросе.
Условие where av.attrib_id in (.....), я привел для примера, лучше сделать join из значений.
Код: SQL
1.
2.
3.
4.
select rw.id, av.attrib_id, av.value_id
From lsdbo.Ric_Get_Select(2,100000038900005,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw
 inner join LSDBO.attrib_value AS av ON rw.id = av.Object_ID
where av.attrib_id in (.....)
Далее эти данные можно связать с LSDBO.value_string, LSDBO.value_datetime и LSDBO.value_numeric тремя запросами в конечный запрос.

К таблицам БД, будет всего 4 запроса, остальное с временными.
Вместо временных можно попробовать cte
Написал очень упрощенно, если интересно, поясню подробнее, можно пообщаться и голосом.
...
Изменено: 27.03.2025, 17:28 - Alex_Toms
Рейтинг: 0 / 0
27.03.2025, 17:11
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140459
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
Alex_Toms [игнорируется] 

честно говоря я уже запутался окончательно, но вышел на шаблон который наверно буду использовать на все про все
(уже очень много времени потерял... на все это)
голосом созвонится - не поможет )), бекап базы дать postgresql тоже не вариант - может не развернутся (я на 16 и 17 версии тестирую) проще удаленно подключится (в любое время в онлайне, просто время согласовать)

типовой шаблон основной объект+ связанные + все их атрибуты (выборка Total rows: 39071хcols: 57 Query complete 00:01:46.296 (это без внешних функций - с ними наверно минут на 50))

- в cte набираю объекты (id и фильтрация)
- в cte добавляю вызовы внешних функций - это убивает все (наверно вызовы на клиента перенесу...)
- при чтении cte подгоняю к этим id значения атрибутов (и вызов результатов внешних функций...)
итоговый шаблон
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
265.
266.
267.
268.
269.
270.
271.
272.
273.
274.
275.
276.
277.
278.
279.
280.
281.
282.
283.
284.
285.
286.
287.
288.
289.
290.
291.
292.
293.
294.
295.
296.
297.
298.
299.
300.
301.
302.
303.
304.
305.
306.
307.
308.
309.
310.
311.
312.
313.
314.
315.
316.
317.
--explain analyze
with
-- основная выборка + id + фильтрация + поля для работы с внешними функциями
_base as MATERIALIZED (
  Select *
    From
    (
    Select
--объекты от table_prod  (основной объект)
      rw.id as prod_id,
          coalesce(vv5,0) AS prod_del_id,
          vv2 as prod_dvl_id,
           vv6 as prod_clt_id,
          vv8 as prod_cnt_id,
      rw.description as prod_description
    From
      lsdbo.Ric_Get_Select(1,0,'PSft','%%','',0,'') rw
      left join lateral
      (
        select
            min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081600000) vv0
             ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100000648000000) vv5
                ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081200000) vv2
             ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004060300000) vv6
             ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081100000) vv8
        from
          LSDBO.attrib_value av left join
          LSDBO.value_numeric nv on av.Value_ID = nv.id
        where
          rw.id = av.Object_ID
        ) nv
          on true
    where vv0>0
    --limit 10--000--000 --speed test
    ) as table_prod

---------------------------------------------------------
--вспомогательные объекты
---------------------------------------------------------

    left join
    (
    SELECT
--объекты от table_dvl
          rw.id as dvl_id,
      rw.description AS dvl_description
    FROM
      LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
    WHERE
      tw.mnemo ='Dvl'
    ) as table_dvl
    on (table_prod.prod_dvl_id = table_dvl.dvl_id)

---------------------------------------------------------
    left join
    (
    Select
--объекты от table_del
        rw.id AS del_id,
      rw.description AS del_description
    From
      LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
    Where
      tw.mnemo='Del'
    ) as table_del
    on (table_prod.prod_del_id =table_del.del_id)

---------------------------------------------------------
    left join
    (
    Select
--объекты от table_clt
        rw.id AS clt_id,
      rw.description AS clt_description
    From
      LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
    Where
      tw.mnemo=any(values ('Dvl'),('Clt'),('Cnt'))
    ) as table_clt
    on (table_prod.prod_clt_id =table_clt.clt_id)

---------------------------------------------------------
    left join
    (
    Select
--объекты от table_cnt
        rw.id AS cnt_Id,
      rw.description AS cnt_description
    From
      LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
    Where
      tw.mnemo = 'Cnt'
    )as table_cnt
    on (table_prod.prod_cnt_id =table_cnt.cnt_Id)
),
--внешние функции для объектов clt
_clt_ext as MATERIALIZED (
  select
    DISTINCT ON (prod_clt_id) prod_clt_id as table_obj_id
    ,(Select lsdbo.Ric_Get_MarketingActionsAndSales(prod_clt_id::numeric,12)) as Clt_Block
    ,(select LSDBO.Ric_Get_DealsFirstSaleDate(prod_clt_id::numeric)) as Clt_dFirstSale
    ,(select LSDBO.Ric_Get_OccurrenceSource(prod_clt_id::numeric)) as Req_ist_CRM
    ,(select LSDBO.Ric_Get_DealsAllSaleCount(prod_clt_id::numeric)) as Clt_cAllSale
  from _base
  where prod_clt_id>0
)

  select
    b.*
  --атрибуты объектов
    ,vv,vv5,vv6,vv8,vv2,vv0
    ,vv12,vv3,vv13,vv4,vv9
    ,vv1,vv11,vv7

    ,vv_,vv3_,vv6_,vv8_
    ,vv0_,vv1_,vv2_

    ,vv5__
    ,vv0__,vv1__,vv3__,vv2__,vv6__,vv7__

    ,vv___,vv1___,vv2___,vv4___,vv5___,vv6___,vv7___,vv8___,vv9___,vv10___,vv11___,vv14___,vv15___,vv16___,vv17___

  --внешние функции
  --  ,ce.*

  from
--читаем подготовленные объекты
    _base b
--добиваем их справа атрибутами

--атрибуты от продуктов (b.prod_id)
    cross join lateral (
          select max(nv.value) as value
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 100000019000005) vv
    cross join lateral (
          select max(nv.value) as value
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 100000648000000) vv5
    cross join lateral (
          select max(nv.value) as value
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 100004060300000) vv6
    cross join lateral (
          select max(nv.value) as value
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 100004081100000) vv8
    cross join lateral (
          select max(nv.value) as value
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 100004081200000) vv2
    cross join lateral (
          select max(nv.value) as value
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 100004081600000) vv0

    cross join lateral (
          select max(sv.value || coalesce(sv.value1,'')) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 3000000000101) vv12
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 3000000000122) vv3
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 100004091100000) vv13
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 100007986500005) vv4
    cross join lateral (
          select max(sv.value || coalesce(sv.value1,'')) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 100037488300000) vv9

    cross join lateral (
          select max(dv.value) as value
          from LSDBO.value_datetime dv left join LSDBO.attrib_value av on av.Value_ID = dv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 8) vv1
    cross join lateral (
          select max(dv.value) as value
          from LSDBO.value_datetime dv left join LSDBO.attrib_value av on av.Value_ID = dv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 9) vv11
    cross join lateral (
          select max(dv.value) as value
          from LSDBO.value_datetime dv left join LSDBO.attrib_value av on av.Value_ID = dv.id
             where b.prod_id = av.Object_ID and av.Attrib_ID = 3000000000108) vv7

--атрибуты от сделок (b.del_id)
    cross join lateral (
          select max(nv.value) as value
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
             where b.del_id = av.Object_ID and av.Attrib_ID = 100000019000005) vv_
    cross join lateral (
          select max(nv.value) as value
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
             where b.del_id = av.Object_ID and av.Attrib_ID = 100004060200000) vv3_
    cross join lateral (
          select max(nv.value) as value
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
             where b.del_id = av.Object_ID and av.Attrib_ID = 100004087100000) vv6_
    cross join lateral (
          select max(nv.value) as value
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
             where b.del_id = av.Object_ID and av.Attrib_ID = 100004087200000) vv8_

    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.del_id = av.Object_ID and av.Attrib_ID = 3000000000105) vv0_
    cross join lateral (
          select max(sv.value || coalesce(sv.value1,'')) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.del_id = av.Object_ID and av.Attrib_ID = 100004086600000) vv1_

    cross join lateral (
          select max(dv.value) as value
          from LSDBO.value_datetime dv left join LSDBO.attrib_value av on av.Value_ID = dv.id
             where b.del_id = av.Object_ID and av.Attrib_ID = 3000000000106) vv2_

--атрибуты от компаний (b.clt_id)
    cross join lateral (
          select max(nv.value) as value
          from LSDBO.value_numeric nv left join LSDBO.attrib_value av on av.Value_ID = nv.id
             where b.clt_id = av.Object_ID and av.Attrib_ID = 100004087100000) vv5__

    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.clt_id = av.Object_ID and av.Attrib_ID = 100000016000000) vv0__
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.clt_id = av.Object_ID and av.Attrib_ID = 3000000000002) vv1__
    cross join lateral (
          select max(sv.value || coalesce(sv.value1,'')) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.clt_id = av.Object_ID and av.Attrib_ID = 100004974000000) vv3__
    cross join lateral (
          select max(sv.value || coalesce(sv.value1,'')) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.clt_id = av.Object_ID and av.Attrib_ID = 100003121500000) vv2__
    cross join lateral (
          select max(sv.value || coalesce(sv.value1,'')) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.clt_id = av.Object_ID and av.Attrib_ID = 3000000000004) vv6__
    cross join lateral (
          select max(sv.value || coalesce(sv.value1,'')) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.clt_id = av.Object_ID and av.Attrib_ID = 100004086100000) vv7__

--атрибуты от контактов (b.cnt_Id)
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000086) vv___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000085) vv1___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 100004086400000) vv2___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000021) vv4___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000024) vv5___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000023) vv6___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000025) vv7___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000041) vv8___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000042) vv9___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000043) vv10___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000062) vv11___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000022) vv14___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000087) vv15___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 100004975500000) vv16___
    cross join lateral (
          select max(sv.value) as value
          from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
             where b.cnt_Id = av.Object_ID and av.Attrib_ID = 3000000000061) vv17___

--добиваем данными от внешних функций (здесь теряем громадное количество времени на внешних запросах)
  --  left join _clt_ext ce on ce.table_obj_id=b.clt_id
...
Рейтинг: 0 / 0
28.03.2025, 11:35
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140466
Alex_Toms
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Toms
Участник
ef1 [игнорируется] 

Добрый день.

это без внешних функций - с ними наверно минут на 50
Что это?

Сделал вариант первого запроса, сгруппировал атрибуты, получилось три запроса к базе.
Интересно попробовать на реальных данных. Время выполнения и правильно ли выводит данные?
Спойлер
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
select *
  from Ric_Get_Select (2,100000038900005,'PSft, PHrd, PExp, PSrv, pPls','','',0,'') rw
 cross join lateral (select max(case when av.Attrib_ID = 100004068400000 then v.value end) as ds100004068400000
                          , max(case when av.Attrib_ID = 3000000000101   then v.value end) as ds3000000000101
                          , max(case when av.Attrib_ID = 100003121500000 then v.value end) as ds100003121500000
                          , max(case when av.Attrib_ID = 100004086600000 then v.value end) as ds100004086600000
                          , max(case when av.Attrib_ID = 100005428000000 then v.value end) as ds100005428000000
                          , max(case when av.Attrib_ID = 100007986500005 then v.value end) as ds100007986500005
                          , max(case when av.Attrib_ID = 100003121500000 then v.value end) as ds100003121500000
                          , max(case when av.Attrib_ID = 3000000000122   then v.value end) as ds3000000000122
                       from attrib_value av
                      inner join value_string v on av.Value_ID = v.id
                      where rw.id = av.Object_ID
                        and av.Attrib_ID in (100004068400000,3000000000101,100003121500000,100004086600000,100005428000000,100007986500005,100003121500000,3000000000122)) vs
 cross join lateral (select max(case when av.Attrib_ID = 3000000000106 then v.value end) as dv3000000000106
                          , max(case when av.Attrib_ID = 3000000000108 then v.value end) as dv3000000000108
                          , max(case when av.Attrib_ID =             9 then v.value end) as dv9
                       from attrib_value av
                      inner join value_datetime v on av.Value_ID = v.id
                      where rw.id = av.Object_ID
                        and av.Attrib_ID in (3000000000106,3000000000108,9)) vd
 cross join lateral (select max(case when av.Attrib_ID = 100033995800000 then v.value end) as nv100033995800000
                          , max(case when av.Attrib_ID = 100007971500005 then v.value end) as nv100007971500005
                          , max(case when av.Attrib_ID = 100038030700000 then v.value end) as nv100038030700000
                          , max(case when av.Attrib_ID = 3000000000103   then v.value end) as nv3000000000103
                          , max(case when av.Attrib_ID = 100000648000000 then v.value end) as nv100000648000000
                          , max(case when av.Attrib_ID = 100004081600000 then v.value end) as nv100004081600000
                          , max(case when av.Attrib_ID = 100004087100000 then v.value end) as nv100004087100000
                          , max(case when av.Attrib_ID = 100004060300000 then v.value end) as nv100004060300000
                       from attrib_value av
                      inner join value_numeric v on av.Value_ID = v.id
                      where rw.id = av.Object_ID
                        and av.Attrib_ID in (100033995800000,100007971500005,100038030700000,3000000000103,100000648000000,100004081600000,100004087100000,100004060300000)) vn
...
Изменено: 30.03.2025, 10:22 - Alex_Toms
Рейтинг: 0 / 0
30.03.2025, 10:20
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140478
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
Alex_Toms [игнорируется] 

!!!круто, да чуть быстрее и план чистый (без повторов) как с pivot СПАСИБО.
сейчас с этим типом запроса объектов одного типа и их атрибутами - проблем практически не осталось
(ps
1. from attrib_value av inner join value_string v менял на from value_string v left join attrib_value av поскольку attrib_value очень большая ~9 000 000 строк а таблицы дат чисел и строк значительно короче.. типа гонки за микросекундами
2. >>это без внешних функций - с ними наверно минут на 50 - это в результатах итоговой select выборки - читаю вспомогательные данные из udf - подзапросы короче - в mssql теже тормоза - мои проблемы
)

сейчас основная засада когда делаю запрос по нескольким объектам через left join с подгонкой атрибутов к ним
в итоге в двух словах
1. если сначала собрать все id разных типов объектов в одном cte и дальше при его чтении бомбить таблицы атрибутов = более менее быстро
Спойлер
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
--explain analyze
with
-- основная выборка + id + фильтрация + поля для работы с внешними функциями
_base as not MATERIALIZED (
  Select *
    From
    (
    Select
--объекты от table_prod  (основной объект)
      rw.id as prod_id,
          coalesce(vv5,0) AS prod_del_id,
          vv2 as prod_dvl_id,
           vv6 as prod_clt_id,
          vv8 as prod_cnt_id,
      rw.description as prod_description
    From
      lsdbo.Ric_Get_Select(1,0,'PSft','%%','',0,'') rw
      left join lateral
      (
        select
            min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081600000) vv0
             ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100000648000000) vv5
                ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081200000) vv2
             ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004060300000) vv6
             ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081100000) vv8
        from
          LSDBO.attrib_value av left join
          LSDBO.value_numeric nv on av.Value_ID = nv.id
        where
          rw.id = av.Object_ID
        ) nv
          on true
    where vv0>0
    --limit 10--000--000 --speed test
    ) as table_prod

---------------------------------------------------------
--вспомогательные объекты
---------------------------------------------------------

    left join
    (
    SELECT
--объекты от table_dvl
          rw.id as dvl_id,
      rw.description AS dvl_description
    FROM
      LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
    WHERE
      tw.mnemo ='Dvl'
    ) as table_dvl
    on (table_prod.prod_dvl_id = table_dvl.dvl_id)

---------------------------------------------------------
    left join
    (
    Select
--объекты от table_del
        rw.id AS del_id,
      rw.description AS del_description
    From
      LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
    Where
      tw.mnemo='Del'
    ) as table_del
    on (table_prod.prod_del_id =table_del.del_id)

---------------------------------------------------------
    left join
    (
    Select
--объекты от table_clt
        rw.id AS clt_id,
      rw.description AS clt_description
    From
      LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
    Where
      tw.mnemo=any(values ('Dvl'),('Clt'),('Cnt'))
    ) as table_clt
    on (table_prod.prod_clt_id =table_clt.clt_id)

---------------------------------------------------------
    left join
    (
    Select
--объекты от table_cnt
        rw.id AS cnt_Id,
      rw.description AS cnt_description
    From
      LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
    Where
      tw.mnemo = 'Cnt'
    )as table_cnt
    on (table_prod.prod_cnt_id =table_cnt.cnt_Id)
),
--внешние функции для объектов clt
_clt_ext as MATERIALIZED (
  select
    DISTINCT ON (prod_clt_id) prod_clt_id as table_obj_id
    ,(Select lsdbo.Ric_Get_MarketingActionsAndSales(prod_clt_id::numeric,12)) as Clt_Block
    ,(select LSDBO.Ric_Get_DealsFirstSaleDate(prod_clt_id::numeric)) as Clt_dFirstSale
    ,(select LSDBO.Ric_Get_OccurrenceSource(prod_clt_id::numeric)) as Req_ist_CRM
    ,(select LSDBO.Ric_Get_DealsAllSaleCount(prod_clt_id::numeric)) as Clt_cAllSale
  from _base
  where prod_clt_id>0
)

  select
    b.*
  --атрибуты объектов
    ,vv,vv5,vv6,vv8,vv2,vv0
    ,vv12,vv3,vv13,vv4,vv9
    ,vv1,vv11,vv7

    ,vv_,vv3_,vv6_,vv8_
    ,vv0_,vv1_,vv2_

    ,vv5__
    ,vv0__,vv1__,vv3__,vv2__,vv6__,vv7__

    ,vv___,vv1___,vv2___,vv4___,vv5___,vv6___,vv7___,vv8___,vv9___,vv10___,vv11___,vv14___,vv15___,vv16___,vv17___

  --внешние функции
  --  ,ce.*

  from
--читаем подготовленные объекты
    _base b
--добиваем их справа атрибутами

--атрибуты от продуктов (b.prod_id)
  cross join lateral (
    select
      max(case when av.Attrib_ID = 100000019000005 then v.value end) as vv
      ,max(case when av.Attrib_ID = 100000648000000 then v.value end) as vv5
      ,max(case when av.Attrib_ID = 100004060300000 then v.value end) as vv6
      ,max(case when av.Attrib_ID = 100004081100000 then v.value end) as vv8
      ,max(case when av.Attrib_ID = 100004081200000 then v.value end) as vv2
      ,max(case when av.Attrib_ID = 100004081600000 then v.value end) as vv0
    from attrib_value av
          inner join value_numeric v on av.Value_ID = v.id
    where b.prod_id = av.Object_ID
      and av.Attrib_ID =any (values (100000019000005),(100000648000000),(100004060300000),(100004081100000),(100004081200000),(100004081600000))
  )vn
  cross join lateral (
    select
      max(case when av.Attrib_ID = 3000000000101 then v.value end) as vv12
      ,max(case when av.Attrib_ID = 3000000000122 then v.value end) as vv3
      ,max(case when av.Attrib_ID = 100004091100000 then v.value end) as vv13
      ,max(case when av.Attrib_ID = 100007986500005 then v.value end) as vv4
      ,max(case when av.Attrib_ID = 100037488300000 then v.value end) as vv9
    from attrib_value av
          inner join value_string v on av.Value_ID = v.id
    where b.prod_id = av.Object_ID
      and av.Attrib_ID =any (values (3000000000101),(3000000000122),(100004091100000),(100007986500005),(100037488300000))
  )vs
  cross join lateral (
    select
      max(case when av.Attrib_ID = 8 then v.value end) as vv1
      ,max(case when av.Attrib_ID = 9 then v.value end) as vv11
      ,max(case when av.Attrib_ID = 3000000000108 then v.value end) as vv7
    from attrib_value av
          inner join value_datetime v on av.Value_ID = v.id
    where b.prod_id = av.Object_ID
      and av.Attrib_ID =any (values (8),(9),(3000000000108))
  )vd

--атрибуты от сделок (b.del_id)
  cross join lateral (
    select
      max(case when av.Attrib_ID = 100000019000005 then v.value end) as vv_
      ,max(case when av.Attrib_ID = 100004060200000 then v.value end) as vv3_
      ,max(case when av.Attrib_ID = 100004087100000 then v.value end) as vv6_
      ,max(case when av.Attrib_ID = 100004087200000 then v.value end) as vv8_
    from attrib_value av
          inner join value_numeric v on av.Value_ID = v.id
    where b.del_id = av.Object_ID
      and av.Attrib_ID =any (values (100000019000005),(100004060200000),(100004087100000),(100004087200000))
  )vn_
  cross join lateral (
    select
      max(case when av.Attrib_ID = 3000000000105 then v.value end) as vv0_
      ,max(case when av.Attrib_ID = 100004086600000 then v.value end) as vv1_
    from attrib_value av
          inner join value_string v on av.Value_ID = v.id
    where b.del_id = av.Object_ID
      and av.Attrib_ID =any (values (3000000000105),(100004086600000))
  )vs_
  cross join lateral (
    select
      max(case when av.Attrib_ID = 3000000000106 then v.value end) as vv2_
    from attrib_value av
          inner join value_datetime v on av.Value_ID = v.id
    where b.del_id = av.Object_ID
      and av.Attrib_ID =any (values (3000000000106))
  )vd_

--атрибуты от компаний (b.clt_id)
  cross join lateral (
    select
      max(case when av.Attrib_ID = 100004087100000 then v.value end) as vv5__
    from attrib_value av
          inner join value_numeric v on av.Value_ID = v.id
    where b.clt_id = av.Object_ID
      and av.Attrib_ID =any (values (100004087100000))
  )vn__
  cross join lateral (
    select
      max(case when av.Attrib_ID = 100000016000000 then v.value end) as vv0__
      ,max(case when av.Attrib_ID = 3000000000002 then v.value end) as vv1__
      ,max(case when av.Attrib_ID = 100004974000000 then v.value end) as vv3__
      ,max(case when av.Attrib_ID=100003121500000 then v.value end) as vv2__
      ,max(case when av.Attrib_ID = 3000000000004 then v.value end) as vv6__
      ,max(case when av.Attrib_ID = 100004086100000 then v.value end) as vv7__
    from attrib_value av
          inner join value_string v on av.Value_ID = v.id
    where b.clt_id = av.Object_ID
      and av.Attrib_ID =any (values (100000016000000),(3000000000002),(100004974000000),(100003121500000),(3000000000004),(100004086100000))
  )vs__

--атрибуты от контактов (b.cnt_Id)
  cross join lateral (
    select
      max(case when av.Attrib_ID = 3000000000086 then v.value end) as vv___
      ,max(case when av.Attrib_ID = 3000000000085 then v.value end) as vv1___
      ,max(case when av.Attrib_ID = 100004086400000 then v.value end) as vv2___
      ,max(case when av.Attrib_ID = 3000000000021 then v.value end) as vv4___
      ,max(case when av.Attrib_ID = 3000000000024 then v.value end) as vv5___
      ,max(case when av.Attrib_ID = 3000000000023 then v.value end) as vv6___
      ,max(case when av.Attrib_ID = 3000000000025 then v.value end) as vv7___
      ,max(case when av.Attrib_ID = 3000000000041 then v.value end) as vv8___
      ,max(case when av.Attrib_ID = 3000000000042 then v.value end) as vv9___
      ,max(case when av.Attrib_ID = 3000000000043 then v.value end) as vv10___
      ,max(case when av.Attrib_ID = 3000000000062 then v.value end) as vv11___
      ,max(case when av.Attrib_ID = 3000000000022 then v.value end) as vv14___
      ,max(case when av.Attrib_ID = 3000000000087 then v.value end) as vv15___
      ,max(case when av.Attrib_ID = 100004975500000 then v.value end) as vv16___
      ,max(case when av.Attrib_ID = 3000000000061 then v.value end) as vv17___
  from attrib_value av
          inner join value_string v on av.Value_ID = v.id
    where b.cnt_id = av.Object_ID
      and av.Attrib_ID =any (values
        (3000000000086),(3000000000085),(100004086400000),(3000000000021),(3000000000024),(3000000000023),(3000000000025),
        (3000000000041),(3000000000042),( 3000000000043),(3000000000062),(3000000000022),(3000000000087),(100004975500000),(3000000000061)
        )
  )vs___

--добиваем данными от внешних функций (здесь теряем громадное количество времени на внешних запросах)
  --  left join _clt_ext ce on ce.table_obj_id=b.clt_id
2. если сделать несколько cte запросов в каждом из которых запрос по одному типу объекта (с уникальностью и т.д.) + его атрибуты и потом при чтении в итоге сложить их через left join все очень медленно (хотя каждый из них по отдельности быстрый) - вот с этим ничего пока не могу сделать (ели бы их параллельно запускать эти cte по каждому типу... )
имел в виду вот этот тормоз - просто общий смысл показать
Спойлер
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
--explain analyze
with
-- основная выборка + id + фильтрация + поля для работы с внешними функциями
_base as  MATERIALIZED (
  Select
--объекты от table_prod  (основной объект)
    rw.id as prod_id,
        coalesce(vv5,0) AS prod_del_id,
        vv2 as prod_dvl_id,
        vv6 as prod_clt_id,
        vv8 as prod_cnt_id,
        rw.description as prod_description
    From
      lsdbo.Ric_Get_Select(1,0,'PSft','%%','',0,'') rw
      left join lateral
        (
    select
          min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081600000) vv0
            ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100000648000000) vv5
            ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081200000) vv2
            ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004060300000) vv6
            ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004081100000) vv8
        from
          LSDBO.attrib_value av left join
          LSDBO.value_numeric nv on av.Value_ID = nv.id
        where
          rw.id = av.Object_ID
        ) nv
          on true
    where vv0>0
    --limit 1000 --000--000 --speed test
),
_prod as not MATERIALIZED (
  select
    rw.id as isobject_id,
        rw.description as Prod_Name,
        rw.author_id as Prod_idAuthor,
    vv3 AS Prod_Manager,
        vv1 AS Prod_dBegin,
        Case abs(vv) when 11 then 'Программы' when 21 then 'Оборудование'
                            when 31 then 'Прочие продукты' when 41 then 'Услуги'
                            when 50 then 'Заявки на курсы' when 51 then 'Заявки на курсы'
                            else '...' end AS Prod_Type,
         vv12 as Prod_aName,
         vv0 as Prod_Count,
         vv4 AS Prod_Status,
         vv7 AS Prod_dStatus,
--         coalesce(vv5,0) AS Prod_Id_Deal,
         vv11 AS Prod_dEnd,
--         vv6 as Prod_idClt,
--         vv8 as Prod_idCnt,
--         vv2 as Prod_idDvl,
         vv9 AS Prod_xml,
     vv13 as Prod_sNumber
  from LSDBO.object_reference AS rw
    left join lateral
        (
    select
      min(nv.value) FILTER(WHERE av.Attrib_ID = 100000019000005) vv
      ,min(nv.value) FILTER(WHERE av.Attrib_ID = 100004081600000) vv0
      ,min(dv.value) FILTER(WHERE av.Attrib_ID = 8) vv1
--            ,min(nv.value) FILTER(WHERE av.Attrib_ID = 100004081200000) vv2
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000122) vv3
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 100007986500005) vv4
--            ,min(nv.value) FILTER(WHERE av.Attrib_ID = 100000648000000) vv5
--            ,min(nv.value) FILTER(WHERE nv.Attrib_ID = 100004060300000) vv6
      ,min(dv.value) FILTER(WHERE av.Attrib_ID = 3000000000108) vv7
--            ,min(nv.value) FILTER(WHERE av.Attrib_ID = 100004081100000) vv8
      ,min(sv.value || coalesce(sv.value1,'')) FILTER(WHERE av.Attrib_ID = 100037488300000) vv9
      ,min(dv.value) FILTER(WHERE av.Attrib_ID = 9) vv11
      ,min(sv.value || coalesce(sv.value1,'')) FILTER(WHERE av.Attrib_ID = 3000000000101) vv12
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 100004091100000) vv13
    from
          LSDBO.attrib_value av left join
          LSDBO.value_numeric nv on av.Value_ID = nv.id left join
          LSDBO.value_string sv on av.Value_ID = sv.id left join
          LSDBO.value_datetime dv on av.Value_ID = dv.id
        where
          rw.id = av.Object_ID
        ) nv
          on true
  where rw.id in (select DISTINCT prod_id from _base where prod_id is not null)
),
_dev as not MATERIALIZED (
  select
    rw.id as Dvl_ID,
    rw.description as Dvl_Name
  from LSDBO.object_reference AS rw
  where rw.id in (select DISTINCT prod_dvl_id from _base where prod_dvl_id is not null)
),
_del as not MATERIALIZED (
  select
    rw.id AS Del_ID,
        rw.description as Del_Name,
        vv1 as Del_Account,
        vv2 AS Del_dAccount,
    vv0 AS Del_Status,
--     (Select LSDBO.Ric_Get_OriginalSource(vv3.value)) as Del_ReqIst_Original,
    vv3,
        Case abs(vv) when 10 then 'Клиент' when 20 then 'Дилер'
                            when 30 then 'Клиент Дилера' when 40 then 'Частное лицо'
                           when 50 then 'Агент' else '...' end AS Del_Channel
  from LSDBO.object_reference AS rw
    left join lateral
        (
    select
      min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000105) vv0
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 100004086600000) vv1
      ,min(dv.value) FILTER(WHERE av.Attrib_ID = 3000000000106) vv2
            ,min(nv.value) FILTER(WHERE av.Attrib_ID = 100000019000005) vv
            ,min(nv.value) FILTER(WHERE av.Attrib_ID = 100004060200000) vv3
    from
          LSDBO.attrib_value av left join
          LSDBO.value_numeric nv on av.Value_ID = nv.id left join
          LSDBO.value_string sv on av.Value_ID = sv.id left join
          LSDBO.value_datetime dv on av.Value_ID = dv.id
        where
          rw.id = av.Object_ID
        ) nv
          on true
  where rw.id in (select DISTINCT prod_del_id from _base where prod_del_id is not null)
),
_clt as not MATERIALIZED (
  select
      rw.id AS Clt_id,
    coalesce(vv0,'Частное лицо') AS Clt_Status,
        rw.description || case rtrim(Coalesce(vv1,'')) when '' then '' else ', ' || rtrim(Coalesce(vv1,'')) end as Clt_Name,
         coalesce(vv5,0) as Clt_ExtStatus,
        --(Select lsdbo.Ric_Get_MarketingActionsAndSales(rw.id,12)) as Clt_Warning,
    rw.id,
        vv3 as Clt_Manager,
    vv6 || coalesce(' (' || vv7 || ')','') as Clt_Ist
  from LSDBO.object_reference AS rw
    left join lateral
        (
    select
      min(sv.value) FILTER(WHERE av.Attrib_ID = 100000016000000) vv0
      ,min(nv.value) FILTER(WHERE av.Attrib_ID = 100004087100000) vv5
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000002) vv1
      ,min(sv.value || coalesce(sv.value1,'')) FILTER(WHERE av.Attrib_ID = 100004974000000) vv3
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000004) vv6
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 100004086100000) vv7
    from
          LSDBO.attrib_value av left join
          LSDBO.value_numeric nv on av.Value_ID = nv.id left join
          LSDBO.value_string sv on av.Value_ID = sv.id --left join
        where
          rw.id = av.Object_ID
        ) nv
          on true
  where rw.id in (select DISTINCT prod_clt_id from _base where prod_clt_id is not null)
),
_cnt as not MATERIALIZED (
  select
    rw.id as cId,
    coalesce(vv || ' ','') || ltrim(coalesce(vv15,'')) as cStatus,
    rw.description as cName,
    vv1 as cAction,
    vv14 as cIndex,
    vv4 as cStrana,
    vv5 as cOkrug,
    vv6 as cSity,
    vv7 as cStreet,
    vv8 as cKod,
    vv9 as cFax,
    vv10 as cTel,
    vv11 as cEmail,
    vv2 as cMessanger,
    vv16 as cmTel,
        vv17 as cWWW
  from LSDBO.object_reference AS rw
    left join lateral
        (
    select
      min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000086) vv
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000085) vv1
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 100004086400000) vv2
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000021) vv4
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000024) vv5
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000023) vv6
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000025) vv7
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000041) vv8
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000042) vv9
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000043) vv10
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000062) vv11
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000022) vv14
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000087) vv15
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 100004975500000) vv16
      ,min(sv.value) FILTER(WHERE av.Attrib_ID = 3000000000061) vv17
  from
          LSDBO.attrib_value av left join
          LSDBO.value_string sv on av.Value_ID = sv.id
        where
          rw.id = av.Object_ID
        ) nv
          on true
  where rw.id in (select DISTINCT prod_cnt_id from _base where prod_cnt_id is not null)
)

  select
    prod.isobject_id,
         prod.Prod_idAuthor,
    prod.Prod_Manager,
         prod.Prod_dBegin,
         prod.Prod_Type,
         clt.Clt_id,
         clt.Clt_Status,
         clt.Clt_Name,
         clt .Clt_ExtStatus,
         --(Select lsdbo.Ric_Get_MarketingActionsAndSales(clt.Clt_id,12)) as Clt_Block,--coalesce(Clt_Table.Clt_Warning,'') as Clt_Block,
         clt.Clt_Manager,
         cnt.cName,
        cnt.cStatus,
        cnt.cAction,
    cnt.cIndex,
    cnt.cStrana,
    cnt.cOkrug,
    cnt.cSity,
    cnt.cStreet,
    cnt.cKod,
    cnt.cFax,
    cnt.cTel,
    cnt.cEmail,
    cnt.cMessanger,
    cnt.cmTel,
    cnt.cWWW,
        prod.Prod_Name,
        prod.Prod_aName,
        prod.Prod_Status,
        prod.Prod_dStatus,
        prod.Prod_Count,
        prod.Prod_dEnd,
        dev.Dvl_Name,
        del.Del_Channel,
        Coalesce(del.Del_Account,'') as Del_Account,
        del.Del_dAccount,
        del.Del_Name,
        del.Del_Status,
      prod.Prod_xml,
       --(select LSDBO.Ric_Get_DealsFirstSaleDate(clt.Clt_id)) as Clt_dFirstSale,
       --(select LSDBO.Ric_Get_OccurrenceSource(clt.Clt_id)) as Req_ist_CRM,
      clt.Clt_Ist,
     -- (Select LSDBO.Ric_Get_OriginalSource(del.vv3::numeric)) as Del_ReqIst_Original,  --del.Del_ReqIst_Original,
       --(select LSDBO.Ric_Get_DealsAllSaleCount(clt.Clt_id)) as Clt_cAllSale,
      prod.Prod_sNumber--,
       --(select LSDBO.Ric_Get_AddressByCity(cnt.cSity)) as Clt_fDistrict

  from _base b left join
    _prod prod on b.prod_id = prod.isobject_id left join
    _dev dev on b.prod_dvl_id= dev.Dvl_ID left join
    _del del on b.prod_del_id= del.Del_ID left join
    _clt clt on b.prod_clt_id= clt.Clt_id left join
    _cnt cnt on b.prod_cnt_id= cnt.cId
поэтому пошел по первому варианту, добавил в него новый подход от вас типа так (потеряв конечно красоту плана - поскольку анализирую уже разные id от разных типов объектов в одной выборке)
...
Изменено: 31.03.2025, 14:07 - ef1
Рейтинг: 0 / 0
31.03.2025, 14:04
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140483
Alex_Toms
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Toms
Участник
ef1 [игнорируется] 

Добрый день.
В моем запросе после выкладке увидел опечатку, Дважды указан код 100003121500000.
Убрать , max(case when av.Attrib_ID = 100003121500000 then v.value end) as ds100003121500000 и из and av.Attrib_ID in (
Мой косяк однако.

Как я понял, у вас сейчас второй вариант.
На вскидку, попробовать убрать внешние объединения.
Получилось ведь найти более оптимальный вариант первого запроса без внешних объединений и повторов.
Посмотрю на досуге, может что нибудь придумаю.
...
Рейтинг: 0 / 0
31.03.2025, 16:35
| Ответить | Цитировать | Написать  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140927
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ArtToms [игнорируется] 
привет, начали усиленный переход, подготовку mssql>postgresql
кроме синтаксиса, у нас в бд нужно в платформе переписать формы объектов с вызовами SQL и скрипты(vbscript) в действиях(макросах) над объектами с вызовами SQL

выяснился интересный момент по динамическим запросам postgresql вызываемых из vbscript
может подскажешь? специально здесь написал - чтобы адресно

проблема в фильтрации where причем проблемные запросы в pgadmin работают(фильтруют) а из скрипта нет
вот так подключаемся
Код: VBScript
1.
2.
3.
set cn = CreateObject("ADODB.Connection")
cn.Open "Driver={PostgreSQL UNICODE};Server=web-crm;Port=5432;Database=crm_580_new;UID=ххх;PWD=ххх;"
Set rs = CreateObject("ADODB.Recordset")
вот так используем
Код: VBScript
1.
rs.Open sql, cn
а вот теперь проблема
динамический запрос в таком синтаксисе не фильтрует
Код: PL/pgSQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
select
  ffio, '{' || CAST(ucn as text) || '}' || CAST(rw.id as text)
from
  lsdbo.Ric_Get_chUsers(100004487800000) rw left join
  lsdbo.attrib_value_view av0 on rw.id=av0.object_id and av0.attrib_id=3000000000122 left join
  lsdbo.value_string_view vv0 on av0.value_id=vv0.id and av0.attrib_id=3000000000122
where
    fID>0 and vv0.value = 'Менеджер'   order by ffio
...
select vv0.value, '{' || CAST(vv.value as text) || '}' || CAST(rw.id as text)
from lsdbo.Ric_Get_objChild(100004614300000,'Clts') rw left join
  lsdbo.attrib_value_view av on rw.id=av.object_id and av.attrib_id=100004087100000 left join
  lsdbo.value_numeric_view vv on av.value_id=vv.id and av.attrib_id=100004087100000 left join
  lsdbo.attrib_value_view av0 on rw.id=av0.object_id and av0.attrib_id=3000000000121 left join
  lsdbo.value_string_view vv0 on av0.value_id=vv0.id and av0.attrib_id=3000000000121
where vv0.value not in('Маркетинг', 'Контакт от СМИ', 'Контакт с сайта www.dwg.ru', 'Контакт с сайта www.softmagazin.ru') order by vv.value desc, vv0.value
а вот в таком (то что ты предложил cross join lateral) - фильтрует
Код: PL/pgSQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
select
  ffio, '{' || CAST(ucn as text) || '}' || CAST(rw.id as text)
from
  lsdbo.Ric_Get_chUsers(100004487800000) rw
  cross join lateral(
    select max(sv.value) as sValue
        from LSDBO.value_string sv left join LSDBO.attrib_value av on av.Value_ID = sv.id
        where rw.id = av.Object_ID and av.Attrib_ID = 3000000000122) vv0
where  fID>0 and vv0.sValue = 'Менеджер'   order by ffio
...
select vv0, '{' || CAST(vv as text) || '}' || CAST(rw.id as text)
from lsdbo.Ric_Get_objChild(100004614300000,'Clts') rw
cross join lateral (
  select max(case when av.Attrib_ID = 100004087100000 then v.value end) as vv
   from lsdbo.attrib_value av inner join lsdbo.value_numeric v on av.Value_ID = v.id
   where rw.id = av.Object_ID and av.Attrib_ID =any (values (100004087100000)))vn
    cross join lateral (
    select max(case when av.Attrib_ID = 3000000000121 then v.value end) as vv0
    from lsdbo.attrib_value av inner join lsdbo.value_string v on av.Value_ID = v.id
    where rw.id = av.Object_ID and av.Attrib_ID =any (values (3000000000121)))vs
where vv0 not in('Маркетинг', 'Контакт от СМИ', 'Контакт с сайта www.dwg.ru', 'Контакт с сайта www.softmagazin.ru') order by vv desc, vv0
т.е запросы и там и там 'фактически' одинаковые... и в pgadmin все работает - но при вызове из скрипта vbscript фильтрация по vv0.value не работает!!??
у меня даже мыслей нет никаких))) сначала думал дело в русских буквах но нет... возможно строка подключения или сам объект ADODB должен быть другой... не знаю - ...

может есть какие нибудь мысли на этот счет?
...
Изменено: 05.06.2025, 19:10 - ef1
Рейтинг: 0 / 0
05.06.2025, 19:07
| Ответить | Цитировать | Написать | Ответы  
оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
    #40140928
ef1
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ef1
Участник
ef1 [игнорируется] 

нашел (ошибка типа copy/paste... скриптов из базы в базу) короче в проблемном запросе читал из вьюшки _view - в нормальном напрямую из таблицы
_view возвращала тип сравниваемого столбца типа text а таблица character varyng(255)

и сравнение vv0.value = 'Менеджер' с типом текст (при внешнем обращении через объект "ADODB.Connection","ADODB.Recordset") не работало а с типом (конвертация) vv0.value::varchar = 'Менеджер' работает

убило то что PgAdmin работает в сравнении нормально так (без конвертации) и так (с конвертацией)
...
Рейтинг: 0 / 0
06.06.2025, 09:50
| Ответить | Цитировать | Написать  
45 сообщений из 45, показаны все 2 страниц
  все  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=46&tid=2187185]:
 0ms
get settings:
 11ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 49ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 78ms
get tp. blocked users:
 1ms
others: 15ms
total:  192ms
созд. / загр.: 192ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]