postgres. Вопрос о работе планировщика, наверное :)

wwoland

Есть вьюха view1, примерно следующего вида
 
select id, sum(val) as val, stamp from tbl1 group by id,stamp;

хочется из этой вьюхи получить сумму val определенных каким нибудь сложным условием, реализуемым в подзапросе subquery, возвращающем набор id'ов.
 
select sum(val) from view1 where id in (subquery).

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

tokuchu

Может быть EXPLAIN тебе поможет?

hprt

Думаю, имеет смысл заджойнить вьюху на подзапрос и потом уже проводить суммирование

wwoland

Как он мне поможет?
Я смотрел планы выполнения, и не понимаю, почему так и как с этим бороться. Если научишь, как сделать так, чтоб помогло, то я только рад буду :)

mbolik1

Выложи сюда план выполнения.

wwoland

"Aggregate  (cost=82848.73..82848.74 rows=1 width=32)"

"  ->  Hash Join  (cost=80412.09..82820.68 rows=11221 width=32)"

"        Hash Cond: (content_news_clicks.content_id = "IN_subquery".content_id)"

"        ->  GroupAggregate  (cost=69551.75..69804.22 rows=11221 width=24)"

"              ->  Sort  (cost=69551.75..69579.80 rows=11221 width=24)"

"                    Sort Key: content.content_id, content.stamp"

"                    ->  Merge Join  (cost=67258.39..68796.91 rows=11221 width=24)"

"                          Merge Cond: (content.content_id = counter_daily."object")"

"                          Join Filter: (counter_daily."day" < (content.stamp + '14 days'::interval"

"                          ->  Sort  (cost=54901.05..55230.46 rows=131765 width=16)"

"                                Sort Key: content.content_id"

"                                ->  Seq Scan on content  (cost=0.00..41218.01 rows=131765 width=16)"

"                                      Filter: (content_role = 2)"

"                          ->  Sort  (cost=12357.34..12544.91 rows=75027 width=16)"

"                                Sort Key: counter_daily."object""

"                                ->  Seq Scan on counter_daily  (cost=0.00..4871.49 rows=75027 width=16)"

"                                      Filter: (object_type = 1)"

"        ->  Hash  (cost=10816.37..10816.37 rows=3518 width=8)"

"              ->  Unique  (cost=10763.60..10781.19 rows=3518 width=8)"

"                    ->  Sort  (cost=10763.60..10772.40 rows=3518 width=8)"

"                          Sort Key: c.content_id"

"                          ->  Bitmap Heap Scan on content c  (cost=76.34..10556.38 rows=3518 width=8)"

"                                Recheck Cond: stamp >= '2009-07-16 00:00:00'::timestamp without time zone) AND (stamp < '2009-08-16 00:00:00'::timestamp without time zone"

"                                ->  Bitmap Index Scan on content_stamp_idx  (cost=0.00..75.46 rows=3518 width=0)"

"                                      Index Cond: stamp >= '2009-07-16 00:00:00'::timestamp without time zone) AND (stamp < '2009-08-16 00:00:00'::timestamp without time zone"

mbolik1

-> Hash (cost=10816.37..10816.37 rows=3518 width=8)"

Если я правильно понимаю этот кусок про подзапрос который выдаёт ограничение по id.
База решила что на выходе твой подзапрос выдаст 3518 строк что примерно треть от всех строк (11221) в основном представлении.
В таком случае вполне адекватно со стороны базы решить получить все данные из представления и потом сделать соединение.
Если твой подзапрос и правда возвращает порядка 3 тыс. строк то всё в порядке и работает как надо.
Если он возвращает заметно меньше, то нужно разбираться почему оптимизатор так ошибается; например: статистику пересобрать.

hprt

Мне вот не нравится, что сортировка для мержа жрет почти все время

mbolik1

Мне вот не нравится, что сортировка для мержа жрет почти все время
Мне честно говоря вообще непонятно зачем там эта сортировка и почему используется merge вместо hash, хотя объединение таблиц идёт по ключу content.content_id = counter_daily."object".
To :
Приведи полностью текст запроса.

hprt

ну собственно, это я и имею в виду - сортировка появляется именно из-за мержа

hprt

Еще такой момент может быть - подзапрос реально возвращает 3000 строк, из них скажем 5 уникальных

wwoland

запрос такой:
select sum(clicks) from content_news_clicks where content_id in (

			select distinct 

				content_id 

			from 

				content c

			where 	true

		

				and c.stamp >= '2009-07-16' and c.stamp < '2009-08-16' )

content_news_clicks - вьюха

mbolik1

Убери distinct (он здесь не нужен, если конечно у тебя не вылезает по 10-100 дубликатов одной записи) и приведи текст представления.

wwoland

Да, distinct просто забыл убрать :)


CREATE OR REPLACE VIEW content_news_clicks AS 

 SELECT content.content_id, content.stamp, sum(counter_daily.counter) AS clicks

   FROM content, counter_daily

  WHERE content.content_role = 2 AND counter_daily.object_type = 1 AND counter_daily."object" = content.content_id AND counter_daily."day" < (content.stamp + '14 days'::interval)

  GROUP BY content.content_id, content.stamp;

Запрос такой.

wwoland

А поп поводу количества строк, возвращаемыз из под запроса, то там по разному, но в думаю где-то около 1000.

mbolik1

А поп поводу количества строк, возвращаемыз из под запроса, то там по разному, но в думаю где-то около 1000.
Ну тогда всё нормально: прокидывать ограничение по 1000 id не очень-то выгодно. Единственное что смущает это merge в плане. попробуй запрос через join переписать вдруг поможет.
Вот так:
CREATE OR REPLACE VIEW content_news_clicks AS 

 SELECT content.content_id, content.stamp, sum(counter_daily.counter) AS clicks

   FROM content join counter_daily on (counter_daily."object" = content.content_id)

  WHERE content.content_role = 2 AND counter_daily.object_type = 1 AND counter_daily."day" < (content.stamp + '14 days'::interval)

  GROUP BY content.content_id, content.stamp;

wwoland

Может и нормально, я не спорю, но факт в том, что если на внешнем уровне добавить ограничение по полю stamp, то агрегатные функции в представлении выполняются уже после применения ограничений по stamp. Вот и хочется такого эффекта достичь, джойн по совету Ыукупф пробовал делать - ничего не изменилось, буду ботать доки постгреса далее :)

mbolik1

До меня вдруг дошло: а почему ты не пишешь:
select sum(clicks) from content_news_clicks where stamp >= '2009-07-16' and stamp < '2009-08-16' )

делает вроде тоже самое что и твой запрос с подзапросом.

wwoland

ну я в итоге что-то подобное и сделал, на самом деле :)
Но вопрос понимания почему не получается в другом случае остался, ведь это хорошо, что можно так свой запрос переиначить, но ведь такой возможности может и не быть :)

mbolik1

Но вопрос понимания почему не получается в другом случае остался, ведь это хорошо, что можно так свой запрос переиначить, но ведь такой возможности может и не быть :)
А что именно ты не понимаешь:
Почему когда ты подставляешь несколько значений то всё работает быстро, а когда у тебя подзапрос на 3 тыс. то всё медленно? Тут нужно просто умножить время выполнения первого на 1000, сравнить со вторым и понять действительно ли всё так плохо. Вряд ли.
Или
Почему когда ты подставляешь несколько значений то всё работает быстро, а когда у тебя подзапрос возвращает несколько значений то всё медленно? Для этого варианта мы всё ещё не видели плана и понять что и почему невозможно.

wwoland

Ща, объясню.
Мне непонятно, почему, если есть хоть какое то ограничение, а в моем случае по content_id, сначала выполняется агрегатная функция по всему представлению, а потом уже только идет отсчение по услови. Даже если подзапрос возвращает много записей, все равно ведь экономней будет сначала применить условие? или нет?
Что собственно и делается, если вместо подзапроса прописать константу.
вот, кстати, план выполнения запроса, при возврате 3 записей из подзапроса:
"Aggregate  (cost=70619.42..70619.43 rows=1 width=32)"

"  ->  Merge IN Join  (cost=70202.92..70619.41 rows=2 width=32)"

"        Merge Cond: (content_news_clicks.content_id = c.content_id)"

"        ->  GroupAggregate  (cost=70028.75..70296.35 rows=11893 width=24)"

"              ->  Sort  (cost=70028.75..70058.49 rows=11893 width=24)"

"                    Sort Key: content.content_id, content.stamp"

"                    ->  Merge Join  (cost=67636.90..69223.73 rows=11893 width=24)"

"                          Merge Cond: (content.content_id = counter_daily."object")"

"                          Join Filter: (counter_daily."day" < (content.stamp + '14 days'::interval"

"                          ->  Sort  (cost=54642.41..54965.95 rows=129415 width=16)"

"                                Sort Key: content.content_id"

"                                ->  Seq Scan on content  (cost=0.00..41218.01 rows=129415 width=16)"

"                                      Filter: (content_role = 2)"

"                          ->  Sort  (cost=12994.49..13196.89 rows=80963 width=16)"

"                                Sort Key: counter_daily."object""

"                                ->  Seq Scan on counter_daily  (cost=0.00..4871.49 rows=80963 width=16)"

"                                      Filter: (object_type = 1)"

"        ->  Sort  (cost=174.17..174.28 rows=43 width=8)"

"              Sort Key: c.content_id"

"              ->  Bitmap Heap Scan on content c  (cost=4.72..173.00 rows=43 width=8)"

"                    Recheck Cond: stamp >= '2009-07-31 00:00:00'::timestamp without time zone) AND (stamp < '2009-07-31 08:00:00'::timestamp without time zone"

"                    ->  Bitmap Index Scan on content_stamp_idx  (cost=0.00..4.71 rows=43 width=0)"

"                          Index Cond: stamp >= '2009-07-31 00:00:00'::timestamp without time zone) AND (stamp < '2009-07-31 08:00:00'::timestamp without time zone"

Принципиально ничего не изменилось :)
А если руками как константу прописать 4000 записей, то все равно выполняется быстро, и отсечение по условию идет до применения арегатной функции, вот план запроса(немного усеченный :)):
 "Aggregate  (cost=58027.35..58027.36 rows=1 width=32)"

"  ->  HashAggregate  (cost=58023.17..58025.26 rows=167 width=24)"

"        ->  Merge Join  (cost=57600.52..58021.92 rows=167 width=24)"

"              Merge Cond: (content.content_id = counter_daily."object")"

"              Join Filter: (counter_daily."day" < (content.stamp + '14 days'::interval"

"              ->  Sort  (cost=44606.03..44610.57 rows=1816 width=16)"

"                    Sort Key: content.content_id"

"                    ->  Bitmap Heap Scan on content  (cost=11218.56..44507.72 rows=1816 width=16)"

"                          Recheck Cond: (content_id = ANY ('{341846,341847,341854,341855,341858,341859,341860,341861,341832,341845,341844,341850,341856,341862,341863,341864,341833,341834,341836,341838,341839,341842,341843,341848,341852,341853,341857,341837,341849,341851,341865,341841,341840,341870,341872,341866,341869,341868,341873,341874,341871,341875,341876,341877}'::bigint[]"

"                          Filter: (content_role = 2)"

"                          ->  Bitmap Index Scan on content_pkey  (cost=0.00..11218.10 rows=4122 width=0)"

"                                Index Cond: (content_id = ANY ('{341846,341847,341854,341855,341858,341859,341860,341861,341832,341845,341844,341850,341856,341862,341863,341864,341833,341834,341836,341838,341839,341842,341843,341848,341852,341853,341857,341837,341849,341851,341865,341841,341840,341870,341872,341866,341869,341868,341873,341874,341871,341875,341876,341877}'::bigint[]"

"              ->  Sort  (cost=12994.49..13196.89 rows=80963 width=16)"

"                    Sort Key: counter_daily."object""

"                    ->  Seq Scan on counter_daily  (cost=0.00..4871.49 rows=80963 width=16)"

"                          Filter: (object_type = 1)"

hprt

Можешь приложить план с прописанными константами?

wwoland

так вот он, второй план - с константами как раз :)

mbolik1

Мне непонятно, почему, если есть хоть какое то ограничение, а в моем случае по content_id, сначала выполняется агрегатная функция по всему представлению, а потом уже только идет отсчение по услови. Даже если подзапрос возвращает много записей, все равно ведь экономней будет сначала применить условие? или нет?
1. Не обязательно, отсечение по большому количеству записей ничуть не эффективнее соединения, потому как именно соединением и делается.
2. По поводу случая когда выбирается сравнительно небольшое количество записей из известных мне баз (правда может у меня кругозор маленький) ни одна не умеет проталкивать результат такого подзапроса (даже если он сравнительно мал) в фильтр, вместо этого делается цикл по результату подзапроса и уже каждое значение по отдельности проталкивается в представление и по нему делается фильтрация.
В твоём случае СУБД явно посчитало это неэффективным, т.к. стоимость вычисления представления с ограниченным количеством записей 57600.52 а с полным 70028.75, т.е. для базы 2 раза с ограниченным набором дороже чем один раз с полным соответственно чего париться.
P.s. Пункт 2 не про случай когда подзапрос обязан вернуть одну запись.
Оставить комментарий
Имя или ник:
Комментарий: