ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Чудеса оптимизатора.
12 сообщений из 12, страница 1 из 1
Чудеса оптимизатора.
    #32888013
Alex Kab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex Kab
Гость
Есть запрос
Код: plaintext
1.
2.
3.
4.
5.
6.
        SELECT u.username, sum(l.requestsize) as size
        FROM   sq_username u, sq_logfile l
        WHERE  tstime BETWEEN '2005-01-17 00:00:00' AND '2005-01-19 23:59:59'::timestamp 
        AND l.username_id=u.id
        GROUP BY username
        ORDER BY size,username DESC;

табличка sq_logfile более 10 млн записей, остальные - не большие, seq scan на них выполняется мгновенно, поэтому их в расчет не берем.
В таблице sq_logfile есть индексы по полям tstime, username

Так вот, когда интервал выборки не больше пары дней, то EXPLAIN пишет

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
Sort  (cost= 285763 . 31 .. 285764 . 95  rows= 657  width= 17 )
 Sort Key: sum(l.requestsize), u.username
  ->  HashAggregate  (cost= 285730 . 92 .. 285732 . 56  rows= 657  width= 17 )
   ->  Hash Join  (cost= 13 . 21 .. 285040 . 02  rows= 138180  width= 17 )
    Hash Cond: ("outer".username_id = "inner".id)
     ->  Index Scan using idx_sq_logfile1 on sq_logfile l  (cost= 0 . 00 .. 282954 . 11  rows= 138180  width= 8 )
      Index Cond: ((tstime >= '2005-01-17 00:00:00'::timestamp without time zone) AND (tstime <= '2005-01-18 23:59:59'::timestamp without time zone))
    ->  Hash  (cost= 11 . 57 .. 11 . 57  rows= 657  width= 17 )
       ->  Seq Scan on sq_username u  (cost= 0 . 00 .. 11 . 57  rows= 657  width= 17 )

Используется индекс idx_sq_logfile1 ("idx_sq_logfile1" btree (tstime)), запрос выполняется быстро (1-2 сек),
но когда интервал увеличить (например, выборка за неделю), то оптимизатор почему-то включает Seq Scan

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
Sort  (cost= 358085 . 48 .. 358087 . 12  rows= 657  width= 17 )
 Sort Key: sum(l.requestsize), u.username
  ->  HashAggregate  (cost= 358053 . 09 .. 358054 . 73  rows= 657  width= 17 )
   ->  Hash Join  (cost= 13 . 21 .. 355179 . 59  rows= 574700  width= 17 )
         Hash Cond: ("outer".username_id = "inner".id)
          ->  Seq Scan on sq_logfile l  (cost= 0 . 00 .. 346545 . 88  rows= 574700  width= 8 )
                Filter: ((tstime >= '2005-01-10 00:00:00'::timestamp without time zone) AND (tstime <= '2005-01-18 23:59:59'::timestamp without time zone))
          ->  Hash  (cost= 11 . 57 .. 11 . 57  rows= 657  width= 17 )
               ->  Seq Scan on sq_username u  (cost= 0 . 00 .. 11 . 57  rows= 657  width= 17 )

В результате запрос выполняется секунд 70.
Если делать set enable_seqscan to off перед выполнением запроса - то выборка длится всего 3 секунды.
Как грамотно построить запрос, дабы использовался индекс?

P.S. VACUUM, VACUUM ANALYZE делается каждую ночь, VACUUM FULL - каждую неделю.
...
Рейтинг: 0 / 0
27.01.2005, 19:23
| Ответить | Цитировать | Написать  
Чудеса оптимизатора.
    #32888064
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
Приведите пожалуйста результаты EXPLAIN ANALYZE для этих двух запросов, и для третьего, который за неделю с enable_seqscan=off. И какая версия постгреса?
...
Рейтинг: 0 / 0
27.01.2005, 20:01
| Ответить | Цитировать | Написать  
Чудеса оптимизатора.
    #32888555
Alex Kab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex Kab
Гость
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
select version();
                                            version

------------------------------------------------------------------------------------------------
 PostgreSQL  7 . 4 . 5  on i386-portbld-freebsd5. 2 , compiled by GCC cc (GCC)  3 . 3 . 3  [FreeBSD]  20031106 
( 1  row)

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
EXPLAIN ANALYZE 
       SELECT u.username, sum(l.requestsize) as size
       FROM   sq_username u, sq_logfile l
       WHERE  tstime BETWEEN '2005-01-28 00:00:00' AND '2005-01-28 23:59:59'::timestamp
              AND l.username_id=u.id
       GROUP BY username
       ORDER BY size,username DESC;

                                                                             QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost= 8 . 79 .. 8 . 80  rows= 1  width= 17 ) (actual time= 109 . 500 .. 109 . 573  rows= 19  l
oops= 1 )
   Sort Key: sum(l.requestsize), u.username
   ->  HashAggregate  (cost= 8 . 78 .. 8 . 78  rows= 1  width= 17 ) (actual ime= 109 . 066 .. 109 . 161  rows= 19  loops= 1 )
         ->  Nested Loop  (cost= 0 . 00 .. 8 . 77  rows= 1  width= 17 ) (actual time= 0 . 224 .. 93 . 366  rows= 1764  loops= 1 )
               ->  Index Scan using idx_sq_logfile1 on sq_logfile l  (cost= 0 . 00 .. 4 . 77  rows= 1  width= 8 ) (actual time= 0 . 125 .. 17 . 346  rows= 1764  loops= 1 )
                     Index Cond: ((tstime >= '2005-01-28 00:00:00'::timestamp without time zone) AND (tstime <= '2005-01-28 23:59:59'::timestamp without time zone))
               ->  Index Scan using sq_username_pkey on sq_username u  (cost= 0 . 00 .. 3 . 99  rows= 1  width= 17 ) (actual time= 0 . 015 .. 0 . 021  rows= 1  loops= 1764 )
                     Index Cond: ("outer".username_id = u.id)
 Total runtime:  110 . 877  ms
( 9  rows)

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
EXPLAIN ANALYZE 
       SELECT u.username, sum(l.requestsize) as size
       FROM   sq_username u, sq_logfile l
       WHERE  tstime BETWEEN '2005-01-21 00:00:00' AND '2005-01-28 23:59:59'::timestamp
              AND l.username_id=u.id
       GROUP BY username
       ORDER BY size,username DESC;

                                                                                                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost= 357048 . 78 .. 357050 . 43  rows= 659  width= 17 ) (actual time= 78510 . 525 .. 78510 . 891  rows= 91  loops= 1 )
   Sort Key: sum(l.requestsize), u.username
   ->  HashAggregate  (cost= 357016 . 28 .. 357017 . 93  rows= 659  width= 17 ) (actual time= 78509 . 334 .. 78509 . 996  rows= 91  loops= 1 )
         ->  Hash Join  (cost= 13 . 24 .. 354935 . 98  rows= 416060  width= 17 ) (actual time= 13523 . 134 .. 76120 . 278  rows= 401623  loops= 1 )
               Hash Cond: ("outer".username_id = "inner".id)
               ->  Seq Scan on sq_logfile l  (cost= 0 . 00 .. 348681 . 84  rows= 416060  width= 8 ) (actual time= 13515 . 545 .. 71611 . 589  rows= 401623  loops= 1 )
                     Filter: ((tstime >= '2005-01-21 00:00:00'::timestamp without time zone) AND (tstime <= '2005-01-28 23:59:59'::timestamp without time zone))
               ->  Hash  (cost= 11 . 59 .. 11 . 59  rows= 659  width= 17 ) (actual time= 7 . 061 .. 7 . 061  rows= 0  loops= 1 )
                     ->  Seq Scan on sq_username u  (cost= 0 . 00 .. 11 . 59  rows= 659  width= 17 ) (actual time= 0 . 039 .. 3 . 315  rows= 659  loops= 1 )
 Total runtime:  78512 . 273  ms
( 10  rows)

А теперь этот же запрос, но с set enable_seqscan to off;

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
                                                                             QUERY PLAN
-------------------------------------------------------------------------------------
 Sort  (cost= 828123 . 75 .. 828125 . 39  rows= 659  width= 17 ) (actual time= 10414 . 931 .. 104 
 15 . 286  rows= 91  loops= 1 )
   Sort Key: sum(l.requestsize), u.username
   ->  HashAggregate  (cost= 828091 . 24 .. 828092 . 89  rows= 659  width= 17 ) (actual time= 10413 . 780 .. 10414 . 404  rows= 91  loops= 1 )
         ->  Hash Join  (cost= 25 . 47 .. 826010 . 94  rows= 416060  width= 17 ) (actual time= 11 . 254 .. 7818 . 917  rows= 401623  loops= 1 )
               Hash Cond: ("outer".username_id = "inner".id)
               ->  Index Scan using idx_sq_logfile1 on sq_logfile l  (cost= 0 . 00 .. 819744 . 58  rows= 416060  width= 8 ) (actual time= 1 . 669 .. 3301 . 905  rows= 401623  loops= 1 )
                     Index Cond: ((tstime >= '2005-01-21 00:00:00'::timestamp without time zone) AND (tstime <= '2005-01-28 23:59:59'::timestamp without time zone))
               ->  Hash  (cost= 23 . 82 .. 23 . 82  rows= 659  width= 17 ) (actual time= 9 . 135 .. 9 . 135  rows= 0  loops= 1 )
                     ->  Index Scan using sq_username_pkey on sq_username u  (cost= 0 . 00 .. 23 . 82  rows= 659  width= 17 ) (actual time= 0 . 314 .. 4 . 819  rows= 659  loops= 1 )
 Total runtime:  10416 . 383  ms
( 10  rows)

78 секунд на запрос без индекса, и 10 секунд на тот же запрос, но с использованием индекса.
...
Рейтинг: 0 / 0
28.01.2005, 10:20
| Ответить | Цитировать | Написать  
Чудеса оптимизатора.
    #32888958
mwolf
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mwolf
Участник
"Акелла промахнулся! Акелла промахнулся!" (С) Шакал из мультика про Маугли.

Однако случай интересный. Мне вот эти строки понравились:

Код: plaintext
1.
2.
3.
4.
--set enable_seqscan on
Hash Join  (cost= 13 . 24 .. 354935 . 98  rows= 416060  width= 17 ) (actual time= 13523 . 134 .. 76120 . 278  rows= 401623  loops= 1 )
--set enable_seqscan off
Hash Join  (cost= 25 . 47 .. 826010 . 94  rows= 416060  width= 17 ) (actual time= 11 . 254 .. 7818 . 917  rows= 401623  loops= 1 )
То есть, если бы отбросить то, что после actual time, то с точки зрения оптимизатора верхний план предпочтительнее. И выбор первого плана предсказуем - его "стоимость" меньше. Ну а поскольку стоимость - вещь относительная - то удивляться такому не стоит.
У вас часом рейдов каких-нить не стоит? Потому как похоже, что диски работают быстрее, чем об этом думает оптимизатор.
...
Рейтинг: 0 / 0
28.01.2005, 12:30
| Ответить | Цитировать | Написать  
Чудеса оптимизатора.
    #32889164
Alex Kab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex Kab
Гость
Диски для базы - 120 Гиг IDE, 2 штуки, собраны в зеркало (аппаратный RAID 1)
Уменьшил значение random_page_cost с 4 до 3, эффекта не дало.

Код: 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.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
squid=# set enable_seqscan to off;
SET
squid=# EXPLAIN ANALYZE
squid-#        SELECT u.username, sum(l.requestsize) as size
squid-#        FROM   sq_username u, sq_logfile l
squid-#        WHERE  tstime BETWEEN '2005-01-21 00:00:00' AND '2005-01-28 23:59:59'::timestamp
squid-#               AND l.username_id=u.id
squid-#        GROUP BY username
squid-#        ORDER BY size,username DESC;
                                                                             QUERY PLAN                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost= 625851 . 02 .. 625852 . 67  rows= 659  width= 17 ) (actual time= 11559 . 334 .. 11559 . 695  rows= 92  loops= 1 )
   Sort Key: sum(l.requestsize), u.username
   ->  HashAggregate  (cost= 625818 . 52 .. 625820 . 16  rows= 659  width= 17 ) (actual time= 11557 . 823 .. 11558 . 308  rows= 92  loops= 1 )
         ->  Hash Join  (cost= 23 . 61 .. 623738 . 22  rows= 416060  width= 17 ) (actual time= 13 . 463 .. 8827 . 262  rows= 416800  loops= 1 )
               Hash Cond: ("outer".username_id = "inner".id)
               ->  Index Scan using idx_sq_logfile1 on sq_logfile l  (cost= 0 . 00 .. 617473 . 71  rows= 416060  width= 8 ) (actual time= 2 . 495 .. 3804 . 947  rows= 416800  loops= 1 )
                     Index Cond: ((tstime >= '2005-01-21 00:00:00'::timestamp without time zone) AND (tstime <= '2005-01-28 23:59:59'::timestamp without time zone))
               ->  Hash  (cost= 21 . 96 .. 21 . 96  rows= 659  width= 17 ) (actual time= 10 . 434 .. 10 . 434  rows= 0  loops= 1 )
                     ->  Index Scan using sq_username_pkey on sq_username u  (cost= 0 . 00 .. 21 . 96  rows= 659  width= 17 ) (actual time= 0 . 863 .. 6 . 608  rows= 659  loops= 1 )
 Total runtime:  11573 . 509  ms
( 10  rows)


squid=# set enable_seqscan to default;
SET
squid=# EXPLAIN ANALYZE
squid-#        SELECT u.username, sum(l.requestsize) as size
squid-#        FROM   sq_username u, sq_logfile l
squid-#        WHERE  tstime BETWEEN '2005-01-21 00:00:00' AND '2005-01-28 23:59:59'::timestamp
squid-#               AND l.username_id=u.id
squid-#        GROUP BY username
squid-#        ORDER BY size,username DESC;
                                                                           QUERY PLAN                                         
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost= 357048 . 78 .. 357050 . 43  rows= 659  width= 17 ) (actual time= 85267 . 585 .. 85267 . 928  rows= 92  loops= 1 )
   Sort Key: sum(l.requestsize), u.username
   ->  HashAggregate  (cost= 357016 . 28 .. 357017 . 93  rows= 659  width= 17 ) (actual time= 85266 . 022 .. 85266 . 852  rows= 92  loops= 1 )
         ->  Hash Join  (cost= 13 . 24 .. 354935 . 98  rows= 416060  width= 17 ) (actual time= 13185 . 744 .. 82635 . 544  rows= 416800  loops= 1 )
               Hash Cond: ("outer".username_id = "inner".id)
               ->  Seq Scan on sq_logfile l  (cost= 0 . 00 .. 348681 . 84  rows= 416060  width= 8 ) (actual time= 13177 . 598 .. 77785 . 630  rows= 416800  loops= 1 )
                     Filter: ((tstime >= '2005-01-21 00:00:00'::timestamp without time zone) AND (tstime <= '2005-01-28 23:59:59'::timestamp without time zone))
               ->  Hash  (cost= 11 . 59 .. 11 . 59  rows= 659  width= 17 ) (actual time= 7 . 693 .. 7 . 693  rows= 0  loops= 1 )
                     ->  Seq Scan on sq_username u  (cost= 0 . 00 .. 11 . 59  rows= 659  width= 17 ) (actual time= 0 . 131 .. 4 . 374  rows= 659  loops= 1 )
 Total runtime:  85269 . 050  ms
( 10  rows)
...
Рейтинг: 0 / 0
28.01.2005, 13:26
| Ответить | Цитировать | Написать  
Чудеса оптимизатора.
    #32889399
assa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
assa
Участник
авторВ таблице sq_logfile есть индексы по полям tstime, username
индексы составные? или по отдельным полям?

сдаецца, чта составной индекс типа (username,tstime) или (tstime,username) может ускорить данную конкретную выборку. Впрочем, не очевидно.
...
Рейтинг: 0 / 0
28.01.2005, 14:31
| Ответить | Цитировать | Написать  
Чудеса оптимизатора.
    #32889518
Alex Kab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex Kab
Гость
Код: 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.
-----------------------------
 id               | bigint                      | not null default nextval('public.sq_logfile_id_seq'::text)
 tstime           | timestamp without time zone |
 duration         | integer                     |
 client_addr_id   | integer                     | not null default  0 
 resultcode_id    | integer                     | not null default  0 
 requestsize      | integer                     |
 requestmethod_id | integer                     | not null default  0 
 url              | text                        |
 username_id      | integer                     | not null default  0 
 hierarchycode_id | integer                     | not null default  0 
 contenttype_id   | integer                     | not null default  0 
 server_id        | integer                     | not null default  0 
Indexes:
    "sq_logfile_pkey" primary key, btree (id)
    "idx_sq_logfile1" btree (tstime)
    "idx_sq_logfile2" btree (client_addr_id)
    "idx_sq_logfile3" btree (username_id)
    "idx_sq_logfile4" btree (url)
    "idx_sq_logfile5" btree (tstime, url, username_id)
    "idx_sq_logfile_server" btree (server_id)
Foreign-key constraints:
    "$1" FOREIGN KEY (client_addr_id) REFERENCES sq_clientaddr(id)
    "$2" FOREIGN KEY (resultcode_id) REFERENCES sq_resultcodes(id)
    "$3" FOREIGN KEY (requestmethod_id) REFERENCES sq_requestmethods(id)
    "$4" FOREIGN KEY (username_id) REFERENCES sq_username(id)
    "$5" FOREIGN KEY (hierarchycode_id) REFERENCES sq_hierarchycodes(id)
    "$6" FOREIGN KEY (contenttype_id) REFERENCES sq_contenttypes(id)
    "$7" FOREIGN KEY (server_id) REFERENCES sq_server(id)
...
Рейтинг: 0 / 0
28.01.2005, 15:03
| Ответить | Цитировать | Написать  
Чудеса оптимизатора.
    #32889543
assa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
assa
Участник
а порядок в "idx_sq_logfile5" btree (tstime, url, username_id) взят из каких соображений (нет ли возможности поменять на (tstime, username_id, l) , не портя работу других выборок?)

и все таки проверьте скорости таких запрсов c индексом вида (tstime, username_id) (можно на тестовой табличке)
...
Рейтинг: 0 / 0
28.01.2005, 15:10
| Ответить | Цитировать | Написать  
Чудеса оптимизатора.
    #32889586
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
Ничего, кроме как покрутить константы "оценщика", посоветовать не могу. :( Мы остановились на таких значениях.
effective_cache_size = 8192 # typically 8KB each
random_page_cost = 2 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)

assaсдаецца, чта составной индекс типа (username,tstime) или (tstime,username) может ускорить данную конкретную выборку. Впрочем, не очевидно.С помощью индекса (tstime,username) вроде бы можно избавиться от сортировки на последнем этапе выполнения этого запроса, но серьезного выигрыша это не даст, так как сортировка 90 строк, судя по выводу EXPLAIN FNFLYZE работает ~1ms.
...
Рейтинг: 0 / 0
28.01.2005, 15:19
| Ответить | Цитировать | Написать  
Чудеса оптимизатора.
    #32889846
Ф_Д_Л
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ф_Д_Л
Гость
LeXa NalBatНичего, кроме как покрутить константы "оценщика", посоветовать не могу. :( Мы остановились на таких значениях.
effective_cache_size = 8192 # typically 8KB each
random_page_cost = 2 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)


Ну, я ставил
effective_cache_size = 32000
random_page_cost = 2

Ситуация не меняется :(
Переливаю вот в постгрес 8, мож там чего поменяли.
А то workaround'ы типа выключение seq scan, запрос, включение
не особо радуют :-/
...
Рейтинг: 0 / 0
28.01.2005, 16:26
| Ответить | Цитировать | Написать  
Чудеса оптимизатора.
    #32889855
Alex Kab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex Kab
Гость
assaа порядок в "idx_sq_logfile5" btree (tstime, url, username_id) взят из каких соображений (нет ли возможности поменять на (tstime, username_id, l) , не портя работу других выборок?)

и все таки проверьте скорости таких запрсов c индексом вида (tstime, username_id) (можно на тестовой табличке)

Этот индекс специально для другого запроса сделан был, там более детальная выборка производится.
...
Рейтинг: 0 / 0
28.01.2005, 16:29
| Ответить | Цитировать | Написать  
Чудеса оптимизатора.
    #32890796
Alex Kab
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex Kab
Гость
Поставил PostgreSQL 8 - все заработало отлично, выборка за месяц-два использует индексы. Так что прогресс налицо :)
...
Рейтинг: 0 / 0
29.01.2005, 15:37
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Чудеса оптимизатора.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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