VPF::Прошу помочь с оптимизацией запроса

Majestio

Дата 12.8.2014, 14:52 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 83
Регистрация: 19.12.2010

Репутация: нет
Всего: нет

Исходные данные:

Есть четыре таблицы - Clients, RubricaNames, Rubricator, Points2Rubrica

Код

CREATE TABLE public."Clients" (
  "Id" INTEGER DEFAULT nextval('"Clients_Id_seq1"'::regclass) NOT NULL, 
  "CardNumber" INTEGER, 
  "Surname" VARCHAR(48), 
  "FirstName" VARCHAR(48) NOT NULL, 
  "Patronymic" VARCHAR(48), 
  "SurnameOld" VARCHAR(48), 
  "DateContact" DATE, 
  "Gender" BOOLEAN NOT NULL, 
  "YearOfBirth" INTEGER, 
  "Address" VARCHAR(128), 
  "Phone" VARCHAR(64), 
  "Reason" VARCHAR(2048), 
  "FamilyId" INTEGER, 
) WITHOUT OIDS;

Код

CREATE TABLE public."RubricaNames" (
  "Id" SERIAL, 
  "Name" VARCHAR(128), 
  "System" INTEGER DEFAULT 0, 
) WITHOUT OIDS;

Код

CREATE TABLE public."Rubricator" (
  "Id" SERIAL, 
  "GroupId" INTEGER NOT NULL, 
  "Name" VARCHAR(2048), 
) WITHOUT OIDS;

Код

CREATE TABLE public."Points2Rubrica" (
  "Point" INTEGER NOT NULL, 
  "Rubrica" INTEGER NOT NULL, 
  "System" INTEGER
) WITHOUT OIDS;

Где:

  Clients - карточки клиентов
  RubricaNames - заведенные пользователем рубрикаторы
  Rubricator - значения рубрикаторов
  Points2Rubrica - таблица привязок значений рубрикаторов к карточкам клиентов

Задача: вывести плоскую таблицу, в каждой записи которой есть Id, FamilyId из карточки и 12 полей - привязанных значений рубрикатора (или 0, если привязки нет), а так же 1 или 0, в зависимости от "совершеннолетия".

Пока решил следующим запросом:

Код

SELECT 
  c."Id",
  c."FamilyId",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Вдова/Вдовец'
                  )
             )
             LIMIT 1),0) AS "R1", 
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Жертва ЧАЭС'
                  )
             )
             LIMIT 1),0) AS "R2",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Инвалидность'
                  )
             )
             LIMIT 1),0) AS "R3",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Пенсионер'
                  )
             )
             LIMIT 1),0) AS "R4",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Семейное положение'
                  )
             )
             LIMIT 1),0) AS "R5",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Относится к группе риска'
                  )
             )
             LIMIT 1),0) AS "R6",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."Id" AND
              pr."System" = 2 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Получение инвалидности'
                  )
             )
             LIMIT 1),0) AS "R7",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."FamilyId" AND
              pr."System" = 4 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Отселенная семья'
                  )
             )
             LIMIT 1),0) AS "R8",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."FamilyId" AND
              pr."System" = 4 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Получение пособий'
                  )
             )
             LIMIT 1),0) AS "R9",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."FamilyId" AND
              pr."System" = 4 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Опекунская семья'
                  )
             )
             LIMIT 1),0) AS "R10",
  COALESCE ((SELECT pr."Rubrica" FROM public."Points2Rubrica" AS pr
             WHERE 
              pr."Point" = c."FamilyId" AND
              pr."System" = 4 AND
              pr."Rubrica" IN (
                  SELECT r."Id" FROM public."Rubricator" AS r
                  WHERE r."GroupId" IN (
                     SELECT rn."Id" FROM public."RubricaNames" AS rn
                     WHERE rn."Name" = 'Семья погибшего военнослужащего'
                  )
             )
             LIMIT 1),0) AS "R11",
  CASE WHEN EXTRACT(year FROM now()) - c."YearOfBirth" < 18 THEN 1 ELSE 0 END AS "R12"
FROM public."Clients" AS c
WHERE c."DateContact" < '2014-01-11'
ORDER BY c."Id"

Но быстродействие как-то немного сомнительно. 1000 записей обрабатывается в районе 14 секунд. Как считаете, есть куда оптимизировать? И если "да", покажите пожалуйста куда копать. А еще бы лучше на примере 2 рубрикаторов.

Это сообщение отредактировал(а) Majestio - 12.8.2014, 14:53

Akina

Дата 12.8.2014, 15:26 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

СУБД?

--------------------

О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

Majestio

Дата 12.8.2014, 16:09 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 83
Регистрация: 19.12.2010

Репутация: нет
Всего: нет

Цитата(Akina @ 12.8.2014, 15:26

)

СУБД?

В теме же указал) PostgreSQL 9.3.1

tzirechnoy

Дата 12.8.2014, 16:30 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1173
Регистрация: 30.1.2009

Репутация: 2
Всего: 16

1) Да, 14ms на запись -- это много.
2) Но не так, чтобы дофига много. Два оборота диска на 7200.
3) Но, в любом случае, если хорошо подумать -- то можно меньшэ. Конечно, в обычном случае всё сложно -- поскольку дажэ если в points2rubrica закэшыровано всё, кроме листьев индэкса (это реалистичное предположэние, впрочем, если Вы скажэте, какой размер базы и что за жэлезо -- то обусждать это можно будет предметнее), то у нас два обращения -- к листу индэкса и к записи в таблицэ. Но во-первых, в среднем от диска ждут, что он сможэт дойти до нужного места за пол-оборота, во-вторых, некоторый процэнт points2rubrica тожэ можэт быть закэшырован -- так что можно добивать где-то двукратного ускорения без спецыальных операцый вроде прогрева кэша, засасывания в память, перехода на index-only read или покупки SSD.
4) Непонятен смысл двух уровней -- rubricator+rubricanames. Но, в любом случае, если там по 50 записей в каждой таблицэ, то это не важно.
5) Какие индэксы и ключи?

Ну, банальное: points2rubrica должно иметь (point, system) BTREE, а clients -- дату, видимо, тожэ BTREE.

Majestio

Дата 12.8.2014, 16:58 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 83
Регистрация: 19.12.2010

Репутация: нет
Всего: нет

Пробую с left join'ами пока. Еще соображаю че к чему. Плодятся NULL - но все отсекать нельзя, ибо если ни одной привязки нет, хотя бы одна запись со всеми NULL быть дожна.

Железо на серваке пока "обнять и плакать". Pentium 3, RAM 128Мb, FreeBSD 9.0 урезанная "по самое нихачю", HDD 12Gb. Пока держит 10 рабочих мест более-менее. После релиза проекта сядет на Core i3, RAM 16Gb, SATA2 или 3 в RAID-0.

Замеры делал на FreeBSD под виртуальной машиной с примерно последним оборудованием.

Смысл "двух уровней -- rubricator+rubricanames" в том, что rubricanames - хранит названия создаваемых пользователем (вернее админом) рубрикаторов и их ID. rubricator - хранит сами значения рубрикаторов (привязывязка rubricanames.Id = rubricator.GroupId)

Индексы и ключи не указывал в посте. По всем Id и связанными полями.

Akina

Дата 12.8.2014, 17:23 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

http://www.postgresql.org/docs/9.3/static/tablefunc.html
crosstab()

--------------------

Majestio

Дата 12.8.2014, 17:40 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 83
Регистрация: 19.12.2010

Репутация: нет
Всего: нет

Цитата(Akina @ 12.8.2014, 17:23

)

http://www.postgresql.org/docs/9.3/static/tablefunc.html
crosstab()

Большое спасибо! Идею понял. Который раз меня выручаете)

Majestio

Дата 13.8.2014, 10:04 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 83
Регистрация: 19.12.2010

Репутация: нет
Всего: нет

Удалость ускорить в 7 раз, багодаря наводке Влада.
Сейчас исполняется 2 сек вместо 14 на 1048 записях.

Код


WITH cr AS (
SELECT *
FROM crosstab(
     'SELECT z."CardId", z."RubricatorId", COALESCE(z."Rubrica",0) AS "Rubrica" FROM (                                   
     WITH x AS (                                                                                                        
     SELECT cl."Id", r."GroupId", pr."Rubrica"                                                                          
      FROM public."Points2Rubrica" AS pr, public."Rubricator" AS r, public."Clients" AS cl                              
      WHERE                                                                                                             
      -- cl."Id" = 429 AND                                                                                              
            ((pr."Point" = cl."Id" AND pr."System" = 2) OR (pr."Point" = cl."FamilyId" AND pr."System" = 4)) AND        
            pr."Rubrica" = r."Id"                                                                                       
     )                                                                                                                  
     SELECT * FROM (                                                                                                    
     SELECT c."Id" AS "CardId", c."FamilyId" AS "CardFamilyId", rn."Name" AS "RubricatorName", rn."Id" AS "RubricatorId"
     FROM public."Clients" AS c, public."RubricaNames" AS rn                                                            
     WHERE                                                                                                              
       -- c."Id" = 429 AND                                                                                              
       rn."Name" IN (                                                                                                   
      ''Вдова/Вдовец'',                                                                                                   
      ''Жертва ЧАЭС'',                                                                                                    
      ''Инвалидность'',                                                                                                   
      ''Опекунская семья'',                                                                                               
      ''Относится к группе риска'',                                                                                       
      ''Отселенная семья'',                                                                                               
      ''Пенсионер'',                                                                                                      
      ''Получение инвалидности'',                                                                                         
      ''Получение пособий'',                                                                                              
      ''Семейное положение'',                                                                                             
      ''Семья погибшего военнослужащего''                                                                                 
     )) AS s                                                                                                            
     LEFT OUTER JOIN x ON (s."RubricatorId" = x."GroupId" AND s."CardId" = x."Id")                                      
     ) AS z
     ORDER BY 1,2'
    ) as ( "Id"  int, "R1" int, "R2" int, "R3" int, "R4" int, "R5" int, "R6" int, "R7" int, "R8" int, "R9" int, "R10" int, "R11" int)
)
SELECT cr.*, cl."FamilyId", CASE WHEN (cl."YearOfBirth" < 18) OR (EXTRACT(year FROM now()) - cl."YearOfBirth" < 18) THEN 1 ELSE 0 END AS "Infant" 
FROM cr, public."Clients" AS cl
WHERE cr."Id" = cl."Id"

Для функционирования этого хозайства пришлось во FreeBSD установить порт:

Код


cd /usr/ports/databases/postgresql92-contrib
make install clean

Зарегать расширение:

Код


CREATE EXTENSION tablefunc;

Ну вот как бы и все. Для меня вопрос решен. Если есть какие замечания - буду только рад!
Akina, еще раз большое спасибо!

tzirechnoy

Дата 14.8.2014, 19:18 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1173
Регистрация: 30.1.2009

Репутация: 2
Всего: 16

Цитата

Замеры делал на FreeBSD под виртуальной машиной с примерно последним оборудованием.

Вам мало, что вы еврей? © анекдот

Ну, серьёзно, с замерами и так проблем вагон -- зачем ещё виртуалку присобачивать?

Цитата

Удалость ускорить в 7 раз, багодаря наводке Влада.

Это достаточно маловероятно. По идее -- планы запросов отличаться не должны.

Ищите в другом месте. Например, если там всего 1000 записей -- то, очевидно, разница между "из кэша" и "с диска" будет на несколько порядков.

Majestio

Дата 14.8.2014, 20:16 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 83
Регистрация: 19.12.2010

Репутация: нет
Всего: нет

Цитата(tzirechnoy @ 14.8.2014, 19:18)

Цитата

Замеры делал на FreeBSD под виртуальной машиной с примерно последним оборудованием.

Вам мало, что вы еврей? © анекдот

Цитата(tzirechnoy @ 14.8.2014, 19:18)

Ну, серьёзно, с замерами и так проблем вагон -- зачем ещё виртуалку присобачивать?

Выхода нет. Комп один, а различного барахла разработчика - валом. Разнес все по виртуалкам и не жужжу. Все есть в бэкапах. Завалил Фрю - поднял, Мак лег - тем же макаром. Под венду пишу - сидя под Gentoo Linux, имея 4 тулчейна win 32/64 linux 32/64 ... так же под виртуалкой). На хостовой машине тока Far, M$ Офис и FireFox. Вощем - так уютнее, гемора с основной системой нет.

Цитата(tzirechnoy @ 14.8.2014, 19:18)

Цитата

Удалость ускорить в 7 раз, багодаря наводке Влада.

Это достаточно маловероятно. По идее -- планы запросов отличаться не должны.

Комменты дать не могу - тупо гонял в Navicat'е запросы - там время засекается. Склонен доверять увиденному, да и субъективно по ожиданию - заметно.

Цитата(tzirechnoy @ 14.8.2014, 19:18)

Ищите в другом месте. Например, если там всего 1000 записей -- то, очевидно, разница между "из кэша" и "с диска" будет на несколько порядков.

Ничче не понял! Гонял разные выборки - 100, 400, 800, 1000 записей. "В семь раз" - везде работали.

Akina

Дата 15.8.2014, 07:43 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Цитата(tzirechnoy @ 14.8.2014, 20:18

)

Это достаточно маловероятно. По идее -- планы запросов отличаться не должны.

Ага... в первоначальном варианте - 11 коррелированных подзапросов, в окончательном ни одного, а планы должны быть одинаковые?

--------------------

Zloxa

Дата 15.8.2014, 09:53 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(Akina @ 15.8.2014, 08:43)

Цитата(tzirechnoy @ 14.8.2014, 20:18

)

Это достаточно маловероятно. По идее -- планы запросов отличаться не должны.

Просто любопытствую, во что преобразовывает crosstab?
В смысле что ему эквивалентно/с чем это можно сравнить? Группировка?

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Akina

Дата 15.8.2014, 09:55 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Цитата(Zloxa @ 15.8.2014, 10:53

)

во что преобразовывает crosstab?В смысле что ему эквивалентно/с чем это можно сравнить?

Это эмуляция pivot query из готового линейного набора данных. Ну или сводная таблица Экселя.

--------------------

Zloxa

Дата 15.8.2014, 09:57 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Цитата(Akina @ 15.8.2014, 08:43

)

11 коррелированных подзапросов

По индексам(составным) должны бы весьма ништяково отшарохать. smile

Временами, для частных случаяев кореляхами удавлось аггрегаху перефорсить smile

Добавлено через 1 минуту и 10 секунд

Цитата(Akina @ 15.8.2014, 10:55

)

Это эмуляция pivot query из готового линейного набора данных. Ну или сводная таблица Экселя.

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

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Akina

Дата 15.8.2014, 10:44 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Цитата(Zloxa @ 15.8.2014, 10:57

)

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

Если учесть, что запрос, подготавливающий данные, передаётся функции как литерал, а не как набор данных - вполне может быть. Хотя я думаю, что crosstab() просто выполняет функции фронт-энда, сливая серверу переданный ей запрос и самостоятельно парсит текст обёртки, обрабатывая выхлоп. Либо,что мне кажется ещё более вероятным, заворачивает поток данных от внутреннего запроса обратно на сервер, делая вид, что это внешний forward-only набор данных от linked-сервера - то есть выполняет внешний и внутренний запросы раздельно, связывая им входы-выходы. Может, даже формируя для этого отдельные connections... хотя вот это уже вряд ли.

--------------------

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Составление SQL-запросов \| Следующая тема »