оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql) / Microsoft SQL Server

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

25 сообщений из 43, страница 1 из 2

все

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40139989

ef1

Участник

Откуда: Москва

Сообщения: 25

Рейтинг: 0 / 0

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

ответ один и тот же, выглядит примерно так...

статистика без cte

статистика с cte

здесь на выборке ~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

Участник

Откуда: мск

Сообщения: 1 887

Рейтинг: 0 / 0

ef1 [игнорируется]

структуру покажите с индексами, планы, которые у вас получились

...

Рейтинг:

0 / 0

12.02.2025, 14:01

| Ответить | Цитировать | Написать | Ответы

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140001

ef1

Участник

Откуда: Москва

Сообщения: 25

Рейтинг: 0 / 0

Shakill [игнорируется]

здесь структура таблиц для left join

и два вложения с планами без CTE и с ним

plan_with_cte.sqlplan

plan_without_cte.sqlplan

...

Рейтинг:

0 / 0

12.02.2025, 15:41

| Ответить | Цитировать | Написать | Ответы

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140052

ef1

Участник

Откуда: Москва

Сообщения: 25

Рейтинг: 0 / 0

неожиданно на 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

чебуран

Участник

Сообщения: 38

Рейтинг: 8 / 0

В статье по 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

чебуран

Участник

Сообщения: 38

Рейтинг: 8 / 0

Да, в той же статье есть еще всякие разные подходы оптимизации EAV. Отдельно описаны.

...

Рейтинг:

0 / 0

15.02.2025, 20:24

| Ответить | Цитировать | Написать

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140278

Тяпа-ляпа

Участник

Сообщения: 5

Рейтинг: 0 / 0

ef1 [игнорируется]

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

cte c NOT MATERIALIZED не делает этого: "формируем блок", данные читаются из исходных таблиц.

...

Рейтинг:

0 / 0

06.03.2025, 14:50

| Ответить | Цитировать | Написать | Ответы

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140291

ef1

Участник

Откуда: Москва

Сообщения: 25

Рейтинг: 0 / 0

Тяпа-ляпа [игнорируется]

ну не знаю... по факту так = в памяти получается быстрее - так оптимизатор их строит, в конечном счете убив неделю на разборки (вылавливая микросекунды на конструирование запроса), отправив все планы на анализ в 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

Тяпа-ляпа

Участник

Сообщения: 5

Рейтинг: 0 / 0

ef1 [игнорируется]

Все джойны выполняются в памяти, если уж быть точным, даже если данные не в кэше, а на диске на начало выполнения запроса.

...

Рейтинг:

0 / 0

10.03.2025, 12:15

| Ответить | Цитировать | Написать

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140316

ArtToms

Участник

Сообщения: 9

Рейтинг: 0 / 0

Добрый день.

Есть варианты оптимизации запроса, желательно пробовать на данных похожие на реальные.
Можно ли получить скрипты на создания таблиц и обезличенные тестовые данные?
Если не позволяет размер файлов прикрепить к форуму, можно ссылку на облако.
Спасибо.

...

Рейтинг:

0 / 0

11.03.2025, 11:14

| Ответить | Цитировать | Написать | Ответы

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140330

ef1

Участник

Откуда: Москва

Сообщения: 25

Рейтинг: 0 / 0

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

Участник

Сообщения: 9

Рейтинг: 0 / 0

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

Участник

Сообщения: 9

Рейтинг: 0 / 0

ef1 [игнорируется]

А можно хотя бы скрипты таблиц?
Можно оставить только поля указанные в запросе, попробую накидать тестовые данные.
Есть еще один вариант изменения запроса, хочу попробовать.

...

Рейтинг:

0 / 0

12.03.2025, 13:21

| Ответить | Цитировать | Написать | Ответы

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140345

ShIgor

Участник

Откуда: Нижний Новгород

Сообщения: 2 583

Рейтинг: 1 / 0

ef1 [игнорируется]

что мешает вытянуть сначала все объекты и все их атрибуты и поверх PIVOT (для PG crosstab)?

...

Рейтинг:

0 / 0

13.03.2025, 01:24

| Ответить | Цитировать | Написать | Ответы

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140346

ef1

Участник

Откуда: Москва

Сообщения: 25

Рейтинг: 0 / 0

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 чтобы получить одну строку со всеми колонками атрибутов (количество атрибутов может быть разным)(в данном случае для одного объекта)?

...

Изменено: 13.03.2025, 10:09 - ef1

Рейтинг:

0 / 0

13.03.2025, 10:07

| Ответить | Цитировать | Написать | Ответы

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140347

ef1

Участник

Откуда: Москва

Сообщения: 25

Рейтинг: 0 / 0

ArtToms [игнорируется]
предлагаю через удаленный рабочий стол (например anydesk) посмотреть в MS студии - так будет проще всего - напишите zaytsev@cad.ru - потестим
?

...

Рейтинг:

0 / 0

13.03.2025, 10:23

| Ответить | Цитировать | Написать

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140348

ef1

Участник

Откуда: Москва

Сообщения: 25

Рейтинг: 0 / 0

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

Участник

Откуда: Нижний Новгород

Сообщения: 2 583

Рейтинг: 1 / 0

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

Участник

Откуда: Нижний Новгород

Сообщения: 2 583

Рейтинг: 1 / 0

ef1 13.03.2025, 10:46

[игнорируется]

40140348

нет лицензий хотя-бы на 2008

это когда кому мешало?

...

Рейтинг:

0 / 0

13.03.2025, 11:21

| Ответить | Цитировать | Написать

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140351

ShIgor

Участник

Откуда: Нижний Новгород

Сообщения: 2 583

Рейтинг: 1 / 0

ef1 13.03.2025, 10:46

[игнорируется]

40140348

к сожалению pivot попробовать здесь не могу

pivot - это "синтаксический сахар" - все можно сделать скриптом не выходя за рамки простейшего SQL

...

Рейтинг:

0 / 0

13.03.2025, 11:23

| Ответить | Цитировать | Написать | Ответы

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140352

ef1

Участник

Откуда: Москва

Сообщения: 25

Рейтинг: 0 / 0

ShIgor [игнорируется]

спасибо!!! в ожидании досуга, пробую разобраться (смущает только требование функции агрегации, а в моем случае агрегировать в общем то нечего) -
ps
примеров да - навалом, да и 2005 оказалось поддерживает pivot, ок... до связи!

...

Рейтинг:

0 / 0

13.03.2025, 12:13

| Ответить | Цитировать | Написать

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140359

ArtToms

Участник

Сообщения: 9

Рейтинг: 0 / 0

На 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

Участник

Откуда: Москва

Сообщения: 25

Рейтинг: 0 / 0

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

-- 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

-- 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

Участник

Откуда: Москва

Сообщения: 25

Рейтинг: 0 / 0

ArtToms [игнорируется]

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

mssql 2005 (10 выборок, база рабочая кешированная)

postgres16 (~5 выборок, тестовая база - практически не кешированная)

щас второе подготовлю внутреннее соединение...

...

Рейтинг:

0 / 0

14.03.2025, 10:02

| Ответить | Цитировать | Написать

оптимизация '+10left join' на cte (подготовка к переезду mssql>postgresql)

#40140362

ef1

Участник

Откуда: Москва

Сообщения: 25

Рейтинг: 0 / 0

ArtToms [игнорируется]

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

mssql

mssql_test_01.sqlplan

postgresql

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

| Ответить | Цитировать | Написать

25 сообщений из 43, страница 1 из 2

все

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&msg=40140359&tid=2187185]:	0ms
get settings:	10ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	33ms
get topic data:	11ms
get forum data:	3ms
get page messages:	69ms
get tp. blocked users:	1ms
others:	286ms

total:	435ms