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

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
	SELECT
		org_id,
		COUNT(id) AS img_total,
		COUNT(CASE WHEN status = 0 THEN 1 ELSE NULL END) AS status0,
		COUNT(CASE WHEN status = 1 THEN 1 ELSE NULL END) AS status1,
		COUNT(CASE WHEN status = 2 THEN 1 ELSE NULL END) AS status2,
		COUNT(CASE WHEN status = 3 THEN 1 ELSE NULL END) AS status3,
		TRUE
	FROM org_item
	GROUP BY org_id



Текущий план выполнения:

Код: plaintext
1.
2.
3.
HashAggregate  (cost=34685.19..34703.57 rows=1837 width=18) (actual time=383.486..384.107 rows=1857 loops=1)
  ->  Seq Scan on org_item  (cost=0.00..27670.77 rows=280577 width=18) (actual time=0.029..129.736 rows=280577 loops=1)
Total runtime: 384.342 ms
...
Рейтинг: 0 / 0
05.03.2015, 14:47
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38896099
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
нет. Ну только если img_total вам не нужен ну и статусы высокоселективны
...
Рейтинг: 0 / 0
05.03.2015, 15:01
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38896130
Фотография chernomyrdin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chernomyrdin
Участник
В принципе можно пожертвовать total, я его позже посчитаю как сумму статусов.
Какие в подобной ситуации возможны варианты?
...
Рейтинг: 0 / 0
05.03.2015, 15:17
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38896140
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
chernomyrdinВ принципе можно пожертвовать total, я его позже посчитаю как сумму статусов.
Какие в подобной ситуации возможны варианты?
индекс на статус, если статусы высокоселективны и полно других статусов > 3
и тогда where status between 0 and 3 сгенерит поиск по индексу.
...
Рейтинг: 0 / 0
05.03.2015, 15:20
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38896158
Лопата
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Лопата
Участник
chernomyrdinВ принципе можно пожертвовать total, я его позже посчитаю как сумму статусов.
Какие в подобной ситуации возможны варианты?
ничего существенного вы не выиграете,
но если по мелочи на операциях -- сначала
Код: sql
1.
 GROP BY org_id, status


, и только потом -- пивотинг case-ами над готовыми агрегатами.
Код: sql
1.
2.
,SUM (CASE WHEN status = 0 THEN img_total END) AS status0
...



(если есть покрывающий оба поля индекс, и визибилити мап как правило актуальна -- можно ещё немного отжать, копейки -- на IOS)
...
Рейтинг: 0 / 0
05.03.2015, 15:28
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38896160
Лопата
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Лопата
Участник
Ivan DurakchernomyrdinВ принципе можно пожертвовать total, я его позже посчитаю как сумму статусов.
Какие в подобной ситуации возможны варианты?
индекс на статус, если статусы высокоселективны и полно других статусов > 3
и тогда where status between 0 and 3 сгенерит поиск по индексу.у него тотал -- это сумма по остальным, т.ч. только на операциях при агрегировании можно чутка сэкономить. И в 9.3 -- на IOS--агрегате.
...
Рейтинг: 0 / 0
05.03.2015, 15:30
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38896511
Фотография chernomyrdin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chernomyrdin
Участник
Возможно я не прав, возможно я в вел почтенную публику в заблуждение.
Смысл всей этой затеи в том, что есть организации у каждой их них есть некторое количество итемов пребывающих в неких состояниях, соответственно хотелось-бы получить одним запросом количество итемов в тех или иных состояниях для всех организаций.

Как таковой total не интересет так как он легко получается из суммы всех статусов организации.

Как мне показалось запрос приведенный в начале ветки, самый простой, который решает задачу "в лоб",
Возможно так будет оптимальнее:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SELECT
	org_id,
	SUM(count) AS total,
	SUM(CASE WHEN status = 0 THEN count ELSE 0 END) AS status0,
	SUM(CASE WHEN status = 1 THEN count ELSE 0 END) AS status1,
	SUM(CASE WHEN status = 2 THEN count ELSE 0 END) AS status2,
	SUM(CASE WHEN status = 3 THEN count ELSE 0 END) AS status3
FROM (
	SELECT org_id, status, count(*) as count
	FROM org_item
	GROUP BY org_id, status
) AS status
GROUP BY org_id



Но все равно план выполнения:

Код: plaintext
1.
2.
3.
HashAggregate  (cost=30023.09..30025.09 rows=200 width=18) (actual time=278.918..279.648 rows=1862 loops=1)
  ->  HashAggregate  (cost=29775.10..29830.21 rows=5511 width=10) (actual time=273.322..274.256 rows=2687 loops=1)
        ->  Seq Scan on org_item  (cost=0.00..27670.77 rows=280577 width=10) (actual time=0.057..118.753 rows=281149 loops=1)
Total runtime: 280.044 ms

Да, индекс покрывающий org_id + status имеется:

Код: plsql
1.
CREATE INDEX org_item_idx_org_status ON org_item USING btree (org_id, status);
...
Рейтинг: 0 / 0
05.03.2015, 19:25
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38896523
Фотография chernomyrdin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chernomyrdin
Участник
Еще один вариант решения, оно использует индекс, но все равно работает медленнее:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT
	item.org_id,
	count(*) as total,
	(SELECT count(*) FROM org_item WHERE org_id = item.org_id AND status = 0) AS status0,
	(SELECT count(*) FROM org_item WHERE org_id = item.org_id AND status = 1) AS status1,
	(SELECT count(*) FROM org_item WHERE org_id = item.org_id AND status = 2) AS status2,
	(SELECT count(*) FROM org_item WHERE org_id = item.org_id AND status = 3) AS status3
FROM org_item as item
GROUP BY org_id



План выполнения:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
HashAggregate  (cost=29073.66..484895.65 rows=1837 width=8) (actual time=216.277..396.833 rows=1862 loops=1)"
  ->  Seq Scan on org_item item  (cost=0.00..27670.77 rows=280577 width=8) (actual time=0.014..111.748 rows=281149 loops=1)
  SubPlan 1
    ->  Aggregate  (cost=40.80..40.81 rows=1 width=0) (actual time=0.020..0.020 rows=1 loops=1862)
          ->  Index Only Scan using org_item_idx_org_status on org_item  (cost=0.42..40.74 rows=24 width=0) (actual time=0.008..0.017 rows=24 loops=1862)
                Index Cond: ((org_id = item.org_id) AND (status = 0))
                Heap Fetches: 16251
  SubPlan 2
    ->  Aggregate  (cost=8.44..8.45 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1862)
          ->  Index Only Scan using org_item_idx_org_status on org_item org_item_1  (cost=0.42..8.44 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1862)
                Index Cond: ((org_id = item.org_id) AND (status = 1))
                Heap Fetches: 495
  SubPlan 3
    ->  Aggregate  (cost=8.44..8.45 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1862)
          ->  Index Only Scan using org_item_idx_org_status on org_item org_item_2  (cost=0.42..8.44 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1862)
                Index Cond: ((org_id = item.org_id) AND (status = 2))
                Heap Fetches: 0
  SubPlan 4
    ->  Aggregate  (cost=190.39..190.40 rows=1 width=0) (actual time=0.068..0.068 rows=1 loops=1862)
          ->  Index Only Scan using org_item_idx_org_status on org_item org_item_3  (cost=0.42..190.07 rows=128 width=0) (actual time=0.007..0.056 rows=126 loops=1862)
                Index Cond: ((org_id = item.org_id) AND (status = 3))
                Heap Fetches: 86943
Total runtime: 397.326 ms
...
Рейтинг: 0 / 0
05.03.2015, 19:46
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38896752
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
chernomyrdin,

а можно explain analyze этого запроса с set enable_seqscan = off; ?

Код: sql
1.
2.
3.
	SELECT org_id, status, count(*) as count
	FROM org_item
	GROUP BY org_id, status



если будет медленней (наверняка будет), чем с seq scan, то сильно улучшить скорей всего не получится.
...
Рейтинг: 0 / 0
06.03.2015, 06:56
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38896780
этта
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
этта
Гость
Alexiuschernomyrdin,

а можно explain analyze этого запроса с set enable_seqscan = off; ?

Код: sql
1.
2.
3.
	SELECT org_id, status, count(*) as count
	FROM org_item
	GROUP BY org_id, status



если будет медленней (наверняка будет), чем с seq scan, то сильно улучшить скорей всего не получится.сильно улучшить не получится вообще . только если записи таблицы много шире записи индекса, и, при этом, карта видимости -- актуальна почти вся. И то это бцло бы константное улучшение, а не логарифмическое или степенное. Если же сама таблица узкая -- то выигрыша практически не будет (там мелочь на операциях, и на том, что группировать по инлдексу дешевле на dort/hash или т.п.


и интересно, какая версия пж у тс.
9.3. по однопольному индексу умеет IOS count-ы группировать. по составному не проверял. а в 9.2 не задалось (на той же структуре).
...
Рейтинг: 0 / 0
06.03.2015, 08:08
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38896781
этта
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
этта
Гость
этта дешевле на dort/hash или т.п.
Sort/hash

очепятався, пртстт
...
Рейтинг: 0 / 0
06.03.2015, 08:10
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38896828
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
Да, принципиально быстрее никак не будет. Тут принципиально - или уже переходить на что-то инмемори. Или держать предрасчитанную таблицу с количествами. Или не считать сразу по всем-всем организациям.
...
Рейтинг: 0 / 0
06.03.2015, 09:22
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38896842
Фотография chernomyrdin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chernomyrdin
Участник
Alexius,

Вот что получается:

Код: plsql
1.
2.
3.
4.
5.
SET enable_seqscan = off;
EXPLAIN ANALYZE
SELECT org_id, status, count(*) as count
FROM org_item
GROUP BY org_id, status;



Код: plaintext
1.
2.
3.
GroupAggregate  (cost=0.42..168390.83 rows=5511 width=10) (actual time=0.159..137.349 rows=2690 loops=1)
  ->  Index Only Scan using org_item_idx_org_status on org_item  (cost=0.42..166231.39 rows=280577 width=10) (actual time=0.025..86.855 rows=281154 loops=1)
        Heap Fetches: 103289
Total runtime: 137.551 ms

Для сравнения:

Код: plsql
1.
2.
3.
4.
5.
SET enable_seqscan = on;
EXPLAIN ANALYZE
SELECT org_id, status, count(*) as count
FROM org_item
GROUP BY org_id, status;



Код: plaintext
1.
2.
3.
HashAggregate  (cost=29775.10..29830.21 rows=5511 width=10) (actual time=266.897..267.801 rows=2690 loops=1)
  ->  Seq Scan on org_item  (cost=0.00..27670.77 rows=280577 width=10) (actual time=0.009..104.857 rows=281154 loops=1)
Total runtime: 268.166 ms
...
Рейтинг: 0 / 0
06.03.2015, 09:33
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38897330
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
все-таки ios иногда спасает. ~140мс в принципе хорошее время (агрегация сверху еще пару ms добавит).

если база всегда в памяти или диски ssd, можно осторожно подкрутить random_page_cost, чтобы правильный план выбирался. или прописать хак с enable_seqscan в коде/хранимке.

можно еще проверить на всякий случай что автовакуум нормально работает и dead rows оперативно подчищаются.
...
Рейтинг: 0 / 0
06.03.2015, 15:16
| Ответить | Цитировать | Написать  
Оптимизация GROUP BY
    #38897662
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
chernomyrdinAlexius,

Вот что получается:

Код: plsql
1.
2.
3.
4.
5.
SET enable_seqscan = off;
EXPLAIN ANALYZE
SELECT org_id, status, count(*) as count
FROM org_item
GROUP BY org_id, status;



попробуй заменить count(*) на count(org_id) может сам догадается ios сделавть
...
Рейтинг: 0 / 0
06.03.2015, 21:40
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация GROUP BY
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=38896066&tid=1998134]:
 0ms
get settings:
 9ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 28ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 54ms
get tp. blocked users:
 1ms
others: 207ms
total:  334ms
созд. / загр.: 334ms
x
x
Закрыть


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