ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация и объединение двух запросов
12 сообщений из 12, страница 1 из 1
Оптимизация и объединение двух запросов
    #39449855
General4
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
General4
Гость
Доброго времени суток, уважаемые форумчане.
Подскажите, пожалуйста, как можно оптимизировать два запроса в один.

Запрос 1:
Код: sql
1.
SELECT o_id, number, name_o FROM object WHERE name_o ILIKE ''%жилой дом%'' ORDER BY number'


Запрос 2 внутри первого:
Код: sql
1.
SELECT e_id, o_id, e_time, e_sub FROM event WHERE event.o_id=object.o_id AND (e_sub>=13 and e_sub<=14) ORDER BY e_time DESC LIMIT 1


Описание: Есть таблица 1 (object) - Список объектов, в ней формируются объекты. В таблице 2 (event) - События по всем объектам. Мне необходимо сократить время запросов и убрать цикл по второму запросу через JOIN или WHERE o_id IN (SELECT...). Натыкаюсь на ошибки. Также рассматриваю создание процедуры внутри PostgreSQL.

Запросы делают следующие действия. Первый запрос находит все "Жилые дома" в таблице object. Потом в цикле начинается перебор найденных значений с подключением второго запроса, который в таблице 2 находит последнюю запись по времени для этого объекта.

Краткий пример структуры на основе кода Delphi:
Код: pascal
1.
2.
3.
4.
5.
6.
7.
Запрос 1
for i := 0 to RecordCount do
  begin
    Запрос 2
    // дальше идёт отображение Состояния объекта
    ...
  end;



Номер Название Состояние232 Жилой дом Открыт450 Жилой дом Закрыт
Столбец "Состояние" подставляется из второго запроса. Открыт = e_sub = 13. Закрыт = e_sub = 14

event.o_id = object.o_id - специально дописал event и object в запрос, чтобы показать зависимость.
...
Рейтинг: 0 / 0
07.05.2017, 08:58
| Ответить | Цитировать | Написать  
Оптимизация и объединение двух запросов
    #39449888
sereginseregin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sereginseregin
Гость
General4

DISTINCT ON (object.o_id) - отбросит все записи кроме первой для каждого object.o_id

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT DISTINCT ON (object.o_id)
	, object.o_id
	, object.number
	, object.name_o

	, event.e_id
	, event.o_id
	, event.e_time
	, event.e_sub 
FROM object 
INNER JOIN event ON event.o_id=object.o_id
WHERE object.name_o ILIKE ''%жилой дом%''
	AND (event.e_sub>=13 and event.e_sub<=14)
ORDER BY object.o_id, event.e_time DESC
...
Рейтинг: 0 / 0
07.05.2017, 13:56
| Ответить | Цитировать | Написать  
Оптимизация и объединение двух запросов
    #39449895
General4
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
General4
Гость
Огромное спасибо, sereginseregin, всё работает идеально и быстро!
...
Рейтинг: 0 / 0
07.05.2017, 14:22
| Ответить | Цитировать | Написать  
Оптимизация и объединение двух запросов
    #39449901
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
sereginseregin,

ять, стьюденты ,
читать про лейтерал [LATERAL],
ну и про правильное индексирование для быстрого доступа к последней записи с заданными е-саб и obj_id вдоль времени

 
если версия старовата для LATERAL -- помнится можно было дотянуться именно вдоль инд-а в к-ции with (cte). но навык мною утерян. ищите старые топики про выборки топ N чего--нить. кажется статей, или продаж; лет 5 тому проскакивали.

хотя если N=1 это д.б. много проще даже до лейтерала -- засовыванием 2-го в компаунд как поле--кореллят, с последующим распаковыванием (саб)полей компаунда
...
Рейтинг: 0 / 0
07.05.2017, 14:36
| Ответить | Цитировать | Написать  
Оптимизация и объединение двух запросов
    #39449938
General4
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
General4
Гость
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
SELECT object.o_id,object.number,object.name_o,
       event.e_id,event.o_id, event.e_time, event.e_sub
FROM object,
  LATERAL(
    SELECT e_id,o_id,e_time,event.e_sub
    FROM event
    WHERE object.o_id=event.o_id AND (e_sub>=13 and e_sub<=14)
    ORDER BY e_time DESC LIMIT 1
  ) AS event
WHERE object.name_o ILIKE ''%жилой дом%''
ORDER BY object.number


Попробовал через LATERAL. В обоих случаях столкнулся с одной маленькой проблемой. Если в таблице 2 (event) нет строк с e_sub=13 или e_sub=14, то строка исключается и она становится потерянной, хотя совпадения есть "Жилой дом".

Пример:
НомерНазваниеСостояние232Жилой дом450Жилой домЗакрыт
Зачёркнутой строки нет в результате, так как нет значения "Открыт", "Закрыт". Такое тут тоже бывает. Как же можно выкрутиться?
...
Рейтинг: 0 / 0
07.05.2017, 17:05
| Ответить | Цитировать | Написать  
Оптимизация и объединение двух запросов
    #39449946
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
учи скл, стьюдент
left join
например
General4
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT object.o_id,object.number,object.name_o,
       event.e_id,event.o_id, event.e_time, event.e_sub
FROM object 
left join --
  LATERAL(
    SELECT e_id,o_id,e_time,event.e_sub
    FROM event
    WHERE object.o_id=event.o_id AND (e_sub>=13 and e_sub<=14)
    ORDER BY e_time DESC LIMIT 1
  ) AS event
on true --
WHERE object.name_o ILIKE ''%жилой дом%''
ORDER BY object.number




есть такой пример тут:
https://postgrespro.ru/docs/postgresql/9.5/queries-table-expressions.html#QUERIES-LATERAL
...
Рейтинг: 0 / 0
07.05.2017, 18:14
| Ответить | Цитировать | Написать  
Оптимизация и объединение двух запросов
    #39450015
General4
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
General4
Гость
Итог:
Так попробовал запустить запрос на основном сервере, но оказалось, что сторонний сервер для которого всё это пишется содержит версию 8.4.22 и обновляться в ближайший год до версии 9.3 не планируется.
LATERAL не поддерживается версией сервера 8.4.22

Запрос от sereginseregin с применением DISTINCT ON (object.o_id) решил проблему для версии 8.4.22, осталось его переработать, чтобы исключить потерянные строки.
...
Рейтинг: 0 / 0
08.05.2017, 05:43
| Ответить | Цитировать | Написать  
Оптимизация и объединение двух запросов
    #39450036
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
General4,

ну ты стьюдент и наглец
так лефт джойн и не научилсо писать ? он и в 7.3 работал.


 
PS факультативно
в раньшие времена люди в пж ещё кактотаг делали:
Код: 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.
SELECT
	object.o_id
	, object.number
	, object.name_o

	, (event).e_id
	, (event).o_id
	, (event).e_time
	, (event).e_sub 
FROM 
(SELECT
	object.o_id
	, object.number
	, object.name_o
	-- кореллят - компаунд (для топ N - array (SELECT...))
	,( SELECT (event.*)::event FROM event 
		WHERE event.o_id=object.o_id --join
		AND (event.e_sub IN (13,14))
		ORDER BY object.o_id, event.e_time DESC LIMIT 1
          )
		AS event
FROM object 
WHERE object.name_o ILIKE '%жилой дом%'
) AS object


только если в среднем ивентов на обжект много (>>10); при наличии индекса event(o_id,e_sub,e_time)
...
Рейтинг: 0 / 0
08.05.2017, 11:25
| Ответить | Цитировать | Написать  
Оптимизация и объединение двух запросов
    #39450049
General4
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
General4
Гость
Всё, qwwq, получилось! Конечно работает раза в три дольше чем LATERAL, но главное работает. Огромное спасибо!!!
...
Рейтинг: 0 / 0
08.05.2017, 12:21
| Ответить | Цитировать | Написать  
Оптимизация и объединение двух запросов
    #39450055
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
сдаёцца, лефт джойн стьюдентом так и не освоен
sereginsereginGeneral4


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
SELECT DISTINCT ON (object.o_id)
	, object.o_id
	, object.number
	, object.name_o

	, event.e_id
	, event.o_id
	, event.e_time
	, event.e_sub 
FROM object 
LEFT JOIN event ON event.o_id=object.o_id
	AND (event.e_sub>=13 and event.e_sub<=14)
WHERE object.name_o ILIKE ''%жилой дом%''

ORDER BY object.o_id, event.e_time DESC
...
Рейтинг: 0 / 0
08.05.2017, 12:49
| Ответить | Цитировать | Написать  
Оптимизация и объединение двух запросов
    #39450058
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
qwwqGeneral4,
<..>

 
PS поправил
в раньшие времена люди в пж ещё кактотаг делали:
Код: 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.
SELECT
	object.o_id
	, object.number
	, object.name_o

	, (event).e_id
	, (event).o_id
	, (event).e_time
	, (event).e_sub 
FROM 
(SELECT
	object.o_id
	, object.number
	, object.name_o
	-- кореллят - компаунд (для топ N - array (SELECT...))
	,( SELECT (event.*)::event FROM event 
		WHERE event.o_id=object.o_id --join
		AND (event.e_sub IN (13,14))
		ORDER BY event.e_time DESC LIMIT 1
          )
		AS event
FROM object 
WHERE object.name_o ILIKE '%жилой дом%'
) AS object




стьюдент, план запроса покажы
сущ-е индексы на ивентах распишы
тип у поля event.e_sub какой 777
...
Рейтинг: 0 / 0
08.05.2017, 12:53
| Ответить | Цитировать | Написать  
Оптимизация и объединение двух запросов
    #39450167
General4
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
General4
Гость
qwwq, да, учиться-учиться и ещё раз учиться...

Переделанный вами запрос с SELECT DISTINCT ON (object.o_id) подошёл как нельзя лучше, очень шустрый.

Править что либо на сервере, я не имею права, поэтому это всё останется без изменений.
 
Описание полей EVENTe_id serial NOT NULL,
device_id integer,
o_id integer,
o_group_id integer,
e_time timestamp without time zone,
e_type integer,
e_subtype integer,
source_type integer,
source_id integer,
channel_id integer,
object_state integer,
object_state_change integer,
message_id integer,
device_number integer,
partition_number integer,
o_number integer,
sensor_number integer,
pin_number integer,
key_number integer,
info character varying(255),
server_id integer,
device_addr integer,
event_data_size integer,
event_data bytea,
event_create_time timestamp without time zone,
channel_net integer,
shift_id integer,
CONSTRAINT event_pkey PRIMARY KEY (e_id)

План запроса? Если я правильно понял он в первой теме - план запроса из двух запросов, который мы улучшили.
...
Рейтинг: 0 / 0
08.05.2017, 19:38
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация и объединение двух запросов
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=74&tid=1996530]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 27ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 52ms
get tp. blocked users:
 2ms
others: 348ms
total:  471ms
созд. / загр.: 471ms
x
x
Закрыть


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