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

Есть две таблицы (billboards - 180 к, ~ 180 мб, prices - 3.3 мил, ~ 300 мб).

Есть запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
explain analyze
SELECT r_id
FROM regions
LEFT JOIN billboards ON bb_r=r_id AND bb_deleted=0 AND bb_type=0 
LEFT JOIN prices as p1 ON p1.p_bb_id=bb_id AND p1.p_year=2014  AND p1.p_month=4 
WHERE r_parent=2 
GROUP BY r_id



План выполнения
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
HashAggregate  (cost=58678.02..58679.30 rows=128 width=4) (actual time=417.753..417.771 rows=128 loops=1)
  ->  Hash Right Join  (cost=24372.05..58628.80 rows=19686 width=4) (actual time=191.386..414.744 rows=14603 loops=1)
        Hash Cond: (p1.p_bb_id = billboards.bb_id)
        ->  Bitmap Heap Scan on prices p1  (cost=6901.04..39741.75 rows=325114 width=4) (actual time=41.836..169.745 rows=332880 loops=1)
              Recheck Cond: ((p_month = 4) AND (p_year = 2014))
              ->  Bitmap Index Scan on p_month_p_year  (cost=0.00..6819.76 rows=325114 width=0) (actual time=36.856..36.856 rows=332880 loops=1)
                    Index Cond: ((p_month = 4) AND (p_year = 2014))
        ->  Hash  (cost=17252.97..17252.97 rows=17444 width=8) (actual time=149.464..149.464 rows=14446 loops=1)
              Buckets: 2048  Batches: 1  Memory Usage: 565kB
              ->  Hash Right Join  (cost=36.76..17252.97 rows=17444 width=8) (actual time=0.579..146.024 rows=14446 loops=1)
                    Hash Cond: (billboards.bb_r = regions.r_id)
                    ->  Seq Scan on billboards  (cost=0.00..16544.49 rows=132605 width=8) (actual time=0.011..128.980 rows=132867 loops=1)
                          Filter: ((bb_deleted = 0) AND (bb_type = 0))
                          Rows Removed by Filter: 44766
                    ->  Hash  (cost=35.16..35.16 rows=128 width=4) (actual time=0.517..0.517 rows=128 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 5kB
                          ->  Seq Scan on regions  (cost=0.00..35.16 rows=128 width=4) (actual time=0.045..0.471 rows=128 loops=1)
                                Filter: (r_parent = 2)
                                Rows Removed by Filter: 845
Total runtime: 418.250 ms



На выводе генерирует ~120 строк. В таблице regions около 1000 записей.

Можете подсказать способы ускорить запрос. На мой взгляд тут уже ничего не ускоришь, так как строк в самой billboards получается огромное количество (~133к) и в таблице prices (достаточно большое количество ~300к строк).

Сейчас у нас все на Mysql живет. Но тормозит ужасно. Хотим перейти на Postgres. Сейчас оцениваем на сколько получим и получим ли вообще прирост.

Сервер:
2 проца, xeon 5620(4 ядра в каждом)
Оперативки 40 гигов, kvr1333d3d8r9s/4g
массив 14 дисков raid 1+0 контроллер adaptec 5405

Заранее спасибо.
...
Рейтинг: 0 / 0
12.03.2014, 16:59:29
| Ответить | Цитировать | Написать  
Медленный запрос
    #38584334
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
создайте индексы

1) billboards(bb_id) или billboards(bb_type, bb_deleted, bb_id)
и 2) prices(p_bb_id) или prices(p_year, p_month, p_bb_id)

и можно смотреть explain (analyze, buffers) select ...
...
Рейтинг: 0 / 0
12.03.2014, 17:33:05
| Ответить | Цитировать | Написать  
Медленный запрос
    #38584434
vitalik74
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitalik74
Гость
LeXa NalBat,

добавил индексы к существующим billboards(bb_id) и prices(p_bb_id)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
HashAggregate  (cost=58678.02..58679.30 rows=128 width=4) (actual time=384.940..384.960 rows=128 loops=1)
  ->  Hash Right Join  (cost=24372.05..58628.80 rows=19686 width=4) (actual time=177.258..382.000 rows=14603 loops=1)
        Hash Cond: (p1.p_bb_id = billboards.bb_id)
        ->  Bitmap Heap Scan on prices p1  (cost=6901.04..39741.75 rows=325114 width=4) (actual time=41.504..150.874 rows=332880 loops=1)
              Recheck Cond: ((p_year = 2014) AND (p_month = 4))
              ->  Bitmap Index Scan on year_month  (cost=0.00..6819.76 rows=325114 width=0) (actual time=36.524..36.524 rows=332880 loops=1)
                    Index Cond: ((p_year = 2014) AND (p_month = 4))
        ->  Hash  (cost=17252.97..17252.97 rows=17444 width=8) (actual time=135.667..135.667 rows=14446 loops=1)
              Buckets: 2048  Batches: 1  Memory Usage: 565kB
              ->  Hash Right Join  (cost=36.76..17252.97 rows=17444 width=8) (actual time=0.442..132.221 rows=14446 loops=1)
                    Hash Cond: (billboards.bb_r = regions.r_id)
                    ->  Seq Scan on billboards  (cost=0.00..16544.49 rows=132605 width=8) (actual time=0.006..114.938 rows=132867 loops=1)
                          Filter: ((bb_deleted = 0) AND (bb_type = 0))
                          Rows Removed by Filter: 44766
                    ->  Hash  (cost=35.16..35.16 rows=128 width=4) (actual time=0.392..0.392 rows=128 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 5kB
                          ->  Seq Scan on regions  (cost=0.00..35.16 rows=128 width=4) (actual time=0.032..0.353 rows=128 loops=1)
                                Filter: (r_parent = 2)
                                Rows Removed by Filter: 845
Total runtime: 385.421 ms



Удалил billboards(bb_id) и prices(p_bb_id) и добавил billboards(bb_type, bb_deleted, bb_id) и prices(p_year, p_month, p_bb_id)

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
HashAggregate  (cost=58678.02..58679.30 rows=128 width=4) (actual time=384.340..384.358 rows=128 loops=1)
  ->  Hash Right Join  (cost=24372.05..58628.80 rows=19686 width=4) (actual time=175.872..381.379 rows=14603 loops=1)
        Hash Cond: (p1.p_bb_id = billboards.bb_id)
        ->  Bitmap Heap Scan on prices p1  (cost=6901.04..39741.75 rows=325114 width=4) (actual time=42.600..151.945 rows=332880 loops=1)
              Recheck Cond: ((p_year = 2014) AND (p_month = 4))
              ->  Bitmap Index Scan on year_month_p_bb_id  (cost=0.00..6819.76 rows=325114 width=0) (actual time=37.567..37.567 rows=332880 loops=1)
                    Index Cond: ((p_year = 2014) AND (p_month = 4))
        ->  Hash  (cost=17252.97..17252.97 rows=17444 width=8) (actual time=133.185..133.185 rows=14446 loops=1)
              Buckets: 2048  Batches: 1  Memory Usage: 565kB
              ->  Hash Right Join  (cost=36.76..17252.97 rows=17444 width=8) (actual time=0.445..129.789 rows=14446 loops=1)
                    Hash Cond: (billboards.bb_r = regions.r_id)
                    ->  Seq Scan on billboards  (cost=0.00..16544.49 rows=132605 width=8) (actual time=0.008..113.257 rows=132867 loops=1)
                          Filter: ((bb_deleted = 0) AND (bb_type = 0))
                          Rows Removed by Filter: 44766
                    ->  Hash  (cost=35.16..35.16 rows=128 width=4) (actual time=0.393..0.393 rows=128 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 5kB
                          ->  Seq Scan on regions  (cost=0.00..35.16 rows=128 width=4) (actual time=0.031..0.347 rows=128 loops=1)
                                Filter: (r_parent = 2)
                                Rows Removed by Filter: 845
Total runtime: 384.833 ms



Время идентичное.
Индексы последние оставил и сделал explain (analyze, buffers)

Код: 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.
HashAggregate  (cost=58678.02..58679.30 rows=128 width=4) (actual time=398.333..398.348 rows=128 loops=1)
  Buffers: shared hit=37462
  ->  Hash Right Join  (cost=24372.05..58628.80 rows=19686 width=4) (actual time=180.953..395.156 rows=14603 loops=1)
        Hash Cond: (p1.p_bb_id = billboards.bb_id)
        Buffers: shared hit=37462
        ->  Bitmap Heap Scan on prices p1  (cost=6901.04..39741.75 rows=325114 width=4) (actual time=42.533..155.304 rows=332880 loops=1)
              Recheck Cond: ((p_year = 2014) AND (p_month = 4))
              Buffers: shared hit=23559
              ->  Bitmap Index Scan on year_month_p_bb_id  (cost=0.00..6819.76 rows=325114 width=0) (actual time=37.508..37.508 rows=332880 loops=1)
                    Index Cond: ((p_year = 2014) AND (p_month = 4))
                    Buffers: shared hit=916
        ->  Hash  (cost=17252.97..17252.97 rows=17444 width=8) (actual time=138.336..138.336 rows=14446 loops=1)
              Buckets: 2048  Batches: 1  Memory Usage: 565kB
              Buffers: shared hit=13903
              ->  Hash Right Join  (cost=36.76..17252.97 rows=17444 width=8) (actual time=0.454..134.376 rows=14446 loops=1)
                    Hash Cond: (billboards.bb_r = regions.r_id)
                    Buffers: shared hit=13903
                    ->  Seq Scan on billboards  (cost=0.00..16544.49 rows=132605 width=8) (actual time=0.008..115.087 rows=132867 loops=1)
                          Filter: ((bb_deleted = 0) AND (bb_type = 0))
                          Rows Removed by Filter: 44766
                          Buffers: shared hit=13880
                    ->  Hash  (cost=35.16..35.16 rows=128 width=4) (actual time=0.400..0.400 rows=128 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 5kB
                          Buffers: shared hit=23
                          ->  Seq Scan on regions  (cost=0.00..35.16 rows=128 width=4) (actual time=0.030..0.352 rows=128 loops=1)
                                Filter: (r_parent = 2)
                                Rows Removed by Filter: 845
                                Buffers: shared hit=23
Total runtime: 398.818 ms
...
Рейтинг: 0 / 0
12.03.2014, 19:01:33
| Ответить | Цитировать | Написать  
Медленный запрос
    #38584497
Hawkmoon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hawkmoon
Участник
Можно попытаться убрать
time=0.008..115
от Seq Scan on billboards.

Может, что-то выйдет.

http://www.postgresql.org/docs/9.1/static/indexes-expressional.html
CREATE INDEX billboards_active_rows_index ON billboards((bb_deleted = 0) AND (bb_type = 0));
ANALYZE billboards;
еще раз.

?
...
Рейтинг: 0 / 0
12.03.2014, 20:16:05
| Ответить | Цитировать | Написать  
Медленный запрос
    #38584523
vitalik74
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitalik74
Гость
Hawkmoon,

создал индекс так
Код: sql
1.
create index billboards_active_rows_index  on billboards (bb_deleted, bb_type ) where (bb_deleted = 0 AND bb_type = 0);




Код: 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.
HashAggregate  (cost=58533.29..58534.57 rows=128 width=4) (actual time=398.448..398.466 rows=128 loops=1)
  Buffers: shared hit=37462
  ->  Hash Right Join  (cost=24296.93..58484.65 rows=19456 width=4) (actual time=181.317..395.308 rows=14603 loops=1)
        Hash Cond: (p1.p_bb_id = billboards.bb_id)
        Buffers: shared hit=37462
        ->  Bitmap Heap Scan on prices p1  (cost=6824.56..39611.88 rows=321555 width=4) (actual time=42.503..154.400 rows=332880 loops=1)
              Recheck Cond: ((p_year = 2014) AND (p_month = 4))
              Buffers: shared hit=23559
              ->  Bitmap Index Scan on year_month_p_bb_id  (cost=0.00..6744.17 rows=321555 width=0) (actual time=37.468..37.468 rows=332880 loops=1)
                    Index Cond: ((p_year = 2014) AND (p_month = 4))
                    Buffers: shared hit=916
        ->  Hash  (cost=17253.99..17253.99 rows=17471 width=8) (actual time=138.731..138.731 rows=14446 loops=1)
              Buckets: 2048  Batches: 1  Memory Usage: 565kB
              Buffers: shared hit=13903
              ->  Hash Right Join  (cost=36.76..17253.99 rows=17471 width=8) (actual time=0.451..134.923 rows=14446 loops=1)
                    Hash Cond: (billboards.bb_r = regions.r_id)
                    Buffers: shared hit=13903
                    ->  Seq Scan on billboards  (cost=0.00..16544.49 rows=132806 width=8) (actual time=0.008..115.631 rows=132867 loops=1)
                          Filter: ((bb_deleted = 0) AND (bb_type = 0))
                          Rows Removed by Filter: 44766
                          Buffers: shared hit=13880
                    ->  Hash  (cost=35.16..35.16 rows=128 width=4) (actual time=0.397..0.397 rows=128 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 5kB
                          Buffers: shared hit=23
                          ->  Seq Scan on regions  (cost=0.00..35.16 rows=128 width=4) (actual time=0.030..0.350 rows=128 loops=1)
                                Filter: (r_parent = 2)
                                Rows Removed by Filter: 845
                                Buffers: shared hit=23
Total runtime: 398.931 ms



Отмечу, что bb_deleted=1 всего 210 строк. Так что тут в любом случае проще скан таблицы делать.

Может какие-нибудь настройки сервера изменить/показать?
...
Рейтинг: 0 / 0
12.03.2014, 20:44:48
| Ответить | Цитировать | Написать  
Медленный запрос
    #38584538
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
vitalik74
Код: sql
1.
2.
3.
4.
5.
6.
7.
explain analyze
SELECT r_id
FROM regions
LEFT JOIN billboards ON bb_r=r_id AND bb_deleted=0 AND bb_type=0 
LEFT JOIN prices as p1 ON p1.p_bb_id=bb_id AND p1.p_year=2014  AND p1.p_month=4 
WHERE r_parent=2 
GROUP BY r_id


день добрый,
можете пояснить суть запроса? может тогда чтото можно будет предложить, а то покачто уберите LEFT JOIN совсем и GROUP BY, и получите тот же результат но думается на много бстрее. как уже сказал, обясните суть вопроса, т.е. что вы пытаетесь действително получить (как уже знаем), и тогда может ктото предложит вам другой путь который будет более коротким, но будет вести туда куда вам надо.
...
Рейтинг: 0 / 0
12.03.2014, 21:14:48
| Ответить | Цитировать | Написать  
Медленный запрос
    #38584569
vitalik74
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitalik74
Гость
Lonepsycho,

Этот запрос упрощенная часть другого запроса(даже группы запросов). Нужно получить список регионов и сколько в них щитов и сколько щитов с ценами.
Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT r_id,r_capital, r_type, r_other, r_name, COUNT(bb_id) AS bb_all, 
SUM( case when (p1.p_bb_id > 0) then 1 else 0 end ) AS price_cnt
FROM regions 
LEFT JOIN billboards ON (bb_r=r_id) AND bb_deleted=0 AND bb_type=0 
LEFT JOIN prices as p1 ON p1.p_bb_id=bb_id AND p1.p_month=4
WHERE r_parent=2 
GROUP BY r_id,r_id,r_capital, r_type, r_other, r_name



Тут я повторюсь. Интересует даже не сам этот запрос. А как будет себя вести Postgres при агрегировании больших данных когда из billboards выбирается 40к и к ней джойнится prices (как правило не один джойн,а несколько). И эти 40к из billboards никак не сократить, потому что это и есть все верные данные. Поэтому я привел наиболее упрощенную версию запроса.
...
Рейтинг: 0 / 0
12.03.2014, 21:58:55
| Ответить | Цитировать | Написать  
Медленный запрос
    #38584580
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
vitalik74,

пересоздайте индех

Код: sql
1.
create unique index billboards_active_rows_index  on billboards (bb_r, bb_id) where (bb_deleted = 0 AND bb_type = 0);


незабываем ANALYZE billboards;
...
Рейтинг: 0 / 0
12.03.2014, 22:46:58
| Ответить | Цитировать | Написать  
Медленный запрос
    #38584690
vitalik74
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitalik74
Гость
Lonepsycho,

Добавил индекс
Код: sql
1.
CREATE UNIQUE INDEX billboards_active_rows_index2 ON billboards USING btree (bb_r, bb_id) WHERE bb_deleted = 0 AND bb_type = 0



Результат такой же
Код: 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.
QUERY PLAN
HashAggregate  (cost=58458.22..58459.50 rows=128 width=4) (actual time=397.609..397.627 rows=128 loops=1)
  Buffers: shared hit=37462
  ->  Hash Right Join  (cost=24264.46..58411.62 rows=18639 width=4) (actual time=180.477..394.472 rows=14603 loops=1)
        Hash Cond: (p1.p_bb_id = billboards.bb_id)
        Buffers: shared hit=37462
        ->  Bitmap Heap Scan on prices p1  (cost=6790.86..39552.28 rows=319828 width=4) (actual time=43.535..155.254 rows=332880 loops=1)
              Recheck Cond: ((p_year = 2014) AND (p_month = 4))
              Buffers: shared hit=23559
              ->  Bitmap Index Scan on year_month_p_bb_id  (cost=0.00..6710.90 rows=319828 width=0) (actual time=38.471..38.471 rows=332880 loops=1)
                    Index Cond: ((p_year = 2014) AND (p_month = 4))
                    Buffers: shared hit=916
        ->  Hash  (cost=17254.91..17254.91 rows=17495 width=8) (actual time=136.863..136.863 rows=14446 loops=1)
              Buckets: 2048  Batches: 1  Memory Usage: 565kB
              Buffers: shared hit=13903
              ->  Hash Right Join  (cost=36.76..17254.91 rows=17495 width=8) (actual time=0.450..133.061 rows=14446 loops=1)
                    Hash Cond: (billboards.bb_r = regions.r_id)
                    Buffers: shared hit=13903
                    ->  Seq Scan on billboards  (cost=0.00..16544.49 rows=132988 width=8) (actual time=0.007..114.488 rows=132867 loops=1)
                          Filter: ((bb_deleted = 0) AND (bb_type = 0))
                          Rows Removed by Filter: 44766
                          Buffers: shared hit=13880
                    ->  Hash  (cost=35.16..35.16 rows=128 width=4) (actual time=0.396..0.396 rows=128 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 5kB
                          Buffers: shared hit=23
                          ->  Seq Scan on regions  (cost=0.00..35.16 rows=128 width=4) (actual time=0.030..0.351 rows=128 loops=1)
                                Filter: (r_parent = 2)
                                Rows Removed by Filter: 845
                                Buffers: shared hit=23
Total runtime: 398.084 ms
...
Рейтинг: 0 / 0
13.03.2014, 08:27:44
| Ответить | Цитировать | Написать  
Медленный запрос
    #38584879
Lonepsycho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lonepsycho
Участник
vitalik74,

а можете представить DDL всех трёх таблиц?
...
Рейтинг: 0 / 0
13.03.2014, 11:12:27
| Ответить | Цитировать | Написать  
Медленный запрос
    #38584920
vitalik74
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitalik74
Гость
Lonepsycho,


Код: 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.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
CREATE TABLE billboards (
    bb_id integer NOT NULL,
    bb_address character varying(500) NOT NULL,
    bb_address2 character varying(500) NOT NULL,
    bb_type smallint DEFAULT 0::smallint NOT NULL,
    bb_realsize character varying(255) NOT NULL,
    bb_side character varying(4) NOT NULL,
    bb_side_type smallint DEFAULT 0::smallint,
    bb_r integer NOT NULL,
    bb_r2 integer DEFAULT 0 NOT NULL,
    bb_owner integer NOT NULL,
    bb_owner_code character varying(255) NOT NULL,
    bb_baseprice double precision NOT NULL,
    bb_mount integer NOT NULL,
    bb_mount_nds smallint NOT NULL,
    bb_nds smallint NOT NULL,
    bb_prism smallint NOT NULL,
    bb_image character varying(255) NOT NULL,
    bb_image2 character varying(255) NOT NULL,
    bb_delta integer DEFAULT 0 NOT NULL,
    bb_marker integer NOT NULL,
    bb_marker2 integer NOT NULL,
    bb_deleted smallint NOT NULL,
    bb_thumb character varying(255) NOT NULL,
    bb_thumb2 character varying(255) NOT NULL,
    bb_shema character varying(255) NOT NULL,
    bb_x numeric(17,14) NOT NULL,
    bb_y numeric(17,14) NOT NULL,
    bb_x2 integer NOT NULL,
    bb_y2 integer NOT NULL,
    bb_lock_date integer,
    bb_lock_user integer,
    bb_map_last_update_user integer,
    bb_map_last_update_date timestamp without time zone,
    bb_panorama_last_update_date integer,
    bb_panorama_last_update_user integer,
    bb_date timestamp without time zone,
    bb_yandex_panorama character varying(255) NOT NULL,
    bb_ots double precision DEFAULT 0::double precision NOT NULL,
    bb_gps double precision DEFAULT 0::double precision NOT NULL,
    bb_cpt double precision DEFAULT 0::double precision NOT NULL,
    bb_light smallint NOT NULL,
    bb_alco smallint DEFAULT 0::smallint NOT NULL,
    bb_mkad integer DEFAULT 0 NOT NULL,
    bb_dest integer DEFAULT 0 NOT NULL,
    bb_pellicle smallint NOT NULL,
    bb_banner smallint NOT NULL,
    bb_paper smallint NOT NULL,
    bb_pellicle_price integer NOT NULL,
    bb_paper_price integer NOT NULL,
    bb_banner_price integer NOT NULL,
    bb_plastic smallint NOT NULL,
    bb_plastic_price integer NOT NULL,
    bb_ignore_delta integer NOT NULL,
    bb_espar_id character varying(20) NOT NULL,
    bb_lastupdate integer NOT NULL,
    bb_lastupdater integer NOT NULL,
    bb_espar_id_lastupdate integer NOT NULL,
    bb_moderated integer DEFAULT 1 NOT NULL,
    bb_hide_flag smallint NOT NULL,
    bb_link_owner_site character varying(255) NOT NULL,
    bb_fire_update integer NOT NULL
);



CREATE INDEX asdasd ON billboards USING btree (bb_deleted, bb_type, bb_r, bb_r2);

CREATE INDEX bb_deleted_bb_type ON billboards USING btree (bb_deleted, bb_type);

CREATE INDEX bb_image ON billboards USING btree (bb_image);

CREATE INDEX bb_r_bb_r2 ON billboards USING btree (bb_r, bb_r2);

CREATE INDEX billboards_active_rows_index ON billboards USING btree (bb_deleted, bb_type) WHERE ((bb_deleted = 0) AND (bb_type = 0));

CREATE UNIQUE INDEX billboards_active_rows_index2 ON billboards USING btree (bb_r, bb_id) WHERE ((bb_deleted = 0) AND (bb_type = 0));

CREATE INDEX type_deleted_bb_id ON billboards USING btree (bb_type, bb_deleted, bb_id);
...
Рейтинг: 0 / 0
13.03.2014, 11:45:02
| Ответить | Цитировать | Написать  
Медленный запрос
    #38584927
vitalik74
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitalik74
Гость
Lonepsycho,


Сорри, случайно нажал опубликовать, а редактировать не нашел что-то? остальные две таблицы

Код: 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.
CREATE TABLE regions (
    r_id integer NOT NULL,
    r_name character varying(255) NOT NULL,
    r_order integer DEFAULT 0 NOT NULL,
    r_x numeric(17,14) NOT NULL,
    r_y numeric(17,14) NOT NULL,
    map_type character varying(55) DEFAULT 'G'::character varying,
    r_parent integer DEFAULT 0 NOT NULL,
    r_type integer DEFAULT 0 NOT NULL,
    r_capital smallint NOT NULL,
    r_other smallint NOT NULL,
    r_nomap integer DEFAULT 0 NOT NULL,
    r_panorama integer DEFAULT 0 NOT NULL,
    r_trassa integer DEFAULT 0 NOT NULL,
    r_trnum integer DEFAULT 0 NOT NULL,
    r_first double precision NOT NULL,
    r_second double precision NOT NULL,
    r_grp double precision NOT NULL,
    r_people integer NOT NULL,
    r_klatter double precision NOT NULL,
    r_allowance integer NOT NULL,
    r_note text NOT NULL,
    r_show_filter integer DEFAULT 0 NOT NULL,
    r_nopaper integer DEFAULT 0 NOT NULL,
    r_near character varying(255) NOT NULL,
    r_see_to character varying(255) NOT NULL,
    r_see_out character varying(255) NOT NULL
);



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
CREATE TABLE prices (
    p_id integer NOT NULL,
    p_bb_id integer NOT NULL,
    p_year smallint NOT NULL,
    p_month smallint NOT NULL,
    p_flag smallint NOT NULL,
    p_price integer NOT NULL,
    p_price2 integer NOT NULL,
    p_status smallint NOT NULL,
    p_fire smallint NOT NULL,
    p_fire_help character varying(255) NOT NULL,
    p_test smallint NOT NULL,
    p_res smallint DEFAULT 0::smallint NOT NULL,
    p_delta_flag integer NOT NULL
);

CREATE INDEX "all" ON prices USING btree (p_bb_id, p_year, p_month, p_flag, p_price2);

CREATE INDEX year_month_p_bb_id ON prices USING btree (p_year, p_month, p_bb_id);
...
Рейтинг: 0 / 0
13.03.2014, 11:50:44
| Ответить | Цитировать | Написать  
Медленный запрос
    #38585118
Hawkmoon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hawkmoon
Участник
vitalik74Сейчас у нас все на Mysql живет. Но тормозит ужасно.

Интересно посмотреть на циферки из Mysql между делом.

Потом, 4 миллиона записей в prices, 0,5 секунды - это долго? Какой порядок циферок и зачем нужен?
...
Рейтинг: 0 / 0
13.03.2014, 13:36:03
| Ответить | Цитировать | Написать  
Медленный запрос
    #38585881
vitalik74
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitalik74
Гость
Hawkmoon,

Запрос для MySQL
Код: sql
1.
2.
3.
4.
5.
6.
SELECT SQL_NO_CACHE r_id
FROM regions
LEFT JOIN billboards ON bb_r=r_id AND bb_deleted=0 AND bb_type=0 
LEFT JOIN prices as p1 ON p1.p_bb_id=bb_id AND p1.p_year=2014  AND p1.p_month=4 
WHERE r_parent=2 
GROUP BY r_id



В MySQL на рабочей базе выполняется за 0.04-0.05 на горячем, но и на холодном цифры были почти такие же. Но некоторые другие запросы в Postgres быстрее. Может, конечно, Postgres у нас криво настроен.

авторКакой порядок циферок и зачем нужен?

Хотелось бы порядок хотя бы 0.2 для данного запроса в Postgres.
...
Рейтинг: 0 / 0
14.03.2014, 09:22:15
| Ответить | Цитировать | Написать  
Медленный запрос
    #38585975
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
vitalik74,

попробуйте изменить план запроса с hash join на nested loop.
чтобы выбирать из таблиц billboards и prices не 130К и 330К, а только нужные 14К.
но я не уверен, что такой план окажется быстрее.

set enable_hashjoin to off;
...
Рейтинг: 0 / 0
14.03.2014, 10:48:37
| Ответить | Цитировать | Написать  
Медленный запрос
    #38586284
vitalik74
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitalik74
Гость
LeXa NalBat,

Эх... не помогает

Код: 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.
HashAggregate  (cost=107081.12..107082.40 rows=128 width=4) (actual time=784.167..784.183 rows=128 loops=1)
  Buffers: shared hit=37462, temp read=754 written=863
  ->  Merge Right Join  (cost=105298.62..107034.53 rows=18639 width=4) (actual time=699.465..781.263 rows=14603 loops=1)
        Merge Cond: (p1.p_bb_id = billboards.bb_id)
        Buffers: shared hit=37462, temp read=754 written=863
        ->  Sort  (cost=73170.65..73970.22 rows=319828 width=4) (actual time=404.450..452.431 rows=254152 loops=1)
              Sort Key: p1.p_bb_id
              Sort Method: external merge  Disk: 4544kB
              Buffers: shared hit=23559, temp read=461 written=570
              ->  Bitmap Heap Scan on prices p1  (cost=6790.86..39552.28 rows=319828 width=4) (actual time=46.603..175.252 rows=332880 loops=1)
                    Recheck Cond: ((p_year = 2014) AND (p_month = 4))
                    Buffers: shared hit=23559
                    ->  Bitmap Index Scan on year_month_p_bb_id  (cost=0.00..6710.90 rows=319828 width=0) (actual time=41.572..41.572 rows=332880 loops=1)
                          Index Cond: ((p_year = 2014) AND (p_month = 4))
                          Buffers: shared hit=916
        ->  Sort  (cost=32127.97..32171.71 rows=17495 width=8) (actual time=295.009..296.368 rows=14603 loops=1)
              Sort Key: billboards.bb_id
              Sort Method: quicksort  Memory: 1062kB
              Buffers: shared hit=13903, temp read=293 written=293
              ->  Merge Left Join  (cost=29722.04..30895.04 rows=17495 width=8) (actual time=267.124..290.270 rows=14446 loops=1)
                    Merge Cond: (regions.r_id = billboards.bb_r)
                    Buffers: shared hit=13903, temp read=293 written=293
                    ->  Sort  (cost=39.64..39.96 rows=128 width=4) (actual time=0.278..0.294 rows=128 loops=1)
                          Sort Key: regions.r_id
                          Sort Method: quicksort  Memory: 31kB
                          Buffers: shared hit=23
                          ->  Seq Scan on regions  (cost=0.00..35.16 rows=128 width=4) (actual time=0.029..0.246 rows=128 loops=1)
                                Filter: (r_parent = 2)
                                Rows Removed by Filter: 845
                                Buffers: shared hit=23
                    ->  Materialize  (cost=29682.40..30347.34 rows=132988 width=8) (actual time=226.950..270.054 rows=132849 loops=1)
                          Buffers: shared hit=13880, temp read=293 written=293
                          ->  Sort  (cost=29682.40..30014.87 rows=132988 width=8) (actual time=226.944..249.784 rows=132849 loops=1)
                                Sort Key: billboards.bb_r
                                Sort Method: external merge  Disk: 2336kB
                                Buffers: shared hit=13880, temp read=293 written=293
                                ->  Seq Scan on billboards  (cost=0.00..16544.49 rows=132988 width=8) (actual time=0.008..122.021 rows=132867 loops=1)
                                      Filter: ((bb_deleted = 0) AND (bb_type = 0))
                                      Rows Removed by Filter: 44766
                                      Buffers: shared hit=13880
Total runtime: 787.072 ms



Получается что максимальная скорость уже теоретически достигнута. Так как планы до этого показывали по буфферу, что сравнение идет в памяти, я прав?
...
Рейтинг: 0 / 0
14.03.2014, 13:57:23
| Ответить | Цитировать | Написать  
Медленный запрос
    #38586373
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
vitalik74LeXa NalBat,

Эх... не помогаетпопробуйте добиться плана через nested loop. а у вас получился merge join, отключите и его тоже
set enable_mergejoin to off;

vitalik74... планы до этого показывали по буфферу, что сравнение идет в памяти, я прав?вроде бы да, shared read с диска отсутствует, есть только shared hit из памяти. то есть чтение горячее.
...
Рейтинг: 0 / 0
14.03.2014, 14:38:27
| Ответить | Цитировать | Написать  
Медленный запрос
    #38586431
daevy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
daevy
Гость
LeXa NalBat, vitalik74

пардон что вмешиваюсь)) сколько у вас work_mem? попробуйте увеличить. навеяно этим
...
Рейтинг: 0 / 0
14.03.2014, 15:07:32
| Ответить | Цитировать | Написать  
Медленный запрос
    #38586454
Оптимизатор запросов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизатор запросов
Гость
vitalik74,

Попробуйте запрос без explain analyze, у меня это быстрее. Ну и может быть еще
create index prices_idx2 on prices(p_bb_id, p_year, p_month);
...
Рейтинг: 0 / 0
14.03.2014, 15:16:01
| Ответить | Цитировать | Написать  
Медленный запрос
    #38586530
vitalik74
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitalik74
Гость
LeXa NalBatvitalik74LeXa NalBat,

Эх... не помогаетпопробуйте добиться плана через nested loop. а у вас получился merge join, отключите и его тоже
set enable_mergejoin to off;

vitalik74... планы до этого показывали по буфферу, что сравнение идет в памяти, я прав?вроде бы да, shared read с диска отсутствует, есть только shared hit из памяти. то есть чтение горячее.

Есть!))

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
set enable_mergejoin to off;
set enable_hashjoin to off;
explain analyze
SELECT r_id
FROM regions
LEFT JOIN billboards ON bb_r=r_id AND bb_deleted=0 AND bb_type=0 
LEFT JOIN prices as p1 ON p1.p_bb_id=bb_id AND p1.p_year=2014  AND p1.p_month=4 
WHERE r_parent=2 
GROUP BY r_id



Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
HashAggregate  (cost=140325.56..140326.84 rows=128 width=4) (actual time=97.715..97.732 rows=128 loops=1)
  ->  Nested Loop Left Join  (cost=0.00..140278.96 rows=18639 width=4) (actual time=0.189..93.488 rows=14603 loops=1)
        ->  Nested Loop Left Join  (cost=0.00..32640.80 rows=17495 width=8) (actual time=0.118..19.790 rows=14446 loops=1)
              ->  Seq Scan on regions  (cost=0.00..35.16 rows=128 width=4) (actual time=0.034..0.308 rows=128 loops=1)
                    Filter: (r_parent = 2)
                    Rows Removed by Filter: 845
              ->  Index Scan using asdasd on billboards  (cost=0.00..252.91 rows=182 width=8) (actual time=0.009..0.131 rows=113 loops=128)
                    Index Cond: ((bb_deleted = 0) AND (bb_type = 0) AND (bb_r = regions.r_id))
        ->  Index Only Scan using year_month_p_bb_id on prices p1  (cost=0.00..6.14 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=14446)
              Index Cond: ((p_year = 2014) AND (p_month = 4) AND (p_bb_id = billboards.bb_id))
              Heap Fetches: 14304
Total runtime: 97.920 ms




Спасибо за советы)

Вот только встречный вопрос получается подобные запросы надо как-то самому отлавливать и ставить такие настройки, раз оптимизатор не смог лучше сделать?
...
Рейтинг: 0 / 0
14.03.2014, 16:02:48
| Ответить | Цитировать | Написать  
Медленный запрос
    #38586535
vitalik74
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitalik74
Гость
daevy,

Стояло 4 mb.

Я экспериментировал с
Код: sql
1.
SET work_mem = '64kB';


стало в 2 раза медленнее. Ставил

Код: sql
1.
2.
SET work_mem to '8MB';
SET work_mem to '16MB';



Скорости такие же. В данном случае помогли
Код: sql
1.
2.
set enable_mergejoin to off;
set enable_hashjoin to off;
...
Рейтинг: 0 / 0
14.03.2014, 16:05:46
| Ответить | Цитировать | Написать  
Медленный запрос
    #38586536
Фотография Misha Tyurin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Misha Tyurin
Участник
vitalik74,

попробуйте подкрутить автоаналайз на почаще и самое главное таргет по максимуму для статистики.

ну и храните в одной таблице примерно "одинаково" распределенные по колонкам объекты
...
Рейтинг: 0 / 0
14.03.2014, 16:06:31
| Ответить | Цитировать | Написать  
Медленный запрос
    #38586538
vitalik74
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vitalik74
Гость
Оптимизатор запросов,


Думаю этот индекс не поможет, так как уже есть

Код: sql
1.
CREATE INDEX "all" ON prices USING btree (p_bb_id, p_year, p_month, p_flag, p_price2);



Единственное этот новый будет меньше весить при чтении.
...
Рейтинг: 0 / 0
14.03.2014, 16:07:07
| Ответить | Цитировать | Написать  
Медленный запрос
    #38586611
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
vitalik74Вот только встречный вопрос получается подобные запросы надо как-то самому отлавливать и ставить такие настройки, раз оптимизатор не смог лучше сделать?при выборе плана из нескольких возможных вариантов постгрес оценивает стоимость каждого из них - это цифра cost в выдаче explain. идеальное состояние, когда для всех вариантов cost прямо пропорционален реальному времени выполнения actual time. тогда постгрес выберет вариант с наименьшим cost, и у этого же варианта будет и наименьший actual time. в вашем случае постгрес выбрал неоптимальный план из-за того, что ошибся в десять раз: k(hashjoin) = 58679.30 / 417.771 = 140, k(nestedloop) = 140326.84 / 97.732 = 1435.

сможет ли постгрес правильно оценить все варианты некоторого запроса? а если не только этого запроса, а вообще любого? а при разных условиях, холодный запуск vs горячий?

можно попробовать потюнить глобальные константы планировщика в конфиге постгреса 18.7.2. Planner Cost Constants . я как-то давно пробовал это делать в ручном режиме методом научного тыка, в результате тогда мы изменили в конфиге значение одной из констант раза в два-три.
...
Рейтинг: 0 / 0
14.03.2014, 16:59:43
| Ответить | Цитировать | Написать  
Медленный запрос
    #38586685
Hawkmoon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hawkmoon
Участник
LeXa NalBat,

А хинтовать ему (oracle-style) каким методом делать низя?
Ногами не пинать, планы только изучаю.
...
Рейтинг: 0 / 0
14.03.2014, 17:41:53
| Ответить | Цитировать | Написать  
25 сообщений из 29, страница 1 из 2
  1  все
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Медленный запрос
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=38586536&tid=1998721]:
 0ms
get settings:
 8ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 283ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 82ms
get tp. blocked users:
 2ms
others: 242ms
total:  655ms
созд. / загр.: 655ms
x
x
Закрыть


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