Как оптимизировать запрос? / PostgreSQL

ReSQL.ru

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

Гость

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

Новые сообщения | Избранное

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как оптимизировать запрос?

15 сообщений из 15, страница 1 из 1

Как оптимизировать запрос?

#38769214

mdlv

Гость

Добрый день! Есть запрос вида

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

SELECT i_r_id,
    to_char(dt, 'YYYY-MM-DD HH24:MI:SS') as dt,
    s_id, r_id, num_id,
    s.a_name as s,
    r.a_name as r,
    (t_name || ' N' || val_id::TEXT) as val_id,
    array_to_string(arr2arr( i,
    COALESCE('SELECT name_value::TEXT || '' '' || i_num_value::TEXT || '' ед.'' ||
      CASE WHEN lower(COALESCE(tr.tr_name::TEXT,'Неизвестно''))=''Неизвестно'' THEN '''' ELSE
        '' '' || tr.tr_name::TEXT END
      FROM i
      LEFT JOIN tr ON i.tr_id = tr.tr_id
      WHERE i_id = $$', '{}')),'; ') as i,
    u
    FROM i_r
    LEFT JOIN t ON t.t_id = i_r.t_id
    LEFT JOIN a s ON i_r.s_id = s.a_id
    LEFT JOIN a r ON i_r.r_id = r.a_id
    GROUP BY i_r.i_r_id, i_r.dt, i_r.s_id, i_r.r_id, i_r.num_id,
    s.a_name, r.a_name, t.t_name, i_r.val_id, i_r.u, i_r.i

Т.е. основная таблица i_r, и три LEFT JOIN с другими таблицами.
Посоветуйте, пожалуйста, как лучше оптимизировать запрос и как в этом случае использовать индексы? Таблицы i, i_r содержат большое количество записей и записи все время добавляются. Таблицы t, a более-менее постоянны, изменяются редко, записей мало. Запрос используется очень часто.

Результат EXPLAIN ANALYZE:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

"Group  (cost=78.77..81.15 rows=53 width=177) (actual time=1.790..1694.582 rows=53 loops=1)"
"  ->  Sort  (cost=78.77..78.90 rows=53 width=177) (actual time=1.288..1.330 rows=53 loops=1)"
"        Sort Key: i_r.i_r_id, i_r.dt, i_r.s_id, i_r.r_id, i_r.num_id, s.a_name, r.a_name, t.t_name, i_r.val_id, i_r.u, i_r.i"
"        ->  Hash Left Join  (cost=68.27..77.25 rows=53 width=177) (actual time=0.735..1.210 rows=53 loops=1)"
"              Hash Cond: (("outer".r_id)::bigint = "inner".a_id)"
"              ->  Hash Left Join  (cost=36.99..45.47 rows=53 width=145) (actual time=0.518..0.861 rows=53 loops=1)"
"                    Hash Cond: (("outer".s_id)::bigint = "inner".a_id)"
"                    ->  Hash Left Join  (cost=5.70..13.68 rows=53 width=113) (actual time=0.453..0.681 rows=53 loops=1)"
"                          Hash Cond: (("outer".t_id)::bigint = "inner".t_id)"
"                          ->  Seq Scan on i_r  (cost=0.00..4.53 rows=53 width=89) (actual time=0.012..0.097 rows=53 loops=1)"
"                          ->  Hash  (cost=5.16..5.16 rows=216 width=40) (actual time=0.423..0.423 rows=0 loops=1)"
"                                ->  Seq Scan on t  (cost=0.00..5.16 rows=216 width=40) (actual time=0.024..0.249 rows=216 loops=1)"
"                    ->  Hash  (cost=31.23..31.23 rows=23 width=40) (actual time=0.052..0.052 rows=0 loops=1)"
"                          ->  Seq Scan on arms s  (cost=0.00..31.23 rows=23 width=40) (actual time=0.002..0.032 rows=23 loops=1)"
"              ->  Hash  (cost=31.23..31.23 rows=23 width=40) (actual time=0.206..0.206 rows=0 loops=1)"
"                    ->  Seq Scan on arms r  (cost=0.00..31.23 rows=23 width=40) (actual time=0.018..0.185 rows=23 loops=1)"
"Total runtime: 1711.239 ms"

...

Рейтинг:

0 / 0

07.10.2014, 15:20:19

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

Как оптимизировать запрос?

#38769267

V&N

Гость

mdlv, а можно нормальный запрос с ddl и примером данных?

...

Рейтинг:

0 / 0

07.10.2014, 15:39:01

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

Как оптимизировать запрос?

#38769525

mdlv

Гость

V&N mdlv, а можно нормальный запрос с ddl и примером данных?

Да, конечно)

Попытался максимально упростить.

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.

CREATE TABLE types
(
  types_id bigserial NOT NULL,
  types_name text,  
  CONSTRAINT types_pkey PRIMARY KEY (types_id )
);
INSERT INTO types (types_id, types_name) VALUES (1, 'название 1');
INSERT INTO types (types_id, types_name) VALUES (2, 'название 2');

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

CREATE TABLE autos
(
  autos_id bigserial NOT NULL,
  state_id bigint,
  autos_name text,
  autos_text text,
  autos_status bigint,
  autos_type bigint,
  CONSTRAINT autos_pkey PRIMARY KEY (autos_id ) 
);
INSERT INTO autos (autos_id, state_id, autos_name, autos_text, autos_status, autos_type) VALUES (1, 1, 'название 1', 'текст 1', 0, 0);
INSERT INTO autos (autos_id, state_id, autos_name, autos_text, autos_status, autos_type) VALUES (2, 1, 'название 2', 'текст 2', 0, 0);

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.

CREATE TABLE treviews
(
  treviews_id bigserial NOT NULL,
  treviews_name text NOT NULL DEFAULT 'Unknown'::text,
  CONSTRAINT treviews_pkey PRIMARY KEY (treviews_id )
);
INSERT INTO treviews (treviews_id, treviews_name) VALUES (1, 'treview 1');
INSERT INTO treviews (treviews_id, treviews_name) VALUES (2, 'treview 2');

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

CREATE TABLE items
(
  items_id bigserial NOT NULL,
  items_val real,
  treviews_id bigint,
  reports_id bigint,
  status bigint,
  item_name text,
  CONSTRAINT items_pkey PRIMARY KEY (items_id )
);
INSERT INTO items (items_id, items_val, treviews_id, reports_id, status, item_name) VALUES (2, 34.669998, 2, 2, 0, 'название 2');
INSERT INTO items (items_id, items_val, treviews_id, reports_id, status, item_name) VALUES (1, 12.45, 1, 1, 2, 'название 1');

Основная таблица

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

CREATE TABLE items_reviews
(
 _id bigint NOT NULL,
 sender_id bigint,
 receiver_id bigint,
 types_id bigint,
 status_id bigint,
 dt timestamp DEFAULT ('now'::text)::timestamp(6) with time zone,
 is_usual boolean DEFAULT false,
 reviews_id bigint NOT NULL,
  items_reviews_id bigserial NOT NULL,
  t_id bigint,
  items bigint[],
  description text,
  CONSTRAINT items_reviews_pkey PRIMARY KEY (items_reviews_id )
);
INSERT INTO items_reviews (_id, sender_id, receiver_id, types_id, status_id, dt, is_usual, reviews_id, items_reviews_id, t_id, items, description) VALUES (1, 1, 2, 1, 0, '2014-10-07 17:34:18.799645', false, 1, 2, NULL, '{1,2}', NULL);

В запросе используется функция:

Код: plsql

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.

CREATE OR REPLACE FUNCTION arr2arr(anyarray, text)
  RETURNS text[] AS
'
DECLARE
  in_array 	ALIAS FOR $1;
  query 		ALIAS FOR $2;

  res 		TEXT[];
  my_cursor     REFCURSOR;
  countElement1 INT8;
  countElement2 INT8;
  i int8;
  j int8;

  elem TEXT;
  _tmp_arr TEXT[];

BEGIN
  countElement1 := array_upper(in_array, 1);
  IF countElement1 IS NULL THEN
    RETURN NULL;
  END IF;
  countElement2:=array_upper(in_array, 2);

  IF countElement2 IS NULL THEN
	-- Одномерный массив
    FOR i IN 1..countElement1 LOOP
      OPEN my_cursor FOR EXECUTE replace(query, ''$$'',  '''''''' || in_array[i] || '''''''');
      FETCH my_cursor INTO elem;
      CLOSE my_cursor;
      IF elem IS NOT NULL THEN
	IF COALESCE(array_upper(res,1),0) = 0 THEN
              res := ARRAY[elem];
            ELSE
         	  res := array_append(res, elem);
            END IF;
      END IF;
    END LOOP; 

  ELSE
	-- Двумерный массив NULL значения в массиве недопустимы
    FOR i IN 1..countElement1 LOOP
      _tmp_arr := NULL;
      FOR j IN 1..countElement2 LOOP
        OPEN my_cursor FOR EXECUTE replace(query, ''$$'',  '''''''' || in_array[i][j] || '''''''');
        FETCH my_cursor INTO elem;
        CLOSE my_cursor;
        IF COALESCE(array_upper(_tmp_arr,1),0) = 0 THEN
          _tmp_arr := ARRAY[elem];
         ELSE
          _tmp_arr := array_append(_tmp_arr, elem);
        END IF;
      END LOOP; 
      IF COALESCE(array_upper(res,2),0) = 0 THEN
        res := ARRAY[_tmp_arr];
      ELSE
        res := res || ARRAY[_tmp_arr];
      END IF;
    END LOOP; 
  END IF;
RETURN res;
END;
'
  LANGUAGE plpgsql VOLATILE;

А вот сам запрос

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

SELECT items_reviews_id,
    to_char(dt, 'YYYY-MM-DD HH24:MI:SS') as dt,
    sender_id, receiver_id, status_id,
    sender.autos_text as sender,
    receiver.autos_text as receiver,
    (types_name || ' N' || t_id::TEXT) as t_id,
    array_to_string(arr2arr( items,
    COALESCE('SELECT item_name::TEXT || '' '' || items_val::TEXT || '' ед.'' ||
      CASE WHEN lower(COALESCE(treviews.treviews_name::TEXT,''Unknown''))=''unknown'' THEN '''' ELSE
        '' '' || treviews.treviews_name::TEXT END
      FROM items
      LEFT JOIN treviews ON items.treviews_id = treviews.treviews_id
      WHERE items_id = $$', '{}')),'; ') as items,
    is_usual
    FROM items_reviews
    LEFT JOIN types ON types.types_id = items_reviews.types_id
    LEFT JOIN autos sender ON items_reviews.sender_id = sender.autos_id
    LEFT JOIN autos receiver ON items_reviews.receiver_id = receiver.autos_id
    GROUP BY items_reviews.items_reviews_id, items_reviews.dt, items_reviews.sender_id, items_reviews.receiver_id, items_reviews.status_id,
    sender.autos_text, receiver.autos_text, types.types_name, items_reviews.t_id, items_reviews.is_usual, items_reviews.items

И вот результат только с этими таблицами и содержимым на новой базе:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

"Group  (cost=190.08..235.22 rows=1003 width=177) (actual time=1.415..1.416 rows=1 loops=1)"
"  ->  Sort  (cost=190.08..192.59 rows=1003 width=177) (actual time=0.235..0.236 rows=1 loops=1)"
"        Sort Key: items_reviews.items_reviews_id, items_reviews.dt, items_reviews.sender_id, items_reviews.receiver_id, items_reviews.status_id, sender.autos_text, receiver.autos_text, types.types_name, items_reviews.t_id, items_reviews.is_usual, items_reviews.items"
"        ->  Hash Left Join  (cost=67.50..140.08 rows=1003 width=177) (actual time=0.207..0.213 rows=1 loops=1)"
"              Hash Cond: ("outer".receiver_id = "inner".autos_id)"
"              ->  Hash Left Join  (cost=45.00..100.04 rows=1002 width=145) (actual time=0.152..0.156 rows=1 loops=1)"
"                    Hash Cond: ("outer".sender_id = "inner".autos_id)"
"                    ->  Hash Left Join  (cost=22.50..60.01 rows=1001 width=113) (actual time=0.133..0.135 rows=1 loops=1)"
"                          Hash Cond: ("outer".types_id = "inner".types_id)"
"                          ->  Seq Scan on items_reviews  (cost=0.00..20.00 rows=1000 width=89) (actual time=0.093..0.095 rows=1 loops=1)"
"                          ->  Hash  (cost=20.00..20.00 rows=1000 width=40) (actual time=0.015..0.015 rows=0 loops=1)"
"                                ->  Seq Scan on types  (cost=0.00..20.00 rows=1000 width=40) (actual time=0.009..0.011 rows=2 loops=1)"
"                    ->  Hash  (cost=20.00..20.00 rows=1000 width=40) (actual time=0.007..0.007 rows=0 loops=1)"
"                          ->  Seq Scan on autos sender  (cost=0.00..20.00 rows=1000 width=40) (actual time=0.002..0.004 rows=2 loops=1)"
"              ->  Hash  (cost=20.00..20.00 rows=1000 width=40) (actual time=0.043..0.043 rows=0 loops=1)"
"                    ->  Seq Scan on autos receiver  (cost=0.00..20.00 rows=1000 width=40) (actual time=0.033..0.038 rows=2 loops=1)"
"Total runtime: 1.587 ms"

...

Рейтинг:

0 / 0

07.10.2014, 18:09:47

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

Как оптимизировать запрос?

#38769594

mdlv

Гость

Версия Postgresql 7.4

...

Рейтинг:

0 / 0

07.10.2014, 19:16:35

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

Как оптимизировать запрос?

#38769663

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

mdlv,

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

...

Рейтинг:

0 / 0

07.10.2014, 20:30:53

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

Как оптимизировать запрос?

#38769710

Misha Tyurin

Участник

Откуда: Тюмень

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

Рейтинг: 0 / 0

mdlv,

> 7.4

как так? 11 лет назад сняли с поддержки

...

Рейтинг:

0 / 0

07.10.2014, 21:41:00

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

Как оптимизировать запрос?

#38769842

V&N

Гость

mdlv, намудрили ... вникать облом, попробуйте так.

Код: plsql

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.

create or replace function array_to_table(bigint[]) returns setof bigint language plpgsql
as'
declare
  a alias for $1;
  i bigint;
begin
  if a is null or array_upper(a, 1) is null then
    return;
  end if;
  for i in 1..array_upper(a, 1) loop
    return next a[i];
  end loop;
  return;
end;';

SELECT items_reviews_id,
       to_char(dt, 'YYYY-MM-DD HH24:MI:SS') as dt,
       sender_id, receiver_id, status_id,
       sender.autos_text as sender,
       receiver.autos_text as receiver,
       (types_name || ' N' || t_id::TEXT) as t_id,
       array_to_string(ARRAY(SELECT i.item_name::TEXT || ' ' || i.items_val::TEXT || ' ед.' ||
                                    CASE WHEN lower(COALESCE(t.treviews_name::TEXT,'Unknown'))='unknown' THEN '' ELSE ' ' || t.treviews_name::TEXT END
                               FROM array_to_table(items) x(i) 
                               JOIN items i ON i.items_id = x.i 
                               LEFT JOIN treviews t ON t.treviews_id = i.treviews_id), '; ') as items,
    /*array_to_string(arr2arr( items,
    COALESCE('SELECT item_name::TEXT || '' '' || items_val::TEXT || '' ед.'' ||
      CASE WHEN lower(COALESCE(treviews.treviews_name::TEXT,''Unknown''))=''unknown'' THEN '''' ELSE
        '' '' || treviews.treviews_name::TEXT END
      FROM items
      LEFT JOIN treviews ON items.treviews_id = treviews.treviews_id
      WHERE items_id = $$', '{}')),'; ') as items,*/
    is_usual
FROM items_reviews
LEFT JOIN types ON types.types_id = items_reviews.types_id
LEFT JOIN autos sender ON items_reviews.sender_id = sender.autos_id
LEFT JOIN autos receiver ON items_reviews.receiver_id = receiver.autos_id
GROUP BY items_reviews.items_reviews_id, items_reviews.dt, items_reviews.sender_id, items_reviews.receiver_id, items_reviews.status_id,  sender.autos_text, receiver.autos_text, types.types_name, items_reviews.t_id, items_reviews.is_usual, items_reviews.items

...

Рейтинг:

0 / 0

08.10.2014, 05:39:47

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

Как оптимизировать запрос?

#38769943

mdlv

Гость

Alexius, я бы и рад обновить, но вот такая ситуёвина, приходится работать именно с этой версией. Функция странная, согласен)
Без нее побыстрее

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

"Group  (cost=4.14..4.19 rows=1 width=177) (actual time=0.344..0.345 rows=1 loops=1)"
"  ->  Sort  (cost=4.14..4.15 rows=1 width=177) (actual time=0.269..0.270 rows=1 loops=1)"
"        Sort Key: items_reviews.items_reviews_id, items_reviews.dt, items_reviews.sender_id, items_reviews.receiver_id, items_reviews.status_id, sender.autos_text, receiver.autos_text, types.types_name, items_reviews.t_id, items_reviews.is_usual, items_reviews.items"
"        ->  Hash Left Join  (cost=2.05..4.13 rows=1 width=177) (actual time=0.226..0.244 rows=1 loops=1)"
"              Hash Cond: ("outer".receiver_id = "inner".autos_id)"
"              ->  Hash Left Join  (cost=1.02..3.09 rows=1 width=145) (actual time=0.088..0.103 rows=1 loops=1)"
"                    Hash Cond: ("outer".sender_id = "inner".autos_id)"
"                    ->  Nested Loop Left Join  (cost=0.00..2.06 rows=1 width=113) (actual time=0.057..0.070 rows=1 loops=1)"
"                          Join Filter: ("inner".types_id = "outer".types_id)"
"                          ->  Seq Scan on items_reviews  (cost=0.00..1.01 rows=1 width=89) (actual time=0.011..0.013 rows=1 loops=1)"
"                          ->  Seq Scan on types  (cost=0.00..1.02 rows=2 width=40) (actual time=0.003..0.008 rows=2 loops=1)"
"                    ->  Hash  (cost=1.02..1.02 rows=2 width=40) (actual time=0.014..0.014 rows=0 loops=1)"
"                          ->  Seq Scan on autos sender  (cost=0.00..1.02 rows=2 width=40) (actual time=0.003..0.007 rows=2 loops=1)"
"              ->  Hash  (cost=1.02..1.02 rows=2 width=40) (actual time=0.075..0.075 rows=0 loops=1)"
"                    ->  Seq Scan on autos receiver  (cost=0.00..1.02 rows=2 width=40) (actual time=0.061..0.067 rows=2 loops=1)"
"Total runtime: 0.608 ms"

Это если вообще убрать из выборки

Код: plsql

1.
2.
3.
4.
5.
6.
7.

array_to_string(arr2arr( items,
    COALESCE('SELECT item_name::TEXT || '' '' || items_val::TEXT || '' ед.'' ||
      CASE WHEN lower(COALESCE(treviews.treviews_name::TEXT,''Unknown''))=''unknown'' THEN '''' ELSE
        '' '' || treviews.treviews_name::TEXT END
      FROM items
      LEFT JOIN treviews ON items.treviews_id = treviews.treviews_id
      WHERE items_id = $$', '{}')),'; ') as items

В примере выше в массиве было всего 2 элемента.

...

Рейтинг:

0 / 0

08.10.2014, 09:49:51

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

Как оптимизировать запрос?

#38769948

mdlv

Гость

Misha Tyurinmdlv,

> 7.4

как так? 11 лет назад сняли с поддержки

Госсектор...)) Мы впереди планеты всей. Я бы и рад обновить, но, к сожалению, все завязано на этой версии.

...

Рейтинг:

0 / 0

08.10.2014, 09:52:14

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

Как оптимизировать запрос?

#38769957

mdlv

Гость

V&N mdlv, намудрили ... вникать облом, попробуйте так.

Код: plsql

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.

create or replace function array_to_table(bigint[]) returns setof bigint language plpgsql
as'
declare
  a alias for $1;
  i bigint;
begin
  if a is null or array_upper(a, 1) is null then
    return;
  end if;
  for i in 1..array_upper(a, 1) loop
    return next a[i];
  end loop;
  return;
end;';

SELECT items_reviews_id,
       to_char(dt, 'YYYY-MM-DD HH24:MI:SS') as dt,
       sender_id, receiver_id, status_id,
       sender.autos_text as sender,
       receiver.autos_text as receiver,
       (types_name || ' N' || t_id::TEXT) as t_id,
       array_to_string(ARRAY(SELECT i.item_name::TEXT || ' ' || i.items_val::TEXT || ' ед.' ||
                                    CASE WHEN lower(COALESCE(t.treviews_name::TEXT,'Unknown'))='unknown' THEN '' ELSE ' ' || t.treviews_name::TEXT END
                               FROM array_to_table(items) x(i) 
                               JOIN items i ON i.items_id = x.i 
                               LEFT JOIN treviews t ON t.treviews_id = i.treviews_id), '; ') as items,
    /*array_to_string(arr2arr( items,
    COALESCE('SELECT item_name::TEXT || '' '' || items_val::TEXT || '' ед.'' ||
      CASE WHEN lower(COALESCE(treviews.treviews_name::TEXT,''Unknown''))=''unknown'' THEN '''' ELSE
        '' '' || treviews.treviews_name::TEXT END
      FROM items
      LEFT JOIN treviews ON items.treviews_id = treviews.treviews_id
      WHERE items_id = $$', '{}')),'; ') as items,*/
    is_usual
FROM items_reviews
LEFT JOIN types ON types.types_id = items_reviews.types_id
LEFT JOIN autos sender ON items_reviews.sender_id = sender.autos_id
LEFT JOIN autos receiver ON items_reviews.receiver_id = receiver.autos_id
GROUP BY items_reviews.items_reviews_id, items_reviews.dt, items_reviews.sender_id, items_reviews.receiver_id, items_reviews.status_id,  sender.autos_text, receiver.autos_text, types.types_name, items_reviews.t_id, items_reviews.is_usual, items_reviews.items

Спасибо. Ваша функция куда дружелюбнее выглядит) К сожалению, быстрее работать не стало. Результат:

Код: plsql

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.

"Group  (cost=4.14..24.23 rows=1 width=177) (actual time=1.873..1.874 rows=1 loops=1)"
"  ->  Sort  (cost=4.14..4.15 rows=1 width=177) (actual time=0.184..0.185 rows=1 loops=1)"
"        Sort Key: items_reviews.items_reviews_id, items_reviews.dt, items_reviews.sender_id, items_reviews.receiver_id, items_reviews.status_id, sender.autos_text, receiver.autos_text, types.types_name, items_reviews.t_id, items_reviews.is_usual, items_reviews.items"
"        ->  Hash Left Join  (cost=2.05..4.13 rows=1 width=177) (actual time=0.113..0.131 rows=1 loops=1)"
"              Hash Cond: ("outer".receiver_id = "inner".autos_id)"
"              ->  Hash Left Join  (cost=1.02..3.09 rows=1 width=145) (actual time=0.065..0.079 rows=1 loops=1)"
"                    Hash Cond: ("outer".sender_id = "inner".autos_id)"
"                    ->  Nested Loop Left Join  (cost=0.00..2.06 rows=1 width=113) (actual time=0.031..0.043 rows=1 loops=1)"
"                          Join Filter: ("inner".types_id = "outer".types_id)"
"                          ->  Seq Scan on items_reviews  (cost=0.00..1.01 rows=1 width=89) (actual time=0.003..0.004 rows=1 loops=1)"
"                          ->  Seq Scan on types  (cost=0.00..1.02 rows=2 width=40) (actual time=0.007..0.011 rows=2 loops=1)"
"                    ->  Hash  (cost=1.02..1.02 rows=2 width=40) (actual time=0.014..0.014 rows=0 loops=1)"
"                          ->  Seq Scan on autos sender  (cost=0.00..1.02 rows=2 width=40) (actual time=0.003..0.007 rows=2 loops=1)"
"              ->  Hash  (cost=1.02..1.02 rows=2 width=40) (actual time=0.024..0.024 rows=0 loops=1)"
"                    ->  Seq Scan on autos receiver  (cost=0.00..1.02 rows=2 width=40) (actual time=0.011..0.017 rows=2 loops=1)"
"  SubPlan"
"    ->  Hash Left Join  (cost=2.05..20.04 rows=11 width=68) (actual time=1.572..1.586 rows=2 loops=1)"
"          Hash Cond: ("outer".treviews_id = "inner".treviews_id)"
"          ->  Hash Join  (cost=1.02..18.63 rows=11 width=44) (actual time=1.425..1.431 rows=2 loops=1)"
"                Hash Cond: ("outer".i = "inner".items_id)"
"                ->  Function Scan on array_to_table x  (cost=0.00..12.50 rows=1000 width=8) (actual time=1.390..1.392 rows=2 loops=1)"
"                ->  Hash  (cost=1.02..1.02 rows=2 width=52) (actual time=0.021..0.021 rows=0 loops=1)"
"                      ->  Seq Scan on items i  (cost=0.00..1.02 rows=2 width=52) (actual time=0.009..0.013 rows=2 loops=1)"
"          ->  Hash  (cost=1.02..1.02 rows=2 width=40) (actual time=0.019..0.019 rows=0 loops=1)"
"                ->  Seq Scan on treviews t  (cost=0.00..1.02 rows=2 width=40) (actual time=0.009..0.013 rows=2 loops=1)"
"Total runtime: 2.126 ms"

...

Рейтинг:

0 / 0

08.10.2014, 09:58:18

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

Как оптимизировать запрос?

#38769963

mdlv

Гость

V&N,

что-то проглючило, вот результат

Код: plsql

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.

"Group  (cost=4.14..24.23 rows=1 width=177) (actual time=0.389..0.390 rows=1 loops=1)"
"  ->  Sort  (cost=4.14..4.15 rows=1 width=177) (actual time=0.120..0.121 rows=1 loops=1)"
"        Sort Key: items_reviews.items_reviews_id, items_reviews.dt, items_reviews.sender_id, items_reviews.receiver_id, items_reviews.status_id, sender.autos_text, receiver.autos_text, types.types_name, items_reviews.t_id, items_reviews.is_usual, items_reviews.items"
"        ->  Hash Left Join  (cost=2.05..4.13 rows=1 width=177) (actual time=0.082..0.100 rows=1 loops=1)"
"              Hash Cond: ("outer".receiver_id = "inner".autos_id)"
"              ->  Hash Left Join  (cost=1.02..3.09 rows=1 width=145) (actual time=0.047..0.061 rows=1 loops=1)"
"                    Hash Cond: ("outer".sender_id = "inner".autos_id)"
"                    ->  Nested Loop Left Join  (cost=0.00..2.06 rows=1 width=113) (actual time=0.021..0.033 rows=1 loops=1)"
"                          Join Filter: ("inner".types_id = "outer".types_id)"
"                          ->  Seq Scan on items_reviews  (cost=0.00..1.01 rows=1 width=89) (actual time=0.003..0.004 rows=1 loops=1)"
"                          ->  Seq Scan on types  (cost=0.00..1.02 rows=2 width=40) (actual time=0.003..0.006 rows=2 loops=1)"
"                    ->  Hash  (cost=1.02..1.02 rows=2 width=40) (actual time=0.014..0.014 rows=0 loops=1)"
"                          ->  Seq Scan on autos sender  (cost=0.00..1.02 rows=2 width=40) (actual time=0.002..0.007 rows=2 loops=1)"
"              ->  Hash  (cost=1.02..1.02 rows=2 width=40) (actual time=0.022..0.022 rows=0 loops=1)"
"                    ->  Seq Scan on autos receiver  (cost=0.00..1.02 rows=2 width=40) (actual time=0.009..0.014 rows=2 loops=1)"
"  SubPlan"
"    ->  Hash Left Join  (cost=2.05..20.04 rows=11 width=68) (actual time=0.201..0.224 rows=2 loops=1)"
"          Hash Cond: ("outer".treviews_id = "inner".treviews_id)"
"          ->  Hash Join  (cost=1.02..18.63 rows=11 width=44) (actual time=0.113..0.124 rows=2 loops=1)"
"                Hash Cond: ("outer".i = "inner".items_id)"
"                ->  Function Scan on array_to_table x  (cost=0.00..12.50 rows=1000 width=8) (actual time=0.081..0.084 rows=2 loops=1)"
"                ->  Hash  (cost=1.02..1.02 rows=2 width=52) (actual time=0.016..0.016 rows=0 loops=1)"
"                      ->  Seq Scan on items i  (cost=0.00..1.02 rows=2 width=52) (actual time=0.006..0.010 rows=2 loops=1)"
"          ->  Hash  (cost=1.02..1.02 rows=2 width=40) (actual time=0.021..0.021 rows=0 loops=1)"
"                ->  Seq Scan on treviews t  (cost=0.00..1.02 rows=2 width=40) (actual time=0.010..0.014 rows=2 loops=1)"
"Total runtime: 0.546 ms"

Так намного лучше, спасибо)

...

Рейтинг:

0 / 0

08.10.2014, 10:01:36

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

Как оптимизировать запрос?

#38769989

mdlv

Гость

V&N, спасибо огромное Вам!!! Попробовал на реальной базе, уже стало 216 мс против 1442.

...

Рейтинг:

0 / 0

08.10.2014, 10:20:54

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

Как оптимизировать запрос?

#38770133

V&N

Гость

mdlv, я не знаю "назначения" массива,
проанализируйте данные, важен ли порядок следования элементов, есть ли дубликаты,
возможно функция не нужна и все можно свести к банальному i.items_id = ANY(items_reviews.items) ?

Код: plsql

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.

SELECT items_reviews_id,
       to_char(dt, 'YYYY-MM-DD HH24:MI:SS') as dt,
       sender_id, receiver_id, status_id,
       sender.autos_text as sender,
       receiver.autos_text as receiver,
       (types_name || ' N' || t_id::TEXT) as t_id,
       array_to_string(ARRAY(SELECT i.item_name::TEXT || ' ' || i.items_val::TEXT || ' ед.' ||
                                    CASE WHEN lower(COALESCE(t.treviews_name::TEXT,'Unknown'))='unknown' THEN '' ELSE ' ' || t.treviews_name::TEXT END
                               FROM items i
                               LEFT JOIN treviews t ON t.treviews_id = i.treviews_id
                              WHERE i.items_id = ANY(items_reviews.items)
                             ORDER BY i.item_name, i.items_val), '; ') as items,
    /*array_to_string(arr2arr( items,
    COALESCE('SELECT item_name::TEXT || '' '' || items_val::TEXT || '' ед.'' ||
      CASE WHEN lower(COALESCE(treviews.treviews_name::TEXT,''Unknown''))=''unknown'' THEN '''' ELSE
        '' '' || treviews.treviews_name::TEXT END
      FROM items
      LEFT JOIN treviews ON items.treviews_id = treviews.treviews_id
      WHERE items_id = $$', '{}')),'; ') as items,*/
    is_usual
FROM items_reviews
LEFT JOIN types ON types.types_id = items_reviews.types_id
LEFT JOIN autos sender ON items_reviews.sender_id = sender.autos_id
LEFT JOIN autos receiver ON items_reviews.receiver_id = receiver.autos_id
GROUP BY items_reviews.items_reviews_id, items_reviews.dt, items_reviews.sender_id, items_reviews.receiver_id, items_reviews.status_id,  sender.autos_text, receiver.autos_text, types.types_name, items_reviews.t_id, items_reviews.is_usual, items_reviews.items

...

Рейтинг:

0 / 0

08.10.2014, 11:40:17

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

Как оптимизировать запрос?

#38770227

mdlv

Гость

V&N mdlv, я не знаю "назначения" массива,
проанализируйте данные, важен ли порядок следования элементов, есть ли дубликаты,
возможно функция не нужна и все можно свести к банальному i.items_id = ANY(items_reviews.items) ?

Вы гений! Спасибо, да, можно свести к этому самому банальному) Дубликатов нет, порядок важен. Массив используется для вывода списка одной строкой, просто в виде текста.

...

Рейтинг:

0 / 0

08.10.2014, 12:30:55

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

Как оптимизировать запрос?

#38770287

mdlv

Гость

V&N, вариант с функцией получился быстрее, чем с ANY.

...

Рейтинг:

0 / 0

08.10.2014, 12:49:01

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

15 сообщений из 15, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как оптимизировать запрос?

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

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

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

start [/forum/topic.php?fid=53&msg=38769948&tid=1998444]:	0ms
get settings:	11ms
get forum list:	18ms
check forum access:	4ms
check topic access:	4ms
track hit:	209ms
get topic data:	11ms
get forum data:	3ms
get page messages:	75ms
get tp. blocked users:	1ms
others:	249ms

total:	585ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы