улучшить время выполнения / PostgreSQL

ReSQL.ru

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

Гость

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

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

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

25 сообщений из 55, страница 2 из 3

все

улучшить время выполнения

#38724677

--поправил

Гость

best_time--поправилbest_time,

SHOW random_page_cost ; ?
4это дефолт, если не вру

тогда сразу всё прочее, как просит товарисч:

select name,setting,source from pg_settings where name ~ '^autovacuum|cost|enable';

...

Рейтинг:

0 / 0

20.08.2014, 15:38:03

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

улучшить время выполнения

#38724712

best_time

Гость

vyegorovbest_timevyegorov,

там 0 и NULL'ы - все равно важно?

Хм, если там NULL-ы, то у меня вопросы:
как статистики собираются в системе вообще?

что выдаст такой запрос

Код: sql

select name,setting,source from pg_settings where name ~ '^autovacuum|cost|enable';

как изменится план после запуска следующих команд

Код: sql

1.
2.
3.
4.
5.

VACUUM ANALYZE partitions.otahotel_2014w27;
VACUUM ANALYZE partitions.otahotel_2014w32;
VACUUM ANALYZE hotcore.otahotel;
VACUUM ANALYZE public.otahotel;
VACUUM ANALYZE public.region;

виноват - запускал на реплике.

Код: plaintext

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

   relid    | schemaname |     relname      | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | last_vacuum |        last_autovacuum        | last_analyze |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count 
------------+------------+------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+-------------+-------------------------------+--------------+-------------------------------+--------------+------------------+---------------+-------------------
 2520075393 | hotcore    | otahotel         |   253190 |            0 |      111 |             0 |         0 |         0 |         0 |             0 |          0 |          0 |             |                               |              |                               |            0 |                0 |             0 |                 0
 3414794803 | partitions | otahotel_2014w27 |       11 |            0 |      826 |            14 |         0 |         0 |         0 |             0 |          0 |          0 |             |                               |              |                               |            0 |                0 |             0 |                 0
 3570135254 | partitions | otahotel_2014w32 |      939 |   1779000316 |    19999 |    9976752347 |  65726055 |         0 |         0 |             0 |   65843802 |          0 |             | 2014-08-06 11:38:40.44619+00  |              | 2014-08-10 12:35:37.92968+00  |            0 |                2 |             0 |                37
 1586228924 | public     | otahotel         |    23232 |  15159936874 |  2505195 |    1166692195 |    521896 |    467107 |         0 |         22595 |    1009067 |       2421 |             | 2014-08-15 09:22:33.581164+00 |              | 2014-08-15 06:13:27.604283+00 |            0 |                5 |             0 |                 4
  367346872 | public     | region           |      661 |    104102332 | 15072071 |     126861899 |        28 |  11751186 |         0 |        591666 |     167886 |          0 |             | 2014-08-20 06:48:24.268671+00 |              | 2014-08-20 06:55:33.731531+00 |            0 |               56 |             0 |                31
(5 строк)

настройки:

Код: plaintext

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.

              name               |  setting  |       source       
---------------------------------+-----------+--------------------
 autovacuum                      | on        | default
 autovacuum_analyze_scale_factor | 0.1       | default
 autovacuum_analyze_threshold    | 50        | default
 autovacuum_freeze_max_age       | 200000000 | default
 autovacuum_max_workers          | 6         | configuration file
 autovacuum_naptime              | 60        | default
 autovacuum_vacuum_cost_delay    | 20        | default
 autovacuum_vacuum_cost_limit    | -1        | default
 autovacuum_vacuum_scale_factor  | 0.01      | configuration file
 autovacuum_vacuum_threshold     | 50        | default
 cpu_index_tuple_cost            | 0.005     | default
 cpu_operator_cost               | 0.0025    | default
 cpu_tuple_cost                  | 0.01      | default
 enable_bitmapscan               | on        | default
 enable_hashagg                  | on        | default
 enable_hashjoin                 | on        | default
 enable_indexonlyscan            | on        | default
 enable_indexscan                | on        | default
 enable_material                 | on        | default
 enable_mergejoin                | on        | default
 enable_nestloop                 | on        | default
 enable_seqscan                  | on        | default
 enable_sort                     | on        | default
 enable_tidscan                  | on        | default
 random_page_cost                | 4         | default
 seq_page_cost                   | 1         | default
 vacuum_cost_delay               | 0         | default
 vacuum_cost_limit               | 200       | default
 vacuum_cost_page_dirty          | 20        | default
 vacuum_cost_page_hit            | 1         | default
 vacuum_cost_page_miss           | 10        | default

ANALYZE - сейчас запущу

...

Рейтинг:

0 / 0

20.08.2014, 15:56:22

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

улучшить время выполнения

#38724724

--поправил

Гость

best_time,

сделайте, если не сложно

Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.

ANALYZE
with  tt --t
as
(
select h.id, NULL "name" ,h.olap_id,h.region_id,city_en
from
public.otahotel h
where olap_id>=1234
order by olap_id
limit 100

) /*,
tt as
(
select t.id,geo.name,region_id,city_en city
from t
join	public.region r
on	t.region_id = r.id
join	static.geo geo
on	geo.id = any(r.geo_id_array)
and geo.id in (1,2)
) --*/
select	'2014-08-04'::date report_period,
			av.otahotel_id,
			tt.region_id,
			tt.city,
			tt.name,
			--av.booking_window bw,
			--av.length_of_stay los,
			hotcore.parity_type(av.*, 'OTA') result,
			count(av.id) checks,
			sum(case when av.otahotel_price != 0 then 1 else 0 end) avail		
		from
		tt
		join	
		hotcore.otahotel av
		on      av.otahotel_id = tt.id
		where	av.report_date >='2014-08-04'
			and av.report_date < '2014-08-11'
		group 	by 1, 2, 3, 4, 5, 6

-- интересно посмотреть на то, что придумает оптимизатор про rows cost и метод

...

Рейтинг:

0 / 0

20.08.2014, 16:05:32

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

улучшить время выполнения

#38724751

best_time

Гость

--поправил,

Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.

with  tt
as
(
select h.id, NULL::TEXT "name", h.olap_id,h.region_id,city_en
from
public.otahotel h
where olap_id>=1234
order by olap_id
limit 100

)/*,

tt as
(
select t.id,geo.name,region_id,city_en city
from t
join	public.region r
on	t.region_id = r.id
join	static.geo geo
on	geo.id = any(r.geo_id_array)
and geo.id in (1,2)
)
*/
select	'2014-08-04'::date report_period,
			av.otahotel_id,
			tt.region_id,
			
			tt.name,
			--av.booking_window bw,
			--av.length_of_stay los,
			hotcore.parity_type(av.*, 'OTA') result,
			count(av.id) checks,
			sum(case when av.otahotel_price != 0 then 1 else 0 end) avail		
		from
		tt
		join	
		hotcore.otahotel av
		on      av.otahotel_id = tt.id
		where	av.report_date >='2014-08-04'
			and av.report_date < '2014-08-11'
		group 	by 1, 2, 3, 4, 5

Код: plaintext

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

GroupAggregate  (cost=14962660.09..16753007.59 rows=4000000 width=100)
  CTE tt
    ->  Limit  (cost=0.00..12.01 rows=100 width=43)
          ->  Index Scan using inx_u_otahotel_olap_id on otahotel h  (cost=0.00..121090.78 rows=1007859 width=43)
                Index Cond: (olap_id >= 1234)
  ->  Sort  (cost=14962648.07..15046020.02 rows=33348778 width=100)
        Sort Key: ('2014-08-04'::date), av.otahotel_id, tt.region_id, tt.name, (hotcore.parity_type(av.*, 'OTA'::text))
        ->  Nested Loop  (cost=0.00..8971767.25 rows=33348778 width=100)
              ->  CTE Scan on tt  (cost=0.00..2.00 rows=100 width=68)
              ->  Append  (cost=0.00..6329.56 rows=1615 width=64)
                    ->  Seq Scan on otahotel av  (cost=0.00..0.00 rows=1 width=572)
                          Filter: ((report_date >= '2014-08-04'::date) AND (report_date < '2014-08-11'::date) AND (tt.id = otahotel_id))
                    ->  Bitmap Heap Scan on otahotel_2014w32 av  (cost=68.61..6329.56 rows=1614 width=64)
                          Recheck Cond: (otahotel_id = tt.id)
                          Filter: ((report_date >= '2014-08-04'::date) AND (report_date < '2014-08-11'::date))
                          ->  Bitmap Index Scan on otahotel_2014w32_otahotel_id_idx  (cost=0.00..68.20 rows=1614 width=0)
                                Index Cond: (otahotel_id = tt.id)

...

Рейтинг:

0 / 0

20.08.2014, 16:26:01

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

улучшить время выполнения

#38724788

--поправил

Гость

best_time,

он таки упорствует

Код: sql

Nested Loop  (cost=0.00..8971767.25 rows=33348778 width=100)

а что , если вместо JOIN предложить ему агрегат от where exists (план должен быть похожий, ну а вдруг он устанет упорствовать
~пимерно так~

Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.

ANALYZE
with  tt --t
as
(
select h.id, NULL "name" ,h.olap_id,h.region_id,city_en
from
public.otahotel h
where olap_id>=1234
order by olap_id
limit 100

) /*,
tt as
(
select t.id,geo.name,region_id,city_en city
from t
join	public.region r
on	t.region_id = r.id
join	static.geo geo
on	geo.id = any(r.geo_id_array)
and geo.id in (1,2)
) --*/
select	'2014-08-04'::date report_period,
			av.otahotel_id,
/*			tt.region_id,
			tt.city,
			tt.name,
*/
			--av.booking_window bw,
			--av.length_of_stay los,
			hotcore.parity_type(av.*, 'OTA') result,
			count(av.id) checks,
			sum(case when av.otahotel_price != 0 then 1 else 0 end) avail		
		from
/*		tt
		join	*/
		hotcore.otahotel av
--		on      av.otahotel_id = tt.id
		where	av.report_date >='2014-08-04'
			and av.report_date < '2014-08-11'
AND EXISTS (SELECT 1 FROM tt WHERE av.otahotel_id = tt.id)
		group 	by 1, 2, 3 --, 4, 5, 6

...

Рейтинг:

0 / 0

20.08.2014, 16:47:25

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

улучшить время выполнения

#38724803

best_time

Гость

--поправилbest_time,

он таки упорствует

Код: sql

Nested Loop  (cost=0.00..8971767.25 rows=33348778 width=100)

Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.

ANALYZE
with  tt --t
as
(
select h.id, NULL "name" ,h.olap_id,h.region_id,city_en
from
public.otahotel h
where olap_id>=1234
order by olap_id
limit 100

) /*,
tt as
(
select t.id,geo.name,region_id,city_en city
from t
join	public.region r
on	t.region_id = r.id
join	static.geo geo
on	geo.id = any(r.geo_id_array)
and geo.id in (1,2)
) --*/
select	'2014-08-04'::date report_period,
			av.otahotel_id,
/*			tt.region_id,
			tt.city,
			tt.name,
*/
			--av.booking_window bw,
			--av.length_of_stay los,
			hotcore.parity_type(av.*, 'OTA') result,
			count(av.id) checks,
			sum(case when av.otahotel_price != 0 then 1 else 0 end) avail		
		from
/*		tt
		join	*/
		hotcore.otahotel av
--		on      av.otahotel_id = tt.id
		where	av.report_date >='2014-08-04'
			and av.report_date < '2014-08-11'
AND EXISTS (SELECT 1 FROM tt WHERE av.otahotel_id = tt.id)
		group 	by 1, 2, 3 --, 4, 5, 6

Код: plaintext

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

HashAggregate  (cost=9472012.18..9482412.18 rows=40000 width=64)
  CTE tt
    ->  Limit  (cost=0.00..12.01 rows=100 width=43)
          ->  Index Scan using inx_u_otahotel_olap_id on otahotel h  (cost=0.00..121090.78 rows=1007859 width=43)
                Index Cond: (olap_id >= 1234)
  ->  Nested Loop  (cost=2.25..8971768.50 rows=33348778 width=64)
        ->  HashAggregate  (cost=2.25..3.25 rows=100 width=32)
              ->  CTE Scan on tt  (cost=0.00..2.00 rows=100 width=32)
        ->  Append  (cost=0.00..6329.56 rows=1615 width=64)
              ->  Seq Scan on otahotel av  (cost=0.00..0.00 rows=1 width=572)
                    Filter: ((report_date >= '2014-08-04'::date) AND (report_date < '2014-08-11'::date) AND (tt.id = otahotel_id))
              ->  Bitmap Heap Scan on otahotel_2014w32 av  (cost=68.61..6329.56 rows=1614 width=64)
                    Recheck Cond: (otahotel_id = tt.id)
                    Filter: ((report_date >= '2014-08-04'::date) AND (report_date < '2014-08-11'::date))
                    ->  Bitmap Index Scan on otahotel_2014w32_otahotel_id_idx  (cost=0.00..68.20 rows=1614 width=0)
                          Index Cond: (otahotel_id = tt.id)

...

Рейтинг:

0 / 0

20.08.2014, 16:57:58

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

улучшить время выполнения

#38724805

$/\/\/\/\/\/\$

/\/\/\/\/\/\

Гость

--поправил,

Может, вот так в угадайку поиграем:

Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.

with  t
as
(
select h.id,h.olap_id,h.region_id,city_en
from
public.otahotel h
where olap_id>=1234
order by olap_id
limit 100

),
tt as
(
select t.id,geo.name,region_id,city_en city
from t
join	public.region r
on	t.region_id = r.id
join	static.geo geo
on	geo.id = any(r.geo_id_array)
and geo.id in (1,2)
)
SELECT	
  '2014-08-04'::date report_period,
  av.otahotel_id,
  (SELECT tt.region_id FROM tt WHERE tt.id = av.otahotel_id) AS region_id,
  (SELECT tt.city FROM tt WHERE tt.id = av.otahotel_id) AS city,
  (SELECT tt.name FROM tt WHERE tt.id = av.otahotel_id) AS name,
			--av.booking_window bw,
			--av.length_of_stay los,
  hotcore.parity_type(av.*, 'OTA') result,
  count(av.id) checks,
  sum(case when av.otahotel_price != 0 then 1 else 0 end) avail		
FROM hotcore.otahotel av
WHERE av.report_date >='2014-08-04'
  AND av.report_date < '2014-08-11'
  AND av.otahotel_id IN (SELECT id FROM tt)
GROUP BY 1, 2, 3, 4, 5, 6

...

Рейтинг:

0 / 0

20.08.2014, 16:58:18

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

улучшить время выполнения

#38724810

--поправил

Гость

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

...

Рейтинг:

0 / 0

20.08.2014, 17:03:21

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

улучшить время выполнения

#38724815

--поправил

Гость

/\/\/\/\/\/\,

ЗЫ а корреляты кошерно таки прикручивать после агрегации, а не в процессе. так оно дешевле.

...

Рейтинг:

0 / 0

20.08.2014, 17:05:58

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

улучшить время выполнения

#38724823

$/\/\/\/\/\/\$

/\/\/\/\/\/\

Гость

--поправил,

Я исхожу из того, что проще пробежаться по одной табличке и что-то отфильтровать, вместо того, чтобы что-то соединить по условию и потом все равно фильтровать. Достаточно часто бывает удачно.

В моем варианте соединения быть не должно. Так, пара-тройка значений отобрана уже в память (где-то 100 значений) и больше не рассчитывается.

...

Рейтинг:

0 / 0

20.08.2014, 17:09:28

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

улучшить время выполнения

#38724826

$/\/\/\/\/\/\$

/\/\/\/\/\/\

Гость

--поправил/\/\/\/\/\/\,

ЗЫ а корреляты кошерно таки прикручивать после агрегации, а не в процессе. так оно дешевле.

Насколько я здесь вижу, они - часть условия агрегации. То есть все равно агрегация на последнем этапе должна быть.

...

Рейтинг:

0 / 0

20.08.2014, 17:11:44

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

улучшить время выполнения

#38724831

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

--поправилон таки упорствует

Код: sql

Nested Loop  (cost=0.00..8971767.25 rows=33348778 width=100)

а что , если вместо JOIN предложить ему агрегат от where exists (план должен быть похожий, ну а вдруг он устанет упорствовать
~пимерно так~

Оценки дикие конечно. Вот эта

Код: sql

Index Scan using inx_u_otahotel_olap_id on otahotel h  (cost=0.00..121090.78 rows=1007859 width=43)

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

best_time, а покажите пожалуйста план для

Код: sql

SET enable_nestloop TO off;

Вместе с вариантом `EXISTS` конечно же.

...

Рейтинг:

0 / 0

20.08.2014, 17:13:36

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

улучшить время выполнения

#38724832

--поправил

Гость

/\/\/\/\/\/\--поправил,
<>

В моем варианте соединения быть не должно. Так, пара-тройка значений отобрана уже в память (где-то 100 значений) и больше не рассчитывается. у in в пж обычно план как у join , а у NOT IN -- как у ANTI JOIN (LEFT JOIN ...WHERE ...NULL) -- если не врёт память

...

Рейтинг:

0 / 0

20.08.2014, 17:14:23

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

улучшить время выполнения

#38724835

$/\/\/\/\/\/\$

/\/\/\/\/\/\

Гость

/\/\/\/\/\/\,

Эм. Я пошел по формальному пути. Верю исходному запросу.
А вообще да, эти поля можно прикрутить в последнюю очередь.

...

Рейтинг:

0 / 0

20.08.2014, 17:17:22

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

улучшить время выполнения

#38724837

best_time

Гость

/\/\/\/\/\/\--поправил,

Может, вот так в угадайку поиграем:

Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.

with  t
as
(
select h.id,h.olap_id,h.region_id,city_en
from
public.otahotel h
where olap_id>=1234
order by olap_id
limit 100

),
tt as
(
select t.id,geo.name,region_id,city_en city
from t
join	public.region r
on	t.region_id = r.id
join	static.geo geo
on	geo.id = any(r.geo_id_array)
and geo.id in (1,2)
)
SELECT	
  '2014-08-04'::date report_period,
  av.otahotel_id,
  (SELECT tt.region_id FROM tt WHERE tt.id = av.otahotel_id) AS region_id,
  (SELECT tt.city FROM tt WHERE tt.id = av.otahotel_id) AS city,
  (SELECT tt.name FROM tt WHERE tt.id = av.otahotel_id) AS name,
			--av.booking_window bw,
			--av.length_of_stay los,
  hotcore.parity_type(av.*, 'OTA') result,
  count(av.id) checks,
  sum(case when av.otahotel_price != 0 then 1 else 0 end) avail		
FROM hotcore.otahotel av
WHERE av.report_date >='2014-08-04'
  AND av.report_date < '2014-08-11'
  AND av.otahotel_id IN (SELECT id FROM tt)
GROUP BY 1, 2, 3, 4, 5, 6

Код: plaintext

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.

HashAggregate  (cost=29405961.41..29440661.41 rows=40000 width=64)
  CTE t
    ->  Limit  (cost=0.00..12.01 rows=100 width=43)
          ->  Index Scan using inx_u_otahotel_olap_id on otahotel h  (cost=0.00..121090.78 rows=1007859 width=43)
                Index Cond: (olap_id >= 1234)
  CTE tt
    ->  Nested Loop  (cost=0.00..857.24 rows=9 width=584)
          Join Filter: (geo.id = ANY (r.geo_id_array))
          ->  Nested Loop  (cost=0.00..839.49 rows=100 width=589)
                ->  CTE Scan on t  (cost=0.00..2.00 rows=100 width=552)
                ->  Index Scan using region_pkey on region r  (cost=0.00..8.36 rows=1 width=41)
                      Index Cond: (id = t.region_id)
          ->  Materialize  (cost=0.00..12.76 rows=2 width=36)
                ->  Seq Scan on geo  (cost=0.00..12.75 rows=2 width=36)
                      Filter: (id = ANY ('{1,2}'::integer[]))
  ->  Nested Loop  (cost=0.20..28654744.64 rows=33348778 width=64)
        ->  HashAggregate  (cost=0.20..0.29 rows=9 width=32)
              ->  CTE Scan on tt  (cost=0.00..0.18 rows=9 width=32)
        ->  Append  (cost=0.00..6446.87 rows=1615 width=64)
              ->  Seq Scan on otahotel av  (cost=0.00..0.00 rows=1 width=572)
                    Filter: ((report_date >= '2014-08-04'::date) AND (report_date < '2014-08-11'::date) AND (tt.id = otahotel_id))
              ->  Bitmap Heap Scan on otahotel_2014w32 av  (cost=68.65..6446.87 rows=1614 width=64)
                    Recheck Cond: (otahotel_id = tt.id)
                    Filter: ((report_date >= '2014-08-04'::date) AND (report_date < '2014-08-11'::date))
                    ->  Bitmap Index Scan on otahotel_2014w32_otahotel_id_idx  (cost=0.00..68.24 rows=1614 width=0)
                          Index Cond: (otahotel_id = tt.id)
        SubPlan 3
          ->  CTE Scan on tt  (cost=0.00..0.20 rows=1 width=4)
                Filter: (id = av.otahotel_id)
        SubPlan 4
          ->  CTE Scan on tt  (cost=0.00..0.20 rows=1 width=516)
                Filter: (id = av.otahotel_id)
        SubPlan 5
          ->  CTE Scan on tt  (cost=0.00..0.20 rows=1 width=32)
                Filter: (id = av.otahotel_id)

...

Рейтинг:

0 / 0

20.08.2014, 17:18:45

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

улучшить время выполнения

#38724841

--поправил

Гость

vyegorov <>Оценки дикие конечно.

<>Оптимизатор выбирает проход по индексу при возврате 1М записей, что соответствует размеру всей таблицы.

да единственное что напрашивается -- enable-ы и cost-ы получены не совсем в том сеансе, что вот эти вот планы.
а в том, где планы -- там все несколько иначе выставлено.

...

Рейтинг:

0 / 0

20.08.2014, 17:19:54

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

улучшить время выполнения

#38724842

$/\/\/\/\/\/\$

/\/\/\/\/\/\

Гость

--поправил... обычно план...

Когда "обычно" - проблем никаких. Не очень обращал внимание.
А вот когда не обычно... Оптимизатор такие чудеса откаблучивает.

...

Рейтинг:

0 / 0

20.08.2014, 17:20:05

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

улучшить время выполнения

#38724843

best_time

Гость

vyegorov--поправилон таки упорствует

Код: sql

Nested Loop  (cost=0.00..8971767.25 rows=33348778 width=100)

Код: sql

Index Scan using inx_u_otahotel_olap_id on otahotel h  (cost=0.00..121090.78 rows=1007859 width=43)

Код: sql

SET enable_nestloop TO off;

Вместе с вариантом `EXISTS` конечно же.

Код: sql

SET enable_nestloop TO off;

Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.

with  tt --t
as
(
select h.id, NULL "name" ,h.olap_id,h.region_id,city_en
from
public.otahotel h
where olap_id>=1234
order by olap_id
limit 100

) /*,
tt as
(
select t.id,geo.name,region_id,city_en city
from t
join	public.region r
on	t.region_id = r.id
join	static.geo geo
on	geo.id = any(r.geo_id_array)
and geo.id in (1,2)
) --*/
select	'2014-08-04'::date report_period,
			av.otahotel_id,
/*			tt.region_id,
			tt.city,
			tt.name,
*/
			--av.booking_window bw,
			--av.length_of_stay los,
			hotcore.parity_type(av.*, 'OTA') result,
			count(av.id) checks,
			sum(case when av.otahotel_price != 0 then 1 else 0 end) avail		
		from
/*		tt
		join	*/
		hotcore.otahotel av
--		on      av.otahotel_id = tt.id
		where	av.report_date >='2014-08-04'
			and av.report_date < '2014-08-11'
AND EXISTS (SELECT 1 FROM tt WHERE av.otahotel_id = tt.id)
		group 	by 1, 2, 3 --, 4, 5, 6

Код: plaintext

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

HashAggregate  (cost=14308077.00..14318477.00 rows=40000 width=64)
  CTE tt
    ->  Limit  (cost=0.00..12.01 rows=100 width=43)
          ->  Index Scan using inx_u_otahotel_olap_id on otahotel h  (cost=0.00..121090.78 rows=1007859 width=43)
                Index Cond: (olap_id >= 1234)
  ->  Hash Semi Join  (cost=3.25..13807833.31 rows=33348778 width=64)
        Hash Cond: (av.otahotel_id = tt.id)
        ->  Append  (cost=0.00..4924549.33 rows=66697556 width=64)
              ->  Seq Scan on otahotel av  (cost=0.00..0.00 rows=1 width=572)
                    Filter: ((report_date >= '2014-08-04'::date) AND (report_date < '2014-08-11'::date))
              ->  Seq Scan on otahotel_2014w32 av  (cost=0.00..4924549.33 rows=66697555 width=64)
                    Filter: ((report_date >= '2014-08-04'::date) AND (report_date < '2014-08-11'::date))
        ->  Hash  (cost=2.00..2.00 rows=100 width=32)
              ->  CTE Scan on tt  (cost=0.00..2.00 rows=100 width=32)

...

Рейтинг:

0 / 0

20.08.2014, 17:22:03

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

улучшить время выполнения

#38724853

--поправил

Гость

--поправил,
а если тупо вписать otahotel_2014w32 вместо otahotel ?
план не поменяется ?
-- может быть он[, оптимайзер бишь 9.2.8,] аппендить при хешджойне не обучен ?

...

Рейтинг:

0 / 0

20.08.2014, 17:25:52

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

улучшить время выполнения

#38724855

best_time

Гость

--поправил--поправил,
а если тупо вписать otahotel_2014w32 вместо otahotel ?
план не поменяется ?
-- может быть он[, оптимайзер бишь 9.2.8,] аппендить при хешджойне не обучен ?

Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.

--SET enable_nestloop TO on;

with  tt --t
as
(
select h.id, NULL "name" ,h.olap_id,h.region_id,city_en
from
public.otahotel h
where olap_id>=1234
order by olap_id
limit 100

) /*,
tt as
(
select t.id,geo.name,region_id,city_en city
from t
join	public.region r
on	t.region_id = r.id
join	static.geo geo
on	geo.id = any(r.geo_id_array)
and geo.id in (1,2)
) --*/
select	'2014-08-04'::date report_period,
			av.otahotel_id,
/*			tt.region_id,
			tt.city,
			tt.name,
*/
			--av.booking_window bw,
			--av.length_of_stay los,
			hotcore.parity_type(av.*, 'OTA') result,
			count(av.id) checks,
			sum(case when av.otahotel_price != 0 then 1 else 0 end) avail		
		from
/*		tt
		join	*/
		partitions.otahotel_2014w32 av
--		on      av.otahotel_id = tt.id
		where	av.report_date >='2014-08-04'
			and av.report_date < '2014-08-11'
AND EXISTS (SELECT 1 FROM tt WHERE av.otahotel_id = tt.id)
		group 	by 1, 2, 3 --, 4, 5, 6

Код: plaintext

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

HashAggregate  (cost=677350.18..719308.46 rows=161378 width=572)
  CTE tt
    ->  Limit  (cost=0.00..12.01 rows=100 width=43)
          ->  Index Scan using inx_u_otahotel_olap_id on otahotel h  (cost=0.00..121090.78 rows=1007859 width=43)
                Index Cond: (olap_id >= 1234)
  ->  Nested Loop  (cost=70.86..674917.50 rows=161378 width=572)
        ->  HashAggregate  (cost=2.25..3.25 rows=100 width=32)
              ->  CTE Scan on tt  (cost=0.00..2.00 rows=100 width=32)
        ->  Bitmap Heap Scan on otahotel_2014w32 av  (cost=68.61..6329.56 rows=1614 width=572)
              Recheck Cond: (otahotel_id = tt.id)
              Filter: ((report_date >= '2014-08-04'::date) AND (report_date < '2014-08-11'::date))
              ->  Bitmap Index Scan on otahotel_2014w32_otahotel_id_idx  (cost=0.00..68.20 rows=1614 width=0)
                    Index Cond: (otahotel_id = tt.id)

...

Рейтинг:

0 / 0

20.08.2014, 17:28:50

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

улучшить время выполнения

#38724856

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

--поправил,

Было бы интересно глянуть на план в 9.3. Хотя бы только на один запрос по этим 4 таблицам...

...

Рейтинг:

0 / 0

20.08.2014, 17:29:03

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

улучшить время выполнения

#38724862

--поправил

Гость

best_time,
ну вот раньше 16469948 выяснилось, что таки обучен

Код: sql

 ->  Hash Semi Join  (cost=3.25..13807833.31 rows=33348778 width=64)

но оценивает в 1.5 раза дороже чем Nested Loop

Приведите EXPLAIN ANALYZE этого случая [да и извиняюсь за описки и одумки -- невнимательность]

...

Рейтинг:

0 / 0

20.08.2014, 17:33:22

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

улучшить время выполнения

#38724871

best_time

Гость

--поправил,

Код: 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.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.

SET enable_nestloop TO off;
EXPLAIN ANALYZE 
with  tt --t
as
(
select h.id, NULL "name" ,h.olap_id,h.region_id,city_en
from
public.otahotel h
where olap_id>=1234
order by olap_id
limit 100

) /*,
tt as
(
select t.id,geo.name,region_id,city_en city
from t
join	public.region r
on	t.region_id = r.id
join	static.geo geo
on	geo.id = any(r.geo_id_array)
and geo.id in (1,2)
) --*/
select	'2014-08-04'::date report_period,
			av.otahotel_id,
/*			tt.region_id,
			tt.city,
			tt.name,
*/
			--av.booking_window bw,
			--av.length_of_stay los,
			hotcore.parity_type(av.*, 'OTA') result,
			count(av.id) checks,
			sum(case when av.otahotel_price != 0 then 1 else 0 end) avail		
		from
/*		tt
		join	*/
		partitions.otahotel_2014w32 av
--		on      av.otahotel_id = tt.id
		where	av.report_date >='2014-08-04'
			and av.report_date < '2014-08-11'
AND EXISTS (SELECT 1 FROM tt WHERE av.otahotel_id = tt.id)
		group 	by 1, 2, 3 --, 4, 5, 6

Код: plaintext

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

HashAggregate  (cost=5144206.17..5186164.45 rows=161378 width=572) (actual time=200841.046..200841.480 rows=97 loops=1)
  CTE tt
    ->  Limit  (cost=0.00..12.01 rows=100 width=43) (actual time=0.025..0.150 rows=100 loops=1)
          ->  Index Scan using inx_u_otahotel_olap_id on otahotel h  (cost=0.00..121090.78 rows=1007859 width=43) (actual time=0.023..0.135 rows=100 loops=1)
                Index Cond: (olap_id >= 1234)
  ->  Hash Semi Join  (cost=3.25..5141773.49 rows=161378 width=572) (actual time=60.041..200715.451 rows=48394 loops=1)
        Hash Cond: (av.otahotel_id = tt.id)
        ->  Seq Scan on otahotel_2014w32 av  (cost=0.00..4924549.33 rows=66697555 width=572) (actual time=9.375..187810.465 rows=65288691 loops=1)
              Filter: ((report_date >= '2014-08-04'::date) AND (report_date < '2014-08-11'::date))
        ->  Hash  (cost=2.00..2.00 rows=100 width=32) (actual time=0.291..0.291 rows=100 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 6kB
              ->  CTE Scan on tt  (cost=0.00..2.00 rows=100 width=32) (actual time=0.030..0.250 rows=100 loops=1)
Total runtime: 200843.223 ms

...

Рейтинг:

0 / 0

20.08.2014, 17:44:15

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

улучшить время выполнения

#38724880

--поправил

Гость

best_time,
да, пожалуй он мало ошибается (2-3 -- допустимо для сложных запросов).
т.ч. [намного] лучше уже не будет.

ещё выкиньте всё ненужное из агрегата (например константу -- первое поле) -- прикрутите после. -- немного сэкономите работу оптимайзеру. (каким бы путём не пошли).
А всю расшифровку прикрутите после свертки.

...

Рейтинг:

0 / 0

20.08.2014, 17:54:57

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

улучшить время выполнения

#38724915

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

--поправил1. свернуть все 65 лямов на проходе (100 секунд --см выше), потом поджойниться - выиграем вполовину, но никак не больше

или (совсем другое)

2. взять в CTE 100 id, и для каждого из них в конструкции with recursive взять свой агрегат (что оптимайзер [+-километр] и должен делать, но я как-то это плохо читаю).

Первый вариант выглядит оптимальным и выигрыш вполне себе хороший. Второй был представлен ТС в начальном посте (и я на нем зациклился).

...

Рейтинг:

0 / 0

20.08.2014, 18:31:48

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

25 сообщений из 55, страница 2 из 3

все

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

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

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

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

start [/forum/topic.php?fid=53&msg=38724810&tid=1998523]:	0ms
get settings:	6ms
get forum list:	10ms
check forum access:	2ms
check topic access:	2ms
track hit:	178ms
get topic data:	8ms
get forum data:	2ms
get page messages:	41ms
get tp. blocked users:	1ms
others:	196ms

total:	446ms

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