ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса.
5 сообщений из 5, страница 1 из 1
Оптимизация запроса.
    #38985852
indexrequest
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
indexrequest
Гость
Привет.
Нужна помощь в оптимизации запрос.

Таблица
bigint | text | text | text | | int | int | bool | text | bigint | date |
id | table_owner_id | user_id | app_id | creation_time | type | subtype | looked | parameters | grouped_type | creation_date|

Запрос:

SELECT creation_date, grouped_type, user_id, max(creation_time) ,
array_agg(id), array_agg(table_owner_id), array_agg(user_id), array_agg(app_id), array_agg(creation_time), array_agg(type),
array_agg(subtype), array_agg(looked), array_agg(parameters) FROM table WHERE table_owner_id='owner'
GROUP BY 1,2,3
ORDER BY 1 DESC, 4 DESC
LIMIT 5 OFFSET 0;

Индексы:
"table_pkey" PRIMARY KEY, btree (id)
"table_date_asc_grouped_type_user_id" btree (creation_date, grouped_type, user_id)
"table_date_desc_grouped_type_user_id" btree (creation_date DESC, grouped_type, user_id)
"table_feed_owner_idx" btree (table_owner_id)

В таком виде индексы не используются (об этом говорит explain analyze) и запрос работает достаточно медленно (таблица 300к записей, время 2-4 сек).
Если убрать max(creation_time) и ORDER BY 4 DESC - в этом случае работает индексы table_date_asc_grouped_type_user_id & table_date_desc_grouped_type_user_id (время выполнения - меньше 100 мс.), но результат группировки конечно же "ломается".

Возможно ли каким то образом оптимизировать запрос (может добавить какие то вспомогательные колонки или еще что) с сохранением функционала?
...
Рейтинг: 0 / 0
17.06.2015, 14:37
| Ответить | Цитировать | Написать  
Оптимизация запроса.
    #38985910
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
indexrequest,

Вы бы чтоли результаты explain analyze для обоих случаев привели.
Что то я сомневаюсь что:
авторЕсли убрать max(creation_time) и ORDER BY 4 DESC - в этом случае работает индексы table_date_asc_grouped_type_user_id & table_date_desc_grouped_type_user_id (время выполнения - меньше 100 мс.), но результат группировки конечно же "ломается".
Ну и как ни делай всеравно вам все строки пересчитывать у которых table_owner_id='owner'.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
17.06.2015, 15:25
| Ответить | Цитировать | Написать  
Оптимизация запроса.
    #38985958
indexrequest
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
indexrequest
Гость
Maxim Bogukindexrequest,

Вы бы чтоли результаты explain analyze для обоих случаев привели.
1)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
explain (analyze true, format text) SELECT creation_date, grouped_type, user_id, max(creation_time),
array_agg(id), array_agg(table_owner_id), array_agg(user_id), array_agg(app_id), array_agg(creation_time), array_agg(type), 
array_agg(subtype), array_agg(looked), array_agg(parameters) FROM table WHERE table_owner_id='owner' 
GROUP BY 1,2,3 
ORDER BY 1 DESC, 4 DESC
LIMIT 5 OFFSET 0;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=956074.05..956074.06 rows=5 width=183) (actual time=22921.863..22921.864 rows=5 loops=1)
   ->  Sort  (cost=956074.05..956374.05 rows=120000 width=183) (actual time=22921.861..22921.861 rows=5 loops=1)
         Sort Key: creation_date, (max(creation_time))
         Sort Method: top-N heapsort  Memory: 29kB
         ->  GroupAggregate  (cost=875693.61..954080.89 rows=120000 width=183) (actual time=14456.739..22646.709 rows=576534 loops=1)
               ->  Sort  (cost=875693.61..881014.13 rows=2128208 width=183) (actual time=14456.632..18839.425 rows=860437 loops=1)
                     Sort Key: creation_date, grouped_type, user_id
                     Sort Method: external merge  Disk: 170944kB
                     ->  Seq Scan on table (cost=0.00..84617.60 rows=2128208 width=183) (actual time=0.042..821.030 rows=860437 loops=1)
                           Filter: ((table_owner_id)::text = 'owner'::text)
 Total runtime: 22925.334 ms




2)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
explain (analyze true, format text) SELECT creation_date, grouped_type, user_id,  
array_agg(id), array_agg(table_owner_id), array_agg(user_id), array_agg(app_id), array_agg(creation_time), array_agg(type), 
array_agg(subtype), array_agg(looked), array_agg(parameters) FROM table WHERE table_owner_id='table' 
GROUP BY 1,2,3 
ORDER BY 1 DESC        
LIMIT 5 OFFSET 0;
                                                                                            QUERY PLAN                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.55..174.93 rows=5 width=183) (actual time=1307.459..1307.541 rows=5 loops=1)
   ->  GroupAggregate  (cost=0.55..3728873.51 rows=106920 width=183) (actual time=1307.457..1307.539 rows=5 loops=1)
         ->  Index Scan using table_date_desc_sorted_type_user_id on table  (cost=0.55..3693322.67 rows=1069198 width=183) (actual time=1307.339..1307.402 rows=13 loops=1)
               Filter: ((table_owner_id)::text = 'owner'::text)
 Total runtime: 1307.718 ms




Что то я сомневаюсь что:
авторЕсли убрать max(creation_time) и ORDER BY 4 DESC - в этом случае работает индексы table_date_asc_grouped_type_user_id & table_date_desc_grouped_type_user_id (время выполнения - меньше 100 мс.), но результат группировки конечно же "ломается".
Ну и как ни делай всеравно вам все строки пересчитывать у которых table_owner_id='owner'.

--
Maxim Boguk
www.postgresql-consulting.ru
...
Рейтинг: 0 / 0
17.06.2015, 16:01
| Ответить | Цитировать | Написать  
Оптимизация запроса.
    #38985968
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
Maxim Bogukindexrequest,

Вы бы чтоли результаты explain analyze для обоих случаев привели.
Что то я сомневаюсь что:
авторЕсли убрать max(creation_time) и ORDER BY 4 DESC - в этом случае работает индексы table_date_asc_grouped_type_user_id & table_date_desc_grouped_type_user_id (время выполнения - меньше 100 мс.), но результат группировки конечно же "ломается".
Ну и как ни делай всеравно вам все строки пересчитывать у которых table_owner_id='owner'.

--
Maxim Boguk
www.postgresql-consulting.ru
есть конечно идея, если LIMIT 5 OFFSET 0 не будет, можно сперва отобрать
эти 5 наборов:
(
select creation_date, grouped_type, user_id
where table_owner_id='owner'
group by creation_date, grouped_type, user_id
LIMIT 5 OFFSET 0
)
А потом их сджойнить с основной таблицей с целью получить loop join и немного поисков по индексу.
...
Рейтинг: 0 / 0
17.06.2015, 16:09
| Ответить | Цитировать | Написать  
Оптимизация запроса.
    #38986114
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
index (table_owner_id,creation_date,creation_time)

+ идея ивана, в редакции примерно такой {псевдокод}:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
WITH s5 AS (
SELECT {group list} FROM t
WHERE table_owner_id = 'owner'::text
ORDER BY creation_date DESC,creation_time DESC LIMIT 5
)
SELECT s5.{group list},s5.creation_time
,L.{aggregate_list}
FROM s5
,LATERAL (SELECT {aggregate_list} FROM t WHERE t.{group list} =s5.{group list}) AS L
ORDER BY ....
...
Рейтинг: 0 / 0
17.06.2015, 17:46
| Ответить | Цитировать | Написать  
5 сообщений из 5, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=109&tid=1997929]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 15ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 34ms
get tp. blocked users:
 1ms
others: 242ms
total:  328ms
созд. / загр.: 328ms
x
x
Закрыть


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