Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Помогите оптимизировать запрос , если возможно. / 7 сообщений из 7, страница 1 из 1
06.11.2008, 09:38
    #35636572
ignitor
ignitor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос , если возможно.
задача следующая
По определенным признакам выбрать счета с кодами подразделений и получить остаток по каждому счету из огромной таблицы остатков. Остатки хранятся только за те дни, когда были движения по счету, поэтому разбивать таблицу остатков по периодам по моему не имеет смысла.

Код: 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.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
-- создается временная таблица с нужными счетами
create temp table tmp_acc (idaccanl bigint,charcod character( 3 ) null); 
insert into tmp_acc (
select retail.accanl.id,charcode as charcod 
from retail.accanl 
left join retail.subdivision on retail.subdivision.id=retail.accanl.idsubdivision 
where 
substr(retail.accanl.accanl_pic, 1 , 5 ) in ('40817','40820') 
and  (dateopen<='2008-09-30'::date and dateclose is null or dateclose>='2008-09-30')) 
group by retail.accanl.id,charcode 
union 
select retail.accanl.id,charcode as charcod 
from retail.accanl 
left join retail.subdivision on retail.subdivision.id=retail.accanl.idsubdivision 
where 
substr(retail.accanl.accanl_pic, 1 , 5 ) ='40702' 
and position('КБК' in accanl_name)> 0  
and (dateopen<='2008-09-30'::date and (dateclose is null or dateclose>='2008-09-30')) 
group by accanl.id,charcod
)
-- таблица остатков retail.restanl
-- из индексов на таблице были
CREATE INDEX rest_idx_1  ON retail.restanl  USING btree  (restanl_date);
CREATE INDEX rest_idx_2  ON retail.restanl  USING btree  (idaccanl);
-- этот запрос проходил за ~220 сек
explain analyze 
select 
tmp_acc.idaccanl,abs(rest) as rest,'2008-09-30'::date 
from 
retail.restanl r 
join tmp_acc on (r.idaccanl=tmp_acc.idaccanl) 
where 
r.restanl_date=(
   select r1.restanl_date 
      from retail.restanl r1 
          where r1.idaccanl=r.idaccanl 
          and r1.restanl_date<='2008-09-30'::date 
          order by r1.restanl_date desc limit  1 )
/*
"Nested Loop  (cost=100000000.00..267151239.87 rows=16438 width=16) (actual time=0.424..303603.605 rows=61718 loops=1)"
"  ->  Seq Scan on tmp_acc  (cost=100000000.00..100000427.52 rows=65522 width=8) (actual time=0.008..20.308 rows=66970 loops=1)"
"  ->  Index Scan using rest_idx_2 on restanl r  (cost=0.00..2551.06 rows=1 width=20) (actual time=4.257..4.532 rows=1 loops=66970)"
"        Index Cond: (r.idaccanl = tmp_acc.idaccanl)"
"        Filter: (r.restanl_date = (subplan))"
"        SubPlan"
"          ->  Limit  (cost=50.99..50.99 rows=1 width=4) (actual time=0.092..0.092 rows=1 loops=3216196)"
"                ->  Sort  (cost=50.99..51.00 rows=48 width=4) (actual time=0.091..0.091 rows=1 loops=3216196)"
"                      Sort Key: r1.restanl_date"
"                      Sort Method:  quicksort  Memory: 17kB"
"                      ->  Index Scan using rest_idx_2 on restanl r1  (cost=0.00..50.96 rows=48 width=4) (actual time=0.005..0.065 rows=77 loops=3216196)"
"                            Index Cond: (idaccanl = $0)"
"                            Filter: (restanl_date <= '2008-09-30'::date)"
"Total runtime: 303616.663 ms"
*/
-- после добавил индекс
CREATE INDEX rest_idx_3  ON retail.restanl  USING btree  (idaccanl, restanl_date);
-- запрос теперь выполняется 20 сек.
"Nested Loop  (cost=100000000.00..103467678.88 rows=16438 width=16) (actual time=0.237..27678.401 rows=61718 loops=1)"
"  ->  Seq Scan on tmp_acc  (cost=100000000.00..100000427.52 rows=65522 width=8) (actual time=0.008..19.307 rows=66970 loops=1)"
"  ->  Index Scan using rest_idx_2 on restanl r  (cost=0.00..52.92 rows=1 width=20) (actual time=0.385..0.411 rows=1 loops=66970)"
"        Index Cond: (r.idaccanl = tmp_acc.idaccanl)"
"        Filter: (r.restanl_date = (subplan))"
"        SubPlan"
"          ->  Limit  (cost=0.00..1.02 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=3216196)"
"                ->  Index Scan Backward using rest_idx_3 on restanl r1  (cost=0.00..49.16 rows=48 width=4) (actual time=0.005..0.005 rows=1 loops=3216196)"
"                      Index Cond: ((idaccanl = $0) AND (restanl_date <= '2008-09-30'::date))"
"Total runtime: 27690.590 ms"

можно ли чего-то еще сделать для уменьшения времени выборки?
в таблице retail.restanl ~ 6 млн. записей, счетов 67 тыс.
Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.11.2008, 11:06
    #35636822
Ёш
Ёш
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос , если возможно.
если во временной таблице "много" даных - может быть сделать индексы на временной таблице ? + analyze на неё после этого.
ещё судя по стоимости seq scan и nested loop они у Вас отключены ? что-то ещё отключено ? что выводит такой запрос:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select n, current_setting(n) from (values
  ('enable_bitmapscan'),
  ('enable_hashagg'),
  ('enable_hashjoin'),
  ('enable_indexscan'),
  ('enable_mergejoin'),
  ('enable_nestloop'),
  ('enable_seqscan'),
  ('enable_sort'),
  ('enable_tidscan')
) as s(n);


--
„Истина — это вовсе не то, что можно убедительно доказать, это то, что
делает всё проще и понятнее“ — Антуан де Сент-Экзюпери
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.11.2008, 12:01
    #35637108
ignitor
ignitor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос , если возможно.
"enable_bitmapscan";"on"
"enable_hashagg";"on"
"enable_hashjoin";"on"
"enable_indexscan";"on"
"enable_mergejoin";"on"
"enable_nestloop";"on"
"enable_seqscan";"off"
"enable_sort";"on"
"enable_tidscan";"on"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.11.2008, 12:13
    #35637143
ignitor
ignitor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос , если возможно.
создание индексов по временной таблице +ANALYZE по ней ничего не дали. Те же 20 секунд. Видимо сделать больше ничего нельзя? Получение последнего остатка в хранимку на сервер не поможет?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2008, 00:27
    #35638894
LeXa NalBat
LeXa NalBat
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос , если возможно.
попробуйте и покажите explain analyze запроса:

Код: plaintext
1.
2.
3.
4.
5.
6.
select
  tmp_acc.idaccanl,abs(rest) as rest,'2008-09-30'::date
from
  tmp_acc
  join ( select idaccanl, max(restanl_date) as restanl_date from retail.restanl group by 1 ) as r1
    on ( r1.restanl_date<='2008-09-30'::date  and r1.idaccanl=tmp_acc.idaccanl )
  join retail.restanl r on ( r.idaccanl=r1.idaccanl and r.restanl_date=r1.restanl_date );

в таблице restanl комбинация полей (idaccanl,restanl_date) уникальна?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2008, 03:22
    #35638956
Vladimir Sitnikov
Vladimir Sitnikov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос , если возможно.
Если комбинация полей (idaccanl, restanl_date) в таблице restanl уникальна, то должен работать такой вариант (с двойным индексом по idaccanl, restanl_date):
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
select t.idaccanl, 
       (select abs(rest)
          from restanl
         where idaccanl = t.idaccanl
           and restanl_date<='2008-09-30'::date 
         order by restanl_date desc limit  1 
       ) as rest, '2008-09-30'::date 
  from tmp_acc t

Если же комбинация не уникальна (что вряд ли имеет место), то нужно делать так:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select *
  from restanl rl,
       (select t.idaccanl, 
               (select max(restanl_date)
                  from restanl
                 where idaccanl = t.idaccanl
                   and restanl_date<='2008-09-30'::date 
               ) as restanl_date
          from tmp_acc t
       ) as x
 where rl.idaccanl = x.idaccanl
   and rl.restanl_date = x.restanl_date
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.11.2008, 12:30
    #35643623
ignitor
ignitor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите оптимизировать запрос , если возможно.
Действительно:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
explain analyze 
select  idaccanl,
       (select abs(rest)
          from retail.restanl
         where retail.restanl.idaccanl = t.idaccanl
           and retail.restanl.restanl_date<='2008-09-30'::date 
         order by retail.restanl.restanl_date desc limit  1 
       ) as rest,'2008-09-30'::date 
  from tmp_acc t

"Seq Scan on tmp_acc t  (cost=100000000.00..100068916.65 rows=66970 width=8) (actual time=0.022..534.348 rows=66970 loops=1)"
"  SubPlan"
"    ->  Limit  (cost=0.00..1.02 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=66970)"
"          ->  Index Scan Backward using rest_idx_3 on restanl  (cost=0.00..50.11 rows=49 width=12) (actual time=0.005..0.005 rows=1 loops=66970)"
"                Index Cond: ((idaccanl = $0) AND (restanl_date <= '2008-09-30'::date))"
"Total runtime: 546.483 ms"
Выполняется за 1,5 секунды. Большое спасибо всем кто поучаствовал в обсуждении!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Помогите оптимизировать запрос , если возможно. / 7 сообщений из 7, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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