ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / накладные расходы для временной таблицы
12 сообщений из 12, страница 1 из 1
накладные расходы для временной таблицы
    #35272277
tadmin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tadmin
Участник
Наблюдение.
Результаты сложного запроса, возвращающего небольшое число строк удобно записать во врем. таблицу, прежде чем делать JOIN с большими списками.
Если число записей во временной таблице меньше 900-1100, то время исполнения
Код: plaintext
1.
2.
3.
create temp table...
insert into...
 select from temp_table ..
 inner join ....
прямо пропорционально числу записей в temp_table (30-300мс)

Если записей 1200+, то время исполнения растет экспоненциально.
На статических таблицах все довольно линейно, планы запросов предсказуемы и пр.

С чем это связано?
Похоже, при каком-то пороговом размере temp_table ее начинают писать на диск...
...
Рейтинг: 0 / 0
22.04.2008, 18:07
| Ответить | Цитировать | Написать  
накладные расходы для временной таблицы
    #35272360
Thamerlan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Thamerlan
Участник
Попробуйте увеличить temp_buffers.
...
Рейтинг: 0 / 0
22.04.2008, 18:41
| Ответить | Цитировать | Написать  
накладные расходы для временной таблицы
    #35272594
iz
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iz
Гость
Обычно свап происходит в директории pgsql_tmp внутри $PGDATA. Если там не создаются временные файлы в момент выполнения запроса, значит на диск ничего временного не скидывается. Что касается вашего наблюдения про джойны больших таблиц, то оно абсолютно верное. Постгрес не всегда может оптимизировать такие запросы самостоятельно. Если по какой-то таблице из джойна в условиях имеется большая селективность (то есть заранее известно, что рядов это условие вернет не очень много, меньше 10^4, к примеру), то зачастую переписывание джойна на подзапрос дает прирост производительности на пару порядков. И делать при этом временную таблицу нет необходимости -- вы просто в большой запрос добавляете подзапросом тот SQL, которым вы делаете временную таблицу.
...
Рейтинг: 0 / 0
22.04.2008, 22:02
| Ответить | Цитировать | Написать  
накладные расходы для временной таблицы
    #35272722
Фотография Ёш
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ёш
Участник
начиная с 8.3 ещё в explain analyze можно посмотреть какой метод используется для сортировки, там же пишется в памяти или на диске:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
seb=> EXPLAIN ANALYZE select * from a order by i /  2 ;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Sort  (cost= 1446531 . 71 .. 1471531 . 91  rows= 10000080  width= 4 ) (actual time= 34959 . 064 .. 40659 . 453  rows= 10000000  loops= 1 )
   Sort Key: ((i /  2 ))
   Sort Method:  external sort  Disk: 195416kB
   ->  Seq Scan on a  (cost= 0 . 00 .. 164217 . 00  rows= 10000080  width= 4 ) (actual time= 0 . 079 .. 8213 . 999  rows= 10000000  loops= 1 )
 Total runtime:  44338 . 973  ms


--
„Истина — это вовсе не то, что можно убедительно доказать, это то, что
делает всё проще и понятнее“ — Антуан де Сент-Экзюпери
...
Рейтинг: 0 / 0
23.04.2008, 00:46
| Ответить | Цитировать | Написать  
накладные расходы для временной таблицы
    #35273369
tadmin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tadmin
Участник
Ёшначиная с 8.3 ещё в explain analyze можно посмотреть какой метод используется для сортировки, там же пишется в памяти или на диске:
Спасибо,
я что-то не мог сообразить, что можно в транзакции заполнить врем. таблицу, а уж потом explain analyze
...
Рейтинг: 0 / 0
23.04.2008, 11:16
| Ответить | Цитировать | Написать  
накладные расходы для временной таблицы
    #35273399
tadmin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tadmin
Участник
iz Если по какой-то таблице из джойна в условиях имеется большая селективность (то есть заранее известно, что рядов это условие вернет не очень много, меньше 10^4, к примеру), то зачастую переписывание джойна на подзапрос дает прирост производительности на пару порядков.

Верно ли я понимаю, что конструкуция
select ...
from (
select ... from (
select ....from (
select ....from ) as A00
) as A01
)as A02

всегда будет исполнена в явном порядке:
- A00 (тут должна быть хорошая селективность)
- А01
- А02
...
Рейтинг: 0 / 0
23.04.2008, 11:22
| Ответить | Цитировать | Написать  
накладные расходы для временной таблицы
    #35273409
tadmin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tadmin
Участник
извините, теги не поставил
Код: plaintext
1.
2.
3.
4.
5.
6.
select ...
      from (
            select ... from (
                              select ....from (
                                             select ....from ) as A00
                                                ) as A01
      )as A02
...
Рейтинг: 0 / 0
23.04.2008, 11:23
| Ответить | Цитировать | Написать  
накладные расходы для временной таблицы
    #35275800
iz
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iz
Гость
tadminизвините, теги не поставил
Код: plaintext
1.
2.
3.
4.
5.
6.
select ...
      from (
            select ... from (
                              select ....from (
                                             select ....from ) as A00
                                                ) as A01
      )as A02


Да, такой запрос будет выполняться наиболее естественным способом, начиная от самого внутреннего подзапроса.
...
Рейтинг: 0 / 0
24.04.2008, 00:04
| Ответить | Цитировать | Написать  
накладные расходы для временной таблицы
    #35276756
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
izвы просто в большой запрос добавляете подзапросом тот SQL, которым вы делаете временную таблицу. tadminВерно ли я понимаю, что конструкуция
Код: plaintext
1.
2.
3.
4.
5.
6.
select ...
      from (
            select ... from (
                              select ....from (
                                             select ....from ) as A00
                                                ) as A01
      )as A02
всегда будет исполнена в явном порядке:
- A00 (тут должна быть хорошая селективность)
- А01
- А02 izДа, такой запрос будет выполняться наиболее естественным способом, начиная от самого внутреннего подзапроса.нет. порядок обращения к двум самым внутренним таблицам (A00, A01 у вас, или t2 и t3 в моем примере) задать нельзя, даже уменьшив from_collapse_limit и join_collapse_limit

Код: 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.
create table t1 ( id serial primary key, val integer );
create table t2 ( id serial primary key, val integer );
create table t3 ( id serial primary key, val integer );

create index i1 on t1 ( val );
create index i2 on t2 ( val );
create index i3 on t3 ( val );

insert into t1 (val) select generate_series( 1 , 1000 );
insert into t2 (val) select generate_series( 1 , 1000 );
insert into t3 (val) select generate_series( 1 , 1000 );

analyze t1;
analyze t2;
analyze t3;

explain
 --analyze
select * from t1
 join t2
 using (id)
 join t3
 using (id)
 where t1.val <=  5 
 and t2.val <=  50 
 and t3.val <=  500 
;

explain
 --analyze
select * from t1
 join ( select * from t2
  join ( select * from t3
   where t3.val <=  500  ) as a3
  using (id) where t2.val <=  50  ) as a2
 using (id) where t1.val <=  5 
;

explain
 --analyze
select t1.id, t1.val as t1_val, a2.t2_val, a2.t3_val from t1,
 ( select t2.id, t2.val as t2_val, a3.t3_val from t2,
  ( select t3.id, t3.val as t3_val from t3
  where t3.val <=  500  ) as a3
 where t2.val <=  50  and t2.id = a3.id ) as a2
where t1.val <=  5  and t1.id = a2.id
;

set from_collapse_limit to  1 ;
set join_collapse_limit to  1 ;

explain
 --analyze
select * from t1
 join ( select * from t2
  join ( select * from t3
   where t3.val <=  500  ) as a3
  using (id) where t2.val <=  50  ) as a2
 using (id) where t1.val <=  5 
;

explain
 --analyze
select t1.id, t1.val as t1_val, a2.t2_val, a2.t3_val from t1,
 ( select t2.id, t2.val as t2_val, a3.t3_val from t2,
  ( select t3.id, t3.val as t3_val from t3
  where t3.val <=  500  ) as a3
 where t2.val <=  50  and t2.id = a3.id ) as a2
where t1.val <=  5  and t1.id = a2.id
;

drop table t1;
drop table t2;
drop table t3;

Код: 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.
nalbat=> explain
nalbat->  --analyze
nalbat-> select * from t1
nalbat->  join t2
nalbat->  using (id)
nalbat->  join t3
nalbat->  using (id)
nalbat->  where t1.val <=  5 
nalbat->  and t2.val <=  50 
nalbat->  and t3.val <=  500 
nalbat-> ;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost= 8 . 37 .. 25 . 95  rows= 1  width= 16 )
   ->  Hash Join  (cost= 8 . 37 .. 17 . 67  rows= 1  width= 16 )
         Hash Cond: (t2.id = t1.id)
         ->  Index Scan using i2 on t2  (cost= 0 . 00 .. 9 . 11  rows= 49  width= 8 )
               Index Cond: (val <=  50 )
         ->  Hash  (cost= 8 . 32 .. 8 . 32  rows= 4  width= 8 )
               ->  Index Scan using i1 on t1  (cost= 0 . 00 .. 8 . 32  rows= 4  width= 8 )
                     Index Cond: (val <=  5 )
   ->  Index Scan using t3_pkey on t3  (cost= 0 . 00 .. 8 . 27  rows= 1  width= 8 )
         Index Cond: (t3.id = t1.id)
         Filter: (t3.val <=  500 )
( 11  rows)

nalbat=>
nalbat=> explain
nalbat->  --analyze
nalbat-> select * from t1
nalbat->  join ( select * from t2
nalbat(>   join ( select * from t3
nalbat(>    where t3.val <=  500  ) as a3
nalbat(>   using (id) where t2.val <=  50  ) as a2
nalbat->  using (id) where t1.val <=  5 
nalbat-> ;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost= 8 . 37 .. 25 . 95  rows= 1  width= 16 )
   ->  Hash Join  (cost= 8 . 37 .. 17 . 67  rows= 1  width= 16 )
         Hash Cond: (t2.id = t1.id)
         ->  Index Scan using i2 on t2  (cost= 0 . 00 .. 9 . 11  rows= 49  width= 8 )
               Index Cond: (val <=  50 )
         ->  Hash  (cost= 8 . 32 .. 8 . 32  rows= 4  width= 8 )
               ->  Index Scan using i1 on t1  (cost= 0 . 00 .. 8 . 32  rows= 4  width= 8 )
                     Index Cond: (val <=  5 )
   ->  Index Scan using t3_pkey on t3  (cost= 0 . 00 .. 8 . 27  rows= 1  width= 8 )
         Index Cond: (t3.id = t2.id)
         Filter: (t3.val <=  500 )
( 11  rows)

nalbat=>
nalbat=> explain
nalbat->  --analyze
nalbat-> select t1.id, t1.val as t1_val, a2.t2_val, a2.t3_val from t1,
nalbat->  ( select t2.id, t2.val as t2_val, a3.t3_val from t2,
nalbat(>   ( select t3.id, t3.val as t3_val from t3
nalbat(>   where t3.val <=  500  ) as a3
nalbat(>  where t2.val <=  50  and t2.id = a3.id ) as a2
nalbat-> where t1.val <=  5  and t1.id = a2.id
nalbat-> ;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost= 8 . 37 .. 25 . 95  rows= 1  width= 16 )
   ->  Hash Join  (cost= 8 . 37 .. 17 . 67  rows= 1  width= 16 )
         Hash Cond: (t2.id = t1.id)
         ->  Index Scan using i2 on t2  (cost= 0 . 00 .. 9 . 11  rows= 49  width= 8 )
               Index Cond: (val <=  50 )
         ->  Hash  (cost= 8 . 32 .. 8 . 32  rows= 4  width= 8 )
               ->  Index Scan using i1 on t1  (cost= 0 . 00 .. 8 . 32  rows= 4  width= 8 )
                     Index Cond: (val <=  5 )
   ->  Index Scan using t3_pkey on t3  (cost= 0 . 00 .. 8 . 27  rows= 1  width= 8 )
         Index Cond: (t3.id = t2.id)
         Filter: (t3.val <=  500 )
( 11  rows)

nalbat=>
nalbat=> set from_collapse_limit to  1 ;
SET
nalbat=> set join_collapse_limit to  1 ;
SET
nalbat=> explain
nalbat->  --analyze
nalbat-> select * from t1
nalbat->  join ( select * from t2
nalbat(>   join ( select * from t3
nalbat(>    where t3.val <=  500  ) as a3
nalbat(>   using (id) where t2.val <=  50  ) as a2
nalbat->  using (id) where t1.val <=  5 
nalbat-> ;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Hash Join  (cost= 18 . 09 .. 37 . 81  rows= 1  width= 16 )
   Hash Cond: (t2.id = t1.id)
   ->  Hash Join  (cost= 9 . 72 .. 29 . 34  rows= 24  width= 16 )
         Hash Cond: (t3.id = t2.id)
         ->  Seq Scan on t3  (cost= 0 . 00 .. 17 . 50  rows= 500  width= 8 )
               Filter: (val <=  500 )
         ->  Hash  (cost= 9 . 11 .. 9 . 11  rows= 49  width= 8 )
               ->  Index Scan using i2 on t2  (cost= 0 . 00 .. 9 . 11  rows= 49  width= 8 )
                     Index Cond: (val <=  50 )
   ->  Hash  (cost= 8 . 32 .. 8 . 32  rows= 4  width= 8 )
         ->  Index Scan using i1 on t1  (cost= 0 . 00 .. 8 . 32  rows= 4  width= 8 )
               Index Cond: (val <=  5 )
( 12  rows)

nalbat=>
nalbat=> explain
nalbat->  --analyze
nalbat-> select t1.id, t1.val as t1_val, a2.t2_val, a2.t3_val from t1,
nalbat->  ( select t2.id, t2.val as t2_val, a3.t3_val from t2,
nalbat(>   ( select t3.id, t3.val as t3_val from t3
nalbat(>   where t3.val <=  500  ) as a3
nalbat(>  where t2.val <=  50  and t2.id = a3.id ) as a2
nalbat-> where t1.val <=  5  and t1.id = a2.id
nalbat-> ;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Hash Join  (cost= 18 . 09 .. 37 . 81  rows= 1  width= 16 )
   Hash Cond: (t2.id = t1.id)
   ->  Hash Join  (cost= 9 . 72 .. 29 . 34  rows= 24  width= 16 )
         Hash Cond: (t3.id = t2.id)
         ->  Seq Scan on t3  (cost= 0 . 00 .. 17 . 50  rows= 500  width= 8 )
               Filter: (val <=  500 )
         ->  Hash  (cost= 9 . 11 .. 9 . 11  rows= 49  width= 8 )
               ->  Index Scan using i2 on t2  (cost= 0 . 00 .. 9 . 11  rows= 49  width= 8 )
                     Index Cond: (val <=  50 )
   ->  Hash  (cost= 8 . 32 .. 8 . 32  rows= 4  width= 8 )
         ->  Index Scan using i1 on t1  (cost= 0 . 00 .. 8 . 32  rows= 4  width= 8 )
               Index Cond: (val <=  5 )
( 12  rows)
...
Рейтинг: 0 / 0
24.04.2008, 12:16
| Ответить | Цитировать | Написать  
накладные расходы для временной таблицы
    #35276921
tadmin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tadmin
Участник
я, господа, пока так и не решил свой вопрос.
Не стал постить сюда свои explain, потому что слишком много таблиц и дело не в конкретном примере, а в моем понимании.

До этого случая у меня в большинстве случаев планы получались довольно оптимальны.
Там, где это не работало - делал view и, как правило, их комбинация давала отличный результат.


задача возникла при сложной комбинации условий:
- для поиска и отображения товара используется функция, которая выдает динамический sql для формирования курсора.
- параметры поиска - фрагмент названия артикула, или имени (nullable), ID каталога (nullable)
- в курсор отдается список найденных артикулов с остатками товара и ценами.
- view (или запросы), которые подтягивают действующие цены и остатки довольно сложны.

Решение:
Экспериментально обнаружил, что если сделать предсказание о релевантность поиска (длина поисковой строки), то можно сделать более менее оптимальный выбор. Если найдено мало артикулов, то они запихиваются во врем. таблицу, а уж потом JOIN с дорогими запросами. Если ожидается большой список (пустая строка поиска или 1-2 символа), то дешевле оказывается делать запрос, используя естественный порядок сортировки индексов.

В чем проблемы сейчас:
есть (упрощенно) 2 больших таблицы (~40K), по которым может идти поиск:
- собственно артикулы (поиск по коду, названию и пр.)
- их членство в каталогах
Если используются оба критерия, то hash join между ними может занять слишком много времени,
так что выдача 5-6 строк или 800-1000 строк занимает одинаковое время.


Как заставить планировщик первым делать более "релевантный" запрос - не понимаю.
...
Рейтинг: 0 / 0
24.04.2008, 12:49
| Ответить | Цитировать | Написать  
накладные расходы для временной таблицы
    #35277236
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
tadminВ чем проблемы сейчас:
есть (упрощенно) 2 больших таблицы (~40K), по которым может идти поиск:
- собственно артикулы (поиск по коду, названию и пр.)
- их членство в каталогах
Если используются оба критерия, то hash join между ними может занять слишком много времени,
так что выдача 5-6 строк или 800-1000 строк занимает одинаковое время.

Как заставить планировщик первым делать более "релевантный" запрос - не понимаю.если я правильно понял ("hash join между ними может занять слишком много времени"), задача не только "первым делать более релевантный запрос", но и после этого выбирать во втором подзапросе ограничивая по join-критерию по данным, полученным в первом подзапросе, а условие на второй подзапрос проверять фильтром. получилось так сделать, только внимание, этот запрос эквивалентен outer join.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
explain
 --analyze
select t3.id, array_prepend( t3.val, (
 select array_prepend( t2.val, (
  select array[ t1.val ] from t1
  where t1.id=t2.id and t1.val <=  5 
 ) ) from t2 where t2.id=t3.id and t2.val <=  50 
) ) from t3 where t3.val <=  500 
;

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
nalbat=> explain
nalbat->  --analyze
nalbat-> select t3.id, array_prepend( t3.val, (
nalbat(>  select array_prepend( t2.val, (
nalbat(>   select array[ t1.val ] from t1
nalbat(>   where t1.id=t2.id and t1.val <=  5 
nalbat(>  ) ) from t2 where t2.id=t3.id and t2.val <=  50 
nalbat(> ) ) from t3 where t3.val <=  500 
nalbat-> ;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Seq Scan on t3  (cost= 0 . 00 .. 8290 . 20  rows= 500  width= 8 )
   Filter: (val <=  500 )
   SubPlan
     ->  Index Scan using t2_pkey on t2  (cost= 0 . 00 .. 16 . 54  rows= 1  width= 8 )
           Index Cond: (id = $ 2 )
           Filter: (val <=  50 )
           SubPlan
             ->  Index Scan using t1_pkey on t1  (cost= 0 . 00 .. 8 . 27  rows= 1  width= 4 )
                   Index Cond: (id = $ 0 )
                   Filter: (val <=  5 )
( 10  rows)
...
Рейтинг: 0 / 0
24.04.2008, 13:49
| Ответить | Цитировать | Написать  
накладные расходы для временной таблицы
    #35277875
tadmin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tadmin
Участник
Спасибо!
Кажется, начинаю понимать.
У меня, похоже, на каждом уровне select были заданы недостаточно жесткие критерии.
Т.е. итоговый результат был правильным, но на уровне каждого подзапроса оставалось много лишнего, что потом и отсекалось по окончании hash join.

Проверю - отпишусь.
...
Рейтинг: 0 / 0
24.04.2008, 16:33
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / накладные расходы для временной таблицы
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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