ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / вьюха вместо триггеров
18 сообщений из 18, страница 1 из 1
вьюха вместо триггеров
    #33142702
URIX
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
URIX
Гость
такая ситуация: есть две таблицы (аналог для приходных или расходных накладных/счетов)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
create table pwb(
    id      serial primary key,
    dt      timestamp default now(),
    spl_id  integer
);
create index pwb_id_ind on pwb(id);
create index pwb_dt_ind on pwb(dt);
create index pwb_spl_id_ind on pwb(spl_id);

create table pwr(
    pwb_id  integer,
    name    text
);
create index pwr_pwb_id_ind on pwr(pwb_id);
create index pwr_name_ind on pwr(name);
есть огромное желание пользоваться вьюхами и еще большее НЕжелание расставлять триггера (для создания "материализованных вьюх"), т.к. разнородных выборок будет море и меняться они будут по десять раз в неделю
заполняю таблицы:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
#!/usr/bin/perl -W
use strict;
use DBI;
my $dbh=DBI->connect('DBI:Pg:dbname=test','diva');
foreach my $i ( 1  ..  200000 ){
    my $query1="insert into pwb(spl_id) values(".($i %  20 ).")";
    $dbh->do($query1);
    foreach( 0  ..  9 ){
        my $query2="insert into pwr(pwb_id,name) values(currval('pwb_id_seq'),'n
ame".int(rand()*1000)."')";
        $dbh->do($query2);
    }
}
$dbh->disconnect();
вот основной тип запросов:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
EXPLAIN ANALYZE select * from pwr where pwb_id in (select id from pwb where spl_id= 4  and dt between (now()-'30 minute'::interval) and (now()-'10 minute'::interval)) and name='name44';
                                                                            QUERY PLAN                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost= 17 . 17 .. 34 . 26  rows= 1  width= 36 ) (actual time= 140 . 671 .. 460 . 603  rows= 39  loops= 1 )
   ->  HashAggregate  (cost= 17 . 17 .. 17 . 17  rows= 1  width= 4 ) (actual time= 130 . 459 .. 139 . 990  rows= 3601  loops= 1 )
         ->  Index Scan using pwb_spl_id_ind on pwb  (cost= 0 . 00 .. 17 . 17  rows= 1  width= 4 ) (actual time= 4 . 315 .. 122 . 583  rows= 3601  loops= 1 )
               Index Cond: (spl_id =  4 )
               Filter: (((dt)::timestamp with time zone >= (now() - '00:30:00'::interval)) AND ((dt)::timestamp with time zone <= (now() - '00:10:00'::interval)))
   ->  Index Scan using pwr_pwb_id_ind on pwr  (cost= 0 . 00 .. 17 . 08  rows= 1  width= 36 ) (actual time= 0 . 086 .. 0 . 086  rows= 0  loops= 3601 )
         Index Cond: (pwr.pwb_id = "outer".id)
         Filter: (name = 'name44'::text)
 Total runtime:  460 . 957  ms

сильно смущает "Filter: (name = 'name44'::text)" и "Filter: (((dt)::timestamp with time zone >= (now() - '00:30:00'::interval)) AND ((dt)::timestamp with time zone <= (now() - '00:10:00'::interval)))" хотелось бы использовать индексы в данном случае. собственно, вопрос: как это сделать и на сколько вообще это реально (обойтись одними вьюхами)? за год накапливается около сотни тысяч накладных. выгребать их раньше чем через 5 лет крайне не желательно.

з.ы. сейчас гоняю все это дело на рабочей машине. стоит
Код: plaintext
1.
2.
3.
SELECT version();
                                version                                 
------------------------------------------------------------------------
 PostgreSQL  7 . 4 . 6  on i686-pc-linux-gnu, compiled by GCC gcc (GCC)  3 . 3 . 4
рабочий сервак будет на порядок мощнее
з.з.ы. время такой выборки с любыми условиями должно быть не более секунды при максимальной заполненности базы
...
Рейтинг: 0 / 0
30.06.2005, 14:00
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33142839
mwolf
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mwolf
Участник
Дык эта, если поиск идёт по 2 полям одновременно, сделай индекс по двум полям сразу.
Причём в обих таблицах это надо сделать.
В pwb по (spl_id, dt)
В pwr по (pwb_id, name)
...
Рейтинг: 0 / 0
30.06.2005, 14:35
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33142873
mwolf
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mwolf
Участник
Гм. Вопрос про другое был. :-)
В Постгресе представления не индексируются и не "материализуются", отсюда надо и плясать.
Если запрос на котором основано представление достаточно оптимизирован, то представления хватит, но если запрос тяжёлый, а данные не сильно часто изменяются, то прийдётся делать материализированое(индексированое) "представление". То есть создавать таблицу, которая будет заполняться с помощью триггеров.
...
Рейтинг: 0 / 0
30.06.2005, 14:43
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33142971
URIX
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
URIX
Гость
mwolfДык эта, если поиск идёт по 2 полям одновременно, сделай индекс по двум полям сразу.
Причём в обих таблицах это надо сделать.
В pwb по (spl_id, dt)
В pwr по (pwb_id, name)оп... точно %)) виноват, дурак, исправлюсь ;) напрочь забыл %)
немного помогло:EXPLAIN ANALYZE select * from pwr where pwb_id in (select id from pwb where spl_id=4 and dt between (now()-'200 minute'::interval) and (now()-'0 minute'::interval)) and name='name44';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2931.36..4188.21 rows=1 width=36) (actual time=187.951..645.679 rows=98 loops=1)
-> HashAggregate (cost=2931.36..2931.36 rows=6 width=4) (actual time=183.515..208.794 rows=10316 loops=1)
-> Index Scan using pwb_spl_id_ind on pwb (cost=0.00..2931.35 rows=6 width=4) (actual time=0.353..159.445 rows=10316 loops=1)
Index Cond: (spl_id = 4)
Filter: (((dt)::timestamp with time zone >= (now() - '03:20:00'::interval)) AND ((dt)::timestamp with time zone <= (now() - '00:00:00'::interval)))
-> Index Scan using pwr_pwb_id_name_ind on pwr (cost=0.00..208.83 rows=52 width=36) (actual time=0.039..0.039 rows=0 loops=10316)
Index Cond: ((pwr.pwb_id = "outer".id) AND (pwr.name = 'name44'::text))
Total runtime: 646.215 ms(хотя, время увеличилось. может, надо неиспользуемые индексы грохнуть?)
правда, по timestamp все равно, фильтр гоняет. вот что с ним можно сделать, чтоб не было такого?

mwolfВ Постгресе представления не индексируются и не "материализуются", отсюда надо и плясать.
Если запрос на котором основано представление достаточно оптимизирован, то представления хватит, но если запрос тяжёлый, а данные не сильно часто изменяются, то прийдётся делать материализированое(индексированое) "представление". То есть создавать таблицу, которая будет заполняться с помощью триггеров.тут полностью согласен, но стоит учесть и такой момент: индексы-то лежат в "материализованном" виде. да и заполняться он будет по быстрее чем обычная табличка через ХР в триггере. в общем-то именно от этого и отталкиваюсь. т.е. хотелось бы построить индексы, по которым будут идти все эти выборки
...
Рейтинг: 0 / 0
30.06.2005, 15:09
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33143049
URIX
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
URIX
Гость
mwolfВ Постгресе представления не индексируются и не "материализуются", отсюда надо и плясать.пардон, читать разучился %)
т.е. не на ту мысль ответил. собственно, я об этом и говорю. очень хочется построить индексы на все возможные тяжелые запросы и использовать вьюхи с этими запросами. вопрос именно в том, как именно проиндексировать и в каких случаях (если они есть) этот подход не поможет?

з.ы. пардон за очетяпки и косноязычие, бошка уже кругом идет от работы
...
Рейтинг: 0 / 0
30.06.2005, 15:33
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33143101
mwolf
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mwolf
Участник
Жёстка. Время увеличилось в полтора раза!!!
Однако нездоровая какаято хрень наблюдается(((
Ну с первым циклом ясно - индекс по одному ИД оказывается выгоднее, чем по имени-дате. Почему так - не понятно.
Попробуй подправить так
between (now()-'200 minute'::interval)::timestamp with time zone and (now()-'0 minute'::interval))::timestamp with time zone

Со вторым циклом вообще интересно:
в первом случае
pwr_pwb_id_ind on pwr (cost=0.00..17.08 rows=1 width=36) (actual time=0.086..0.086 rows=0 loops=3601)
во втором
pwr_pwb_id_name_ind on pwr (cost=0.00..208.83 rows=52 width=36) (actual time=0.039..0.039 rows=0 loops=10316)

Зачем он выбрал план с большей стимостью? Блин, а что такое loops честно говоря забыл.
Мож таблицы переанализировать попробовать?
...
Рейтинг: 0 / 0
30.06.2005, 15:43
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33143133
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
URIXFilter: (((dt)::timestamp with time zone >= (now() - '03:20:00'::interval)) AND ((dt)::timestamp with time zone <= (now() - '00:00:00'::interval)))Видимо индекс на таблицу pwb надо дополнить полем (dt)::timestamp with time zone, или в запросе приводить (now() - '...'::interval) к типу timestamp.

Попробуйте запрос select * from pwr where name='name44' and exists ( select 1 from pwb where spl_id=4 and id=pwb_id and dt between (now()-'200 minute'::interval) and (now()-'0 minute'::interval) ) с использованием индекса по pwb(spl_id,id,dt::timestamp with time zone). Приведите пожалуйста explain analyze.

mwolfЖёстка. Время увеличилось в полтора раза!!!Неудивительно потому что в первом и втором тестах не совпадают значения now() и '30 minute'::interval, '10 minute'::interval с '200 minute'::interval, '0 minute'::interval.
...
Рейтинг: 0 / 0
30.06.2005, 15:51
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33143296
URIX
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
URIX
Гость
сделал все вакуумы, аналайзы и пр. поставил фиксированное время

без единого индекса:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
EXPLAIN ANALYZE select * from pwr where pwb_id in (select id from pwb where spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp with time zone) and ('2005-06-30 12:50'::timestamp with time zone)) and name='name44';
                                                                                                            QUERY PLAN                                                                                                            
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost= 5856 . 30 .. 43792 . 60  rows= 1  width= 14 ) (actual time= 328 . 493 .. 2289 . 874  rows= 15  loops= 1 )
   Hash Cond: ("outer".pwb_id = "inner".id)
   ->  Seq Scan on pwr  (cost= 0 . 00 .. 37926 . 10  rows= 2037  width= 14 ) (actual time= 0 . 356 .. 2009 . 951  rows= 2093  loops= 1 )
         Filter: (name = 'name44'::text)
   ->  Hash  (cost= 5856 . 17 .. 5856 . 17  rows= 52  width= 4 ) (actual time= 274 . 114 .. 274 . 114  rows= 0  loops= 1 )
         ->  HashAggregate  (cost= 5856 . 17 .. 5856 . 17  rows= 52  width= 4 ) (actual time= 267 . 359 .. 271 . 152  rows= 2207  loops= 1 )
               ->  Seq Scan on pwb  (cost= 0 . 00 .. 5856 . 04  rows= 52  width= 4 ) (actual time= 0 . 130 .. 261 . 562  rows= 2207  loops= 1 )
                     Filter: ((spl_id =  4 ) AND ((dt)::timestamp with time zone >= '2005-06-30 12:40:00+04'::timestamp with time zone) AND ((dt)::timestamp with time zone <= '2005-06-30 12:50:00+04'::timestamp with time zone))
 Total runtime:  2290 . 146  ms
при индексах
create index pwb_spl_id_dt_ind on pwb(spl_id,id,dt); (тип подставить не дает)
create index pwr_pwb_id_name_ind on pwr(pwb_id,name);
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
EXPLAIN ANALYZE select * from pwr where pwb_id in (select id from pwb where spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp with time zone) and ('2005-06-30 12:50'::timestamp with time zone)) and name='name44';
                                                                                                         QUERY PLAN                                                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost= 5856 . 17 .. 6130 . 78  rows= 1  width= 14 ) (actual time= 271 . 174 .. 359 . 184  rows= 15  loops= 1 )
   ->  HashAggregate  (cost= 5856 . 17 .. 5856 . 17  rows= 52  width= 4 ) (actual time= 262 . 528 .. 267 . 864  rows= 2207  loops= 1 )
         ->  Seq Scan on pwb  (cost= 0 . 00 .. 5856 . 04  rows= 52  width= 4 ) (actual time= 0 . 127 .. 258 . 155  rows= 2207  loops= 1 )
               Filter: ((spl_id =  4 ) AND ((dt)::timestamp with time zone >= '2005-06-30 12:40:00+04'::timestamp with time zone) AND ((dt)::timestamp with time zone <= '2005-06-30 12:50:00+04'::timestamp with time zone))
   ->  Index Scan using pwr_pwb_id_name_ind on pwr  (cost= 0 . 00 .. 5 . 27  rows= 1  width= 14 ) (actual time= 0 . 038 .. 0 . 038  rows= 0  loops= 2207 )
         Index Cond: ((pwr.pwb_id = "outer".id) AND (pwr.name = 'name44'::text))
 Total runtime:  359 . 467  ms
...
Рейтинг: 0 / 0
30.06.2005, 16:27
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33143370
URIX
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
URIX
Гость
блин, это я уже сам туплю %)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
test=> \d pwb
                                    Таблица "public.pwb"
 Колонка |             Тип             |                    Модификаторы                     
---------+-----------------------------+-----------------------------------------------------
 id      | integer                     | not null default nextval('public.pwb_id_seq'::text)
 dt      | timestamp without time zone | default now()
 spl_id  | integer                     | 
Индексы:
    "pwb_pkey" ключевое поле, btree (id)
    "pwb_spl_id_dt_ind" btree (spl_id, dt)

test=> \d pwr
       Таблица "public.pwr"
 Колонка |   Тип   | Модификаторы 
---------+---------+--------------
 pwb_id  | integer | 
 name    | text    | 
Индексы:
    "pwr_pwb_id_name_ind" btree (pwb_id, name)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
test=> EXPLAIN ANALYZE select * from pwr where pwb_id in (select id from pwb where spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp)) and name='name44';
                                                                               QUERY PLAN                                                                               
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost= 4406 . 71 .. 16024 . 64  rows= 18  width= 14 ) (actual time= 21 . 753 .. 115 . 269  rows= 15  loops= 1 )
   ->  HashAggregate  (cost= 4406 . 71 .. 4406 . 71  rows= 2200  width= 4 ) (actual time= 18 . 946 .. 25 . 211  rows= 2207  loops= 1 )
         ->  Index Scan using pwb_spl_id_dt_ind on pwb  (cost= 0 . 00 .. 4401 . 21  rows= 2200  width= 4 ) (actual time= 0 . 205 .. 15 . 515  rows= 2207  loops= 1 )
               Index Cond: ((spl_id =  4 ) AND (dt >= '2005-06-30 12:40:00'::timestamp without time zone) AND (dt <= '2005-06-30 12:50:00'::timestamp without time zone))
   ->  Index Scan using pwr_pwb_id_name_ind on pwr  (cost= 0 . 00 .. 5 . 27  rows= 1  width= 14 ) (actual time= 0 . 038 .. 0 . 038  rows= 0  loops= 2207 )
         Index Cond: ((pwr.pwb_id = "outer".id) AND (pwr.name = 'name44'::text))
 Total runtime:  115 . 498  ms
(записей:  7 )
...
Рейтинг: 0 / 0
30.06.2005, 16:47
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33143420
mwolf
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mwolf
Участник
С 460.957 ms до 115.498 ms. Уже хорошо.
Теперь вместо констант ставь выражение с приведением типов и всё будет ок.
...
Рейтинг: 0 / 0
30.06.2005, 16:59
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33144446
URIX
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
URIX
Гость
mwolfС 460.957 ms до 115.498 ms. Уже хорошо.
Теперь вместо констант ставь выражение с приведением типов и всё будет ок.имхо, выражение в таких случаях ставить нельзя, иначе на каждом loop он будет его вычислять. надо либо переменную ставить, либо (select <выражение>) mwolfБлин, а что такое loops честно говоря забылесли я правильно понял, это число джоинов с результатом подзапроса
...
Рейтинг: 0 / 0
01.07.2005, 10:54
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33144500
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
URIX mwolfС 460.957 ms до 115.498 ms. Уже хорошо.
Теперь вместо констант ставь выражение с приведением типов и всё будет ок.имхо, выражение в таких случаях ставить нельзя, иначе на каждом loop он будет его вычислять. надо либо переменную ставить, либо (select <выражение>)Можно ставить выражение с приведением типа в это место при этом плане выполнения. Оно будет вычисляться один раз, как и выражение без приведения. :)

URIX mwolfБлин, а что такое loops честно говоря забылесли я правильно понял, это число джоинов с результатом подзапросаСколько раз выполняется данная ветка. То есть в вашем примере 'Index Scan using pwr_pwb_id_name_ind on pwr' выполняется 2207 раз для разных значений "outer".id (pwb.id).

Почему вы не попробовали exists?
...
Рейтинг: 0 / 0
01.07.2005, 11:10
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33144528
4321
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
4321
Участник
LeXa NalBat Почему вы не попробовали exists?
да и джойн там прокатывает, кажисть.
...
Рейтинг: 0 / 0
01.07.2005, 11:17
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33144553
URIX
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
URIX
Гость
LeXa NalBatПочему вы не попробовали exists?забыл, если честно %)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
test=> EXPLAIN ANALYZE select * from pwr where name='name44' and exists (select  1  from pwb where id=pwr.pwb_id and spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp)) and name='name44';
                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using pwr_name_ind on pwr  (cost= 0 . 00 .. 15 . 67  rows= 1  width= 14 ) (actual time= 47 . 479 .. 152 . 416  rows= 15  loops= 1 )
   Index Cond: ((name = 'name44'::text) AND (name = 'name44'::text))
   Filter: (subplan)
   SubPlan
     ->  Index Scan using pwb_pkey on pwb  (cost= 0 . 00 .. 3 . 02  rows= 1  width= 0 ) (actual time= 0 . 037 .. 0 . 037  rows= 0  loops= 2093 )
           Index Cond: (id = $ 0 )
           Filter: ((spl_id =  4 ) AND (dt >= '2005-06-30 12:40:00'::timestamp without time zone) AND (dt <= '2005-06-30 12:50:00'::timestamp without time zone))
 Total runtime:  152 . 557  ms
(записей:  8 )
...
Рейтинг: 0 / 0
01.07.2005, 11:25
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33144580
URIX
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
URIX
Гость
оп.... еще интереснее ;)) добавил индексы, чтоб с exist проверить. получилось:
Код: 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.
test=> EXPLAIN ANALYZE select * from pwr where pwb_id in (select id from pwb where spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp)) and name='name44';
                                                                               QUERY PLAN                                                                               
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash IN Join  (cost= 4406 . 71 .. 11976 . 05  rows= 18  width= 14 ) (actual time= 40 . 095 .. 92 . 496  rows= 15  loops= 1 )
   Hash Cond: ("outer".pwb_id = "inner".id)
   ->  Index Scan using pwr_name_ind on pwr  (cost= 0 . 00 .. 7554 . 14  rows= 2002  width= 14 ) (actual time= 0 . 225 .. 69 . 365  rows= 2093  loops= 1 )
         Index Cond: (name = 'name44'::text)
   ->  Hash  (cost= 4401 . 21 .. 4401 . 21  rows= 2200  width= 4 ) (actual time= 18 . 472 .. 18 . 472  rows= 0  loops= 1 )
         ->  Index Scan using pwb_spl_id_dt_ind on pwb  (cost= 0 . 00 .. 4401 . 21  rows= 2200  width= 4 ) (actual time= 0 . 286 .. 15 . 553  rows= 2207  loops= 1 )
               Index Cond: ((spl_id =  4 ) AND (dt >= '2005-06-30 12:40:00'::timestamp without time zone) AND (dt <= '2005-06-30 12:50:00'::timestamp without time zone))
 Total runtime:  92 . 701  ms
(записей:  8 )

test=> \d pwb
                                    Таблица "public.pwb"
 Колонка |             Тип             |                    Модификаторы                     
---------+-----------------------------+-----------------------------------------------------
 id      | integer                     | not null default nextval('public.pwb_id_seq'::text)
 dt      | timestamp without time zone | default now()
 spl_id  | integer                     | 
Индексы:
    "pwb_pkey" ключевое поле, btree (id)
    "pwb_spl_id_dt_ind" btree (spl_id, dt)
    "pwb_spl_id_id_dt" btree (spl_id, id, dt)

test=> \d pwr
       Таблица "public.pwr"
 Колонка |   Тип   | Модификаторы 
---------+---------+--------------
 pwb_id  | integer | 
 name    | text    | 
Индексы:
    "pwr_lower_name_ind" btree (lower(name))
    "pwr_name_ind" btree (name)
    "pwr_pwb_id_name_ind" btree (pwb_id, name)
кстати, вариант без exists, по идее, лучше. все таки, там один раз выбирается подзапрос, т.к. в него не передается параметров из внешнего запроса
...
Рейтинг: 0 / 0
01.07.2005, 11:34
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33144725
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
test=> EXPLAIN ANALYZE select * from pwr where name='name44' and exists
(select  1  from pwb where id=pwr.pwb_id and spl_id= 4 
and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp)) and name='name44';
                                                                           QUERY PLAN                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using pwr_name_ind on pwr  (cost= 0 . 00 .. 15 . 67  rows= 1  width= 14 ) (actual time= 47 . 479 .. 152 . 416  rows= 15  loops= 1 )
   Index Cond: ((name = 'name44'::text) AND (name = 'name44'::text))
   Filter: (subplan)
   SubPlan
     ->  Index Scan using pwb_pkey on pwb  (cost= 0 . 00 .. 3 . 02  rows= 1  width= 0 ) (actual time= 0 . 037 .. 0 . 037  rows= 0  loops= 2093 )
           Index Cond: (id = $ 0 )
           Filter: ((spl_id =  4 ) AND (dt >= '2005-06-30 12:40:00'::timestamp without time zone) AND (dt <= '2005-06-30 12:50:00'::timestamp without time zone))
 Total runtime:  152 . 557  ms
(записей:  8 )

loops=2093 - Я ожидал, что это число будет намного меньше, чем 2207. :( Поэтому exists наверное можно более не пробовать, а попытать различные джоины, как сказал 4321. Но приведитете еще пожалуйста статистику (в первом и втором запросе наверное получится 2207, в третьем - 2093, а сколько в четвертом?):
select count(*) from pwb where spl_id=4 and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
select count(distinct id) from pwb where spl_id=4 and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
select count(*) from pwr where name='name44';
select count(distinct pwb_id) from pwr where name='name44';

Джоины типа такие
EXPLAIN ANALYZE select [distinct] * from pwr join pwb on (id=pwb_id) where name='name44' and spl_id=4 and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
с различными установками set_enable_*_join to [on,off] и созданными нужными индексами. :)

URIXкстати, вариант без exists, по идее, лучше. все таки, там один раз выбирается подзапрос, т.к. в него не передается параметров из внешнего запросаДа, наличие subplan - не лучший вариант.
...
Рейтинг: 0 / 0
01.07.2005, 12:19
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33148062
URIX
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
URIX
Гость
алгоритм заполнения таблиц есть в первом посте. на самом деле, я там немного слукавил относительно "(1 .. 200000)": было "(1 .. 300000)", но после 200000 оборвал, т.к. надоело ждать

Код: 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.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
test=> select count(*) from pwb where spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
 count
-------
   2207 
( 1  запись)

test=> select count(distinct id) from pwb where spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
 count
-------
   2207 

test=> select count(*) from pwr where name='name44';
 count
-------
   2093 

test=> select count(distinct pwb_id) from pwr where name='name44';
 count
-------
   2087 


set enable_hashjoin to on;
set enable_mergejoin to on;
test=> EXPLAIN ANALYZE select * from pwr join pwb on (id=pwb_id) where name='name44' and spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
                                                                               QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost= 12187 . 28 .. 12208 . 47  rows= 18  width= 30 ) (actual time= 95 . 522 .. 100 . 901  rows= 15  loops= 1 )
   Merge Cond: ("outer".pwb_id = "inner".id)
   ->  Sort  (cost= 7663 . 93 .. 7668 . 93  rows= 2002  width= 14 ) (actual time= 76 . 878 .. 77 . 223  rows= 467  loops= 1 )
         Sort Key: pwr.pwb_id
         ->  Index Scan using pwr_name_ind on pwr  (cost= 0 . 00 .. 7554 . 14  rows= 2002  width= 14 ) (actual time= 0 . 191 .. 70 . 900  rows= 2093  loops= 1 )
               Index Cond: (name = 'name44'::text)
   ->  Sort  (cost= 4523 . 35 .. 4528 . 85  rows= 2200  width= 16 ) (actual time= 17 . 909 .. 19 . 563  rows= 2207  loops= 1 )
         Sort Key: pwb.id
         ->  Index Scan using pwb_spl_id_dt_ind on pwb  (cost= 0 . 00 .. 4401 . 21  rows= 2200  width= 16 ) (actual time= 0 . 172 .. 13 . 310  rows= 2207  loops= 1 )
               Index Cond: ((spl_id =  4 ) AND (dt >= '2005-06-30 12:40:00'::timestamp without time zone) AND (dt <= '2005-06-30 12:50:00'::timestamp without time zone))
 Total runtime:  101 . 921  ms

test=> EXPLAIN ANALYZE select distinct * from pwr join pwb on (id=pwb_id) where name='name44' and spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
                                                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost= 12208 . 84 .. 12209 . 11  rows= 18  width= 30 ) (actual time= 99 . 258 .. 99 . 306  rows= 15  loops= 1 )
   ->  Sort  (cost= 12208 . 84 .. 12208 . 89  rows= 18  width= 30 ) (actual time= 99 . 254 .. 99 . 265  rows= 15  loops= 1 )
         Sort Key: pwr.pwb_id, pwr.name, pwb.id, pwb.dt, pwb.spl_id
         ->  Merge Join  (cost= 12187 . 28 .. 12208 . 47  rows= 18  width= 30 ) (actual time= 94 . 128 .. 99 . 214  rows= 15  loops= 1 )
               Merge Cond: ("outer".pwb_id = "inner".id)
               ->  Sort  (cost= 7663 . 93 .. 7668 . 93  rows= 2002  width= 14 ) (actual time= 75 . 790 .. 76 . 151  rows= 467  loops= 1 )
                     Sort Key: pwr.pwb_id
                     ->  Index Scan using pwr_name_ind on pwr  (cost= 0 . 00 .. 7554 . 14  rows= 2002  width= 14 ) (actual time= 0 . 187 .. 69 . 952  rows= 2093  loops= 1 )
                           Index Cond: (name = 'name44'::text)
               ->  Sort  (cost= 4523 . 35 .. 4528 . 85  rows= 2200  width= 16 ) (actual time= 17 . 621 .. 19 . 246  rows= 2207  loops= 1 )
                     Sort Key: pwb.id
                     ->  Index Scan using pwb_spl_id_dt_ind on pwb  (cost= 0 . 00 .. 4401 . 21  rows= 2200  width= 16 ) (actual time= 0 . 185 .. 13 . 067  rows= 2207  loops= 1 )
                           Index Cond: ((spl_id =  4 ) AND (dt >= '2005-06-30 12:40:00'::timestamp without time zone) AND (dt <= '2005-06-30 12:50:00'::timestamp without time zone))
 Total runtime:  100 . 266  ms




set enable_hashjoin to on;
set enable_mergejoin to off;
test=> EXPLAIN ANALYZE select * from pwr join pwb on (id=pwb_id) where name='name44' and spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
                                                                               QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost= 4406 . 71 .. 12341 . 54  rows= 18  width= 30 ) (actual time= 41 . 298 .. 92 . 524  rows= 15  loops= 1 )
   Hash Cond: ("outer".pwb_id = "inner".id)
   ->  Index Scan using pwr_name_ind on pwr  (cost= 0 . 00 .. 7554 . 14  rows= 2002  width= 14 ) (actual time= 0 . 221 .. 66 . 602  rows= 2093  loops= 1 )
         Index Cond: (name = 'name44'::text)
   ->  Hash  (cost= 4401 . 21 .. 4401 . 21  rows= 2200  width= 16 ) (actual time= 20 . 592 .. 20 . 592  rows= 0  loops= 1 )
         ->  Index Scan using pwb_spl_id_dt_ind on pwb  (cost= 0 . 00 .. 4401 . 21  rows= 2200  width= 16 ) (actual time= 0 . 165 .. 17 . 526  rows= 2207  loops= 1 )
               Index Cond: ((spl_id =  4 ) AND (dt >= '2005-06-30 12:40:00'::timestamp without time zone) AND (dt <= '2005-06-30 12:50:00'::timestamp without time zone))
 Total runtime:  92 . 734  ms
(записей:  8 )

test=> EXPLAIN ANALYZE select distinct * from pwr join pwb on (id=pwb_id) where name='name44' and spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
                                                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost= 12341 . 91 .. 12342 . 18  rows= 18  width= 30 ) (actual time= 92 . 616 .. 92 . 663  rows= 15  loops= 1 )
   ->  Sort  (cost= 12341 . 91 .. 12341 . 96  rows= 18  width= 30 ) (actual time= 92 . 610 .. 92 . 621  rows= 15  loops= 1 )
         Sort Key: pwr.pwb_id, pwr.name, pwb.id, pwb.dt, pwb.spl_id
         ->  Hash Join  (cost= 4406 . 71 .. 12341 . 54  rows= 18  width= 30 ) (actual time= 41 . 179 .. 92 . 531  rows= 15  loops= 1 )
               Hash Cond: ("outer".pwb_id = "inner".id)
               ->  Index Scan using pwr_name_ind on pwr  (cost= 0 . 00 .. 7554 . 14  rows= 2002  width= 14 ) (actual time= 0 . 231 .. 66 . 722  rows= 2093  loops= 1 )
                     Index Cond: (name = 'name44'::text)
               ->  Hash  (cost= 4401 . 21 .. 4401 . 21  rows= 2200  width= 16 ) (actual time= 20 . 511 .. 20 . 511  rows= 0  loops= 1 )
                     ->  Index Scan using pwb_spl_id_dt_ind on pwb  (cost= 0 . 00 .. 4401 . 21  rows= 2200  width= 16 ) (actual time= 0 . 157 .. 17 . 197  rows= 2207  loops= 1 )
                           Index Cond: ((spl_id =  4 ) AND (dt >= '2005-06-30 12:40:00'::timestamp without time zone) AND (dt <= '2005-06-30 12:50:00'::timestamp without time zone))
 Total runtime:  92 . 831  ms



set enable_hashjoin to off;
set enable_mergejoin to on;
test=> EXPLAIN ANALYZE select * from pwr join pwb on (id=pwb_id) where name='name44' and spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
                                                                               QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost= 12187 . 28 .. 12208 . 47  rows= 18  width= 30 ) (actual time= 96 . 190 .. 101 . 611  rows= 15  loops= 1 )
   Merge Cond: ("outer".pwb_id = "inner".id)
   ->  Sort  (cost= 7663 . 93 .. 7668 . 93  rows= 2002  width= 14 ) (actual time= 77 . 511 .. 77 . 959  rows= 467  loops= 1 )
         Sort Key: pwr.pwb_id
         ->  Index Scan using pwr_name_ind on pwr  (cost= 0 . 00 .. 7554 . 14  rows= 2002  width= 14 ) (actual time= 0 . 195 .. 71 . 238  rows= 2093  loops= 1 )
               Index Cond: (name = 'name44'::text)
   ->  Sort  (cost= 4523 . 35 .. 4528 . 85  rows= 2200  width= 16 ) (actual time= 17 . 928 .. 19 . 514  rows= 2207  loops= 1 )
         Sort Key: pwb.id
         ->  Index Scan using pwb_spl_id_dt_ind on pwb  (cost= 0 . 00 .. 4401 . 21  rows= 2200  width= 16 ) (actual time= 0 . 183 .. 13 . 340  rows= 2207  loops= 1 )
               Index Cond: ((spl_id =  4 ) AND (dt >= '2005-06-30 12:40:00'::timestamp without time zone) AND (dt <= '2005-06-30 12:50:00'::timestamp without time zone))
 Total runtime:  102 . 658  ms

test=> EXPLAIN ANALYZE select distinct * from pwr join pwb on (id=pwb_id) where name='name44' and spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
                                                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost= 12208 . 84 .. 12209 . 11  rows= 18  width= 30 ) (actual time= 99 . 791 .. 99 . 839  rows= 15  loops= 1 )
   ->  Sort  (cost= 12208 . 84 .. 12208 . 89  rows= 18  width= 30 ) (actual time= 99 . 788 .. 99 . 800  rows= 15  loops= 1 )
         Sort Key: pwr.pwb_id, pwr.name, pwb.id, pwb.dt, pwb.spl_id
         ->  Merge Join  (cost= 12187 . 28 .. 12208 . 47  rows= 18  width= 30 ) (actual time= 94 . 693 .. 99 . 746  rows= 15  loops= 1 )
               Merge Cond: ("outer".pwb_id = "inner".id)
               ->  Sort  (cost= 7663 . 93 .. 7668 . 93  rows= 2002  width= 14 ) (actual time= 76 . 114 .. 76 . 449  rows= 467  loops= 1 )
                     Sort Key: pwr.pwb_id
                     ->  Index Scan using pwr_name_ind on pwr  (cost= 0 . 00 .. 7554 . 14  rows= 2002  width= 14 ) (actual time= 0 . 198 .. 70 . 235  rows= 2093  loops= 1 )
                           Index Cond: (name = 'name44'::text)
               ->  Sort  (cost= 4523 . 35 .. 4528 . 85  rows= 2200  width= 16 ) (actual time= 17 . 862 .. 19 . 459  rows= 2207  loops= 1 )
                     Sort Key: pwb.id
                     ->  Index Scan using pwb_spl_id_dt_ind on pwb  (cost= 0 . 00 .. 4401 . 21  rows= 2200  width= 16 ) (actual time= 0 . 173 .. 13 . 361  rows= 2207  loops= 1 )
                           Index Cond: ((spl_id =  4 ) AND (dt >= '2005-06-30 12:40:00'::timestamp without time zone) AND (dt <= '2005-06-30 12:50:00'::timestamp without time zone))
 Total runtime:  100 . 814  ms



set enable_hashjoin to off;
set enable_mergejoin to off;
test=> EXPLAIN ANALYZE select * from pwr join pwb on (id=pwb_id) where name='name44' and spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost= 0 . 00 .. 13627 . 21  rows= 18  width= 30 ) (actual time= 48 . 129 .. 154 . 011  rows= 15  loops= 1 )
   ->  Index Scan using pwr_name_ind on pwr  (cost= 0 . 00 .. 7554 . 14  rows= 2002  width= 14 ) (actual time= 0 . 196 .. 71 . 716  rows= 2093  loops= 1 )
         Index Cond: (name = 'name44'::text)
   ->  Index Scan using pwb_pkey on pwb  (cost= 0 . 00 .. 3 . 02  rows= 1  width= 16 ) (actual time= 0 . 036 .. 0 . 036  rows= 0  loops= 2093 )
         Index Cond: (pwb.id = "outer".pwb_id)
         Filter: ((spl_id =  4 ) AND (dt >= '2005-06-30 12:40:00'::timestamp without time zone) AND (dt <= '2005-06-30 12:50:00'::timestamp without time zone))
 Total runtime:  154 . 135  ms


test=> EXPLAIN ANALYZE select distinct * from pwr join pwb on (id=pwb_id) where name='name44' and spl_id= 4  and dt between ('2005-06-30 12:40'::timestamp) and ('2005-06-30 12:50'::timestamp);
                                                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost= 13627 . 59 .. 13627 . 86  rows= 18  width= 30 ) (actual time= 154 . 142 .. 154 . 190  rows= 15  loops= 1 )
   ->  Sort  (cost= 13627 . 59 .. 13627 . 63  rows= 18  width= 30 ) (actual time= 154 . 138 .. 154 . 150  rows= 15  loops= 1 )
         Sort Key: pwr.pwb_id, pwr.name, pwb.id, pwb.dt, pwb.spl_id
         ->  Nested Loop  (cost= 0 . 00 .. 13627 . 21  rows= 18  width= 30 ) (actual time= 47 . 975 .. 154 . 057  rows= 15  loops= 1 )
               ->  Index Scan using pwr_name_ind on pwr  (cost= 0 . 00 .. 7554 . 14  rows= 2002  width= 14 ) (actual time= 0 . 190 .. 68 . 299  rows= 2093  loops= 1 )
                     Index Cond: (name = 'name44'::text)
               ->  Index Scan using pwb_pkey on pwb  (cost= 0 . 00 .. 3 . 02  rows= 1  width= 16 ) (actual time= 0 . 038 .. 0 . 038  rows= 0  loops= 2093 )
                     Index Cond: (pwb.id = "outer".pwb_id)
                     Filter: ((spl_id =  4 ) AND (dt >= '2005-06-30 12:40:00'::timestamp without time zone) AND (dt <= '2005-06-30 12:50:00'::timestamp without time zone))
 Total runtime:  154 . 307  ms
индексы:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
test=> \d pwb
                                    Таблица "public.pwb"
 Колонка |             Тип             |                    Модификаторы
---------+-----------------------------+-----------------------------------------------------
 id      | integer                     | not null default nextval('public.pwb_id_seq'::text)
 dt      | timestamp without time zone | default now()
 spl_id  | integer                     |
Индексы:
    "pwb_pkey" ключевое поле, btree (id)
    "pwb_spl_id_dt_ind" btree (spl_id, dt)
    "pwb_spl_id_id_dt" btree (spl_id, id, dt)

test=> \d pwr
       Таблица "public.pwr"
 Колонка |   Тип   | Модификаторы
---------+---------+--------------
 pwb_id  | integer |
 name    | text    |
Индексы:
    "pwr_lower_name_ind" btree (lower(name))
    "pwr_name_ind" btree (name)
    "pwr_pwb_id_name_ind" btree (pwb_id, name)
    "pwr_text_name_ind" btree (name text_pattern_ops)
...
Рейтинг: 0 / 0
04.07.2005, 14:41
| Ответить | Цитировать | Написать  
вьюха вместо триггеров
    #33151565
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
URIXалгоритм заполнения таблиц есть в первом постеО! Слона то я только сейчас заметил. :( То есть вы пробуете не на реальных данных, а на тестовых сгенеренных автоматически. Тогда важно чтобы распределения значений в тестовых данных были похожи (напрмер отличались не более чем в 3-10 раз, а чем отличие меньше, тем лучше:) на реальные распределения. Иначе на реальных данных планы могут оказаться другими. В реальных данных у вас:
~ 200 000 pwb.id?
~ 20 различных spl_id, каждому из которых в среднем соответствует 10 000 pwb.id?
~ 1 000 различных name, каждому из которых соотв. в среднем 2 000 pwb.id?

URIXпосле 200000 оборвал, т.к. надоело ждатьЭтот скрипт наверное будет работать в несколько раз быстрее, если сделать autocommit => 0, и делать коммит например через каждые 1 000 строк $i.

URIX... Merge Join ... 101.921 ms 100.266 ms
... Hash Join ... 92.734 ms 92.831 ms
... Merge Join ... 102.658 ms 100.814 ms
... Nested Loop ... 154.135 ms 154.307 msИ примерно такое же время парой постов раньше вы получили для Hash IN Join: 92.701 ms. Наверное можно сделать вывод, что ~100 ms на этой выборке - это и есть тот результат, котороого хотели добиться. :)
...
Рейтинг: 0 / 0
06.07.2005, 10:20
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / вьюха вместо триггеров
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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