ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация SQL-запроса.
16 сообщений из 16, страница 1 из 1
Оптимизация SQL-запроса.
    #39409979
chipakunos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chipakunos
Гость
Есть две таблицы:
Компании с 7599 записями
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE TABLE public.company
(
  id integer NOT NULL DEFAULT nextval('company_id_seq'::regclass),
  name character(30),
  CONSTRAINT company_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);



Их активность с 22кк записей
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
CREATE TABLE public.day
(
  id_company integer NOT NULL,
  date date NOT NULL,
  volume double precision,
  CONSTRAINT day_pkey PRIMARY KEY (date, id_company),
  CONSTRAINT fk_company_id FOREIGN KEY (id_company)
      REFERENCES public.company (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);


Есть запрос вида:
Код: sql
1.
2.
3.
 SELECT *
                  FROM "day" INNER JOIN company ON company.id=day.id_company
                  WHERE company.name='kyivstar' AND day.date BETWEEN '1990-01-01' AND '2017-02-02';


Время выполнения 6 секунд. Мне кажется время катастрофически большим. Как можно оптимизировать время выполнения? В основном будут с базы тянуться именно такие запросы. Буду благодарен за любой совет.
...
Рейтинг: 0 / 0
24.02.2017, 17:43
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39409988
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
explain(analyze, buffers) покажите.

Навскидку:
индекс по company.name.
индекс по id_company & date в day.
...
Рейтинг: 0 / 0
24.02.2017, 18:04
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39409993
chipakunos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chipakunos
Гость
Melkij, вывод EXPLAIN(ANALYZE, BUFFERS)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
Hash Join  (cost=137.00..637385.56 rows=2985 width=187) (actual time=3144.103..20122.347 rows=6823 loops=1)
   Hash Cond: (day.id_company = company.id)
   Buffers: shared hit=3640 read=208370
   ->  Seq Scan on day  (cost=0.00..552167.97 rows=22680198 width=48) (actual time=0.034..12481.900 rows=22680198 loops=1)
         Filter: ((date >= '1990-01-01'::date) AND (date <= '2017-02-02'::date))
         Buffers: shared hit=3595 read=208370
   ->  Hash  (cost=136.99..136.99 rows=1 width=139) (actual time=2.574..2.574 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=42
         ->  Seq Scan on company  (cost=0.00..136.99 rows=1 width=139) (actual time=0.019..2.564 rows=1 loops=1)
               Filter: (name = 'kyivstar'::bpchar)
               Rows Removed by Filter: 7598
               Buffers: shared hit=42
 Planning time: 2.154 ms
 Execution time: 20124.418 ms
(15 rows)
...
Рейтинг: 0 / 0
24.02.2017, 18:11
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39409997
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
И ещё раз, но в с предложенными индексами.
...
Рейтинг: 0 / 0
24.02.2017, 18:20
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39410006
chipakunos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chipakunos
Гость
Melkij, пожалуйста

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
Hash Join  (cost=8.31..637256.88 rows=2985 width=187) (actual time=13146.622..21443.178 rows=6823 loops=1)
   Hash Cond: (day.id_company = company.id)
   Buffers: shared hit=5554 read=206414 written=52
   ->  Seq Scan on day  (cost=0.00..552167.97 rows=22680198 width=48) (actual time=0.018..13390.441 rows=22680198 loops=1)
         Filter: ((date >= '1990-01-01'::date) AND (date <= '2017-02-02'::date))
         Buffers: shared hit=5551 read=206414 written=52
   ->  Hash  (cost=8.30..8.30 rows=1 width=139) (actual time=0.057..0.057 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=3
         ->  Index Scan using fi_company_name on company  (cost=0.28..8.30 rows=1 width=139) (actual time=0.024..0.026 rows=1 lo
ops=1)
               Index Cond: (name = 'kyivstar'::bpchar)
               Buffers: shared hit=3
 Planning time: 0.822 ms
 Execution time: 21446.058 ms
(14 rows)
...
Рейтинг: 0 / 0
24.02.2017, 18:38
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39410031
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Это точно с индексом по id_company & date? Сделайте analyze, если индекс именно этот и ещё не делали.
...
Рейтинг: 0 / 0
24.02.2017, 20:16
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39410035
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
chipakunos,

выбирай меньше с day, вот и время сократиться.


Обычно данные за 27 лет нужны только для репортов, да и то обычно не за все года. Читай про мат вью.
...
Рейтинг: 0 / 0
24.02.2017, 20:24
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39410060
chipakunos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chipakunos
Гость
Melkij,

Да точно, ещё раз перепровел, результат тот же.
...
Рейтинг: 0 / 0
24.02.2017, 21:26
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39410064
chipakunos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chipakunos
Гость
chipakunos,

Интересно ещё, что на другой базе под виндой со стандартными настройками запрос исполняется 90 msec и explain(analyze, buffers) выглядит следуещим образом:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Nested Loop  (cost=0.00..7325.48 rows=2925 width=187) (actual time=10.302..91.102 rows=6823 loops=1)
  Buffers: shared hit=129
  ->  Seq Scan on company  (cost=0.00..138.91 rows=1 width=139) (actual time=10.237..27.541 rows=1 loops=1)
        Filter: (name = 'kyivstar'::bpchar)
        Rows Removed by Filter: 7598
        Buffers: shared hit=42
  ->  Index Scan using day_pkey on day  (cost=0.00..7146.36 rows=4021 width=48) (actual time=0.050..61.931 rows=6823 loops=1)
        Index Cond: ((id_company = company.id) AND (date >= '1990-01-01'::date) AND (date <= '2017-02-02'::date))
        Buffers: shared hit=87
Total runtime: 91.827 ms"
...
Рейтинг: 0 / 0
24.02.2017, 21:46
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39410082
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Вот этот nested loop я и пытался получить. Видно, что планировщик ошибся со статистикой и нехватка индекса по company.name, а в остальном то что нужно.

Что вы там с настройками крутили? Что планировщик теперь решает не идти по явно селективному хотя бы даже префиксу индекса а ползти seqscan'ом.
...
Рейтинг: 0 / 0
24.02.2017, 22:57
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39410131
chipakunos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chipakunos
Гость
Melkij, вот настройки query tuning. Уже не помню, что менял. а что дефотное, ибо такая проблема была сразу (Postgresql 9.6)

 
Код: 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.
45.
46.
47.
48.
49.
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

# - Planner Method Configuration -

enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
enable_nestloop = off
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

seq_page_cost = 1.0                     # measured on an arbitrary scale
random_page_cost = 4.0                  # same scale as above
cpu_tuple_cost = 0.01                   # same scale as above
cpu_index_tuple_cost = 0.005            # same scale as above
cpu_operator_cost = 0.0025              # same scale as above
parallel_tuple_cost = 0.1               # same scale as above
parallel_setup_cost = 1000.0    # same scale as above
min_parallel_relation_size = 8MB
effective_cache_size = 4GB

# - Genetic Query Optimizer -

geqo = on
geqo_threshold = 12
geqo_effort = 5                         # range 1-10
geqo_pool_size = 0                      # selects default based on effort
geqo_generations = 0                    # selects default based on effort
geqo_selection_bias = 2.0               # range 1.5-2.0
geqo_seed = 0.0                         # range 0.0-1.0

# - Other Planner Options -

default_statistics_target = 100         # range 1-10000
constraint_exclusion = partition        # on, off, or partition
cursor_tuple_fraction = 0.1             # range 0.0-1.0
from_collapse_limit = 8
join_collapse_limit = 8                 # 1 disables collapsing of explicit
                                        # JOIN clauses
force_parallel_mode = off

...
Рейтинг: 0 / 0
25.02.2017, 13:24
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39410134
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
chipakunos,

enable_nestloop = off
ну, что-то такое и предполагал, да. Если бедному планировщику запретить выбирать идеальный для этого запроса nested loop, то планировщик и будет страдать какой-нибудь фигнёй.
...
Рейтинг: 0 / 0
25.02.2017, 13:33
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39410138
chipakunos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chipakunos
Гость
Только что поставил

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_indexonlyscan = on
enable_material = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on



После чего получил такой explain
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
                                                           QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------
-
 Nested Loop  (cost=0.72..6985.34 rows=2985 width=187) (actual time=0.113..14.187 rows=6823 loops=1)
   Buffers: shared hit=90
   ->  Index Scan using fi_company_name on company  (cost=0.28..8.30 rows=1 width=139) (actual time=0.035..0.037 rows=1 loops=1)
         Index Cond: (name = 'kyivstar'::bpchar)
         Buffers: shared hit=3
   ->  Index Scan using day_pkey on day  (cost=0.44..6937.93 rows=3911 width=48) (actual time=0.066..6.463 rows=6823 loops=1)
         Index Cond: ((id_company = company.id) AND (date >= '1990-01-01'::date) AND (date <= '2017-02-02'::date))
         Buffers: shared hit=87
 Planning time: 0.788 ms
 Execution time: 17.306 ms
(10 rows)



Всем спасибо огромное! Думаю, тему можно закрывать.

П.С.
Буду благодарен за литературу по настройке и администрированию postgres.
...
Рейтинг: 0 / 0
25.02.2017, 14:07
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39410139
chipakunos
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chipakunos
Гость
Melkij, я запрет nested loop нашёл на одном из форумов. Там говорилось, что эта функция сильно тормозит запрос...
...
Рейтинг: 0 / 0
25.02.2017, 14:13
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39410155
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
chipakunos,

Не всегда, как видите. Далеко не всегда.
Поэтому не надо мешать оптимизатору работать и трогать глобально любые enable_*. А с конкретными случаями, когда планировщик выбирает nested loops, но это оказывается действительно контрпродуктивно - надо разбираться, почему планировщик решил сделать именно так.
...
Рейтинг: 0 / 0
25.02.2017, 15:15
| Ответить | Цитировать | Написать  
Оптимизация SQL-запроса.
    #39410333
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
chipakunosMelkij, я запрет nested loop нашёл на одном из форумов. Там говорилось, что эта функция сильно тормозит запрос...вот перед тем запросом и локально запрещайте.
...
Рейтинг: 0 / 0
26.02.2017, 12:29
| Ответить | Цитировать | Написать  
16 сообщений из 16, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Оптимизация SQL-запроса.
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39410035&tid=1996697]:
 0ms
get settings:
 7ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 97ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 38ms
get tp. blocked users:
 1ms
others: 321ms
total:  492ms
созд. / загр.: 492ms
x
x
Закрыть


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