На пути к идеальному плану / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / На пути к идеальному плану

12 сообщений из 12, страница 1 из 1

На пути к идеальному плану

#38522646

R_Only

Гость

Сервер выполняет много мелких запросов, выполняет быстро. Индексы летают.
Но есть один запрос, который выполняется в функции различное время, это зависит от входных данных (переменные взяты в коммент) - от 11 сек до 3 минут.
Поломали уже голову, перебирая все возможные варианты ускорения. Решили прибегнуть к помощи нашего любимого SQL.RU

Запрос не буду перестраивать, так как на простейших запросах проблем нет - они появляются, когда взаимодействует множество селектов.

Итак, выполняем запрос:

Код: plsql

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.

SET work_mem = '50MB';
EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS on, TIMING on)
WITH
a0 as (select latitude t,longitude g from org_address where org_id=1/*inorg_id*/ and id=any(array[997192]/*inadr_ids*/)),
r0 as (select id i,res_id r from org_rel_res where type=2 and org_id=1/*inorg_id*/ and id=any(array[7,82]/*inres_ids*/)),
d0 as (select r s,rd_id d,(select type from res_description where id=rd_id)t,d.id i,least(value1,value2)v1,greatest(value1,value2)v2,values vs
	,(select count(id) from orrd_value where orrd_id=d.id)vc from orr_rel_res_descr d,r0 where orr_id=i),
s0 as (select i h,rdv_id vp from orrd_value,d0 where orrd_id=i),
r2 as (select r, 1000-case when c=0 then o<<3 when c>3 then (o*(1.0-2.0/(a-o)))::integer<<3 else (o*(1.0-1.0/(a-o)))::integer<<3 end o
from (
	SELECT res_id r,(min(greatest(0,o-c))<<1)+1 o,(avg(greatest(0,o-c))::integer<<1)+4 a,count(res_id)::integer-1 c
	FROM buyer_okved_rel_res, (
		SELECT i,min(o)<<1+1 o,count(o)::integer-1 c
		FROM (
			WITH RECURSIVE
			o0(i,o) AS (SELECT okved_id,ord FROM org_rel_okved WHERE org_id=1/*inorg_id*/ and id=any(array[15054388]/*inokved_ids*/)),
			o1(i,p,o) AS (SELECT id,parent_id,o FROM okved,o0 WHERE id=i UNION ALL SELECT id,parent_id,o+1 FROM okved,o1 WHERE id=p),
			o2(i,p,o) AS (SELECT id,parent_id,o FROM okved,o0 WHERE parent_id=i UNION ALL SELECT id,parent_id,o FROM okved,o2 WHERE parent_id=i)
			SELECT i,o FROM o1 UNION ALL SELECT i,o FROM o2
		)o3 GROUP BY i
	)o4 WHERE okved_id=i GROUP BY res_id
)r1 union all select r,1080
from r0)
select z,o,1+((row_number() over(order by o desc)-1)/100/*inorgonlist*/)::integer p from (
select z,((o*weight)*(0.5+500.0/(500
	+(select coalesce(min(abs(latitude-t)/15000+least(abs(longitude-g+180000000)%180000000,abs(g-longitude+180000000)%180000000)/16500),5000) from a0))))::integer o
from org_address,(
select org_id z,(max(o)+(sum(o)/300)::integer)o from (
	select org_id,o+300/(ord+3) o
	from org_rel_okved, (select okved_id b, max(o)+(sum(o)/300)::integer o from res_rel_okved, r2 where res_id=r group by okved_id/* limit loc_ocnt*/)o5
	where okved_id=b
	union all
	select org_id,o+50+8*
	coalesce(sum((
		select least(count(id),3)+coalesce(count(d),0)+sum(case
			when t is null then 0
			when t=1 then
				case when v1=v2 then case when v1 between value1 and value2 then 3 else -4 end
				else greatest(-5,trunc(1+((least(value2,v2)-greatest(value1,v1))*4.0/(v2-v1))::integer)) end
			when t=2 then -1
			when t=3 then
				(select coalesce(1+(sum(1)*3/vc)::integer,-5) from orrd_value,s0 where orrd_id=e.id and h=i and vp=rdv_id)
			else 0 end)
		from orr_rel_res_descr e left join d0 on d=rd_id and s=r where orr_id=l.id
	)),0)o
	from org_rel_res l, r2 where type=1 and res_id=r
	group by org_id,r,o
)w2 group by org_id having org_id<>1/*inorg_id*/
)w3 where org_id=z and is_sale
)w4 order by o desc limit 1000

Получаем:

Код: plsql

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.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.

Limit  (cost=7389298.88..7389393.88 rows=200 width=24) (actual time=44847.854..44850.356 rows=1000 loops=1)
  Output: "*SELECT* 1".org_id, ((((((max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubP (...)
  Buffers: shared hit=2450902 read=59711
  CTE a0
    ->  Index Scan using org_address_org_search on msn.org_address org_address_1  (cost=0.43..4.54 rows=1 width=8) (actual time=0.069..0.071 rows=1 loops=1)
          Output: org_address_1.latitude, org_address_1.longitude
          Index Cond: (org_address_1.org_id = 1)
          Filter: (org_address_1.id = ANY ('{997192}'::integer[]))
          Buffers: shared hit=1 read=3
  CTE r0
    ->  Seq Scan on msn.org_rel_res  (cost=0.00..3.90 rows=1 width=8) (actual time=0.020..0.035 rows=2 loops=1)
          Output: org_rel_res.id, org_rel_res.res_id
          Filter: ((org_rel_res.id = ANY ('{7,82}'::integer[])) AND (org_rel_res.type = 2) AND (org_rel_res.org_id = 1))
          Rows Removed by Filter: 25
          Buffers: shared hit=1
  CTE d0
    ->  Hash Join  (cost=0.30..10.30 rows=1 width=240) (never executed)
          Output: r0.r, d.rd_id, (SubPlan 3), d.id, LEAST(d.value1, d.value2), GREATEST(d.value1, d.value2), d."values", (SubPlan 4)
          Hash Cond: (d.orr_id = r0.i)
          ->  Seq Scan on msn.orr_rel_res_descr d  (cost=0.00..2.50 rows=15 width=240) (never executed)
                Output: d.id, d.rd_id, d.orr_id, d.date_in, d.user_in, d.value1, d.value2, d."values"
          ->  Hash  (cost=0.20..0.20 rows=1 width=8) (never executed)
                Output: r0.r, r0.i
                ->  CTE Scan on r0  (cost=0.00..0.20 rows=1 width=8) (never executed)
                      Output: r0.r, r0.i
          SubPlan 3
            ->  Index Scan using res_description_id_pk on msn.res_description  (cost=0.28..4.39 rows=1 width=4) (never executed)
                  Output: res_description.type
                  Index Cond: (res_description.id = d.rd_id)
          SubPlan 4
            ->  Aggregate  (cost=2.85..2.95 rows=1 width=4) (never executed)
                  Output: count(orrd_value.id)
                  ->  Seq Scan on msn.orrd_value  (cost=0.00..2.85 rows=1 width=4) (never executed)
                        Output: orrd_value.id, orrd_value.orrd_id, orrd_value.rdv_id, orrd_value.date_in, orrd_value.user_in
                        Filter: (orrd_value.orrd_id = d.id)
  CTE s0
    ->  Hash Join  (cost=0.30..3.27 rows=1 width=8) (never executed)
          Output: d0.i, orrd_value_1.rdv_id
          Hash Cond: (orrd_value_1.orrd_id = d0.i)
          ->  Seq Scan on msn.orrd_value orrd_value_1  (cost=0.00..2.80 rows=18 width=8) (never executed)
                Output: orrd_value_1.id, orrd_value_1.orrd_id, orrd_value_1.rdv_id, orrd_value_1.date_in, orrd_value_1.user_in
          ->  Hash  (cost=0.20..0.20 rows=1 width=4) (never executed)
                Output: d0.i
                ->  CTE Scan on d0  (cost=0.00..0.20 rows=1 width=4) (never executed)
                      Output: d0.i
  CTE r2
    ->  Append  (cost=4118.93..5038.95 rows=4182 width=8) (actual time=1.471..2.656 rows=159 loops=1)
          Buffers: shared hit=75
          ->  Subquery Scan on r1  (cost=4118.93..5038.75 rows=4181 width=8) (actual time=1.470..2.541 rows=157 loops=1)
                Output: r1.r, (1000 - CASE WHEN (r1.c = 0) THEN (r1.o << 3) WHEN (r1.c > 3) THEN ((((r1.o)::numeric * (1.0 - (2.0 / ((r1.a - r1.o))::numeric))))::integer << 3) ELSE ((((r1.o)::numeric * (1.0 - (1.0 / ((r1.a - r1.o))::numeric))))::integer << (...)
                Buffers: shared hit=74
                ->  HashAggregate  (cost=4118.93..4620.65 rows=4181 width=12) (actual time=1.464..2.330 rows=157 loops=1)
                      Output: buyer_okved_rel_res.res_id, ((min(GREATEST(0, (o4.o - o4.c))) << 1) + 1), (((avg(GREATEST(0, (o4.o - o4.c))))::integer << 1) + 4), ((count(buyer_okved_rel_res.res_id))::integer - 1)
                      Buffers: shared hit=74
                      ->  Merge Join  (cost=2044.73..3985.73 rows=8880 width=12) (actual time=0.623..1.011 rows=157 loops=1)
                            Output: buyer_okved_rel_res.res_id, o4.o, o4.c
                            Merge Cond: (buyer_okved_rel_res.okved_id = o4.i)
                            Buffers: shared hit=74
                            ->  Index Only Scan using buyer_okved_rel_res_okved_res_id on msn.buyer_okved_rel_res  (cost=0.29..986.68 rows=8880 width=8) (actual time=0.035..0.138 rows=158 loops=1)
                                  Output: buyer_okved_rel_res.okved_id, buyer_okved_rel_res.res_id
                                  Heap Fetches: 0
                                  Buffers: shared hit=3
                            ->  Sort  (cost=2044.44..2044.94 rows=200 width=12) (actual time=0.581..0.640 rows=183 loops=1)
                                  Output: o4.o, o4.c, o4.i
                                  Sort Key: o4.i
                                  Sort Method: quicksort  Memory: 26kB
                                  Buffers: shared hit=71
                                  ->  Subquery Scan on o4  (cost=1995.30..2036.80 rows=200 width=12) (actual time=0.513..0.548 rows=27 loops=1)
                                        Output: o4.o, o4.c, o4.i
                                        Buffers: shared hit=71
                                        ->  HashAggregate  (cost=1995.30..2016.80 rows=200 width=8) (actual time=0.512..0.536 rows=27 loops=1)
                                              Output: o1_1.i, (min(o1_1.o) << 2), ((count(o1_1.o))::integer - 1)
                                              Buffers: shared hit=71
                                              ->  Append  (cost=1689.64..1888.44 rows=994 width=8) (actual time=0.057..0.450 rows=27 loops=1)
                                                    Buffers: shared hit=71
                                                    CTE o0
                                                      ->  Index Scan using org_rel_okved_id_pk on msn.org_rel_okved org_rel_okved_1  (cost=0.43..4.54 rows=1 width=8) (actual time=0.025..0.026 rows=1 loops=1)
                                                            Output: org_rel_okved_1.okved_id, org_rel_okved_1.ord
                                                            Index Cond: (org_rel_okved_1.id = ANY ('{15054388}'::integer[]))
                                                            Filter: (org_rel_okved_1.org_id = 1)
                                                            Buffers: shared hit=4
                                                    CTE o1
                                                      ->  Recursive Union  (cost=0.28..413.89 rows=101 width=12) (actual time=0.052..0.104 rows=3 loops=1)
                                                            Buffers: shared hit=13
                                                            ->  Nested Loop  (cost=0.28..4.69 rows=1 width=12) (actual time=0.049..0.054 rows=1 loops=1)
                                                                  Output: okved.id, okved.parent_id, o0.o
                                                                  Buffers: shared hit=7
                                                                  ->  CTE Scan on o0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.029..0.031 rows=1 loops=1)
                                                                        Output: o0.i, o0.o
                                                                        Buffers: shared hit=4
                                                                  ->  Index Scan using okved_pk on msn.okved  (cost=0.28..4.39 rows=1 width=8) (actual time=0.012..0.014 rows=1 loops=1)
                                                                        Output: okved.id, okved.parent_id, okved.strcode, okved.name, okved.date_in, okved.user_in
                                                                        Index Cond: (okved.id = o0.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..38.90 rows=10 width=12) (actual time=0.011..0.012 rows=1 loops=3)
                                                                  Output: okved_1.id, okved_1.parent_id, (o1.o + 1)
                                                                  Buffers: shared hit=6
                                                                  ->  WorkTable Scan on o1  (cost=0.00..2.00 rows=10 width=8) (actual time=0.001..0.002 rows=1 loops=3)
                                                                        Output: o1.i, o1.p, o1.o
                                                                  ->  Index Scan using okved_pk on msn.okved okved_1  (cost=0.28..3.59 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=3)
                                                                        Output: okved_1.id, okved_1.parent_id, okved_1.strcode, okved_1.name, okved_1.date_in, okved_1.user_in
                                                                        Index Cond: (okved_1.id = o1.p)
                                                                        Buffers: shared hit=6
                                                    CTE o2
                                                      ->  Recursive Union  (cost=0.28..1271.21 rows=893 width=12) (actual time=0.017..0.287 rows=24 loops=1)
                                                            Buffers: shared hit=58
                                                            ->  Nested Loop  (cost=0.28..7.02 rows=3 width=12) (actual time=0.016..0.021 rows=3 loops=1)
                                                                  Output: okved_2.id, okved_2.parent_id, o0_1.o
                                                                  Buffers: shared hit=3
                                                                  ->  CTE Scan on o0 o0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
                                                                        Output: o0_1.i, o0_1.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_2  (cost=0.28..6.52 rows=3 width=8) (actual time=0.010..0.013 rows=3 loops=1)
                                                                        Output: okved_2.id, okved_2.parent_id, okved_2.strcode, okved_2.name, okved_2.date_in, okved_2.user_in
                                                                        Index Cond: (okved_2.parent_id = o0_1.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..108.56 rows=89 width=12) (actual time=0.050..0.079 rows=7 loops=3)
                                                                  Output: okved_3.id, okved_3.parent_id, o2.o
                                                                  Buffers: shared hit=55
                                                                  ->  WorkTable Scan on o2  (cost=0.00..6.00 rows=30 width=8) (actual time=0.001..0.005 rows=8 loops=3)
                                                                        Output: o2.i, o2.p, o2.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_3  (cost=0.28..3.12 rows=3 width=8) (actual time=0.006..0.007 rows=1 loops=24)
                                                                        Output: okved_3.id, okved_3.parent_id, okved_3.strcode, okved_3.name, okved_3.date_in, okved_3.user_in
                                                                        Index Cond: (okved_3.parent_id = o2.i)
                                                                        Buffers: shared hit=55
                                                    ->  CTE Scan on o1 o1_1  (cost=0.00..20.20 rows=101 width=8) (actual time=0.055..0.110 rows=3 loops=1)
                                                          Output: o1_1.i, o1_1.o
                                                          Buffers: shared hit=13
                                                    ->  CTE Scan on o2 o2_1  (cost=0.00..178.60 rows=893 width=8) (actual time=0.022..0.327 rows=24 loops=1)
                                                          Output: o2_1.i, o2_1.o
                                                          Buffers: shared hit=58
          ->  CTE Scan on r0 r0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.023..0.046 rows=2 loops=1)
                Output: r0_1.r, 1080
                Buffers: shared hit=1
  ->  WindowAgg  (cost=7384237.92..7384332.92 rows=200 width=24) (actual time=44847.851..44849.989 rows=1000 loops=1)
        Output: "*SELECT* 1".org_id, ((((((max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (...)
        Buffers: shared hit=2450902 read=59711
        ->  Sort  (cost=7384237.92..7384238.42 rows=200 width=24) (actual time=44847.818..44848.230 rows=1000 loops=1)
              Output: ((((((max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubPlan 11))) (...)
              Sort Key: ((((((max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubPlan 11) (...)
              Sort Method: quicksort  Memory: 50035kB
              Buffers: shared hit=2450902 read=59711
              ->  Nested Loop  (cost=7383659.71..7384230.28 rows=200 width=24) (actual time=17672.153..43600.402 rows=703321 loops=1)
                    Output: (((((max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubPlan  (...)
                    Buffers: shared hit=2450902 read=59711
                    ->  HashAggregate  (cost=7383659.28..7383681.28 rows=200 width=12) (actual time=17640.727..19351.758 rows=703323 loops=1)
                          Output: "*SELECT* 1".org_id, (max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)
                          Buffers: shared hit=16832 read=34570
                          ->  Append  (cost=10855.91..5490315.85 rows=17612497 width=12) (actual time=75.641..15260.786 rows=1100072 loops=1)
                                Buffers: shared hit=16832 read=34570
                                ->  Subquery Scan on "*SELECT* 1"  (cost=10855.91..5488062.78 rows=17612288 width=12) (actual time=75.640..14828.132 rows=1100071 loops=1)
                                      Output: "*SELECT* 1".org_id, "*SELECT* 1".o
                                      Buffers: shared hit=16832 read=34568
                                      ->  Merge Join  (cost=10855.91..3726833.98 rows=17612288 width=12) (actual time=75.636..13956.927 rows=1100071 loops=1)
                                            Output: org_rel_okved.org_id, (o5.o + (300 / (org_rel_okved.ord + 3)))
                                            Merge Cond: (org_rel_okved.okved_id = o5.b)
                                            Buffers: shared hit=16832 read=34568
                                            ->  Index Only Scan using org_rel_okved_org_okved on msn.org_rel_okved  (cost=0.56..1697753.09 rows=14737260 width=12) (actual time=0.100..8286.733 rows=10016721 loops=1)
                                                  Output: org_rel_okved.okved_id, org_rel_okved.org_id, org_rel_okved.ord
                                                  Filter: (org_rel_okved.org_id <> 1)
                                                  Rows Removed by Filter: 5
                                                  Heap Fetches: 0
                                                  Buffers: shared hit=16274 read=34567
                                            ->  Sort  (cost=10855.35..10859.36 rows=1605 width=8) (actual time=21.807..369.028 rows=1094420 loops=1)
                                                  Output: o5.o, o5.b
                                                  Sort Key: o5.b
                                                  Sort Method: quicksort  Memory: 33kB
                                                  Buffers: shared hit=558 read=1
                                                  ->  Subquery Scan on o5  (cost=10436.85..10769.89 rows=1605 width=8) (actual time=21.400..21.641 rows=178 loops=1)
                                                        Output: o5.o, o5.b
                                                        Buffers: shared hit=558 read=1
                                                        ->  HashAggregate  (cost=10436.85..10609.39 rows=1605 width=8) (actual time=21.399..21.572 rows=178 loops=1)
                                                              Output: res_rel_okved.okved_id, (max(r2.o) + ((sum(r2.o) / 300))::integer)
                                                              Buffers: shared hit=558 read=1
                                                              ->  Nested Loop  (cost=0.29..10179.40 rows=34328 width=8) (actual time=1.499..20.468 rows=716 loops=1)
                                                                    Output: res_rel_okved.okved_id, r2.o
                                                                    Buffers: shared hit=558 read=1
                                                                    ->  CTE Scan on r2  (cost=0.00..836.40 rows=4182 width=8) (actual time=1.476..2.965 rows=159 loops=1)
                                                                          Output: r2.r, r2.o
                                                                          Buffers: shared hit=75
                                                                    ->  Index Scan using res_rel_okved_res on msn.res_rel_okved  (cost=0.29..1.43 rows=8 width=8) (actual time=0.101..0.105 rows=5 loops=159)
                                                                          Output: res_rel_okved.id, res_rel_okved.res_id, res_rel_okved.okved_id
                                                                          Index Cond: (res_rel_okved.res_id = r2.r)
                                                                          Buffers: shared hit=483 read=1
                                ->  Subquery Scan on "*SELECT* 2"  (cost=2209.18..2253.07 rows=209 width=16) (actual time=0.346..0.347 rows=1 loops=1)
                                      Output: "*SELECT* 2".org_id, "*SELECT* 2".o
                                      Buffers: shared read=2
                                      ->  HashAggregate  (cost=2209.18..2232.17 rows=209 width=16) (actual time=0.344..0.345 rows=1 loops=1)
                                            Output: l.org_id, (((r2_1.o + 50))::double precision + (8::double precision * COALESCE(sum((SubPlan 13)), 0::double precision))), r2_1.r, r2_1.o
                                            Buffers: shared read=2
                                            ->  Hash Join  (cost=4.86..898.75 rows=209 width=16) (actual time=0.265..0.269 rows=1 loops=1)
                                                  Output: l.org_id, l.id, r2_1.r, r2_1.o
                                                  Hash Cond: (r2_1.r = l.res_id)
                                                  Buffers: shared read=1
                                                  ->  CTE Scan on r2 r2_1  (cost=0.00..836.40 rows=4182 width=8) (actual time=0.003..0.067 rows=159 loops=1)
                                                        Output: r2_1.r, r2_1.o
                                                  ->  Hash  (cost=3.84..3.84 rows=10 width=12) (actual time=0.080..0.080 rows=9 loops=1)
                                                        Output: l.org_id, l.id, l.res_id
                                                        Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                                        Buffers: shared read=1
                                                        ->  Seq Scan on msn.org_rel_res l  (cost=0.00..3.84 rows=10 width=12) (actual time=0.056..0.068 rows=9 loops=1)
                                                              Output: l.org_id, l.id, l.res_id
                                                              Filter: ((l.org_id <> 1) AND (l.type = 1))
                                                              Rows Removed by Filter: 18
                                                              Buffers: shared read=1
                                            SubPlan 13
                                              ->  Aggregate  (cost=6.15..6.26 rows=1 width=74) (actual time=0.051..0.051 rows=1 loops=1)
                                                    Output: (((LEAST(count(e.id), 3::bigint) + COALESCE(count(d0_1.d), 0::bigint)))::double precision + sum(CASE WHEN (d0_1.t IS NULL) THEN 0::double precision WHEN (d0_1.t = 1) THEN CASE WHEN (d0_1.v1 = d0_1 (...)
                                                    Buffers: shared read=1
                                                    ->  Nested Loop Left Join  (cost=0.00..2.84 rows=1 width=74) (actual time=0.041..0.041 rows=0 loops=1)
                                                          Output: e.id, e.value1, e.value2, d0_1.d, d0_1.t, d0_1.v1, d0_1.v2, d0_1.vc, d0_1.i
                                                          Join Filter: (d0_1.d = e.rd_id)
                                                          Buffers: shared read=1
                                                          ->  Seq Scan on msn.orr_rel_res_descr e  (cost=0.00..2.54 rows=1 width=18) (actual time=0.035..0.035 rows=0 loops=1)
                                                                Output: e.id, e.rd_id, e.orr_id, e.date_in, e.user_in, e.value1, e.value2, e."values"
                                                                Filter: (e.orr_id = l.id)
                                                                Rows Removed by Filter: 15
                                                                Buffers: shared read=1
                                                          ->  CTE Scan on d0 d0_1  (cost=0.00..0.20 rows=1 width=60) (never executed)
                                                                Output: d0_1.s, d0_1.d, d0_1.t, d0_1.i, d0_1.v1, d0_1.v2, d0_1.vs, d0_1.vc
                                                                Filter: (d0_1.s = r2_1.r)
                                                    SubPlan 12
                                                      ->  Aggregate  (cost=3.15..3.26 rows=1 width=0) (never executed)
                                                            Output: COALESCE((1 + (((sum(1) * 3) / d0_1.vc))::integer), (-5))
                                                            ->  Nested Loop  (cost=0.00..3.15 rows=1 width=0) (never executed)
                                                                  Join Filter: (orrd_value_2.rdv_id = s0.vp)
                                                                  ->  Seq Scan on msn.orrd_value orrd_value_2  (cost=0.00..2.85 rows=1 width=4) (never executed)
                                                                        Output: orrd_value_2.id, orrd_value_2.orrd_id, orrd_value_2.rdv_id, orrd_value_2.date_in, orrd_value_2.user_in
                                                                        Filter: (orrd_value_2.orrd_id = e.id)
                                                                  ->  CTE Scan on s0  (cost=0.00..0.20 rows=1 width=4) (never executed)
                                                                        Output: s0.h, s0.vp
                                                                        Filter: (s0.h = d0_1.i)
                    ->  Index Only Scan using org_address_org_search on msn.org_address  (cost=0.43..2.54 rows=1 width=16) (actual time=0.013..0.015 rows=1 loops=703323)
                          Output: org_address.org_id, org_address.is_sale, org_address.latitude, org_address.longitude, org_address.weight
                          Index Cond: ((org_address.org_id = "*SELECT* 1".org_id) AND (org_address.is_sale = true))
                          Filter: org_address.is_sale
                          Heap Fetches: 0
                          Buffers: shared hit=2434069 read=25138
                    SubPlan 11
                      ->  Aggregate  (cost=0.24..0.34 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=703321)
                            Output: COALESCE(min(((abs((org_address.latitude - a0.t)) / 15000) + (LEAST((abs(((org_address.longitude - a0.g) + 180000000)) % 180000000), (abs(((a0.g - org_address.longitude) + 180000000)) % 180000000)) / 16500))), 5000)
                            Buffers: shared hit=1 read=3
                            ->  CTE Scan on a0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=703321)
                                  Output: a0.t, a0.g
                                  Buffers: shared hit=1 read=3
Total runtime: 44861.792 ms

Что первое бросается в глаза - это то, что оптимизатор сильно ошибается в подсчете строк:

Код: plsql

1.
2.
3.
4.

                    ->  HashAggregate  (cost=7383659.28..7383681.28 rows=200 width=12) (actual time=17640.727..19351.758 rows=703323 loops=1)
                                      ->  Merge Join  (cost=10855.91..3726833.98 rows=17612288 width=12) (actual time=75.636..13956.927 rows=1100071 loops=1)
                                            ->  Index Only Scan using org_rel_okved_org_okved on msn.org_rel_okved  (cost=0.56..1697753.09 rows=14737260 width=12) (actual time=0.100..8286.733 rows=10016721 loops=1)
                                                              ->  Nested Loop  (cost=0.29..10179.40 rows=34328 width=8) (actual time=1.499..20.468 rows=716 loops=1)

Пробовал статистику для колонок, но это повлияло на оценку строк совсем незначительно:

Код: plsql

1.
2.
3.

ALTER TABLE res_rel_okved ALTER COLUMN okved_id SET STATISTICS 1000;
ALTER TABLE buyer_okved_rel_res ALTER COLUMN okved_id SET STATISTICS 1000;
ALTER TABLE org_rel_okved ALTER COLUMN okved_id SET STATISTICS 1000;

Перед работой выполнялась команда

Код: powershell

/usr/local/pgsql/bin/vacuumdb -U postgres --quiet --analyze --dbname=msn1

Железо: Intel D525MUD, DDR3 1Gb
PostgreSQL 9.3.1

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

#------------------------------------------------------------------------------
# pgtune run on 2013-11-18
# Based on 1009064 KB RAM, platform Linux
#------------------------------------------------------------------------------
default_statistics_target = 100
maintenance_work_mem = 60MB
checkpoint_completion_target = 0.9
effective_cache_size = 704MB
work_mem = 4608kB
wal_buffers = 7MB
checkpoint_segments = 32
shared_buffers = 240MB
#my
random_page_cost = 2
cpu_tuple_cost = 0.1
autovacuum = off

Вот инормация о количестве записей:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

Таблица	Estimated row count
buyer_okved_rel_res	8880
okdp_rel_okved	1489
okved	1875
org_address	4994383
org_detail	31432532
org_group	3
org_rel_okved	14737270
org_rel_org_group	3
org_rel_res	27
organization	5020716
orr_rel_res_descr	15
orrd_value	18
rdv_okdp_code	32879
res_descr_value	32880
res_description	6535
res_rel_okved	66940
resource	9679

Если какие-то детали нужны - отвечу. Дамп прикрепил на всякий случай :)

Пробовал в памяти диск создать (добавлял планку на 1Гб), пробовал индексы/таблицы туда разные пихать, прирост небольшой.

Сильно помог "Index Only Scan using org_address_org_search". Пробовал org_address объединить с a0, и это дало хороший прирост(около 30%), если строка в a0 - одна; если больше, то объединение замедляется на количество строк в a0 линейно.

Пробовал посчитать количество записей перед выполнением большого запроса "limit loc_ocnt", чтобы подсказать оптимизатору, что записей не надо брать много, а надо столько, сколько в лимите сказано - конечно, это немного ускорило запрос, но как то не красиво, да и дальше опять оптимизатор из-за количества не верного выбирает не верную дорожку.

У кого есть идеи, поделитесь, пожалуйста.

...

Рейтинг:

0 / 0

10.01.2014, 23:09:33

| Ответить | Цитировать | Написать

На пути к идеальному плану

#38522914

LeXa NalBat

Участник

Откуда: Москва

Сообщения: 2 843

Рейтинг: 0 / 0

попробуйте set enable_sort to off. чтобы не вытаскивать целиком 10М строк и группировать их в 700К, лишь для того чтобы выдать 1К.

подробную статистику попробуйте собрать по колонкам org_id. кстати, максимально подробная - 10000.

...

Рейтинг:

0 / 0

11.01.2014, 14:00:52

| Ответить | Цитировать | Написать

На пути к идеальному плану

#38523174

R_Only

Гость

Итак, сначала :) Делаю вакуум с анализом, и два раза запускаю запрос упомянутый выше(на первый ушло 120 сек), получаю план на 45 сек:

Код: plsql

Limit  (cost=7532577.74..7532672.74 rows=200 width=24) (actual time=45126.027..45128.508 rows=1000 loops=1)
  Output: *SELECT* 1".org_id, ((((((max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubP (...)"
  Buffers: shared hit=2447036 read=63577
  CTE a0
    ->  Index Scan using org_address_org_search on msn.org_address org_address_1  (cost=0.43..4.54 rows=1 width=8) (actual time=0.055..0.058 rows=1 loops=1)
          Output: org_address_1.latitude, org_address_1.longitude
          Index Cond: (org_address_1.org_id = 1)
          Filter: (org_address_1.id = ANY ('{997192}'::integer[]))
          Buffers: shared hit=1 read=3
  CTE r0
    ->  Seq Scan on msn.org_rel_res  (cost=0.00..3.90 rows=1 width=8) (actual time=0.021..0.033 rows=2 loops=1)
          Output: org_rel_res.id, org_rel_res.res_id
          Filter: ((org_rel_res.id = ANY ('{7,82}'::integer[])) AND (org_rel_res.type = 2) AND (org_rel_res.org_id = 1))
          Rows Removed by Filter: 25
          Buffers: shared hit=1
  CTE d0
    ->  Hash Join  (cost=0.30..10.30 rows=1 width=240) (never executed)
          Output: r0.r, d.rd_id, (SubPlan 3), d.id, LEAST(d.value1, d.value2), GREATEST(d.value1, d.value2), d.values", (SubPlan 4)"
          Hash Cond: (d.orr_id = r0.i)
          ->  Seq Scan on msn.orr_rel_res_descr d  (cost=0.00..2.50 rows=15 width=240) (never executed)
                Output: d.id, d.rd_id, d.orr_id, d.date_in, d.user_in, d.value1, d.value2, d.values""
          ->  Hash  (cost=0.20..0.20 rows=1 width=8) (never executed)
                Output: r0.r, r0.i
                ->  CTE Scan on r0  (cost=0.00..0.20 rows=1 width=8) (never executed)
                      Output: r0.r, r0.i
          SubPlan 3
            ->  Index Scan using res_description_id_pk on msn.res_description  (cost=0.28..4.39 rows=1 width=4) (never executed)
                  Output: res_description.type
                  Index Cond: (res_description.id = d.rd_id)
          SubPlan 4
            ->  Aggregate  (cost=2.85..2.95 rows=1 width=4) (never executed)
                  Output: count(orrd_value.id)
                  ->  Seq Scan on msn.orrd_value  (cost=0.00..2.85 rows=1 width=4) (never executed)
                        Output: orrd_value.id, orrd_value.orrd_id, orrd_value.rdv_id, orrd_value.date_in, orrd_value.user_in
                        Filter: (orrd_value.orrd_id = d.id)
  CTE s0
    ->  Hash Join  (cost=0.30..3.27 rows=1 width=8) (never executed)
          Output: d0.i, orrd_value_1.rdv_id
          Hash Cond: (orrd_value_1.orrd_id = d0.i)
          ->  Seq Scan on msn.orrd_value orrd_value_1  (cost=0.00..2.80 rows=18 width=8) (never executed)
                Output: orrd_value_1.id, orrd_value_1.orrd_id, orrd_value_1.rdv_id, orrd_value_1.date_in, orrd_value_1.user_in
          ->  Hash  (cost=0.20..0.20 rows=1 width=4) (never executed)
                Output: d0.i
                ->  CTE Scan on d0  (cost=0.00..0.20 rows=1 width=4) (never executed)
                      Output: d0.i
  CTE r2
    ->  Append  (cost=4118.93..5038.95 rows=4182 width=8) (actual time=50.209..51.580 rows=159 loops=1)
          Buffers: shared hit=65 read=10
          ->  Subquery Scan on r1  (cost=4118.93..5038.75 rows=4181 width=8) (actual time=50.208..51.460 rows=157 loops=1)
                Output: r1.r, (1000 - CASE WHEN (r1.c = 0) THEN (r1.o << 3) WHEN (r1.c > 3) THEN ((((r1.o)::numeric * (1.0 - (2.0 / ((r1.a - r1.o))::numeric))))::integer << 3) ELSE ((((r1.o)::numeric * (1.0 - (1.0 / ((r1.a - r1.o))::numeric))))::integer << (...)
                Buffers: shared hit=64 read=10
                ->  HashAggregate  (cost=4118.93..4620.65 rows=4181 width=12) (actual time=50.201..51.183 rows=157 loops=1)
                      Output: buyer_okved_rel_res.res_id, ((min(GREATEST(0, (o4.o - o4.c))) << 1) + 1), (((avg(GREATEST(0, (o4.o - o4.c))))::integer << 1) + 4), ((count(buyer_okved_rel_res.res_id))::integer - 1)
                      Buffers: shared hit=64 read=10
                      ->  Merge Join  (cost=2044.73..3985.73 rows=8880 width=12) (actual time=49.370..49.758 rows=157 loops=1)
                            Output: buyer_okved_rel_res.res_id, o4.o, o4.c
                            Merge Cond: (buyer_okved_rel_res.okved_id = o4.i)
                            Buffers: shared hit=64 read=10
                            ->  Index Only Scan using buyer_okved_rel_res_okved_res_id on msn.buyer_okved_rel_res  (cost=0.29..986.68 rows=8880 width=8) (actual time=25.784..25.887 rows=158 loops=1)
                                  Output: buyer_okved_rel_res.okved_id, buyer_okved_rel_res.res_id
                                  Heap Fetches: 0
                                  Buffers: shared hit=2 read=1
                            ->  Sort  (cost=2044.44..2044.94 rows=200 width=12) (actual time=23.578..23.642 rows=183 loops=1)
                                  Output: o4.o, o4.c, o4.i
                                  Sort Key: o4.i
                                  Sort Method: quicksort  Memory: 26kB
                                  Buffers: shared hit=62 read=9
                                  ->  Subquery Scan on o4  (cost=1995.30..2036.80 rows=200 width=12) (actual time=23.483..23.528 rows=27 loops=1)
                                        Output: o4.o, o4.c, o4.i
                                        Buffers: shared hit=62 read=9
                                        ->  HashAggregate  (cost=1995.30..2016.80 rows=200 width=8) (actual time=23.481..23.506 rows=27 loops=1)
                                              Output: o1_1.i, (min(o1_1.o) << 2), ((count(o1_1.o))::integer - 1)
                                              Buffers: shared hit=62 read=9
                                              ->  Append  (cost=1689.64..1888.44 rows=994 width=8) (actual time=0.126..23.412 rows=27 loops=1)
                                                    Buffers: shared hit=62 read=9
                                                    CTE o0
                                                      ->  Index Scan using org_rel_okved_id_pk on msn.org_rel_okved org_rel_okved_1  (cost=0.43..4.54 rows=1 width=8) (actual time=0.073..0.074 rows=1 loops=1)
                                                            Output: org_rel_okved_1.okved_id, org_rel_okved_1.ord
                                                            Index Cond: (org_rel_okved_1.id = ANY ('{15054388}'::integer[]))
                                                            Filter: (org_rel_okved_1.org_id = 1)
                                                            Buffers: shared read=4
                                                    CTE o1
                                                      ->  Recursive Union  (cost=0.28..413.89 rows=101 width=12) (actual time=0.122..0.187 rows=3 loops=1)
                                                            Buffers: shared hit=6 read=7
                                                            ->  Nested Loop  (cost=0.28..4.69 rows=1 width=12) (actual time=0.118..0.122 rows=1 loops=1)
                                                                  Output: okved.id, okved.parent_id, o0.o
                                                                  Buffers: shared hit=1 read=6
                                                                  ->  CTE Scan on o0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.077..0.079 rows=1 loops=1)
                                                                        Output: o0.i, o0.o
                                                                        Buffers: shared read=4
                                                                  ->  Index Scan using okved_pk on msn.okved  (cost=0.28..4.39 rows=1 width=8) (actual time=0.031..0.033 rows=1 loops=1)
                                                                        Output: okved.id, okved.parent_id, okved.strcode, okved.name, okved.date_in, okved.user_in
                                                                        Index Cond: (okved.id = o0.i)
                                                                        Buffers: shared hit=1 read=2
                                                            ->  Nested Loop  (cost=0.28..38.90 rows=10 width=12) (actual time=0.016..0.017 rows=1 loops=3)
                                                                  Output: okved_1.id, okved_1.parent_id, (o1.o + 1)
                                                                  Buffers: shared hit=5 read=1
                                                                  ->  WorkTable Scan on o1  (cost=0.00..2.00 rows=10 width=8) (actual time=0.001..0.001 rows=1 loops=3)
                                                                        Output: o1.i, o1.p, o1.o
                                                                  ->  Index Scan using okved_pk on msn.okved okved_1  (cost=0.28..3.59 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=3)
                                                                        Output: okved_1.id, okved_1.parent_id, okved_1.strcode, okved_1.name, okved_1.date_in, okved_1.user_in
                                                                        Index Cond: (okved_1.id = o1.p)
                                                                        Buffers: shared hit=5 read=1
                                                    CTE o2
                                                      ->  Recursive Union  (cost=0.28..1271.21 rows=893 width=12) (actual time=0.017..23.161 rows=24 loops=1)
                                                            Buffers: shared hit=56 read=2
                                                            ->  Nested Loop  (cost=0.28..7.02 rows=3 width=12) (actual time=0.016..0.021 rows=3 loops=1)
                                                                  Output: okved_2.id, okved_2.parent_id, o0_1.o
                                                                  Buffers: shared hit=3
                                                                  ->  CTE Scan on o0 o0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
                                                                        Output: o0_1.i, o0_1.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_2  (cost=0.28..6.52 rows=3 width=8) (actual time=0.009..0.013 rows=3 loops=1)
                                                                        Output: okved_2.id, okved_2.parent_id, okved_2.strcode, okved_2.name, okved_2.date_in, okved_2.user_in
                                                                        Index Cond: (okved_2.parent_id = o0_1.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..108.56 rows=89 width=12) (actual time=0.037..7.703 rows=7 loops=3)
                                                                  Output: okved_3.id, okved_3.parent_id, o2.o
                                                                  Buffers: shared hit=53 read=2
                                                                  ->  WorkTable Scan on o2  (cost=0.00..6.00 rows=30 width=8) (actual time=0.001..0.005 rows=8 loops=3)
                                                                        Output: o2.i, o2.p, o2.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_3  (cost=0.28..3.12 rows=3 width=8) (actual time=0.958..0.960 rows=1 loops=24)
                                                                        Output: okved_3.id, okved_3.parent_id, okved_3.strcode, okved_3.name, okved_3.date_in, okved_3.user_in
                                                                        Index Cond: (okved_3.parent_id = o2.i)
                                                                        Buffers: shared hit=53 read=2
                                                    ->  CTE Scan on o1 o1_1  (cost=0.00..20.20 rows=101 width=8) (actual time=0.125..0.197 rows=3 loops=1)
                                                          Output: o1_1.i, o1_1.o
                                                          Buffers: shared hit=6 read=7
                                                    ->  CTE Scan on o2 o2_1  (cost=0.00..178.60 rows=893 width=8) (actual time=0.020..23.203 rows=24 loops=1)
                                                          Output: o2_1.i, o2_1.o
                                                          Buffers: shared hit=56 read=2
          ->  CTE Scan on r0 r0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.026..0.041 rows=2 loops=1)
                Output: r0_1.r, 1080
                Buffers: shared hit=1
  ->  WindowAgg  (cost=7527516.78..7527611.78 rows=200 width=24) (actual time=45126.023..45128.107 rows=1000 loops=1)
        Output: *SELECT* 1".org_id, ((((((max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (...)"
        Buffers: shared hit=2447036 read=63577
        ->  Sort  (cost=7527516.78..7527517.28 rows=200 width=24) (actual time=45125.991..45126.374 rows=1000 loops=1)
              Output: ((((((max(((*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubPlan 11))) (...)"
              Sort Key: ((((((max(((*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubPlan 11) (...)"
              Sort Method: quicksort  Memory: 50035kB
              Buffers: shared hit=2447036 read=63577
              ->  Nested Loop  (cost=7526938.57..7527509.13 rows=200 width=24) (actual time=17758.731..43897.394 rows=703321 loops=1)
                    Output: (((((max(((*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubPlan  (...)"
                    Buffers: shared hit=2447036 read=63577
                    ->  HashAggregate  (cost=7526938.13..7526960.13 rows=200 width=12) (actual time=17758.543..19482.805 rows=703323 loops=1)
                          Output: *SELECT* 1".org_id, (max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)"
                          Buffers: shared hit=12966 read=38436
                          ->  Append  (cost=10856.67..5585438.68 rows=18060460 width=12) (actual time=225.611..15434.977 rows=1100072 loops=1)
                                Buffers: shared hit=12966 read=38436
                                ->  Subquery Scan on *SELECT* 1"  (cost=10856.67..5583185.61 rows=18060251 width=12) (actual time=225.611..14994.678 rows=1100071 loops=1)"
                                      Output: *SELECT* 1".org_id, "*SELECT* 1".o"
                                      Buffers: shared hit=12966 read=38434
                                      ->  Merge Join  (cost=10856.67..3777160.51 rows=18060251 width=12) (actual time=225.606..14115.042 rows=1100071 loops=1)
                                            Output: org_rel_okved.org_id, (o5.o + (300 / (org_rel_okved.ord + 3)))
                                            Merge Cond: (org_rel_okved.okved_id = o5.b)
                                            Buffers: shared hit=12966 read=38434
                                            ->  Index Only Scan using org_rel_okved_org_okved on msn.org_rel_okved  (cost=0.56..1697684.61 rows=14736624 width=12) (actual time=29.407..8307.660 rows=10016721 loops=1)
                                                  Output: org_rel_okved.okved_id, org_rel_okved.org_id, org_rel_okved.ord
                                                  Filter: (org_rel_okved.org_id <> 1)
                                                  Rows Removed by Filter: 5
                                                  Heap Fetches: 0
                                                  Buffers: shared hit=12459 read=38382
                                            ->  Sort  (cost=10856.11..10860.11 rows=1603 width=8) (actual time=142.241..487.008 rows=1094420 loops=1)
                                                  Output: o5.o, o5.b
                                                  Sort Key: o5.b
                                                  Sort Method: quicksort  Memory: 33kB
                                                  Buffers: shared hit=507 read=52
                                                  ->  Subquery Scan on o5  (cost=10438.15..10770.77 rows=1603 width=8) (actual time=141.835..142.099 rows=178 loops=1)
                                                        Output: o5.o, o5.b
                                                        Buffers: shared hit=507 read=52
                                                        ->  HashAggregate  (cost=10438.15..10610.47 rows=1603 width=8) (actual time=141.833..142.004 rows=178 loops=1)
                                                              Output: res_rel_okved.okved_id, (max(r2.o) + ((sum(r2.o) / 300))::integer)
                                                              Buffers: shared hit=507 read=52
                                                              ->  Nested Loop  (cost=0.29..10179.40 rows=34501 width=8) (actual time=50.237..140.749 rows=716 loops=1)
                                                                    Output: res_rel_okved.okved_id, r2.o
                                                                    Buffers: shared hit=507 read=52
                                                                    ->  CTE Scan on r2  (cost=0.00..836.40 rows=4182 width=8) (actual time=50.214..51.893 rows=159 loops=1)
                                                                          Output: r2.r, r2.o
                                                                          Buffers: shared hit=65 read=10
                                                                    ->  Index Scan using res_rel_okved_res on msn.res_rel_okved  (cost=0.29..1.43 rows=8 width=8) (actual time=0.542..0.553 rows=5 loops=159)
                                                                          Output: res_rel_okved.id, res_rel_okved.res_id, res_rel_okved.okved_id
                                                                          Index Cond: (res_rel_okved.res_id = r2.r)
                                                                          Buffers: shared hit=442 read=42
                                ->  Subquery Scan on *SELECT* 2"  (cost=2209.18..2253.07 rows=209 width=16) (actual time=0.298..0.299 rows=1 loops=1)"
                                      Output: *SELECT* 2".org_id, "*SELECT* 2".o"
                                      Buffers: shared read=2
                                      ->  HashAggregate  (cost=2209.18..2232.17 rows=209 width=16) (actual time=0.297..0.297 rows=1 loops=1)
                                            Output: l.org_id, (((r2_1.o + 50))::double precision + (8::double precision * COALESCE(sum((SubPlan 13)), 0::double precision))), r2_1.r, r2_1.o
                                            Buffers: shared read=2
                                            ->  Hash Join  (cost=4.86..898.75 rows=209 width=16) (actual time=0.229..0.232 rows=1 loops=1)
                                                  Output: l.org_id, l.id, r2_1.r, r2_1.o
                                                  Hash Cond: (r2_1.r = l.res_id)
                                                  Buffers: shared read=1
                                                  ->  CTE Scan on r2 r2_1  (cost=0.00..836.40 rows=4182 width=8) (actual time=0.003..0.074 rows=159 loops=1)
                                                        Output: r2_1.r, r2_1.o
                                                  ->  Hash  (cost=3.84..3.84 rows=10 width=12) (actual time=0.059..0.059 rows=9 loops=1)
                                                        Output: l.org_id, l.id, l.res_id
                                                        Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                                        Buffers: shared read=1
                                                        ->  Seq Scan on msn.org_rel_res l  (cost=0.00..3.84 rows=10 width=12) (actual time=0.035..0.046 rows=9 loops=1)
                                                              Output: l.org_id, l.id, l.res_id
                                                              Filter: ((l.org_id <> 1) AND (l.type = 1))
                                                              Rows Removed by Filter: 18
                                                              Buffers: shared read=1
                                            SubPlan 13
                                              ->  Aggregate  (cost=6.15..6.26 rows=1 width=74) (actual time=0.039..0.039 rows=1 loops=1)
                                                    Output: (((LEAST(count(e.id), 3::bigint) + COALESCE(count(d0_1.d), 0::bigint)))::double precision + sum(CASE WHEN (d0_1.t IS NULL) THEN 0::double precision WHEN (d0_1.t = 1) THEN CASE WHEN (d0_1.v1 = d0_1 (...)
                                                    Buffers: shared read=1
                                                    ->  Nested Loop Left Join  (cost=0.00..2.84 rows=1 width=74) (actual time=0.029..0.029 rows=0 loops=1)
                                                          Output: e.id, e.value1, e.value2, d0_1.d, d0_1.t, d0_1.v1, d0_1.v2, d0_1.vc, d0_1.i
                                                          Join Filter: (d0_1.d = e.rd_id)
                                                          Buffers: shared read=1
                                                          ->  Seq Scan on msn.orr_rel_res_descr e  (cost=0.00..2.54 rows=1 width=18) (actual time=0.023..0.023 rows=0 loops=1)
                                                                Output: e.id, e.rd_id, e.orr_id, e.date_in, e.user_in, e.value1, e.value2, e.values""
                                                                Filter: (e.orr_id = l.id)
                                                                Rows Removed by Filter: 15
                                                                Buffers: shared read=1
                                                          ->  CTE Scan on d0 d0_1  (cost=0.00..0.20 rows=1 width=60) (never executed)
                                                                Output: d0_1.s, d0_1.d, d0_1.t, d0_1.i, d0_1.v1, d0_1.v2, d0_1.vs, d0_1.vc
                                                                Filter: (d0_1.s = r2_1.r)
                                                    SubPlan 12
                                                      ->  Aggregate  (cost=3.15..3.26 rows=1 width=0) (never executed)
                                                            Output: COALESCE((1 + (((sum(1) * 3) / d0_1.vc))::integer), (-5))
                                                            ->  Nested Loop  (cost=0.00..3.15 rows=1 width=0) (never executed)
                                                                  Join Filter: (orrd_value_2.rdv_id = s0.vp)
                                                                  ->  Seq Scan on msn.orrd_value orrd_value_2  (cost=0.00..2.85 rows=1 width=4) (never executed)
                                                                        Output: orrd_value_2.id, orrd_value_2.orrd_id, orrd_value_2.rdv_id, orrd_value_2.date_in, orrd_value_2.user_in
                                                                        Filter: (orrd_value_2.orrd_id = e.id)
                                                                  ->  CTE Scan on s0  (cost=0.00..0.20 rows=1 width=4) (never executed)
                                                                        Output: s0.h, s0.vp
                                                                        Filter: (s0.h = d0_1.i)
                    ->  Index Only Scan using org_address_org_search on msn.org_address  (cost=0.43..2.54 rows=1 width=16) (actual time=0.014..0.015 rows=1 loops=703323)
                          Output: org_address.org_id, org_address.is_sale, org_address.latitude, org_address.longitude, org_address.weight
                          Index Cond: ((org_address.org_id = *SELECT* 1".org_id) AND (org_address.is_sale = true))"
                          Filter: org_address.is_sale
                          Heap Fetches: 0
                          Buffers: shared hit=2434069 read=25138
                    SubPlan 11
                      ->  Aggregate  (cost=0.24..0.34 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=703321)
                            Output: COALESCE(min(((abs((org_address.latitude - a0.t)) / 15000) + (LEAST((abs(((org_address.longitude - a0.g) + 180000000)) % 180000000), (abs(((a0.g - org_address.longitude) + 180000000)) % 180000000)) / 16500))), 5000)
                            Buffers: shared hit=1 read=3
                            ->  CTE Scan on a0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=703321)
                                  Output: a0.t, a0.g
                                  Buffers: shared hit=1 read=3
Total runtime: 45137.243 ms

пробую статистику на 10000 и добавляю по полю org_id:

Код: plsql

1.
2.
3.
4.
5.
6.
7.

ALTER TABLE res_rel_okved ALTER COLUMN okved_id SET STATISTICS 10000;
ALTER TABLE buyer_okved_rel_res ALTER COLUMN okved_id SET STATISTICS 10000;
ALTER TABLE org_rel_okved ALTER COLUMN okved_id SET STATISTICS 10000;

ALTER TABLE org_address ALTER COLUMN org_id SET STATISTICS 10000;
ALTER TABLE org_rel_res ALTER COLUMN org_id SET STATISTICS 10000;
ALTER TABLE org_rel_okved ALTER COLUMN org_id SET STATISTICS 10000;

делаю вакуум с анализом(он заметно замедлился), результат второго запуска 33 сек (первый - 40 сек):

Код: plsql

Limit  (cost=6193395.44..6193490.44 rows=200 width=24) (actual time=33083.580..33086.066 rows=1000 loops=1)
  Output: *SELECT* 1".org_id, ((((((max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubP (...)"
  Buffers: shared hit=2466480
  CTE a0
    ->  Index Scan using org_address_org_search on msn.org_address org_address_1  (cost=0.43..4.54 rows=1 width=8) (actual time=0.020..0.022 rows=1 loops=1)
          Output: org_address_1.latitude, org_address_1.longitude
          Index Cond: (org_address_1.org_id = 1)
          Filter: (org_address_1.id = ANY ('{997192}'::integer[]))
          Buffers: shared hit=4
  CTE r0
    ->  Seq Scan on msn.org_rel_res  (cost=0.00..3.90 rows=1 width=8) (actual time=0.019..0.031 rows=2 loops=1)
          Output: org_rel_res.id, org_rel_res.res_id
          Filter: ((org_rel_res.id = ANY ('{7,82}'::integer[])) AND (org_rel_res.type = 2) AND (org_rel_res.org_id = 1))
          Rows Removed by Filter: 25
          Buffers: shared hit=1
  CTE d0
    ->  Hash Join  (cost=0.30..10.30 rows=1 width=240) (never executed)
          Output: r0.r, d.rd_id, (SubPlan 3), d.id, LEAST(d.value1, d.value2), GREATEST(d.value1, d.value2), d.values", (SubPlan 4)"
          Hash Cond: (d.orr_id = r0.i)
          ->  Seq Scan on msn.orr_rel_res_descr d  (cost=0.00..2.50 rows=15 width=240) (never executed)
                Output: d.id, d.rd_id, d.orr_id, d.date_in, d.user_in, d.value1, d.value2, d.values""
          ->  Hash  (cost=0.20..0.20 rows=1 width=8) (never executed)
                Output: r0.r, r0.i
                ->  CTE Scan on r0  (cost=0.00..0.20 rows=1 width=8) (never executed)
                      Output: r0.r, r0.i
          SubPlan 3
            ->  Index Scan using res_description_id_pk on msn.res_description  (cost=0.28..4.39 rows=1 width=4) (never executed)
                  Output: res_description.type
                  Index Cond: (res_description.id = d.rd_id)
          SubPlan 4
            ->  Aggregate  (cost=2.85..2.95 rows=1 width=4) (never executed)
                  Output: count(orrd_value.id)
                  ->  Seq Scan on msn.orrd_value  (cost=0.00..2.85 rows=1 width=4) (never executed)
                        Output: orrd_value.id, orrd_value.orrd_id, orrd_value.rdv_id, orrd_value.date_in, orrd_value.user_in
                        Filter: (orrd_value.orrd_id = d.id)
  CTE s0
    ->  Hash Join  (cost=0.30..3.27 rows=1 width=8) (never executed)
          Output: d0.i, orrd_value_1.rdv_id
          Hash Cond: (orrd_value_1.orrd_id = d0.i)
          ->  Seq Scan on msn.orrd_value orrd_value_1  (cost=0.00..2.80 rows=18 width=8) (never executed)
                Output: orrd_value_1.id, orrd_value_1.orrd_id, orrd_value_1.rdv_id, orrd_value_1.date_in, orrd_value_1.user_in
          ->  Hash  (cost=0.20..0.20 rows=1 width=4) (never executed)
                Output: d0.i
                ->  CTE Scan on d0  (cost=0.00..0.20 rows=1 width=4) (never executed)
                      Output: d0.i
  CTE r2
    ->  Append  (cost=4118.93..5038.95 rows=4182 width=8) (actual time=1.517..2.779 rows=159 loops=1)
          Buffers: shared hit=75
          ->  Subquery Scan on r1  (cost=4118.93..5038.75 rows=4181 width=8) (actual time=1.516..2.667 rows=157 loops=1)
                Output: r1.r, (1000 - CASE WHEN (r1.c = 0) THEN (r1.o << 3) WHEN (r1.c > 3) THEN ((((r1.o)::numeric * (1.0 - (2.0 / ((r1.a - r1.o))::numeric))))::integer << 3) ELSE ((((r1.o)::numeric * (1.0 - (1.0 / ((r1.a - r1.o))::numeric))))::integer << (...)
                Buffers: shared hit=74
                ->  HashAggregate  (cost=4118.93..4620.65 rows=4181 width=12) (actual time=1.510..2.434 rows=157 loops=1)
                      Output: buyer_okved_rel_res.res_id, ((min(GREATEST(0, (o4.o - o4.c))) << 1) + 1), (((avg(GREATEST(0, (o4.o - o4.c))))::integer << 1) + 4), ((count(buyer_okved_rel_res.res_id))::integer - 1)
                      Buffers: shared hit=74
                      ->  Merge Join  (cost=2044.73..3985.73 rows=8880 width=12) (actual time=0.649..1.070 rows=157 loops=1)
                            Output: buyer_okved_rel_res.res_id, o4.o, o4.c
                            Merge Cond: (buyer_okved_rel_res.okved_id = o4.i)
                            Buffers: shared hit=74
                            ->  Index Only Scan using buyer_okved_rel_res_okved_res_id on msn.buyer_okved_rel_res  (cost=0.29..986.68 rows=8880 width=8) (actual time=0.044..0.136 rows=158 loops=1)
                                  Output: buyer_okved_rel_res.okved_id, buyer_okved_rel_res.res_id
                                  Heap Fetches: 0
                                  Buffers: shared hit=3
                            ->  Sort  (cost=2044.44..2044.94 rows=200 width=12) (actual time=0.598..0.646 rows=183 loops=1)
                                  Output: o4.o, o4.c, o4.i
                                  Sort Key: o4.i
                                  Sort Method: quicksort  Memory: 26kB
                                  Buffers: shared hit=71
                                  ->  Subquery Scan on o4  (cost=1995.30..2036.80 rows=200 width=12) (actual time=0.526..0.568 rows=27 loops=1)
                                        Output: o4.o, o4.c, o4.i
                                        Buffers: shared hit=71
                                        ->  HashAggregate  (cost=1995.30..2016.80 rows=200 width=8) (actual time=0.524..0.547 rows=27 loops=1)
                                              Output: o1_1.i, (min(o1_1.o) << 2), ((count(o1_1.o))::integer - 1)
                                              Buffers: shared hit=71
                                              ->  Append  (cost=1689.64..1888.44 rows=994 width=8) (actual time=0.061..0.460 rows=27 loops=1)
                                                    Buffers: shared hit=71
                                                    CTE o0
                                                      ->  Index Scan using org_rel_okved_id_pk on msn.org_rel_okved org_rel_okved_1  (cost=0.43..4.54 rows=1 width=8) (actual time=0.027..0.029 rows=1 loops=1)
                                                            Output: org_rel_okved_1.okved_id, org_rel_okved_1.ord
                                                            Index Cond: (org_rel_okved_1.id = ANY ('{15054388}'::integer[]))
                                                            Filter: (org_rel_okved_1.org_id = 1)
                                                            Buffers: shared hit=4
                                                    CTE o1
                                                      ->  Recursive Union  (cost=0.28..413.89 rows=101 width=12) (actual time=0.056..0.143 rows=3 loops=1)
                                                            Buffers: shared hit=13
                                                            ->  Nested Loop  (cost=0.28..4.69 rows=1 width=12) (actual time=0.054..0.059 rows=1 loops=1)
                                                                  Output: okved.id, okved.parent_id, o0.o
                                                                  Buffers: shared hit=7
                                                                  ->  CTE Scan on o0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.032..0.034 rows=1 loops=1)
                                                                        Output: o0.i, o0.o
                                                                        Buffers: shared hit=4
                                                                  ->  Index Scan using okved_pk on msn.okved  (cost=0.28..4.39 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1)
                                                                        Output: okved.id, okved.parent_id, okved.strcode, okved.name, okved.date_in, okved.user_in
                                                                        Index Cond: (okved.id = o0.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..38.90 rows=10 width=12) (actual time=0.023..0.025 rows=1 loops=3)
                                                                  Output: okved_1.id, okved_1.parent_id, (o1.o + 1)
                                                                  Buffers: shared hit=6
                                                                  ->  WorkTable Scan on o1  (cost=0.00..2.00 rows=10 width=8) (actual time=0.012..0.012 rows=1 loops=3)
                                                                        Output: o1.i, o1.p, o1.o
                                                                  ->  Index Scan using okved_pk on msn.okved okved_1  (cost=0.28..3.59 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=3)
                                                                        Output: okved_1.id, okved_1.parent_id, okved_1.strcode, okved_1.name, okved_1.date_in, okved_1.user_in
                                                                        Index Cond: (okved_1.id = o1.p)
                                                                        Buffers: shared hit=6
                                                    CTE o2
                                                      ->  Recursive Union  (cost=0.28..1271.21 rows=893 width=12) (actual time=0.018..0.255 rows=24 loops=1)
                                                            Buffers: shared hit=58
                                                            ->  Nested Loop  (cost=0.28..7.02 rows=3 width=12) (actual time=0.015..0.023 rows=3 loops=1)
                                                                  Output: okved_2.id, okved_2.parent_id, o0_1.o
                                                                  Buffers: shared hit=3
                                                                  ->  CTE Scan on o0 o0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
                                                                        Output: o0_1.i, o0_1.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_2  (cost=0.28..6.52 rows=3 width=8) (actual time=0.010..0.013 rows=3 loops=1)
                                                                        Output: okved_2.id, okved_2.parent_id, okved_2.strcode, okved_2.name, okved_2.date_in, okved_2.user_in
                                                                        Index Cond: (okved_2.parent_id = o0_1.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..108.56 rows=89 width=12) (actual time=0.039..0.067 rows=7 loops=3)
                                                                  Output: okved_3.id, okved_3.parent_id, o2.o
                                                                  Buffers: shared hit=55
                                                                  ->  WorkTable Scan on o2  (cost=0.00..6.00 rows=30 width=8) (actual time=0.001..0.004 rows=8 loops=3)
                                                                        Output: o2.i, o2.p, o2.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_3  (cost=0.28..3.12 rows=3 width=8) (actual time=0.005..0.006 rows=1 loops=24)
                                                                        Output: okved_3.id, okved_3.parent_id, okved_3.strcode, okved_3.name, okved_3.date_in, okved_3.user_in
                                                                        Index Cond: (okved_3.parent_id = o2.i)
                                                                        Buffers: shared hit=55
                                                    ->  CTE Scan on o1 o1_1  (cost=0.00..20.20 rows=101 width=8) (actual time=0.060..0.153 rows=3 loops=1)
                                                          Output: o1_1.i, o1_1.o
                                                          Buffers: shared hit=13
                                                    ->  CTE Scan on o2 o2_1  (cost=0.00..178.60 rows=893 width=8) (actual time=0.021..0.295 rows=24 loops=1)
                                                          Output: o2_1.i, o2_1.o
                                                          Buffers: shared hit=58
          ->  CTE Scan on r0 r0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.024..0.038 rows=2 loops=1)
                Output: r0_1.r, 1080
                Buffers: shared hit=1
  ->  WindowAgg  (cost=6188334.49..6188429.49 rows=200 width=24) (actual time=33083.577..33085.652 rows=1000 loops=1)
        Output: *SELECT* 1".org_id, ((((((max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (...)"
        Buffers: shared hit=2466480
        ->  Sort  (cost=6188334.49..6188334.99 rows=200 width=24) (actual time=33083.545..33083.927 rows=1000 loops=1)
              Output: ((((((max(((*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubPlan 11))) (...)"
              Sort Key: ((((((max(((*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubPlan 11) (...)"
              Sort Method: quicksort  Memory: 50035kB
              Buffers: shared hit=2466480
              ->  Nested Loop  (cost=6187756.28..6188326.84 rows=200 width=24) (actual time=5845.793..31747.921 rows=703321 loops=1)
                    Output: (((((max(((*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubPlan  (...)"
                    Buffers: shared hit=2466480
                    ->  HashAggregate  (cost=6187755.84..6187777.84 rows=200 width=12) (actual time=5845.688..7650.757 rows=703323 loops=1)
                          Output: *SELECT* 1".org_id, (max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)"
                          Buffers: shared hit=7014
                          ->  Append  (cost=9518.66..4653741.59 rows=14269900 width=12) (actual time=6.842..3590.169 rows=1100072 loops=1)
                                Buffers: shared hit=7014
                                ->  Subquery Scan on *SELECT* 1"  (cost=9518.66..4651488.52 rows=14269691 width=12) (actual time=6.841..3148.235 rows=1100071 loops=1)"
                                      Output: *SELECT* 1".org_id, "*SELECT* 1".o"
                                      Buffers: shared hit=7012
                                      ->  Nested Loop  (cost=9518.66..3224519.42 rows=14269691 width=12) (actual time=6.837..2344.281 rows=1100071 loops=1)
                                            Output: org_rel_okved.org_id, (((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))
                                            Buffers: shared hit=7012
                                            ->  HashAggregate  (cost=9518.10..9695.26 rows=1648 width=8) (actual time=6.807..7.664 rows=178 loops=1)
                                                  Output: res_rel_okved.okved_id, (max(r2.o) + ((sum(r2.o) / 300))::integer)
                                                  Buffers: shared hit=559
                                                  ->  Nested Loop  (cost=0.29..9301.17 rows=28923 width=8) (actual time=1.543..5.853 rows=716 loops=1)
                                                        Output: res_rel_okved.okved_id, r2.o
                                                        Buffers: shared hit=559
                                                        ->  CTE Scan on r2  (cost=0.00..836.40 rows=4182 width=8) (actual time=1.522..3.088 rows=159 loops=1)
                                                              Output: r2.r, r2.o
                                                              Buffers: shared hit=75
                                                        ->  Index Scan using res_rel_okved_res on msn.res_rel_okved  (cost=0.29..1.32 rows=7 width=8) (actual time=0.008..0.012 rows=5 loops=159)
                                                              Output: res_rel_okved.id, res_rel_okved.res_id, res_rel_okved.okved_id
                                                              Index Cond: (res_rel_okved.res_id = r2.r)
                                                              Buffers: shared hit=484
                                            ->  Index Only Scan using org_rel_okved_org_okved on msn.org_rel_okved  (cost=0.56..1019.80 rows=8659 width=12) (actual time=0.034..6.863 rows=6180 loops=178)
                                                  Output: org_rel_okved.okved_id, org_rel_okved.org_id, org_rel_okved.ord
                                                  Index Cond: (org_rel_okved.okved_id = res_rel_okved.okved_id)
                                                  Filter: (org_rel_okved.org_id <> 1)
                                                  Rows Removed by Filter: 0
                                                  Heap Fetches: 0
                                                  Buffers: shared hit=6453
                                ->  Subquery Scan on *SELECT* 2"  (cost=2209.18..2253.07 rows=209 width=16) (actual time=0.271..0.272 rows=1 loops=1)"
                                      Output: *SELECT* 2".org_id, "*SELECT* 2".o"
                                      Buffers: shared hit=2
                                      ->  HashAggregate  (cost=2209.18..2232.17 rows=209 width=16) (actual time=0.269..0.270 rows=1 loops=1)
                                            Output: l.org_id, (((r2_1.o + 50))::double precision + (8::double precision * COALESCE(sum((SubPlan 13)), 0::double precision))), r2_1.r, r2_1.o
                                            Buffers: shared hit=2
                                            ->  Hash Join  (cost=4.86..898.75 rows=209 width=16) (actual time=0.206..0.209 rows=1 loops=1)
                                                  Output: l.org_id, l.id, r2_1.r, r2_1.o
                                                  Hash Cond: (r2_1.r = l.res_id)
                                                  Buffers: shared hit=1
                                                  ->  CTE Scan on r2 r2_1  (cost=0.00..836.40 rows=4182 width=8) (actual time=0.003..0.069 rows=159 loops=1)
                                                        Output: r2_1.r, r2_1.o
                                                  ->  Hash  (cost=3.84..3.84 rows=10 width=12) (actual time=0.043..0.043 rows=9 loops=1)
                                                        Output: l.org_id, l.id, l.res_id
                                                        Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                                        Buffers: shared hit=1
                                                        ->  Seq Scan on msn.org_rel_res l  (cost=0.00..3.84 rows=10 width=12) (actual time=0.019..0.032 rows=9 loops=1)
                                                              Output: l.org_id, l.id, l.res_id
                                                              Filter: ((l.org_id <> 1) AND (l.type = 1))
                                                              Rows Removed by Filter: 18
                                                              Buffers: shared hit=1
                                            SubPlan 13
                                              ->  Aggregate  (cost=6.15..6.26 rows=1 width=74) (actual time=0.035..0.035 rows=1 loops=1)
                                                    Output: (((LEAST(count(e.id), 3::bigint) + COALESCE(count(d0_1.d), 0::bigint)))::double precision + sum(CASE WHEN (d0_1.t IS NULL) THEN 0::double precision WHEN (d0_1.t = 1) THEN CASE WHEN (d0_1.v1 = d0_1 (...)
                                                    Buffers: shared hit=1
                                                    ->  Nested Loop Left Join  (cost=0.00..2.84 rows=1 width=74) (actual time=0.025..0.025 rows=0 loops=1)
                                                          Output: e.id, e.value1, e.value2, d0_1.d, d0_1.t, d0_1.v1, d0_1.v2, d0_1.vc, d0_1.i
                                                          Join Filter: (d0_1.d = e.rd_id)
                                                          Buffers: shared hit=1
                                                          ->  Seq Scan on msn.orr_rel_res_descr e  (cost=0.00..2.54 rows=1 width=18) (actual time=0.014..0.014 rows=0 loops=1)
                                                                Output: e.id, e.rd_id, e.orr_id, e.date_in, e.user_in, e.value1, e.value2, e.values""
                                                                Filter: (e.orr_id = l.id)
                                                                Rows Removed by Filter: 15
                                                                Buffers: shared hit=1
                                                          ->  CTE Scan on d0 d0_1  (cost=0.00..0.20 rows=1 width=60) (never executed)
                                                                Output: d0_1.s, d0_1.d, d0_1.t, d0_1.i, d0_1.v1, d0_1.v2, d0_1.vs, d0_1.vc
                                                                Filter: (d0_1.s = r2_1.r)
                                                    SubPlan 12
                                                      ->  Aggregate  (cost=3.15..3.26 rows=1 width=0) (never executed)
                                                            Output: COALESCE((1 + (((sum(1) * 3) / d0_1.vc))::integer), (-5))
                                                            ->  Nested Loop  (cost=0.00..3.15 rows=1 width=0) (never executed)
                                                                  Join Filter: (orrd_value_2.rdv_id = s0.vp)
                                                                  ->  Seq Scan on msn.orrd_value orrd_value_2  (cost=0.00..2.85 rows=1 width=4) (never executed)
                                                                        Output: orrd_value_2.id, orrd_value_2.orrd_id, orrd_value_2.rdv_id, orrd_value_2.date_in, orrd_value_2.user_in
                                                                        Filter: (orrd_value_2.orrd_id = e.id)
                                                                  ->  CTE Scan on s0  (cost=0.00..0.20 rows=1 width=4) (never executed)
                                                                        Output: s0.h, s0.vp
                                                                        Filter: (s0.h = d0_1.i)
                    ->  Index Only Scan using org_address_org_search on msn.org_address  (cost=0.43..2.54 rows=1 width=16) (actual time=0.013..0.014 rows=1 loops=703323)
                          Output: org_address.org_id, org_address.is_sale, org_address.latitude, org_address.longitude, org_address.weight
                          Index Cond: ((org_address.org_id = *SELECT* 1".org_id) AND (org_address.is_sale = true))"
                          Filter: org_address.is_sale
                          Heap Fetches: 0
                          Buffers: shared hit=2459462
                    SubPlan 11
                      ->  Aggregate  (cost=0.24..0.34 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=703321)
                            Output: COALESCE(min(((abs((org_address.latitude - a0.t)) / 15000) + (LEAST((abs(((org_address.longitude - a0.g) + 180000000)) % 180000000), (abs(((a0.g - org_address.longitude) + 180000000)) % 180000000)) / 16500))), 5000)
                            Buffers: shared hit=4
                            ->  CTE Scan on a0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=703321)
                                  Output: a0.t, a0.g
                                  Buffers: shared hit=4
Total runtime: 33089.856 ms

теперь к этому добавляю "set enable_sort to off;", второй запуск получился на 32 сек (первый - 33 сек):

Код: plsql

Limit  (cost=10006193535.21..10006193630.21 rows=200 width=24) (actual time=32227.349..32229.765 rows=1000 loops=1)
  Output: *SELECT* 1".org_id, ((((((max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubP (...)"
  Buffers: shared hit=2466525
  CTE a0
    ->  Index Scan using org_address_org_search on msn.org_address org_address_1  (cost=0.43..4.54 rows=1 width=8) (actual time=0.020..0.022 rows=1 loops=1)
          Output: org_address_1.latitude, org_address_1.longitude
          Index Cond: (org_address_1.org_id = 1)
          Filter: (org_address_1.id = ANY ('{997192}'::integer[]))
          Buffers: shared hit=4
  CTE r0
    ->  Seq Scan on msn.org_rel_res  (cost=0.00..3.90 rows=1 width=8) (actual time=0.019..0.031 rows=2 loops=1)
          Output: org_rel_res.id, org_rel_res.res_id
          Filter: ((org_rel_res.id = ANY ('{7,82}'::integer[])) AND (org_rel_res.type = 2) AND (org_rel_res.org_id = 1))
          Rows Removed by Filter: 25
          Buffers: shared hit=1
  CTE d0
    ->  Hash Join  (cost=0.30..10.30 rows=1 width=240) (never executed)
          Output: r0.r, d.rd_id, (SubPlan 3), d.id, LEAST(d.value1, d.value2), GREATEST(d.value1, d.value2), d.values", (SubPlan 4)"
          Hash Cond: (d.orr_id = r0.i)
          ->  Seq Scan on msn.orr_rel_res_descr d  (cost=0.00..2.50 rows=15 width=240) (never executed)
                Output: d.id, d.rd_id, d.orr_id, d.date_in, d.user_in, d.value1, d.value2, d.values""
          ->  Hash  (cost=0.20..0.20 rows=1 width=8) (never executed)
                Output: r0.r, r0.i
                ->  CTE Scan on r0  (cost=0.00..0.20 rows=1 width=8) (never executed)
                      Output: r0.r, r0.i
          SubPlan 3
            ->  Index Scan using res_description_id_pk on msn.res_description  (cost=0.28..4.39 rows=1 width=4) (never executed)
                  Output: res_description.type
                  Index Cond: (res_description.id = d.rd_id)
          SubPlan 4
            ->  Aggregate  (cost=2.85..2.95 rows=1 width=4) (never executed)
                  Output: count(orrd_value.id)
                  ->  Seq Scan on msn.orrd_value  (cost=0.00..2.85 rows=1 width=4) (never executed)
                        Output: orrd_value.id, orrd_value.orrd_id, orrd_value.rdv_id, orrd_value.date_in, orrd_value.user_in
                        Filter: (orrd_value.orrd_id = d.id)
  CTE s0
    ->  Hash Join  (cost=0.30..3.27 rows=1 width=8) (never executed)
          Output: d0.i, orrd_value_1.rdv_id
          Hash Cond: (orrd_value_1.orrd_id = d0.i)
          ->  Seq Scan on msn.orrd_value orrd_value_1  (cost=0.00..2.80 rows=18 width=8) (never executed)
                Output: orrd_value_1.id, orrd_value_1.orrd_id, orrd_value_1.rdv_id, orrd_value_1.date_in, orrd_value_1.user_in
          ->  Hash  (cost=0.20..0.20 rows=1 width=4) (never executed)
                Output: d0.i
                ->  CTE Scan on d0  (cost=0.00..0.20 rows=1 width=4) (never executed)
                      Output: d0.i
  CTE r2
    ->  Append  (cost=4258.70..5178.72 rows=4182 width=8) (actual time=7.654..8.852 rows=159 loops=1)
          Buffers: shared hit=120
          ->  Subquery Scan on r1  (cost=4258.70..5178.52 rows=4181 width=8) (actual time=7.653..8.728 rows=157 loops=1)
                Output: r1.r, (1000 - CASE WHEN (r1.c = 0) THEN (r1.o << 3) WHEN (r1.c > 3) THEN ((((r1.o)::numeric * (1.0 - (2.0 / ((r1.a - r1.o))::numeric))))::integer << 3) ELSE ((((r1.o)::numeric * (1.0 - (1.0 / ((r1.a - r1.o))::numeric))))::integer << (...)
                Buffers: shared hit=119
                ->  HashAggregate  (cost=4258.70..4760.42 rows=4181 width=12) (actual time=7.647..8.521 rows=157 loops=1)
                      Output: buyer_okved_rel_res.res_id, ((min(GREATEST(0, (o4.o - o4.c))) << 1) + 1), (((avg(GREATEST(0, (o4.o - o4.c))))::integer << 1) + 4), ((count(buyer_okved_rel_res.res_id))::integer - 1)
                      Buffers: shared hit=119
                      ->  Hash Join  (cost=2057.30..4125.50 rows=8880 width=12) (actual time=0.625..7.200 rows=157 loops=1)
                            Output: buyer_okved_rel_res.res_id, o4.o, o4.c
                            Hash Cond: (buyer_okved_rel_res.okved_id = o4.i)
                            Buffers: shared hit=119
                            ->  Seq Scan on msn.buyer_okved_rel_res  (cost=0.00..936.00 rows=8880 width=8) (actual time=0.015..2.567 rows=8880 loops=1)
                                  Output: buyer_okved_rel_res.id, buyer_okved_rel_res.okved_id, buyer_okved_rel_res.res_id
                                  Buffers: shared hit=48
                            ->  Hash  (cost=2036.80..2036.80 rows=200 width=12) (actual time=0.585..0.585 rows=27 loops=1)
                                  Output: o4.o, o4.c, o4.i
                                  Buckets: 1024  Batches: 1  Memory Usage: 2kB
                                  Buffers: shared hit=71
                                  ->  Subquery Scan on o4  (cost=1995.30..2036.80 rows=200 width=12) (actual time=0.521..0.557 rows=27 loops=1)
                                        Output: o4.o, o4.c, o4.i
                                        Buffers: shared hit=71
                                        ->  HashAggregate  (cost=1995.30..2016.80 rows=200 width=8) (actual time=0.520..0.547 rows=27 loops=1)
                                              Output: o1_1.i, (min(o1_1.o) << 2), ((count(o1_1.o))::integer - 1)
                                              Buffers: shared hit=71
                                              ->  Append  (cost=1689.64..1888.44 rows=994 width=8) (actual time=0.064..0.454 rows=27 loops=1)
                                                    Buffers: shared hit=71
                                                    CTE o0
                                                      ->  Index Scan using org_rel_okved_id_pk on msn.org_rel_okved org_rel_okved_1  (cost=0.43..4.54 rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=1)
                                                            Output: org_rel_okved_1.okved_id, org_rel_okved_1.ord
                                                            Index Cond: (org_rel_okved_1.id = ANY ('{15054388}'::integer[]))
                                                            Filter: (org_rel_okved_1.org_id = 1)
                                                            Buffers: shared hit=4
                                                    CTE o1
                                                      ->  Recursive Union  (cost=0.28..413.89 rows=101 width=12) (actual time=0.059..0.111 rows=3 loops=1)
                                                            Buffers: shared hit=13
                                                            ->  Nested Loop  (cost=0.28..4.69 rows=1 width=12) (actual time=0.057..0.062 rows=1 loops=1)
                                                                  Output: okved.id, okved.parent_id, o0.o
                                                                  Buffers: shared hit=7
                                                                  ->  CTE Scan on o0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.036..0.038 rows=1 loops=1)
                                                                        Output: o0.i, o0.o
                                                                        Buffers: shared hit=4
                                                                  ->  Index Scan using okved_pk on msn.okved  (cost=0.28..4.39 rows=1 width=8) (actual time=0.012..0.014 rows=1 loops=1)
                                                                        Output: okved.id, okved.parent_id, okved.strcode, okved.name, okved.date_in, okved.user_in
                                                                        Index Cond: (okved.id = o0.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..38.90 rows=10 width=12) (actual time=0.011..0.013 rows=1 loops=3)
                                                                  Output: okved_1.id, okved_1.parent_id, (o1.o + 1)
                                                                  Buffers: shared hit=6
                                                                  ->  WorkTable Scan on o1  (cost=0.00..2.00 rows=10 width=8) (actual time=0.001..0.001 rows=1 loops=3)
                                                                        Output: o1.i, o1.p, o1.o
                                                                  ->  Index Scan using okved_pk on msn.okved okved_1  (cost=0.28..3.59 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=3)
                                                                        Output: okved_1.id, okved_1.parent_id, okved_1.strcode, okved_1.name, okved_1.date_in, okved_1.user_in
                                                                        Index Cond: (okved_1.id = o1.p)
                                                                        Buffers: shared hit=6
                                                    CTE o2
                                                      ->  Recursive Union  (cost=0.28..1271.21 rows=893 width=12) (actual time=0.021..0.258 rows=24 loops=1)
                                                            Buffers: shared hit=58
                                                            ->  Nested Loop  (cost=0.28..7.02 rows=3 width=12) (actual time=0.018..0.024 rows=3 loops=1)
                                                                  Output: okved_2.id, okved_2.parent_id, o0_1.o
                                                                  Buffers: shared hit=3
                                                                  ->  CTE Scan on o0 o0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
                                                                        Output: o0_1.i, o0_1.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_2  (cost=0.28..6.52 rows=3 width=8) (actual time=0.011..0.014 rows=3 loops=1)
                                                                        Output: okved_2.id, okved_2.parent_id, okved_2.strcode, okved_2.name, okved_2.date_in, okved_2.user_in
                                                                        Index Cond: (okved_2.parent_id = o0_1.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..108.56 rows=89 width=12) (actual time=0.038..0.067 rows=7 loops=3)
                                                                  Output: okved_3.id, okved_3.parent_id, o2.o
                                                                  Buffers: shared hit=55
                                                                  ->  WorkTable Scan on o2  (cost=0.00..6.00 rows=30 width=8) (actual time=0.001..0.004 rows=8 loops=3)
                                                                        Output: o2.i, o2.p, o2.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_3  (cost=0.28..3.12 rows=3 width=8) (actual time=0.004..0.006 rows=1 loops=24)
                                                                        Output: okved_3.id, okved_3.parent_id, okved_3.strcode, okved_3.name, okved_3.date_in, okved_3.user_in
                                                                        Index Cond: (okved_3.parent_id = o2.i)
                                                                        Buffers: shared hit=55
                                                    ->  CTE Scan on o1 o1_1  (cost=0.00..20.20 rows=101 width=8) (actual time=0.063..0.120 rows=3 loops=1)
                                                          Output: o1_1.i, o1_1.o
                                                          Buffers: shared hit=13
                                                    ->  CTE Scan on o2 o2_1  (cost=0.00..178.60 rows=893 width=8) (actual time=0.055..0.323 rows=24 loops=1)
                                                          Output: o2_1.i, o2_1.o
                                                          Buffers: shared hit=58
          ->  CTE Scan on r0 r0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.024..0.038 rows=2 loops=1)
                Output: r0_1.r, 1080
                Buffers: shared hit=1
  ->  WindowAgg  (cost=10006188334.49..10006188429.49 rows=200 width=24) (actual time=32227.346..32229.387 rows=1000 loops=1)
        Output: *SELECT* 1".org_id, ((((((max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (...)"
        Buffers: shared hit=2466525
        ->  Sort  (cost=10006188334.49..10006188334.99 rows=200 width=24) (actual time=32227.313..32227.680 rows=1000 loops=1)
              Output: ((((((max(((*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubPlan 11))) (...)"
              Sort Key: ((((((max(((*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubPlan 11) (...)"
              Sort Method: quicksort  Memory: 50035kB
              Buffers: shared hit=2466525
              ->  Nested Loop  (cost=6187756.28..6188326.84 rows=200 width=24) (actual time=5830.601..31099.486 rows=703321 loops=1)
                    Output: (((((max(((*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)) * (org_address.weight)::double precision) * ((0.5 + (500.0 / ((500 + (SubPlan  (...)"
                    Buffers: shared hit=2466525
                    ->  HashAggregate  (cost=6187755.84..6187777.84 rows=200 width=12) (actual time=5830.495..7542.066 rows=703323 loops=1)
                          Output: *SELECT* 1".org_id, (max((("*SELECT* 1".o)::double precision)) + (((sum((("*SELECT* 1".o)::double precision)) / 300::double precision))::integer)::double precision)"
                          Buffers: shared hit=7059
                          ->  Append  (cost=9518.66..4653741.59 rows=14269900 width=12) (actual time=12.822..3609.170 rows=1100072 loops=1)
                                Buffers: shared hit=7059
                                ->  Subquery Scan on *SELECT* 1"  (cost=9518.66..4651488.52 rows=14269691 width=12) (actual time=12.821..3173.978 rows=1100071 loops=1)"
                                      Output: *SELECT* 1".org_id, "*SELECT* 1".o"
                                      Buffers: shared hit=7057
                                      ->  Nested Loop  (cost=9518.66..3224519.42 rows=14269691 width=12) (actual time=12.818..2382.230 rows=1100071 loops=1)
                                            Output: org_rel_okved.org_id, (((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))
                                            Buffers: shared hit=7057
                                            ->  HashAggregate  (cost=9518.10..9695.26 rows=1648 width=8) (actual time=12.786..13.652 rows=178 loops=1)
                                                  Output: res_rel_okved.okved_id, (max(r2.o) + ((sum(r2.o) / 300))::integer)
                                                  Buffers: shared hit=604
                                                  ->  Nested Loop  (cost=0.29..9301.17 rows=28923 width=8) (actual time=7.689..11.847 rows=716 loops=1)
                                                        Output: res_rel_okved.okved_id, r2.o
                                                        Buffers: shared hit=604
                                                        ->  CTE Scan on r2  (cost=0.00..836.40 rows=4182 width=8) (actual time=7.660..9.146 rows=159 loops=1)
                                                              Output: r2.r, r2.o
                                                              Buffers: shared hit=120
                                                        ->  Index Scan using res_rel_okved_res on msn.res_rel_okved  (cost=0.29..1.32 rows=7 width=8) (actual time=0.008..0.012 rows=5 loops=159)
                                                              Output: res_rel_okved.id, res_rel_okved.res_id, res_rel_okved.okved_id
                                                              Index Cond: (res_rel_okved.res_id = r2.r)
                                                              Buffers: shared hit=484
                                            ->  Index Only Scan using org_rel_okved_org_okved on msn.org_rel_okved  (cost=0.56..1019.80 rows=8659 width=12) (actual time=0.034..6.778 rows=6180 loops=178)
                                                  Output: org_rel_okved.okved_id, org_rel_okved.org_id, org_rel_okved.ord
                                                  Index Cond: (org_rel_okved.okved_id = res_rel_okved.okved_id)
                                                  Filter: (org_rel_okved.org_id <> 1)
                                                  Rows Removed by Filter: 0
                                                  Heap Fetches: 0
                                                  Buffers: shared hit=6453
                                ->  Subquery Scan on *SELECT* 2"  (cost=2209.18..2253.07 rows=209 width=16) (actual time=0.267..0.269 rows=1 loops=1)"
                                      Output: *SELECT* 2".org_id, "*SELECT* 2".o"
                                      Buffers: shared hit=2
                                      ->  HashAggregate  (cost=2209.18..2232.17 rows=209 width=16) (actual time=0.265..0.265 rows=1 loops=1)
                                            Output: l.org_id, (((r2_1.o + 50))::double precision + (8::double precision * COALESCE(sum((SubPlan 13)), 0::double precision))), r2_1.r, r2_1.o
                                            Buffers: shared hit=2
                                            ->  Hash Join  (cost=4.86..898.75 rows=209 width=16) (actual time=0.204..0.207 rows=1 loops=1)
                                                  Output: l.org_id, l.id, r2_1.r, r2_1.o
                                                  Hash Cond: (r2_1.r = l.res_id)
                                                  Buffers: shared hit=1
                                                  ->  CTE Scan on r2 r2_1  (cost=0.00..836.40 rows=4182 width=8) (actual time=0.003..0.066 rows=159 loops=1)
                                                        Output: r2_1.r, r2_1.o
                                                  ->  Hash  (cost=3.84..3.84 rows=10 width=12) (actual time=0.043..0.043 rows=9 loops=1)
                                                        Output: l.org_id, l.id, l.res_id
                                                        Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                                        Buffers: shared hit=1
                                                        ->  Seq Scan on msn.org_rel_res l  (cost=0.00..3.84 rows=10 width=12) (actual time=0.019..0.031 rows=9 loops=1)
                                                              Output: l.org_id, l.id, l.res_id
                                                              Filter: ((l.org_id <> 1) AND (l.type = 1))
                                                              Rows Removed by Filter: 18
                                                              Buffers: shared hit=1
                                            SubPlan 13
                                              ->  Aggregate  (cost=6.15..6.26 rows=1 width=74) (actual time=0.033..0.033 rows=1 loops=1)
                                                    Output: (((LEAST(count(e.id), 3::bigint) + COALESCE(count(d0_1.d), 0::bigint)))::double precision + sum(CASE WHEN (d0_1.t IS NULL) THEN 0::double precision WHEN (d0_1.t = 1) THEN CASE WHEN (d0_1.v1 = d0_1 (...)
                                                    Buffers: shared hit=1
                                                    ->  Nested Loop Left Join  (cost=0.00..2.84 rows=1 width=74) (actual time=0.025..0.025 rows=0 loops=1)
                                                          Output: e.id, e.value1, e.value2, d0_1.d, d0_1.t, d0_1.v1, d0_1.v2, d0_1.vc, d0_1.i
                                                          Join Filter: (d0_1.d = e.rd_id)
                                                          Buffers: shared hit=1
                                                          ->  Seq Scan on msn.orr_rel_res_descr e  (cost=0.00..2.54 rows=1 width=18) (actual time=0.013..0.013 rows=0 loops=1)
                                                                Output: e.id, e.rd_id, e.orr_id, e.date_in, e.user_in, e.value1, e.value2, e.values""
                                                                Filter: (e.orr_id = l.id)
                                                                Rows Removed by Filter: 15
                                                                Buffers: shared hit=1
                                                          ->  CTE Scan on d0 d0_1  (cost=0.00..0.20 rows=1 width=60) (never executed)
                                                                Output: d0_1.s, d0_1.d, d0_1.t, d0_1.i, d0_1.v1, d0_1.v2, d0_1.vs, d0_1.vc
                                                                Filter: (d0_1.s = r2_1.r)
                                                    SubPlan 12
                                                      ->  Aggregate  (cost=3.15..3.26 rows=1 width=0) (never executed)
                                                            Output: COALESCE((1 + (((sum(1) * 3) / d0_1.vc))::integer), (-5))
                                                            ->  Nested Loop  (cost=0.00..3.15 rows=1 width=0) (never executed)
                                                                  Join Filter: (orrd_value_2.rdv_id = s0.vp)
                                                                  ->  Seq Scan on msn.orrd_value orrd_value_2  (cost=0.00..2.85 rows=1 width=4) (never executed)
                                                                        Output: orrd_value_2.id, orrd_value_2.orrd_id, orrd_value_2.rdv_id, orrd_value_2.date_in, orrd_value_2.user_in
                                                                        Filter: (orrd_value_2.orrd_id = e.id)
                                                                  ->  CTE Scan on s0  (cost=0.00..0.20 rows=1 width=4) (never executed)
                                                                        Output: s0.h, s0.vp
                                                                        Filter: (s0.h = d0_1.i)
                    ->  Index Only Scan using org_address_org_search on msn.org_address  (cost=0.43..2.54 rows=1 width=16) (actual time=0.012..0.014 rows=1 loops=703323)
                          Output: org_address.org_id, org_address.is_sale, org_address.latitude, org_address.longitude, org_address.weight
                          Index Cond: ((org_address.org_id = *SELECT* 1".org_id) AND (org_address.is_sale = true))"
                          Filter: org_address.is_sale
                          Heap Fetches: 0
                          Buffers: shared hit=2459462
                    SubPlan 11
                      ->  Aggregate  (cost=0.24..0.34 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=703321)
                            Output: COALESCE(min(((abs((org_address.latitude - a0.t)) / 15000) + (LEAST((abs(((org_address.longitude - a0.g) + 180000000)) % 180000000), (abs(((a0.g - org_address.longitude) + 180000000)) % 180000000)) / 16500))), 5000)
                            Buffers: shared hit=4
                            ->  CTE Scan on a0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=703321)
                                  Output: a0.t, a0.g
                                  Buffers: shared hit=4
Total runtime: 32233.228 ms

возвращаю статистику и enable_sort, второй запрос получился на 46 сек (первый - 130 сек)

Выводы:
К сожалению, максимально подробная статистика (10000), не помогла - строки оптимизатор так и считает не верно. Только первый запуск стал очень хорошим...
Более подробная статистика по полю org_id также эффекта не дала.
"set enable_sort to off" повлиял немного на план в одном месте:
Hash Join + Sec Scan + Hash -> Merge Join + Index Only Scan + Sort
Только выигрыша по времени от этого не произошло :(

...

Рейтинг:

0 / 0

11.01.2014, 21:22:32

| Ответить | Цитировать | Написать

На пути к идеальному плану

#38523847

R_Only

Гость

Для упрощения понимания сократим запрос

Код: plsql

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.

EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS on, TIMING on)
WITH
a0 as (select latitude t,longitude g from org_address where org_id=1/*inorg_id*/ and id=any(array[997192]/*inadr_ids*/)),
r2 as (select r, 1000-case when c=0 then o<<3 when c>3 then (o*(1.0-2.0/(a-o)))::integer<<3 else (o*(1.0-1.0/(a-o)))::integer<<3 end o
from (
	SELECT res_id r,(min(greatest(0,o-c))<<1)+1 o,(avg(greatest(0,o-c))::integer<<1)+4 a,count(res_id)::integer-1 c
	FROM buyer_okved_rel_res, (
		SELECT i,min(o)<<1+1 o,count(o)::integer-1 c
		FROM (
			WITH RECURSIVE
			o0(i,o) AS (SELECT okved_id,ord FROM org_rel_okved WHERE org_id=1/*inorg_id*/ and id=any(array[15054388]/*inokved_ids*/)),
			o1(i,p,o) AS (SELECT id,parent_id,o FROM okved,o0 WHERE id=i UNION ALL SELECT id,parent_id,o+1 FROM okved,o1 WHERE id=p),
			o2(i,p,o) AS (SELECT id,parent_id,o FROM okved,o0 WHERE parent_id=i UNION ALL SELECT id,parent_id,o FROM okved,o2 WHERE parent_id=i)
			SELECT i,o FROM o1 UNION ALL SELECT i,o FROM o2
		)o3 GROUP BY i
	)o4 WHERE okved_id=i GROUP BY res_id
)r1 /*limit 157*/)
select z,o,1+((row_number() over(order by o desc)-1)/100/*inorgonlist*/)::integer p from (
select z,((o*weight)*(0.5+500.0/(500
	+(select coalesce(min(abs(latitude-t)/15000+least(abs(longitude-g+180000000)%180000000
		,abs(g-longitude+180000000)%180000000)/16500),5000) from a0))))::integer o
from org_address,(
select org_id z,(max(o)+(sum(o)/300)::integer)o from (
	select org_id,o+300/(ord+3) o
	from org_rel_okved, (select okved_id b, max(o)+(sum(o)/300)::integer o from res_rel_okved, r2 where res_id=r group by okved_id /*limit 701*/)o5
	where okved_id=b
)w2 group by org_id having org_id<>1/*inorg_id*/
)w3 where org_id=z and is_sale
)w4 order by o desc limit 1000

соберем статистику

ALTER STATISTIC 10k

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

ALTER TABLE res_rel_okved ALTER COLUMN okved_id SET STATISTICS 10000;
ALTER TABLE buyer_okved_rel_res ALTER COLUMN okved_id SET STATISTICS 10000;
ALTER TABLE org_rel_okved ALTER COLUMN okved_id SET STATISTICS 10000;

ALTER TABLE org_address ALTER COLUMN org_id SET STATISTICS 10000;
ALTER TABLE org_rel_res ALTER COLUMN org_id SET STATISTICS 10000;
ALTER TABLE org_rel_okved ALTER COLUMN org_id SET STATISTICS 10000;

ALTER TABLE res_rel_okved ALTER COLUMN res_id SET STATISTICS 10000;
ALTER TABLE okved ALTER COLUMN parent_id SET STATISTICS 10000;
ALTER TABLE org_address ALTER COLUMN is_sale SET STATISTICS 10000;

, проанализируем. Получим план на 27963мс

EXPLAIN PLAN

Код: plsql

Limit  (cost=7418924.86..7419397.36 rows=1000 width=20) (actual time=27939.209..27941.643 rows=1000 loops=1)
  Output: org_rel_okved.org_id, (((((((max((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) + ((sum((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) / 300))::integer)) * org_address.weight)) (...)
  Buffers: shared hit=61926 read=13626, temp read=2950 written=2950
  CTE a0
    ->  Index Scan using org_address_org_search on msn.org_address org_address_1  (cost=0.43..4.54 rows=1 width=8) (actual time=0.026..0.028 rows=1 loops=1)
          Output: org_address_1.latitude, org_address_1.longitude
          Index Cond: (org_address_1.org_id = 1)
          Filter: (org_address_1.id = ANY ('{997192}'::integer[]))
          Buffers: shared hit=4
  CTE r2
    ->  Subquery Scan on r1  (cost=4118.93..5247.80 rows=4181 width=16) (actual time=1.466..2.584 rows=157 loops=1)
          Output: r1.r, (1000 - CASE WHEN (r1.c = 0) THEN (r1.o << 3) WHEN (r1.c > 3) THEN ((((r1.o)::numeric * (1.0 - (2.0 / ((r1.a - r1.o))::numeric))))::integer << 3) ELSE ((((r1.o)::numeric * (1.0 - (1.0 / ((r1.a - r1.o))::numeric))))::integer << 3) EN (...)
          Buffers: shared hit=74
          ->  HashAggregate  (cost=4118.93..4620.65 rows=4181 width=12) (actual time=1.460..2.345 rows=157 loops=1)
                Output: buyer_okved_rel_res.res_id, ((min(GREATEST(0, (o4.o - o4.c))) << 1) + 1), (((avg(GREATEST(0, (o4.o - o4.c))))::integer << 1) + 4), ((count(buyer_okved_rel_res.res_id))::integer - 1)
                Buffers: shared hit=74
                ->  Merge Join  (cost=2044.73..3985.73 rows=8880 width=12) (actual time=0.653..1.043 rows=157 loops=1)
                      Output: buyer_okved_rel_res.res_id, o4.o, o4.c
                      Merge Cond: (buyer_okved_rel_res.okved_id = o4.i)
                      Buffers: shared hit=74
                      ->  Index Only Scan using buyer_okved_rel_res_okved_res_id on msn.buyer_okved_rel_res  (cost=0.29..986.68 rows=8880 width=8) (actual time=0.043..0.142 rows=158 loops=1)
                            Output: buyer_okved_rel_res.okved_id, buyer_okved_rel_res.res_id
                            Heap Fetches: 0
                            Buffers: shared hit=3
                      ->  Sort  (cost=2044.44..2044.94 rows=200 width=12) (actual time=0.602..0.669 rows=183 loops=1)
                            Output: o4.o, o4.c, o4.i
                            Sort Key: o4.i
                            Sort Method: quicksort  Memory: 26kB
                            Buffers: shared hit=71
                            ->  Subquery Scan on o4  (cost=1995.30..2036.80 rows=200 width=12) (actual time=0.531..0.573 rows=27 loops=1)
                                  Output: o4.o, o4.c, o4.i
                                  Buffers: shared hit=71
                                  ->  HashAggregate  (cost=1995.30..2016.80 rows=200 width=8) (actual time=0.529..0.552 rows=27 loops=1)
                                        Output: o1_1.i, (min(o1_1.o) << 2), ((count(o1_1.o))::integer - 1)
                                        Buffers: shared hit=71
                                        ->  Append  (cost=1689.64..1888.44 rows=994 width=8) (actual time=0.060..0.465 rows=27 loops=1)
                                              Buffers: shared hit=71
                                              CTE o0
                                                ->  Index Scan using org_rel_okved_id_pk on msn.org_rel_okved org_rel_okved_1  (cost=0.43..4.54 rows=1 width=8) (actual time=0.027..0.028 rows=1 loops=1)
                                                      Output: org_rel_okved_1.okved_id, org_rel_okved_1.ord
                                                      Index Cond: (org_rel_okved_1.id = ANY ('{15054388}'::integer[]))
                                                      Filter: (org_rel_okved_1.org_id = 1)
                                                      Buffers: shared hit=4
                                              CTE o1
                                                ->  Recursive Union  (cost=0.28..413.89 rows=101 width=12) (actual time=0.055..0.106 rows=3 loops=1)
                                                      Buffers: shared hit=13
                                                      ->  Nested Loop  (cost=0.28..4.69 rows=1 width=12) (actual time=0.053..0.058 rows=1 loops=1)
                                                            Output: okved.id, okved.parent_id, o0.o
                                                            Buffers: shared hit=7
                                                            ->  CTE Scan on o0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.031..0.033 rows=1 loops=1)
                                                                  Output: o0.i, o0.o
                                                                  Buffers: shared hit=4
                                                            ->  Index Scan using okved_pk on msn.okved  (cost=0.28..4.39 rows=1 width=8) (actual time=0.013..0.016 rows=1 loops=1)
                                                                  Output: okved.id, okved.parent_id, okved.strcode, okved.name, okved.date_in, okved.user_in
                                                                  Index Cond: (okved.id = o0.i)
                                                                  Buffers: shared hit=3
                                                      ->  Nested Loop  (cost=0.28..38.90 rows=10 width=12) (actual time=0.012..0.013 rows=1 loops=3)
                                                            Output: okved_1.id, okved_1.parent_id, (o1.o + 1)
                                                            Buffers: shared hit=6
                                                            ->  WorkTable Scan on o1  (cost=0.00..2.00 rows=10 width=8) (actual time=0.001..0.001 rows=1 loops=3)
                                                                  Output: o1.i, o1.p, o1.o
                                                            ->  Index Scan using okved_pk on msn.okved okved_1  (cost=0.28..3.59 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=3)
                                                                  Output: okved_1.id, okved_1.parent_id, okved_1.strcode, okved_1.name, okved_1.date_in, okved_1.user_in
                                                                  Index Cond: (okved_1.id = o1.p)
                                                                  Buffers: shared hit=6
                                              CTE o2
                                                ->  Recursive Union  (cost=0.28..1271.21 rows=893 width=12) (actual time=0.018..0.295 rows=24 loops=1)
                                                      Buffers: shared hit=58
                                                      ->  Nested Loop  (cost=0.28..7.02 rows=3 width=12) (actual time=0.016..0.023 rows=3 loops=1)
                                                            Output: okved_2.id, okved_2.parent_id, o0_1.o
                                                            Buffers: shared hit=3
                                                            ->  CTE Scan on o0 o0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
                                                                  Output: o0_1.i, o0_1.o
                                                            ->  Index Scan using okved_parent on msn.okved okved_2  (cost=0.28..6.52 rows=3 width=8) (actual time=0.009..0.013 rows=3 loops=1)
                                                                  Output: okved_2.id, okved_2.parent_id, okved_2.strcode, okved_2.name, okved_2.date_in, okved_2.user_in
                                                                  Index Cond: (okved_2.parent_id = o0_1.i)
                                                                  Buffers: shared hit=3
                                                      ->  Nested Loop  (cost=0.28..108.56 rows=89 width=12) (actual time=0.039..0.081 rows=7 loops=3)
                                                            Output: okved_3.id, okved_3.parent_id, o2.o
                                                            Buffers: shared hit=55
                                                            ->  WorkTable Scan on o2  (cost=0.00..6.00 rows=30 width=8) (actual time=0.001..0.005 rows=8 loops=3)
                                                                  Output: o2.i, o2.p, o2.o
                                                            ->  Index Scan using okved_parent on msn.okved okved_3  (cost=0.28..3.12 rows=3 width=8) (actual time=0.005..0.006 rows=1 loops=24)
                                                                  Output: okved_3.id, okved_3.parent_id, okved_3.strcode, okved_3.name, okved_3.date_in, okved_3.user_in
                                                                  Index Cond: (okved_3.parent_id = o2.i)
                                                                  Buffers: shared hit=55
                                              ->  CTE Scan on o1 o1_1  (cost=0.00..20.20 rows=101 width=8) (actual time=0.059..0.115 rows=3 loops=1)
                                                    Output: o1_1.i, o1_1.o
                                                    Buffers: shared hit=13
                                              ->  CTE Scan on o2 o2_1  (cost=0.00..178.60 rows=893 width=8) (actual time=0.022..0.337 rows=24 loops=1)
                                                    Output: o2_1.i, o2_1.o
                                                    Buffers: shared hit=58
  ->  WindowAgg  (cost=7413672.52..9023331.66 rows=3406686 width=20) (actual time=27939.207..27941.267 rows=1000 loops=1)
        Output: org_rel_okved.org_id, (((((((max((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) + ((sum((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) / 300))::integer)) * org_address.we (...)
        Buffers: shared hit=61926 read=13626, temp read=2950 written=2950
        ->  Sort  (cost=7413672.52..7422189.24 rows=3406686 width=20) (actual time=27939.175..27939.523 rows=1000 loops=1)
              Output: (((((((max((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) + ((sum((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) / 300))::integer)) * org_address.weight))::numeric  (...)
              Sort Key: (((((((max((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) + ((sum((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) / 300))::integer)) * org_address.weight))::numeri (...)
              Sort Method: quicksort  Memory: 49949kB
              Buffers: shared hit=61926 read=13626, temp read=2950 written=2950
              ->  Merge Join  (cost=4972504.11..6994143.16 rows=3406686 width=20) (actual time=5035.734..26945.593 rows=701489 loops=1)
                    Output: ((((((max((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) + ((sum((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) / 300))::integer)) * org_address.weight))::num (...)
                    Merge Cond: (org_rel_okved.org_id = org_address.org_id)
                    Buffers: shared hit=61926 read=13626, temp read=2950 written=2950
                    ->  GroupAggregate  (cost=4972503.67..5694419.77 rows=3406686 width=12) (actual time=5035.581..9608.598 rows=701491 loops=1)
                          Output: org_rel_okved.org_id, (max((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) + ((sum((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) / 300))::integer)
                          Buffers: shared hit=5237 read=1740, temp read=2950 written=2950
                          ->  Sort  (cost=4972503.67..5008073.41 rows=14227894 width=12) (actual time=5035.548..6096.242 rows=1097003 loops=1)
                                Output: org_rel_okved.org_id, ((max(r2.o) + ((sum(r2.o) / 300))::integer)), org_rel_okved.ord
                                Sort Key: org_rel_okved.org_id
                                Sort Method: external merge  Disk: 23592kB
                                Buffers: shared hit=5237 read=1740, temp read=2950 written=2950
                                ->  Nested Loop  (cost=9516.64..3108389.52 rows=14227894 width=12) (actual time=6.535..1544.311 rows=1097003 loops=1)
                                      Output: org_rel_okved.org_id, ((max(r2.o) + ((sum(r2.o) / 300))::integer)), org_rel_okved.ord
                                      Buffers: shared hit=5237 read=1740
                                      ->  HashAggregate  (cost=9516.08..9693.24 rows=1648 width=8) (actual time=6.508..7.407 rows=176 loops=1)
                                            Output: res_rel_okved.okved_id, (max(r2.o) + ((sum(r2.o) / 300))::integer)
                                            Buffers: shared hit=552
                                            ->  Nested Loop  (cost=0.29..9299.21 rows=28916 width=8) (actual time=1.504..5.582 rows=701 loops=1)
                                                  Output: res_rel_okved.okved_id, r2.o
                                                  Buffers: shared hit=552
                                                  ->  CTE Scan on r2  (cost=0.00..836.20 rows=4181 width=8) (actual time=1.470..2.889 rows=157 loops=1)
                                                        Output: r2.r, r2.o
                                                        Buffers: shared hit=74
                                                  ->  Index Scan using res_rel_okved_res on msn.res_rel_okved  (cost=0.29..1.32 rows=7 width=8) (actual time=0.008..0.012 rows=4 loops=157)
                                                        Output: res_rel_okved.id, res_rel_okved.res_id, res_rel_okved.okved_id
                                                        Index Cond: (res_rel_okved.res_id = r2.r)
                                                        Buffers: shared hit=478
                                      ->  Index Only Scan using org_rel_okved_org_okved on msn.org_rel_okved  (cost=0.56..1016.88 rows=8633 width=12) (actual time=0.033..5.606 rows=6233 loops=176)
                                            Output: org_rel_okved.okved_id, org_rel_okved.org_id, org_rel_okved.ord
                                            Index Cond: (org_rel_okved.okved_id = res_rel_okved.okved_id)
                                            Filter: (org_rel_okved.org_id <> 1)
                                            Rows Removed by Filter: 0
                                            Heap Fetches: 0
                                            Buffers: shared hit=4685 read=1740
                    ->  Index Only Scan using org_address_org_search on msn.org_address  (cost=0.43..597267.81 rows=5040664 width=16) (actual time=0.039..4347.807 rows=5040093 loops=1)
                          Output: org_address.org_id, org_address.is_sale, org_address.latitude, org_address.longitude, org_address.weight
                          Index Cond: (org_address.is_sale = true)
                          Filter: org_address.is_sale
                          Heap Fetches: 0
                          Buffers: shared hit=56685 read=11886
                    SubPlan 6
                      ->  Aggregate  (cost=0.24..0.34 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=701489)
                            Output: COALESCE(min(((abs((org_address.latitude - a0.t)) / 15000) + (LEAST((abs(((org_address.longitude - a0.g) + 180000000)) % 180000000), (abs(((a0.g - org_address.longitude) + 180000000)) % 180000000)) / 16500))), 5000)
                            Buffers: shared hit=4
                            ->  CTE Scan on a0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=701489)
                                  Output: a0.t, a0.g
                                  Buffers: shared hit=4
Total runtime: 27963.644 ms

если теперь в запросе раскомментировать limit и выполнить "set enable_sort to off;", то получим 28053мс

EXPLAIN PLAN

Код: plsql

Limit  (cost=20004323412.41..20004323884.91 rows=1000 width=20) (actual time=28029.069..28031.551 rows=1000 loops=1)
  Output: org_rel_okved.org_id, (((((((max((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) + ((sum((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) / 300))::integer)) * org_address.weight)) (...)
  Buffers: shared hit=61143 read=14454, temp read=2950 written=2950
  CTE a0
    ->  Index Scan using org_address_org_search on msn.org_address org_address_1  (cost=0.43..4.54 rows=1 width=8) (actual time=0.030..0.032 rows=1 loops=1)
          Output: org_address_1.latitude, org_address_1.longitude
          Index Cond: (org_address_1.org_id = 1)
          Filter: (org_address_1.id = ANY ('{997192}'::integer[]))
          Buffers: shared hit=4
  CTE r2
    ->  Limit  (cost=4258.70..4301.09 rows=157 width=16) (actual time=28.885..30.135 rows=157 loops=1)
          Output: r1.r, ((1000 - CASE WHEN (r1.c = 0) THEN (r1.o << 3) WHEN (r1.c > 3) THEN ((((r1.o)::numeric * (1.0 - (2.0 / ((r1.a - r1.o))::numeric))))::integer << 3) ELSE ((((r1.o)::numeric * (1.0 - (1.0 / ((r1.a - r1.o))::numeric))))::integer << 3) E (...)
          Buffers: shared hit=69 read=50
          ->  Subquery Scan on r1  (cost=4258.70..5387.57 rows=4181 width=16) (actual time=28.884..30.040 rows=157 loops=1)
                Output: r1.r, (1000 - CASE WHEN (r1.c = 0) THEN (r1.o << 3) WHEN (r1.c > 3) THEN ((((r1.o)::numeric * (1.0 - (2.0 / ((r1.a - r1.o))::numeric))))::integer << 3) ELSE ((((r1.o)::numeric * (1.0 - (1.0 / ((r1.a - r1.o))::numeric))))::integer << (...)
                Buffers: shared hit=69 read=50
                ->  HashAggregate  (cost=4258.70..4760.42 rows=4181 width=12) (actual time=28.877..29.813 rows=157 loops=1)
                      Output: buyer_okved_rel_res.res_id, ((min(GREATEST(0, (o4.o - o4.c))) << 1) + 1), (((avg(GREATEST(0, (o4.o - o4.c))))::integer << 1) + 4), ((count(buyer_okved_rel_res.res_id))::integer - 1)
                      Buffers: shared hit=69 read=50
                      ->  Hash Join  (cost=2057.30..4125.50 rows=8880 width=12) (actual time=0.748..28.417 rows=157 loops=1)
                            Output: buyer_okved_rel_res.res_id, o4.o, o4.c
                            Hash Cond: (buyer_okved_rel_res.okved_id = o4.i)
                            Buffers: shared hit=69 read=50
                            ->  Seq Scan on msn.buyer_okved_rel_res  (cost=0.00..936.00 rows=8880 width=8) (actual time=0.078..23.693 rows=8880 loops=1)
                                  Output: buyer_okved_rel_res.id, buyer_okved_rel_res.okved_id, buyer_okved_rel_res.res_id
                                  Buffers: shared read=48
                            ->  Hash  (cost=2036.80..2036.80 rows=200 width=12) (actual time=0.608..0.608 rows=27 loops=1)
                                  Output: o4.o, o4.c, o4.i
                                  Buckets: 1024  Batches: 1  Memory Usage: 2kB
                                  Buffers: shared hit=69 read=2
                                  ->  Subquery Scan on o4  (cost=1995.30..2036.80 rows=200 width=12) (actual time=0.544..0.583 rows=27 loops=1)
                                        Output: o4.o, o4.c, o4.i
                                        Buffers: shared hit=69 read=2
                                        ->  HashAggregate  (cost=1995.30..2016.80 rows=200 width=8) (actual time=0.542..0.568 rows=27 loops=1)
                                              Output: o1_1.i, (min(o1_1.o) << 2), ((count(o1_1.o))::integer - 1)
                                              Buffers: shared hit=69 read=2
                                              ->  Append  (cost=1689.64..1888.44 rows=994 width=8) (actual time=0.092..0.472 rows=27 loops=1)
                                                    Buffers: shared hit=69 read=2
                                                    CTE o0
                                                      ->  Index Scan using org_rel_okved_id_pk on msn.org_rel_okved org_rel_okved_1  (cost=0.43..4.54 rows=1 width=8) (actual time=0.057..0.059 rows=1 loops=1)
                                                            Output: org_rel_okved_1.okved_id, org_rel_okved_1.ord
                                                            Index Cond: (org_rel_okved_1.id = ANY ('{15054388}'::integer[]))
                                                            Filter: (org_rel_okved_1.org_id = 1)
                                                            Buffers: shared hit=3 read=1
                                                    CTE o1
                                                      ->  Recursive Union  (cost=0.28..413.89 rows=101 width=12) (actual time=0.087..0.139 rows=3 loops=1)
                                                            Buffers: shared hit=12 read=1
                                                            ->  Nested Loop  (cost=0.28..4.69 rows=1 width=12) (actual time=0.084..0.088 rows=1 loops=1)
                                                                  Output: okved.id, okved.parent_id, o0.o
                                                                  Buffers: shared hit=6 read=1
                                                                  ->  CTE Scan on o0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.062..0.064 rows=1 loops=1)
                                                                        Output: o0.i, o0.o
                                                                        Buffers: shared hit=3 read=1
                                                                  ->  Index Scan using okved_pk on msn.okved  (cost=0.28..4.39 rows=1 width=8) (actual time=0.014..0.016 rows=1 loops=1)
                                                                        Output: okved.id, okved.parent_id, okved.strcode, okved.name, okved.date_in, okved.user_in
                                                                        Index Cond: (okved.id = o0.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..38.90 rows=10 width=12) (actual time=0.011..0.012 rows=1 loops=3)
                                                                  Output: okved_1.id, okved_1.parent_id, (o1.o + 1)
                                                                  Buffers: shared hit=6
                                                                  ->  WorkTable Scan on o1  (cost=0.00..2.00 rows=10 width=8) (actual time=0.001..0.001 rows=1 loops=3)
                                                                        Output: o1.i, o1.p, o1.o
                                                                  ->  Index Scan using okved_pk on msn.okved okved_1  (cost=0.28..3.59 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=3)
                                                                        Output: okved_1.id, okved_1.parent_id, okved_1.strcode, okved_1.name, okved_1.date_in, okved_1.user_in
                                                                        Index Cond: (okved_1.id = o1.p)
                                                                        Buffers: shared hit=6
                                                    CTE o2
                                                      ->  Recursive Union  (cost=0.28..1271.21 rows=893 width=12) (actual time=0.018..0.284 rows=24 loops=1)
                                                            Buffers: shared hit=57 read=1
                                                            ->  Nested Loop  (cost=0.28..7.02 rows=3 width=12) (actual time=0.015..0.022 rows=3 loops=1)
                                                                  Output: okved_2.id, okved_2.parent_id, o0_1.o
                                                                  Buffers: shared hit=3
                                                                  ->  CTE Scan on o0 o0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
                                                                        Output: o0_1.i, o0_1.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_2  (cost=0.28..6.52 rows=3 width=8) (actual time=0.010..0.013 rows=3 loops=1)
                                                                        Output: okved_2.id, okved_2.parent_id, okved_2.strcode, okved_2.name, okved_2.date_in, okved_2.user_in
                                                                        Index Cond: (okved_2.parent_id = o0_1.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..108.56 rows=89 width=12) (actual time=0.038..0.074 rows=7 loops=3)
                                                                  Output: okved_3.id, okved_3.parent_id, o2.o
                                                                  Buffers: shared hit=54 read=1
                                                                  ->  WorkTable Scan on o2  (cost=0.00..6.00 rows=30 width=8) (actual time=0.001..0.004 rows=8 loops=3)
                                                                        Output: o2.i, o2.p, o2.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_3  (cost=0.28..3.12 rows=3 width=8) (actual time=0.005..0.007 rows=1 loops=24)
                                                                        Output: okved_3.id, okved_3.parent_id, okved_3.strcode, okved_3.name, okved_3.date_in, okved_3.user_in
                                                                        Index Cond: (okved_3.parent_id = o2.i)
                                                                        Buffers: shared hit=54 read=1
                                                    ->  CTE Scan on o1 o1_1  (cost=0.00..20.20 rows=101 width=8) (actual time=0.091..0.149 rows=3 loops=1)
                                                          Output: o1_1.i, o1_1.o
                                                          Buffers: shared hit=12 read=1
                                                    ->  CTE Scan on o2 o2_1  (cost=0.00..178.60 rows=893 width=8) (actual time=0.021..0.317 rows=24 loops=1)
                                                          Output: o2_1.i, o2_1.o
                                                          Buffers: shared hit=57 read=1
  ->  WindowAgg  (cost=20004319106.78..20005928765.92 rows=3406686 width=20) (actual time=28029.066..28031.151 rows=1000 loops=1)
        Output: org_rel_okved.org_id, (((((((max((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) + ((sum((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) / 300))::integer)) * org_address.we (...)
        Buffers: shared hit=61143 read=14454, temp read=2950 written=2950
        ->  Sort  (cost=20004319106.78..20004327623.50 rows=3406686 width=20) (actual time=28029.034..28029.421 rows=1000 loops=1)
              Output: (((((((max((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) + ((sum((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) / 300))::integer)) * org_address.weight))::numeric  (...)
              Sort Key: (((((((max((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) + ((sum((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) / 300))::integer)) * org_address.weight))::numeri (...)
              Sort Method: quicksort  Memory: 49949kB
              Buffers: shared hit=61143 read=14454, temp read=2950 written=2950
              ->  Merge Join  (cost=10002082334.84..10003899577.41 rows=3406686 width=20) (actual time=5107.948..27054.077 rows=701489 loops=1)
                    Output: ((((((max((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) + ((sum((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) / 300))::integer)) * org_address.weight))::num (...)
                    Merge Cond: (org_rel_okved.org_id = org_address.org_id)
                    Buffers: shared hit=61143 read=14454, temp read=2950 written=2950
                    ->  GroupAggregate  (cost=10002082334.41..10002599854.03 rows=3406686 width=12) (actual time=5075.737..9684.743 rows=701491 loops=1)
                          Output: org_rel_okved.org_id, (max((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) + ((sum((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (org_rel_okved.ord + 3)))) / 300))::integer)
                          Buffers: shared hit=5316 read=1706, temp read=2950 written=2950
                          ->  Sort  (cost=10002082334.41..10002097464.49 rows=6052035 width=12) (actual time=5075.704..6157.680 rows=1097003 loops=1)
                                Output: org_rel_okved.org_id, ((max(r2.o) + ((sum(r2.o) / 300))::integer)), org_rel_okved.ord
                                Sort Key: org_rel_okved.org_id
                                Sort Method: external merge  Disk: 23592kB
                                Buffers: shared hit=5316 read=1706, temp read=2950 written=2950
                                ->  Nested Loop  (cost=799.04..1326725.76 rows=6052035 width=12) (actual time=44.038..1589.387 rows=1097003 loops=1)
                                      Output: org_rel_okved.org_id, ((max(r2.o) + ((sum(r2.o) / 300))::integer)), org_rel_okved.ord
                                      Buffers: shared hit=5316 read=1706
                                      ->  Limit  (cost=798.48..873.84 rows=701 width=8) (actual time=44.009..45.146 rows=176 loops=1)
                                            Output: res_rel_okved.okved_id, ((max(r2.o) + ((sum(r2.o) / 300))::integer))
                                            Buffers: shared hit=527 read=70
                                            ->  HashAggregate  (cost=798.48..915.22 rows=1086 width=8) (actual time=44.008..44.935 rows=176 loops=1)
                                                  Output: res_rel_okved.okved_id, (max(r2.o) + ((sum(r2.o) / 300))::integer)
                                                  Buffers: shared hit=527 read=70
                                                  ->  Nested Loop  (cost=0.29..790.33 rows=1086 width=8) (actual time=28.926..43.038 rows=701 loops=1)
                                                        Output: res_rel_okved.okved_id, r2.o
                                                        Buffers: shared hit=527 read=70
                                                        ->  CTE Scan on r2  (cost=0.00..31.40 rows=157 width=8) (actual time=28.891..30.424 rows=157 loops=1)
                                                              Output: r2.r, r2.o
                                                              Buffers: shared hit=69 read=50
                                                        ->  Index Scan using res_rel_okved_res on msn.res_rel_okved  (cost=0.29..4.13 rows=7 width=8) (actual time=0.010..0.075 rows=4 loops=157)
                                                              Output: res_rel_okved.id, res_rel_okved.res_id, res_rel_okved.okved_id
                                                              Index Cond: (res_rel_okved.res_id = r2.r)
                                                              Buffers: shared hit=458 read=20
                                      ->  Index Only Scan using org_rel_okved_org_okved on msn.org_rel_okved  (cost=0.56..1027.97 rows=8633 width=12) (actual time=0.035..5.577 rows=6233 loops=176)
                                            Output: org_rel_okved.okved_id, org_rel_okved.org_id, org_rel_okved.ord
                                            Index Cond: (org_rel_okved.okved_id = res_rel_okved.okved_id)
                                            Filter: (org_rel_okved.org_id <> 1)
                                            Rows Removed by Filter: 0
                                            Heap Fetches: 0
                                            Buffers: shared hit=4789 read=1636
                    ->  Index Only Scan using org_address_org_search on msn.org_address  (cost=0.43..597267.81 rows=5040664 width=16) (actual time=32.080..4401.271 rows=5040093 loops=1)
                          Output: org_address.org_id, org_address.is_sale, org_address.latitude, org_address.longitude, org_address.weight
                          Index Cond: (org_address.is_sale = true)
                          Filter: org_address.is_sale
                          Heap Fetches: 0
                          Buffers: shared hit=55823 read=12748
                    SubPlan 6
                      ->  Aggregate  (cost=0.24..0.34 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=701489)
                            Output: COALESCE(min(((abs((org_address.latitude - a0.t)) / 15000) + (LEAST((abs(((org_address.longitude - a0.g) + 180000000)) % 180000000), (abs(((a0.g - org_address.longitude) + 180000000)) % 180000000)) / 16500))), 5000)
                            Buffers: shared hit=4
                            ->  CTE Scan on a0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=701489)
                                  Output: a0.t, a0.g
                                  Buffers: shared hit=4
Total runtime: 28053.539 ms

Тоесть limit перестал оказывать влияние после сбора более подробной статистики, а "enable_sort" повлиял только на стоимость:
offWindowAgg (cost=20007413672.5220GonWindowAgg (cost=7413672.527.4MИ ошибка при подсчете строк стала значительно меньше, и пропало безумное "200", став 3.5млн вместо 0.7млн - и это уже хорошо!
Только для org_address должен использоваться SeqScan для такого количества(SeqScan для этой таблицы около 7-10 сек), а заставить его использоваться не получается. Для этого пробовал перебирать random_page_cost и cpu_tuple_cost.

SHOW ALLnamesettingallow_system_table_modsoffapplication_namepgAdmin III - ???????????????????? ????????????????archive_command(disabled)archive_modeoffarchive_timeout0array_nullsonauthentication_timeout1minautovacuumoffautovacuum_analyze_scale_factor0.1autovacuum_analyze_threshold50autovacuum_freeze_max_age200000000autovacuum_max_workers3autovacuum_naptime1minautovacuum_vacuum_cost_delay20msautovacuum_vacuum_cost_limit-1autovacuum_vacuum_scale_factor0.2autovacuum_vacuum_threshold50backslash_quotesafe_encodingbgwriter_delay200msbgwriter_lru_maxpages100bgwriter_lru_multiplier2block_size8192bonjouroffbonjour_namebytea_outputescapecheck_function_bodiesoncheckpoint_completion_target0.9checkpoint_segments32checkpoint_timeout5mincheckpoint_warning30sclient_encodingUNICODEclient_min_messagesnoticecommit_delay0commit_siblings5config_file/home/pgsql/postgresql.confconstraint_exclusionpartitioncpu_index_tuple_cost0.005cpu_operator_cost0.0025cpu_tuple_cost0.1cursor_tuple_fraction0.1data_directory/home/pgsqlDateStyleISO MDYdb_user_namespaceoffdeadlock_timeout1sdebug_assertionsoffdebug_pretty_printondebug_print_parseoffdebug_print_planoffdebug_print_rewrittenoffdefault_statistics_target100default_tablespacedefault_text_search_configpg_catalog.englishdefault_transaction_deferrableoffdefault_transaction_isolationread committeddefault_transaction_read_onlyoffdefault_with_oidsoffdynamic_library_path$libdireffective_cache_size704MBeffective_io_concurrency1enable_bitmapscanonenable_hashaggonenable_hashjoinonenable_indexonlyscanonenable_indexscanonenable_materialonenable_mergejoinonenable_nestlooponenable_seqscanonenable_sortonenable_tidscanonescape_string_warningonevent_sourcePostgreSQLexit_on_erroroffexternal_pid_fileextra_float_digits0from_collapse_limit8fsynconfull_page_writesongeqoongeqo_effort5geqo_generations0geqo_pool_size0geqo_seed0geqo_selection_bias2geqo_threshold12gin_fuzzy_search_limit0hba_file/home/pgsql/pg_hba.confhot_standbyoffhot_standby_feedbackoffident_file/home/pgsql/pg_ident.confignore_checksum_failureoffignore_system_indexesoffinteger_datetimesonIntervalStylepostgresjoin_collapse_limit8krb_caseins_usersoffkrb_server_keyfilekrb_srvnamepostgreslc_collateen_US.UTF-8lc_ctypeen_US.UTF-8lc_messagesen_US.UTF-8lc_monetaryen_US.UTF-8lc_numericen_US.UTF-8lc_timeen_US.UTF-8listen_addresses*lo_compat_privilegesofflocal_preload_librarieslock_timeout0log_autovacuum_min_duration-1log_checkpointsofflog_connectionsofflog_destinationstderrlog_directorypg_loglog_disconnectionsofflog_durationofflog_error_verbositydefaultlog_executor_statsofflog_file_mode0600log_filenamepostgresql-%Y-%m-%d_%H%M%S.loglog_hostnameofflog_line_prefixlog_lock_waitsofflog_min_duration_statement-1log_min_error_statementerrorlog_min_messageswarninglog_parser_statsofflog_planner_statsofflog_rotation_age1dlog_rotation_size10MBlog_statementnonelog_statement_statsofflog_temp_files-1log_timezoneAsia/Yekaterinburglog_truncate_on_rotationofflogging_collectoroffmaintenance_work_mem60MBmax_connections100max_files_per_process1000max_function_args100max_identifier_length63max_index_keys32max_locks_per_transaction64max_pred_locks_per_transaction64max_prepared_transactions0max_stack_depth2MBmax_standby_archive_delay30smax_standby_streaming_delay30smax_wal_senders0password_encryptiononport5432post_auth_delay0pre_auth_delay0quote_all_identifiersoffrandom_page_cost2restart_after_crashonsearch_pathmsn publicsegment_size1GBseq_page_cost1server_encodingUTF8server_version9.3.1server_version_num90301session_replication_roleoriginshared_buffers240MBshared_preload_librariessql_inheritanceonssloffssl_ca_filessl_cert_fileserver.crtssl_ciphersnonessl_crl_filessl_key_fileserver.keyssl_renegotiation_limit512MBstandard_conforming_stringsonstatement_timeout0stats_temp_directorypg_stat_tmpsuperuser_reserved_connections3synchronize_seqscansonsynchronous_commitonsynchronous_standby_namessyslog_facilitylocal0syslog_identpostgrestcp_keepalives_count9tcp_keepalives_idle7200tcp_keepalives_interval75temp_buffers8MBtemp_file_limit-1temp_tablespacesTimeZoneAsia/Yekaterinburgtimezone_abbreviationsDefaulttrace_notifyofftrace_recovery_messageslogtrace_sortofftrack_activitiesontrack_activity_query_size1024track_countsontrack_functionsnonetrack_io_timingofftransaction_deferrableofftransaction_isolationread committedtransaction_read_onlyofftransform_null_equalsoffunix_socket_directories/tmpunix_socket_groupunix_socket_permissions0777update_process_titleonvacuum_cost_delay0vacuum_cost_limit200vacuum_cost_page_dirty20vacuum_cost_page_hit1vacuum_cost_page_miss10vacuum_defer_cleanup_age0vacuum_freeze_min_age50000000vacuum_freeze_table_age150000000wal_block_size8192wal_buffers7MBwal_keep_segments0wal_levelminimalwal_receiver_status_interval10swal_receiver_timeout1minwal_segment_size16MBwal_sender_timeout1minwal_sync_methodfdatasyncwal_writer_delay200mswork_mem50MBxmlbinarybase64xmloptioncontentzero_damaged_pagesoff

...

Рейтинг:

0 / 0

13.01.2014, 08:27:11

| Ответить | Цитировать | Написать

На пути к идеальному плану

#38525525

R_Only

Гость

Путем перестановки внутри полного запроса удалось снизить время на 5 секунд:

Запрос полный, 28286мс

Код: plsql

SET work_mem = '10MB';
EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS on, TIMING on)
WITH
a0 as (select latitude t,longitude g from org_address where org_id=1/*inorg_id*/ and id=any(array[997192]/*inadr_ids*/)),
r0 as (select id i,res_id r from org_rel_res where type=2 and org_id=1/*inorg_id*/ and id=any(array[7,82]/*inres_ids*/)),
d0 as (select r s,rd_id d,(select type from res_description where id=rd_id)t,d.id i,least(value1,value2)v1,greatest(value1,value2)v2,values vs
	,(select count(id) from orrd_value where orrd_id=d.id)vc from orr_rel_res_descr d,r0 where orr_id=i),
s0 as (select i h,rdv_id vp from orrd_value,d0 where orrd_id=i),
r2 as (select r, 1000-case when c=0 then o<<3 when c>3 then (o*(1.0-2.0/(a-o)))::integer<<3 else (o*(1.0-1.0/(a-o)))::integer<<3 end o
from (
	SELECT res_id r,((min(greatest(0,o-c))<<1)+1) o,((avg(greatest(0,o-c))::integer<<1)+4) a,count(res_id)::integer-1 c
	FROM buyer_okved_rel_res b, (
		SELECT i,min(o)<<1+1 o,count(o)::integer-1 c
		FROM (
			WITH RECURSIVE
			o0(i,o) AS (SELECT okved_id,ord FROM org_rel_okved WHERE org_id=1/*inorg_id*/ and id=any(array[15054388]/*inokved_ids*/)),
			o1(i,p,o) AS (SELECT id,parent_id,o FROM okved,o0 WHERE id=i UNION ALL SELECT id,parent_id,o+1 FROM okved,o1 WHERE id=p),
			o2(i,p,o) AS (SELECT id,parent_id,o FROM okved,o0 WHERE parent_id=i UNION ALL SELECT id,parent_id,o FROM okved,o2 WHERE parent_id=i)
			SELECT i,o FROM o1 UNION ALL SELECT i,o FROM o2
		)o3 GROUP BY i
	)o4 WHERE okved_id=i GROUP BY res_id
)r1 union all select r,1080
from r0 /*limit 157*/
)
select i,z,o,1+((row_number()over()-1)/100/*inorgonlist*/) p from (
select i,z,o from (
select is_sale i,org_id z,((o*weight)*(0.5+500.0/(500
	+(select coalesce(min(abs(latitude-t)/15000+least(abs(longitude-g+180000000)%180000000,abs(g-longitude+180000000)%180000000)/16500),5000) from a0))))::integer o
from org_address a,(
select org_id z,max(o)+(sum(o)/300)::integer o from (
	select org_id,o+(300/(ord+3))::integer o
	from org_rel_okved e, (select okved_id b, max(o)+(sum(o)/300)::integer o from res_rel_okved l, r2 where res_id=r group by okved_id limit 10000/*loc_ocnt*/)o5
	where okved_id=b
	union all
	select org_id,(o+50+8*
	coalesce(sum((
		select least(count(id),3)+coalesce(count(d),0)+sum(case
			when t is null then 0
			when t=1 then
				case when v1=v2 then case when v1 between value1 and value2 then 3 else -4 end
				else greatest(-5,trunc(1+((least(value2,v2)-greatest(value1,v1))*4.0/(v2-v1))::integer)) end
			when t=2 then -1
			when t=3 then
				(select coalesce(1+(sum(1)*3/vc)::integer,-5) from orrd_value,s0 where orrd_id=e.id and h=i and vp=rdv_id)
			else 0 end)
		from orr_rel_res_descr e left join d0 on d=rd_id and s=r where orr_id=l.id
	)),0))::integer o
	from org_rel_res l, r2 where type=1 and res_id=r
	group by org_id,r,o
)w2 group by org_id
)w3 where org_id=z
)w4 order by o desc limit 10000
)w5 where o<>1/*inorg_id*/ and i

EXPLAIN PLAN полный, 28286мс

Код: plsql

WindowAgg  (cost=4728940.63..4728983.52 rows=199 width=9) (actual time=28251.874..28282.835 rows=10000 loops=1)
  Output: w5.i, w5.z, w5.o, (1 + ((row_number() OVER (?) - 1) / 100))
  Buffers: shared hit=2466493
  CTE a0
    ->  Index Scan using org_address_org_search on msn.org_address  (cost=0.43..4.54 rows=1 width=8) (actual time=0.021..0.023 rows=1 loops=1)
          Output: org_address.latitude, org_address.longitude
          Index Cond: (org_address.org_id = 1)
          Filter: (org_address.id = ANY ('{997192}'::integer[]))
          Buffers: shared hit=4
  CTE r0
    ->  Seq Scan on msn.org_rel_res  (cost=0.00..3.90 rows=1 width=8) (actual time=0.019..0.031 rows=2 loops=1)
          Output: org_rel_res.id, org_rel_res.res_id
          Filter: ((org_rel_res.id = ANY ('{7,82}'::integer[])) AND (org_rel_res.type = 2) AND (org_rel_res.org_id = 1))
          Rows Removed by Filter: 25
          Buffers: shared hit=1
  CTE d0
    ->  Hash Join  (cost=0.30..10.30 rows=1 width=240) (actual time=0.101..0.106 rows=1 loops=1)
          Output: r0.r, d.rd_id, (SubPlan 3), d.id, LEAST(d.value1, d.value2), GREATEST(d.value1, d.value2), d.values", (SubPlan 4)"
          Hash Cond: (d.orr_id = r0.i)
          Buffers: shared hit=5
          ->  Seq Scan on msn.orr_rel_res_descr d  (cost=0.00..2.50 rows=15 width=240) (actual time=0.002..0.007 rows=15 loops=1)
                Output: d.id, d.rd_id, d.orr_id, d.date_in, d.user_in, d.value1, d.value2, d.values""
                Buffers: shared hit=1
          ->  Hash  (cost=0.20..0.20 rows=1 width=8) (actual time=0.008..0.008 rows=2 loops=1)
                Output: r0.r, r0.i
                Buckets: 1024  Batches: 1  Memory Usage: 1kB
                ->  CTE Scan on r0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.003..0.005 rows=2 loops=1)
                      Output: r0.r, r0.i
          SubPlan 3
            ->  Index Scan using res_description_id_pk on msn.res_description  (cost=0.28..4.39 rows=1 width=4) (actual time=0.020..0.022 rows=1 loops=1)
                  Output: res_description.type
                  Index Cond: (res_description.id = d.rd_id)
                  Buffers: shared hit=3
          SubPlan 4
            ->  Aggregate  (cost=2.85..2.95 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)
                  Output: count(orrd_value.id)
                  Buffers: shared hit=1
                  ->  Seq Scan on msn.orrd_value  (cost=0.00..2.85 rows=1 width=4) (actual time=0.010..0.013 rows=1 loops=1)
                        Output: orrd_value.id, orrd_value.orrd_id, orrd_value.rdv_id, orrd_value.date_in, orrd_value.user_in
                        Filter: (orrd_value.orrd_id = d.id)
                        Rows Removed by Filter: 17
                        Buffers: shared hit=1
  CTE s0
    ->  Hash Join  (cost=0.30..3.27 rows=1 width=8) (actual time=0.042..0.047 rows=1 loops=1)
          Output: d0.i, orrd_value_1.rdv_id
          Hash Cond: (orrd_value_1.orrd_id = d0.i)
          Buffers: shared hit=1
          ->  Seq Scan on msn.orrd_value orrd_value_1  (cost=0.00..2.80 rows=18 width=8) (actual time=0.002..0.006 rows=18 loops=1)
                Output: orrd_value_1.id, orrd_value_1.orrd_id, orrd_value_1.rdv_id, orrd_value_1.date_in, orrd_value_1.user_in
                Buffers: shared hit=1
          ->  Hash  (cost=0.20..0.20 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)
                Output: d0.i
                Buckets: 1024  Batches: 1  Memory Usage: 1kB
                ->  CTE Scan on d0  (cost=0.00..0.20 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)
                      Output: d0.i
  CTE r2
    ->  Append  (cost=4257.86..5235.30 rows=4443 width=8) (actual time=1.525..2.728 rows=163 loops=1)
          Buffers: shared hit=77
          ->  Subquery Scan on r1  (cost=4257.86..5235.10 rows=4442 width=8) (actual time=1.524..2.611 rows=161 loops=1)
                Output: r1.r, (1000 - CASE WHEN (r1.c = 0) THEN (r1.o << 3) WHEN (r1.c > 3) THEN ((((r1.o)::numeric * (1.0 - (2.0 / ((r1.a - r1.o))::numeric))))::integer << 3) ELSE ((((r1.o)::numeric * (1.0 - (1.0 / ((r1.a - r1.o))::numeric))))::integer << (...)
                Buffers: shared hit=76
                ->  HashAggregate  (cost=4257.86..4790.90 rows=4442 width=12) (actual time=1.519..2.393 rows=161 loops=1)
                      Output: b.res_id, ((min(GREATEST(0, (o4.o - o4.c))) << 1) + 1), (((avg(GREATEST(0, (o4.o - o4.c))))::integer << 1) + 4), ((count(b.res_id))::integer - 1)
                      Buffers: shared hit=76
                      ->  Merge Join  (cost=2044.73..4116.33 rows=9435 width=12) (actual time=0.640..1.063 rows=161 loops=1)
                            Output: b.res_id, o4.o, o4.c
                            Merge Cond: (b.okved_id = o4.i)
                            Buffers: shared hit=76
                            ->  Index Only Scan using buyer_okved_rel_res_okved_res_id on msn.buyer_okved_rel_res b  (cost=0.29..1057.63 rows=9435 width=8) (actual time=0.042..0.156 rows=162 loops=1)
                                  Output: b.okved_id, b.res_id
                                  Heap Fetches: 4
                                  Buffers: shared hit=5
                            ->  Sort  (cost=2044.44..2044.94 rows=200 width=12) (actual time=0.558..0.629 rows=187 loops=1)
                                  Output: o4.o, o4.c, o4.i
                                  Sort Key: o4.i
                                  Sort Method: quicksort  Memory: 26kB
                                  Buffers: shared hit=71
                                  ->  Subquery Scan on o4  (cost=1995.30..2036.80 rows=200 width=12) (actual time=0.486..0.519 rows=27 loops=1)
                                        Output: o4.o, o4.c, o4.i
                                        Buffers: shared hit=71
                                        ->  HashAggregate  (cost=1995.30..2016.80 rows=200 width=8) (actual time=0.484..0.509 rows=27 loops=1)
                                              Output: o1_1.i, (min(o1_1.o) << 2), ((count(o1_1.o))::integer - 1)
                                              Buffers: shared hit=71
                                              ->  Append  (cost=1689.64..1888.44 rows=994 width=8) (actual time=0.062..0.421 rows=27 loops=1)
                                                    Buffers: shared hit=71
                                                    CTE o0
                                                      ->  Index Scan using org_rel_okved_id_pk on msn.org_rel_okved  (cost=0.43..4.54 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=1)
                                                            Output: org_rel_okved.okved_id, org_rel_okved.ord
                                                            Index Cond: (org_rel_okved.id = ANY ('{15054388}'::integer[]))
                                                            Filter: (org_rel_okved.org_id = 1)
                                                            Buffers: shared hit=4
                                                    CTE o1
                                                      ->  Recursive Union  (cost=0.28..413.89 rows=101 width=12) (actual time=0.058..0.110 rows=3 loops=1)
                                                            Buffers: shared hit=13
                                                            ->  Nested Loop  (cost=0.28..4.69 rows=1 width=12) (actual time=0.055..0.060 rows=1 loops=1)
                                                                  Output: okved.id, okved.parent_id, o0.o
                                                                  Buffers: shared hit=7
                                                                  ->  CTE Scan on o0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.033..0.035 rows=1 loops=1)
                                                                        Output: o0.i, o0.o
                                                                        Buffers: shared hit=4
                                                                  ->  Index Scan using okved_pk on msn.okved  (cost=0.28..4.39 rows=1 width=8) (actual time=0.013..0.016 rows=1 loops=1)
                                                                        Output: okved.id, okved.parent_id, okved.strcode, okved.name, okved.date_in, okved.user_in
                                                                        Index Cond: (okved.id = o0.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..38.90 rows=10 width=12) (actual time=0.011..0.012 rows=1 loops=3)
                                                                  Output: okved_1.id, okved_1.parent_id, (o1.o + 1)
                                                                  Buffers: shared hit=6
                                                                  ->  WorkTable Scan on o1  (cost=0.00..2.00 rows=10 width=8) (actual time=0.002..0.002 rows=1 loops=3)
                                                                        Output: o1.i, o1.p, o1.o
                                                                  ->  Index Scan using okved_pk on msn.okved okved_1  (cost=0.28..3.59 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=3)
                                                                        Output: okved_1.id, okved_1.parent_id, okved_1.strcode, okved_1.name, okved_1.date_in, okved_1.user_in
                                                                        Index Cond: (okved_1.id = o1.p)
                                                                        Buffers: shared hit=6
                                                    CTE o2
                                                      ->  Recursive Union  (cost=0.28..1271.21 rows=893 width=12) (actual time=0.019..0.256 rows=24 loops=1)
                                                            Buffers: shared hit=58
                                                            ->  Nested Loop  (cost=0.28..7.02 rows=3 width=12) (actual time=0.017..0.023 rows=3 loops=1)
                                                                  Output: okved_2.id, okved_2.parent_id, o0_1.o
                                                                  Buffers: shared hit=3
                                                                  ->  CTE Scan on o0 o0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
                                                                        Output: o0_1.i, o0_1.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_2  (cost=0.28..6.52 rows=3 width=8) (actual time=0.010..0.014 rows=3 loops=1)
                                                                        Output: okved_2.id, okved_2.parent_id, okved_2.strcode, okved_2.name, okved_2.date_in, okved_2.user_in
                                                                        Index Cond: (okved_2.parent_id = o0_1.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..108.56 rows=89 width=12) (actual time=0.038..0.068 rows=7 loops=3)
                                                                  Output: okved_3.id, okved_3.parent_id, o2.o
                                                                  Buffers: shared hit=55
                                                                  ->  WorkTable Scan on o2  (cost=0.00..6.00 rows=30 width=8) (actual time=0.001..0.003 rows=8 loops=3)
                                                                        Output: o2.i, o2.p, o2.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_3  (cost=0.28..3.12 rows=3 width=8) (actual time=0.005..0.006 rows=1 loops=24)
                                                                        Output: okved_3.id, okved_3.parent_id, okved_3.strcode, okved_3.name, okved_3.date_in, okved_3.user_in
                                                                        Index Cond: (okved_3.parent_id = o2.i)
                                                                        Buffers: shared hit=55
                                                    ->  CTE Scan on o1 o1_1  (cost=0.00..20.20 rows=101 width=8) (actual time=0.062..0.118 rows=3 loops=1)
                                                          Output: o1_1.i, o1_1.o
                                                          Buffers: shared hit=13
                                                    ->  CTE Scan on o2 o2_1  (cost=0.00..178.60 rows=893 width=8) (actual time=0.022..0.291 rows=24 loops=1)
                                                          Output: o2_1.i, o2_1.o
                                                          Buffers: shared hit=58
          ->  CTE Scan on r0 r0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.024..0.037 rows=2 loops=1)
                Output: r0_1.r, 1080
                Buffers: shared hit=1
  ->  Subquery Scan on w5  (cost=4723683.33..4723704.33 rows=199 width=9) (actual time=28251.857..28265.751 rows=10000 loops=1)
        Output: w5.i, w5.z, w5.o
        Filter: (w5.i AND (w5.o <> 1))
        Buffers: shared hit=2466493
        ->  Limit  (cost=4723683.33..4723683.83 rows=200 width=21) (actual time=28251.847..28258.707 rows=10000 loops=1)
              Output: a.is_sale, a.org_id, (((((((max(((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3))))) + ((sum(((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3))))) / 300))::integer)) * a.weight))::numeric * (0.5 +  (...)
              Buffers: shared hit=2466493
              ->  Sort  (cost=4723683.33..4723683.83 rows=200 width=21) (actual time=28251.844..28255.067 rows=10000 loops=1)
                    Output: a.is_sale, a.org_id, (((((((max(((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3))))) + ((sum(((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3))))) / 300))::integer)) * a.weight))::numeric * ( (...)
                    Sort Key: (((((((max(((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3))))) + ((sum(((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3))))) / 300))::integer)) * a.weight))::numeric * (0.5 + (500.0 / ((50 (...)
                    Sort Method: top-N heapsort  Memory: 1237kB
                    Buffers: shared hit=2466493
                    ->  Nested Loop  (cost=4723035.11..4723675.68 rows=200 width=21) (actual time=4516.714..27198.532 rows=703708 loops=1)
                          Output: a.is_sale, a.org_id, ((((((max(((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3))))) + ((sum(((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3))))) / 300))::integer)) * a.weight))::numeri (...)
                          Buffers: shared hit=2466493
                          ->  HashAggregate  (cost=4723034.68..4723056.18 rows=200 width=8) (actual time=4516.608..5929.550 rows=703710 loops=1)
                                Output: e.org_id, (max(((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3))))) + ((sum(((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3))))) / 300))::integer)
                                Buffers: shared hit=5943
                                ->  Append  (cost=9320.41..4616072.15 rows=14261671 width=8) (actual time=6.297..2454.112 rows=1100643 loops=1)
                                      Buffers: shared hit=5943
                                      ->  Nested Loop  (cost=9320.41..3186670.88 rows=14261316 width=12) (actual time=6.297..2006.958 rows=1100641 loops=1)
                                            Output: e.org_id, (((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3)))
                                            Buffers: shared hit=5933
                                            ->  Limit  (cost=9319.85..9497.01 rows=1648 width=8) (actual time=6.266..7.318 rows=179 loops=1)
                                                  Output: l.okved_id, ((max(r2.o) + ((sum(r2.o) / 300))::integer))
                                                  Buffers: shared hit=410
                                                  ->  HashAggregate  (cost=9319.85..9497.01 rows=1648 width=8) (actual time=6.265..7.119 rows=179 loops=1)
                                                        Output: l.okved_id, (max(r2.o) + ((sum(r2.o) / 300))::integer)
                                                        Buffers: shared hit=410
                                                        ->  Nested Loop  (cost=0.29..9089.39 rows=30728 width=8) (actual time=1.551..5.349 rows=732 loops=1)
                                                              Output: l.okved_id, r2.o
                                                              Buffers: shared hit=410
                                                              ->  CTE Scan on r2  (cost=0.00..888.60 rows=4443 width=8) (actual time=1.529..3.019 rows=163 loops=1)
                                                                    Output: r2.r, r2.o
                                                                    Buffers: shared hit=77
                                                              ->  Index Only Scan using res_rel_okved_resokved on msn.res_rel_okved l  (cost=0.29..1.15 rows=7 width=8) (actual time=0.007..0.010 rows=4 loops=163)
                                                                    Output: l.res_id, l.okved_id
                                                                    Index Cond: (l.res_id = r2.r)
                                                                    Heap Fetches: 0
                                                                    Buffers: shared hit=333
                                            ->  Index Only Scan using org_rel_okved_org_okved on msn.org_rel_okved e  (cost=0.56..997.49 rows=8654 width=12) (actual time=0.033..4.630 rows=6149 loops=179)
                                                  Output: e.okved_id, e.org_id, e.ord
                                                  Index Cond: (e.okved_id = l.okved_id)
                                                  Heap Fetches: 0
                                                  Buffers: shared hit=5523
                                      ->  Subquery Scan on *SELECT* 2"  (cost=3194.23..3269.67 rows=355 width=8) (actual time=0.534..0.539 rows=2 loops=1)"
                                            Output: *SELECT* 2".org_id, "*SELECT* 2".o"
                                            Buffers: shared hit=10
                                            ->  HashAggregate  (cost=3194.23..3234.17 rows=355 width=16) (actual time=0.532..0.536 rows=2 loops=1)
                                                  Output: l_1.org_id, ((((r2_1.o + 50))::double precision + (8::double precision * COALESCE(sum((SubPlan 13)), 0::double precision))))::integer, r2_1.r, r2_1.o
                                                  Buffers: shared hit=10
                                                  ->  Hash Join  (cost=5.41..968.38 rows=355 width=16) (actual time=0.213..0.219 rows=2 loops=1)
                                                        Output: l_1.org_id, l_1.id, r2_1.r, r2_1.o
                                                        Hash Cond: (r2_1.r = l_1.res_id)
                                                        Buffers: shared hit=1
                                                        ->  CTE Scan on r2 r2_1  (cost=0.00..888.60 rows=4443 width=8) (actual time=0.003..0.070 rows=163 loops=1)
                                                              Output: r2_1.r, r2_1.o
                                                        ->  Hash  (cost=3.77..3.77 rows=16 width=12) (actual time=0.046..0.046 rows=16 loops=1)
                                                              Output: l_1.org_id, l_1.id, l_1.res_id
                                                              Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                                              Buffers: shared hit=1
                                                              ->  Seq Scan on msn.org_rel_res l_1  (cost=0.00..3.77 rows=16 width=12) (actual time=0.014..0.030 rows=16 loops=1)
                                                                    Output: l_1.org_id, l_1.id, l_1.res_id
                                                                    Filter: (l_1.type = 1)
                                                                    Rows Removed by Filter: 11
                                                                    Buffers: shared hit=1
                                                  SubPlan 13
                                                    ->  Aggregate  (cost=6.15..6.26 rows=1 width=74) (actual time=0.134..0.134 rows=1 loops=2)
                                                          Output: (((LEAST(count(e_1.id), 3::bigint) + COALESCE(count(d0_1.d), 0::bigint)))::double precision + sum(CASE WHEN (d0_1.t IS NULL) THEN 0::double precision WHEN (d0_1.t = 1) THEN CASE WHEN (d0_1.v (...)
                                                          Buffers: shared hit=9
                                                          ->  Nested Loop Left Join  (cost=0.00..2.84 rows=1 width=74) (actual time=0.075..0.081 rows=1 loops=2)
                                                                Output: e_1.id, e_1.value1, e_1.value2, d0_1.d, d0_1.t, d0_1.v1, d0_1.v2, d0_1.vc, d0_1.i
                                                                Join Filter: (d0_1.d = e_1.rd_id)
                                                                Rows Removed by Join Filter: 0
                                                                Buffers: shared hit=7
                                                                ->  Seq Scan on msn.orr_rel_res_descr e_1  (cost=0.00..2.54 rows=1 width=18) (actual time=0.009..0.011 rows=1 loops=2)
                                                                      Output: e_1.id, e_1.rd_id, e_1.orr_id, e_1.date_in, e_1.user_in, e_1.value1, e_1.value2, e_1.values""
                                                                      Filter: (e_1.orr_id = l_1.id)
                                                                      Rows Removed by Filter: 14
                                                                      Buffers: shared hit=2
                                                                ->  CTE Scan on d0 d0_1  (cost=0.00..0.20 rows=1 width=60) (actual time=0.054..0.058 rows=1 loops=2)
                                                                      Output: d0_1.s, d0_1.d, d0_1.t, d0_1.i, d0_1.v1, d0_1.v2, d0_1.vs, d0_1.vc
                                                                      Filter: (d0_1.s = r2_1.r)
                                                                      Buffers: shared hit=5
                                                          SubPlan 12
                                                            ->  Aggregate  (cost=3.15..3.26 rows=1 width=0) (actual time=0.078..0.078 rows=1 loops=1)
                                                                  Output: COALESCE((1 + (((sum(1) * 3) / d0_1.vc))::integer), (-5))
                                                                  Buffers: shared hit=2
                                                                  ->  Nested Loop  (cost=0.00..3.15 rows=1 width=0) (actual time=0.070..0.070 rows=0 loops=1)
                                                                        Join Filter: (orrd_value_2.rdv_id = s0.vp)
                                                                        Rows Removed by Join Filter: 1
                                                                        Buffers: shared hit=2
                                                                        ->  Seq Scan on msn.orrd_value orrd_value_2  (cost=0.00..2.85 rows=1 width=4) (actual time=0.009..0.011 rows=1 loops=1)
                                                                              Output: orrd_value_2.id, orrd_value_2.orrd_id, orrd_value_2.rdv_id, orrd_value_2.date_in, orrd_value_2.user_in
                                                                              Filter: (orrd_value_2.orrd_id = e_1.id)
                                                                              Rows Removed by Filter: 17
                                                                              Buffers: shared hit=1
                                                                        ->  CTE Scan on s0  (cost=0.00..0.20 rows=1 width=4) (actual time=0.046..0.053 rows=1 loops=1)
                                                                              Output: s0.h, s0.vp
                                                                              Filter: (s0.h = d0_1.i)
                                                                              Buffers: shared hit=1
                          ->  Index Only Scan using org_address_org_search on msn.org_address a  (cost=0.43..2.54 rows=1 width=17) (actual time=0.011..0.013 rows=1 loops=703710)
                                Output: a.org_id, a.is_sale, a.latitude, a.longitude, a.weight
                                Index Cond: (a.org_id = e.org_id)
                                Heap Fetches: 0
                                Buffers: shared hit=2460546
                          SubPlan 11
                            ->  Aggregate  (cost=0.24..0.34 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=703708)
                                  Output: COALESCE(min(((abs((a.latitude - a0.t)) / 15000) + (LEAST((abs(((a.longitude - a0.g) + 180000000)) % 180000000), (abs(((a0.g - a.longitude) + 180000000)) % 180000000)) / 16500))), 5000)
                                  Buffers: shared hit=4
                                  ->  CTE Scan on a0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=703708)
                                        Output: a0.t, a0.g
                                        Buffers: shared hit=4
Total runtime: 28286.184 ms

Предыдущий запрос был сильно урезан, и план в нем был, конечно, другой. "200" пропадало за счет вырезания UNION ALL. А основное ускорение было достигнуто за счет нескольких фильтров, которые выкидывают меньше 0.01% записей. Эти фильтры я переместил в конец, и сейчас они применяются на 10к записей, а не на 700к.

Встречал в некоторых статьях рекоммендации "против объединения" - OFFSET 0, но эта конструкция на EXPLAIN никак не влияла и на конечное время тоже. Зато помогает LIMIT с заведомо большим числом, чем количество возвращаемых запросом записей - сразу и EXPLAIN поменялся и время уменьшилось.

В запросе пропали сортировки! (гиганские) - боритесь с сортировками в запросах! не нужно будет выставлять большой work_mem! У меня данный запрос выполняется одинаково по времени и с work_mem=1MB и с work_mem=100MB. Конечно, Postgres сам увеличивает work_mem, если ему не хватает, но это Варнинг, желательно чтобы это было не часто.

Подскажите, пожалуйста, что еще можно попробовать для ускорения, и как провести испытания по применению SeqScan для org_address?

Сейчас основное замедление происходит в момент склейки a0 и w3, а также когда работаем с 1100641 строк при подготовке w3.

...

Рейтинг:

0 / 0

14.01.2014, 13:28:10

| Ответить | Цитировать | Написать

На пути к идеальному плану

#38525733

tadmin

Участник

Сообщения: 1 189

Рейтинг: 0 / 0

R_Onlyчто еще можно попробовать для ускорения, и как провести испытания по применению SeqScan для org_address?
Разные подстройки таких сложных планов не слишком устойчивы. В какой-то момент, типа улучшения работы планировщика или роста таблиц, это _внезапно_ перестанет работать. Выйдет pg 10.0, и вам снова придется долго копать эти жуткие планы.

IMHO, самое надежное - выгружать отдельные части во временные таблицы. И оптимизировать просто, и планы устойчивы.

...

Рейтинг:

0 / 0

14.01.2014, 15:14:22

| Ответить | Цитировать | Написать

На пути к идеальному плану

#38525933

Hawkmoon

Участник

Сообщения: 520

Рейтинг: 0 / 0

tadmin,

А как насчет materialized view для этих целей?

Все хочу попробовать, руки все чешутся - но никак не доходят. Ибо заняты.
Запросы тоже страшные, с упоминанием okved и иже :)

...

Рейтинг:

0 / 0

14.01.2014, 16:59:22

| Ответить | Цитировать | Написать

На пути к идеальному плану

#38526031

tadmin

Участник

Сообщения: 1 189

Рейтинг: 0 / 0

Hawkmoon,
мат.вью мы пока не используем, хотя есть похожие отчеты и знакомые ключевые слова -)
На мой взгляд, для очень сложных запросов, по которым не нужен реалтайм, проще набивать из временных таблиц.

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

По моему опыту, структура pl/pgsql процедуры более наглядна, нежели один многоуровневый запрос.
В очень тяжелых случаях планировщик разделяет на отдельные транзакции наполнение промежуточных данных.

Для случаев попроще мы используем самодельную процедуру, которая анализирует pg_attribute и из view создает таблицу.
В отличие от select into или мат.вью, наша функция понимает, где нужно создать индексы в этой таблице. Поскольку у нас свои domain и довольно строгая нотация в названиях полей, это несложно.

...

Рейтинг:

0 / 0

14.01.2014, 18:06:18

| Ответить | Цитировать | Написать

На пути к идеальному плану

#38526853

R_Only

Гость

tadminРазные подстройки таких сложных планов не слишком устойчивы. В какой-то момент, типа улучшения работы планировщика или роста таблиц, это _внезапно_ перестанет работать. Выйдет pg 10.0, и вам снова придется долго копать эти жуткие планы.
IMHO, самое надежное - выгружать отдельные части во временные таблицы. И оптимизировать просто, и планы устойчивы.У нас такой запрос один всего, так-что смена версии нас особо не пугает - чем быстрее он будет выполняться на нашей базе, тем лучше! Новую версию ПО надо, конечно, тестить внимательно, а потом уже выкладывать в продакшн.
К тому же чем больше я вижу и изучаю эти планы, тем легче мне будет делать эту работу повторно. Да и в других местах пригодятся эти знания/способности анализа.

Да, временная таблица очень хорошо помогла, спасибо за совет , tadmin ! Запрос ускорился примерно на 30%! Так что представляю на суд публики более понятные планы и запросы!

Заполняем временную таблицу (источник w3) - 7261мс

Код: plsql

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.

SET work_mem = '50MB';
SET temp_buffers = '16MB';
create temporary table tmp_seller_allorg (z integer,o integer);
EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS on, TIMING on)
WITH
r0 as (select id i,res_id r from org_rel_res where type=2 and org_id=1/*inorg_id*/ and id=any(array[7,82]/*inres_ids*/)),
d0 as (select r s,rd_id d,(select type from res_description where id=rd_id)t,d.id i,least(value1,value2)v1,greatest(value1,value2)v2,values vs
	,(select count(id) from orrd_value where orrd_id=d.id)vc from orr_rel_res_descr d,r0 where orr_id=i),
s0 as (select i h,rdv_id vp from orrd_value,d0 where orrd_id=i),
r2 as (select r, 1000-case when c=0 then o<<3 when c>3 then (o*(1.0-2.0/(a-o)))::integer<<3 else (o*(1.0-1.0/(a-o)))::integer<<3 end o
from (
	SELECT res_id r,((min(greatest(0,o-c))<<1)+1) o,((avg(greatest(0,o-c))::integer<<1)+4) a,count(res_id)::integer-1 c
	FROM buyer_okved_rel_res b, (
		SELECT i,min(o)<<1+1 o,count(o)::integer-1 c
		FROM (
			WITH RECURSIVE
			o0(i,o) AS (SELECT okved_id,ord FROM org_rel_okved WHERE org_id=1/*inorg_id*/ and id=any(array[15054388]/*inokved_ids*/)),
			o1(i,p,o) AS (SELECT id,parent_id,o FROM okved,o0 WHERE id=i UNION ALL SELECT id,parent_id,o+1 FROM okved,o1 WHERE id=p),
			o2(i,p,o) AS (SELECT id,parent_id,o FROM okved,o0 WHERE parent_id=i UNION ALL SELECT id,parent_id,o FROM okved,o2 WHERE parent_id=i)
			SELECT i,o FROM o1 UNION ALL SELECT i,o FROM o2
		)o3 GROUP BY i
	)o4 WHERE okved_id=i GROUP BY res_id
)r1 union all select r,1080
from r0)
insert into tmp_seller_allorg (z,o)
select org_id z,max(o)+(sum(o)/300)::integer o from (
	select org_id,o+(300/(ord+3))::integer o
	from org_rel_okved e, (select okved_id b, max(o)+(sum(o)/300)::integer o from res_rel_okved l, r2 where res_id=r group by okved_id limit 10000/*loc_ocnt*/)o5
	where okved_id=b
	union all
	select org_id,(o+50+8*
	coalesce(sum((
		select least(count(id),3)+coalesce(count(d),0)+sum(case
			when t is null then 0
			when t=1 then
				case when v1=v2 then case when v1 between value1 and value2 then 3 else -4 end
				else greatest(-5,trunc(1+((least(value2,v2)-greatest(value1,v1))*4.0/(v2-v1))::integer)) end
			when t=2 then -1
			when t=3 then
				(select coalesce(1+(sum(1)*3/vc)::integer,-5) from orrd_value,s0 where orrd_id=e.id and h=i and vp=rdv_id)
			else 0 end)
		from orr_rel_res_descr e left join d0 on d=rd_id and s=r where orr_id=l.id
	)),0))::integer o
	from org_rel_res l, r2 where type=1 and res_id=r
	group by org_id,r,o
)w2 group by org_id

EXPLAIN

Код: plsql

Insert on pg_temp_8.tmp_seller_allorg  (cost=4728287.45..4728328.95 rows=200 width=8) (actual time=7261.642..7261.642 rows=0 loops=1)
  Buffers: shared hit=5943, local hit=709933 read=3116 dirtied=3114 written=3114
  CTE r0
    ->  Seq Scan on msn.org_rel_res  (cost=0.00..3.90 rows=1 width=8) (actual time=0.020..0.031 rows=2 loops=1)
          Output: org_rel_res.id, org_rel_res.res_id
          Filter: ((org_rel_res.id = ANY ('{7,82}'::integer[])) AND (org_rel_res.type = 2) AND (org_rel_res.org_id = 1))
          Rows Removed by Filter: 25
          Buffers: shared hit=1
  CTE d0
    ->  Hash Join  (cost=0.30..10.30 rows=1 width=240) (actual time=0.087..0.093 rows=1 loops=1)
          Output: r0.r, d.rd_id, (SubPlan 2), d.id, LEAST(d.value1, d.value2), GREATEST(d.value1, d.value2), d.values", (SubPlan 3)"
          Hash Cond: (d.orr_id = r0.i)
          Buffers: shared hit=5
          ->  Seq Scan on msn.orr_rel_res_descr d  (cost=0.00..2.50 rows=15 width=240) (actual time=0.003..0.007 rows=15 loops=1)
                Output: d.id, d.rd_id, d.orr_id, d.date_in, d.user_in, d.value1, d.value2, d.values""
                Buffers: shared hit=1
          ->  Hash  (cost=0.20..0.20 rows=1 width=8) (actual time=0.007..0.007 rows=2 loops=1)
                Output: r0.r, r0.i
                Buckets: 1024  Batches: 1  Memory Usage: 1kB
                ->  CTE Scan on r0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.002..0.003 rows=2 loops=1)
                      Output: r0.r, r0.i
          SubPlan 2
            ->  Index Scan using res_description_id_pk on msn.res_description  (cost=0.28..4.39 rows=1 width=4) (actual time=0.020..0.021 rows=1 loops=1)
                  Output: res_description.type
                  Index Cond: (res_description.id = d.rd_id)
                  Buffers: shared hit=3
          SubPlan 3
            ->  Aggregate  (cost=2.85..2.95 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)
                  Output: count(orrd_value.id)
                  Buffers: shared hit=1
                  ->  Seq Scan on msn.orrd_value  (cost=0.00..2.85 rows=1 width=4) (actual time=0.010..0.012 rows=1 loops=1)
                        Output: orrd_value.id, orrd_value.orrd_id, orrd_value.rdv_id, orrd_value.date_in, orrd_value.user_in
                        Filter: (orrd_value.orrd_id = d.id)
                        Rows Removed by Filter: 17
                        Buffers: shared hit=1
  CTE s0
    ->  Hash Join  (cost=0.30..3.27 rows=1 width=8) (actual time=0.031..0.037 rows=1 loops=1)
          Output: d0.i, orrd_value_1.rdv_id
          Hash Cond: (orrd_value_1.orrd_id = d0.i)
          Buffers: shared hit=1
          ->  Seq Scan on msn.orrd_value orrd_value_1  (cost=0.00..2.80 rows=18 width=8) (actual time=0.002..0.008 rows=18 loops=1)
                Output: orrd_value_1.id, orrd_value_1.orrd_id, orrd_value_1.rdv_id, orrd_value_1.date_in, orrd_value_1.user_in
                Buffers: shared hit=1
          ->  Hash  (cost=0.20..0.20 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)
                Output: d0.i
                Buckets: 1024  Batches: 1  Memory Usage: 1kB
                ->  CTE Scan on d0  (cost=0.00..0.20 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)
                      Output: d0.i
  CTE r2
    ->  Append  (cost=4257.86..5235.30 rows=4443 width=8) (actual time=1.534..2.770 rows=163 loops=1)
          Buffers: shared hit=77
          ->  Subquery Scan on r1  (cost=4257.86..5235.10 rows=4442 width=8) (actual time=1.533..2.667 rows=161 loops=1)
                Output: r1.r, (1000 - CASE WHEN (r1.c = 0) THEN (r1.o << 3) WHEN (r1.c > 3) THEN ((((r1.o)::numeric * (1.0 - (2.0 / ((r1.a - r1.o))::numeric))))::integer << 3) ELSE ((((r1.o)::numeric * (1.0 - (1.0 / ((r1.a - r1.o))::numeric))))::integer << (...)
                Buffers: shared hit=76
                ->  HashAggregate  (cost=4257.86..4790.90 rows=4442 width=12) (actual time=1.527..2.432 rows=161 loops=1)
                      Output: b.res_id, ((min(GREATEST(0, (o4.o - o4.c))) << 1) + 1), (((avg(GREATEST(0, (o4.o - o4.c))))::integer << 1) + 4), ((count(b.res_id))::integer - 1)
                      Buffers: shared hit=76
                      ->  Merge Join  (cost=2044.73..4116.33 rows=9435 width=12) (actual time=0.653..1.101 rows=161 loops=1)
                            Output: b.res_id, o4.o, o4.c
                            Merge Cond: (b.okved_id = o4.i)
                            Buffers: shared hit=76
                            ->  Index Only Scan using buyer_okved_rel_res_okved_res_id on msn.buyer_okved_rel_res b  (cost=0.29..1057.63 rows=9435 width=8) (actual time=0.046..0.158 rows=162 loops=1)
                                  Output: b.okved_id, b.res_id
                                  Heap Fetches: 4
                                  Buffers: shared hit=5
                            ->  Sort  (cost=2044.44..2044.94 rows=200 width=12) (actual time=0.599..0.677 rows=187 loops=1)
                                  Output: o4.o, o4.c, o4.i
                                  Sort Key: o4.i
                                  Sort Method: quicksort  Memory: 26kB
                                  Buffers: shared hit=71
                                  ->  Subquery Scan on o4  (cost=1995.30..2036.80 rows=200 width=12) (actual time=0.491..0.552 rows=27 loops=1)
                                        Output: o4.o, o4.c, o4.i
                                        Buffers: shared hit=71
                                        ->  HashAggregate  (cost=1995.30..2016.80 rows=200 width=8) (actual time=0.489..0.548 rows=27 loops=1)
                                              Output: o1_1.i, (min(o1_1.o) << 2), ((count(o1_1.o))::integer - 1)
                                              Buffers: shared hit=71
                                              ->  Append  (cost=1689.64..1888.44 rows=994 width=8) (actual time=0.061..0.427 rows=27 loops=1)
                                                    Buffers: shared hit=71
                                                    CTE o0
                                                      ->  Index Scan using org_rel_okved_id_pk on msn.org_rel_okved  (cost=0.43..4.54 rows=1 width=8) (actual time=0.028..0.030 rows=1 loops=1)
                                                            Output: org_rel_okved.okved_id, org_rel_okved.ord
                                                            Index Cond: (org_rel_okved.id = ANY ('{15054388}'::integer[]))
                                                            Filter: (org_rel_okved.org_id = 1)
                                                            Buffers: shared hit=4
                                                    CTE o1
                                                      ->  Recursive Union  (cost=0.28..413.89 rows=101 width=12) (actual time=0.056..0.108 rows=3 loops=1)
                                                            Buffers: shared hit=13
                                                            ->  Nested Loop  (cost=0.28..4.69 rows=1 width=12) (actual time=0.054..0.058 rows=1 loops=1)
                                                                  Output: okved.id, okved.parent_id, o0.o
                                                                  Buffers: shared hit=7
                                                                  ->  CTE Scan on o0  (cost=0.00..0.20 rows=1 width=8) (actual time=0.032..0.034 rows=1 loops=1)
                                                                        Output: o0.i, o0.o
                                                                        Buffers: shared hit=4
                                                                  ->  Index Scan using okved_pk on msn.okved  (cost=0.28..4.39 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1)
                                                                        Output: okved.id, okved.parent_id, okved.strcode, okved.name, okved.date_in, okved.user_in
                                                                        Index Cond: (okved.id = o0.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..38.90 rows=10 width=12) (actual time=0.011..0.012 rows=1 loops=3)
                                                                  Output: okved_1.id, okved_1.parent_id, (o1.o + 1)
                                                                  Buffers: shared hit=6
                                                                  ->  WorkTable Scan on o1  (cost=0.00..2.00 rows=10 width=8) (actual time=0.002..0.002 rows=1 loops=3)
                                                                        Output: o1.i, o1.p, o1.o
                                                                  ->  Index Scan using okved_pk on msn.okved okved_1  (cost=0.28..3.59 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=3)
                                                                        Output: okved_1.id, okved_1.parent_id, okved_1.strcode, okved_1.name, okved_1.date_in, okved_1.user_in
                                                                        Index Cond: (okved_1.id = o1.p)
                                                                        Buffers: shared hit=6
                                                    CTE o2
                                                      ->  Recursive Union  (cost=0.28..1271.21 rows=893 width=12) (actual time=0.018..0.261 rows=24 loops=1)
                                                            Buffers: shared hit=58
                                                            ->  Nested Loop  (cost=0.28..7.02 rows=3 width=12) (actual time=0.016..0.023 rows=3 loops=1)
                                                                  Output: okved_2.id, okved_2.parent_id, o0_1.o
                                                                  Buffers: shared hit=3
                                                                  ->  CTE Scan on o0 o0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)
                                                                        Output: o0_1.i, o0_1.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_2  (cost=0.28..6.52 rows=3 width=8) (actual time=0.011..0.014 rows=3 loops=1)
                                                                        Output: okved_2.id, okved_2.parent_id, okved_2.strcode, okved_2.name, okved_2.date_in, okved_2.user_in
                                                                        Index Cond: (okved_2.parent_id = o0_1.i)
                                                                        Buffers: shared hit=3
                                                            ->  Nested Loop  (cost=0.28..108.56 rows=89 width=12) (actual time=0.039..0.068 rows=7 loops=3)
                                                                  Output: okved_3.id, okved_3.parent_id, o2.o
                                                                  Buffers: shared hit=55
                                                                  ->  WorkTable Scan on o2  (cost=0.00..6.00 rows=30 width=8) (actual time=0.001..0.004 rows=8 loops=3)
                                                                        Output: o2.i, o2.p, o2.o
                                                                  ->  Index Scan using okved_parent on msn.okved okved_3  (cost=0.28..3.12 rows=3 width=8) (actual time=0.005..0.006 rows=1 loops=24)
                                                                        Output: okved_3.id, okved_3.parent_id, okved_3.strcode, okved_3.name, okved_3.date_in, okved_3.user_in
                                                                        Index Cond: (okved_3.parent_id = o2.i)
                                                                        Buffers: shared hit=55
                                                    ->  CTE Scan on o1 o1_1  (cost=0.00..20.20 rows=101 width=8) (actual time=0.059..0.116 rows=3 loops=1)
                                                          Output: o1_1.i, o1_1.o
                                                          Buffers: shared hit=13
                                                    ->  CTE Scan on o2 o2_1  (cost=0.00..178.60 rows=893 width=8) (actual time=0.021..0.298 rows=24 loops=1)
                                                          Output: o2_1.i, o2_1.o
                                                          Buffers: shared hit=58
          ->  CTE Scan on r0 r0_1  (cost=0.00..0.20 rows=1 width=8) (actual time=0.025..0.039 rows=2 loops=1)
                Output: r0_1.r, 1080
                Buffers: shared hit=1
  ->  HashAggregate  (cost=4723034.68..4723056.18 rows=200 width=8) (actual time=4452.255..5526.364 rows=703710 loops=1)
        Output: e.org_id, (max(((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3))))) + ((sum(((((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3))))) / 300))::integer)
        Buffers: shared hit=5943
        ->  Append  (cost=9320.41..4616072.15 rows=14261671 width=8) (actual time=6.403..2428.554 rows=1100643 loops=1)
              Buffers: shared hit=5943
              ->  Nested Loop  (cost=9320.41..3186670.88 rows=14261316 width=12) (actual time=6.402..2001.744 rows=1100641 loops=1)
                    Output: e.org_id, (((max(r2.o) + ((sum(r2.o) / 300))::integer)) + (300 / (e.ord + 3)))
                    Buffers: shared hit=5933
                    ->  Limit  (cost=9319.85..9497.01 rows=1648 width=8) (actual time=6.355..7.402 rows=179 loops=1)
                          Output: l.okved_id, ((max(r2.o) + ((sum(r2.o) / 300))::integer))
                          Buffers: shared hit=410
                          ->  HashAggregate  (cost=9319.85..9497.01 rows=1648 width=8) (actual time=6.354..7.184 rows=179 loops=1)
                                Output: l.okved_id, (max(r2.o) + ((sum(r2.o) / 300))::integer)
                                Buffers: shared hit=410
                                ->  Nested Loop  (cost=0.29..9089.39 rows=30728 width=8) (actual time=1.562..5.427 rows=732 loops=1)
                                      Output: l.okved_id, r2.o
                                      Buffers: shared hit=410
                                      ->  CTE Scan on r2  (cost=0.00..888.60 rows=4443 width=8) (actual time=1.538..3.072 rows=163 loops=1)
                                            Output: r2.r, r2.o
                                            Buffers: shared hit=77
                                      ->  Index Only Scan using res_rel_okved_resokved on msn.res_rel_okved l  (cost=0.29..1.15 rows=7 width=8) (actual time=0.007..0.010 rows=4 loops=163)
                                            Output: l.res_id, l.okved_id
                                            Index Cond: (l.res_id = r2.r)
                                            Heap Fetches: 0
                                            Buffers: shared hit=333
                    ->  Index Only Scan using org_rel_okved_org_okved on msn.org_rel_okved e  (cost=0.56..997.49 rows=8654 width=12) (actual time=0.032..4.676 rows=6149 loops=179)
                          Output: e.okved_id, e.org_id, e.ord
                          Index Cond: (e.okved_id = l.okved_id)
                          Heap Fetches: 0
                          Buffers: shared hit=5523
              ->  Subquery Scan on *SELECT* 2"  (cost=3194.23..3269.67 rows=355 width=8) (actual time=0.495..0.499 rows=2 loops=1)"
                    Output: *SELECT* 2".org_id, "*SELECT* 2".o"
                    Buffers: shared hit=10
                    ->  HashAggregate  (cost=3194.23..3234.17 rows=355 width=16) (actual time=0.494..0.498 rows=2 loops=1)
                          Output: l_1.org_id, ((((r2_1.o + 50))::double precision + (8::double precision * COALESCE(sum((SubPlan 11)), 0::double precision))))::integer, r2_1.r, r2_1.o
                          Buffers: shared hit=10
                          ->  Hash Join  (cost=5.41..968.38 rows=355 width=16) (actual time=0.213..0.217 rows=2 loops=1)
                                Output: l_1.org_id, l_1.id, r2_1.r, r2_1.o
                                Hash Cond: (r2_1.r = l_1.res_id)
                                Buffers: shared hit=1
                                ->  CTE Scan on r2 r2_1  (cost=0.00..888.60 rows=4443 width=8) (actual time=0.003..0.067 rows=163 loops=1)
                                      Output: r2_1.r, r2_1.o
                                ->  Hash  (cost=3.77..3.77 rows=16 width=12) (actual time=0.046..0.046 rows=16 loops=1)
                                      Output: l_1.org_id, l_1.id, l_1.res_id
                                      Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                      Buffers: shared hit=1
                                      ->  Seq Scan on msn.org_rel_res l_1  (cost=0.00..3.77 rows=16 width=12) (actual time=0.014..0.030 rows=16 loops=1)
                                            Output: l_1.org_id, l_1.id, l_1.res_id
                                            Filter: (l_1.type = 1)
                                            Rows Removed by Filter: 11
                                            Buffers: shared hit=1
                          SubPlan 11
                            ->  Aggregate  (cost=6.15..6.26 rows=1 width=74) (actual time=0.121..0.121 rows=1 loops=2)
                                  Output: (((LEAST(count(e_1.id), 3::bigint) + COALESCE(count(d0_1.d), 0::bigint)))::double precision + sum(CASE WHEN (d0_1.t IS NULL) THEN 0::double precision WHEN (d0_1.t = 1) THEN CASE WHEN (d0_1.v1 = d0_1.v2) THEN (CASE  (...)
                                  Buffers: shared hit=9
                                  ->  Nested Loop Left Join  (cost=0.00..2.84 rows=1 width=74) (actual time=0.067..0.072 rows=1 loops=2)
                                        Output: e_1.id, e_1.value1, e_1.value2, d0_1.d, d0_1.t, d0_1.v1, d0_1.v2, d0_1.vc, d0_1.i
                                        Join Filter: (d0_1.d = e_1.rd_id)
                                        Rows Removed by Join Filter: 0
                                        Buffers: shared hit=7
                                        ->  Seq Scan on msn.orr_rel_res_descr e_1  (cost=0.00..2.54 rows=1 width=18) (actual time=0.009..0.011 rows=1 loops=2)
                                              Output: e_1.id, e_1.rd_id, e_1.orr_id, e_1.date_in, e_1.user_in, e_1.value1, e_1.value2, e_1.values""
                                              Filter: (e_1.orr_id = l_1.id)
                                              Rows Removed by Filter: 14
                                              Buffers: shared hit=2
                                        ->  CTE Scan on d0 d0_1  (cost=0.00..0.20 rows=1 width=60) (actual time=0.046..0.050 rows=1 loops=2)
                                              Output: d0_1.s, d0_1.d, d0_1.t, d0_1.i, d0_1.v1, d0_1.v2, d0_1.vs, d0_1.vc
                                              Filter: (d0_1.s = r2_1.r)
                                              Buffers: shared hit=5
                                  SubPlan 10
                                    ->  Aggregate  (cost=3.15..3.26 rows=1 width=0) (actual time=0.066..0.066 rows=1 loops=1)
                                          Output: COALESCE((1 + (((sum(1) * 3) / d0_1.vc))::integer), (-5))
                                          Buffers: shared hit=2
                                          ->  Nested Loop  (cost=0.00..3.15 rows=1 width=0) (actual time=0.058..0.058 rows=0 loops=1)
                                                Join Filter: (orrd_value_2.rdv_id = s0.vp)
                                                Rows Removed by Join Filter: 1
                                                Buffers: shared hit=2
                                                ->  Seq Scan on msn.orrd_value orrd_value_2  (cost=0.00..2.85 rows=1 width=4) (actual time=0.009..0.011 rows=1 loops=1)
                                                      Output: orrd_value_2.id, orrd_value_2.orrd_id, orrd_value_2.rdv_id, orrd_value_2.date_in, orrd_value_2.user_in
                                                      Filter: (orrd_value_2.orrd_id = e_1.id)
                                                      Rows Removed by Filter: 17
                                                      Buffers: shared hit=1
                                                ->  CTE Scan on s0  (cost=0.00..0.20 rows=1 width=4) (actual time=0.035..0.041 rows=1 loops=1)
                                                      Output: s0.h, s0.vp
                                                      Filter: (s0.h = d0_1.i)
                                                      Buffers: shared hit=1
Total runtime: 7262.752 ms

Далее можно было бы сделать CREATE INDEX tmp_seller_allorg_idx ON tmp_seller_allorg USING btree (z,o); vacuum tmp_seller_allorg; что позволило бы сохранить память и не выполнять операцию сортировки (будет использоваться Index Only Scan и ускорится на 1с) в дальнейшем, но увеличит общее время выполнения на 1с. Конечно, temp_buffers надо было бы также увеличить на размер индекса.
Но к сожалению vacuum не делается из функции, а у нас все эти действия выполняются в одном месте - в функции pg/sql.

Выводим результат - 13694мс

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.

EXPLAIN (ANALYZE on, VERBOSE on, COSTS on, BUFFERS on, TIMING on)
select i,z,o,1+((row_number()over()-1)/100/*inorgonlist*/) p from (
	select i,z,o from (
		select a.is_sale i, a.org_id z, ((o*a.weight)*(0.5+500.0/(500
			+(coalesce((abs(a.latitude-r.latitude)/15000+least(abs(a.longitude-r.longitude+180000000)%180000000,abs(r.longitude-a.longitude+180000000)%180000000)/16500),5000)))))::integer o
		from org_address a, tmp_seller_allorg w3, org_address r
		where a.org_id=z and r.id=any(array[997192]/*inadr_ids*/) and r.org_id=1/*inorg_id*/
	)w4 order by o desc limit 1000
)w5 where o<>1/*inorg_id*/ and i

EXPLAIN

Код: plsql

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.

WindowAgg  (cost=945275.85..947420.35 rows=9950 width=9) (actual time=13662.972..13694.305 rows=10000 loops=1)
  Output: w5.i, w5.z, w5.o, (1 + ((row_number() OVER (?) - 1) / 100))
  Buffers: shared hit=66377, local read=3114
  ->  Subquery Scan on w5  (cost=945275.85..946325.85 rows=9950 width=9) (actual time=13662.955..13677.016 rows=10000 loops=1)
        Output: w5.i, w5.z, w5.o
        Filter: (w5.i AND (w5.o <> 1))
        Buffers: shared hit=66377, local read=3114
        ->  Limit  (cost=945275.85..945300.85 rows=10000 width=29) (actual time=13662.945..13669.888 rows=10000 loops=1)
              Output: a.is_sale, a.org_id, (((((w3.o * a.weight))::numeric * (0.5 + (500.0 / ((500 + COALESCE(((abs((a.latitude - r.latitude)) / 15000) + (LEAST((abs(((a.longitude - r.longitude) + 180000000)) % 180000000), (abs(((r.longitude - a.longitude) (...)
              Buffers: shared hit=66377, local read=3114
              ->  Sort  (cost=945275.85..946941.84 rows=666396 width=29) (actual time=13662.943..13666.199 rows=10000 loops=1)
                    Output: a.is_sale, a.org_id, (((((w3.o * a.weight))::numeric * (0.5 + (500.0 / ((500 + COALESCE(((abs((a.latitude - r.latitude)) / 15000) + (LEAST((abs(((a.longitude - r.longitude) + 180000000)) % 180000000), (abs(((r.longitude - a.long (...)
                    Sort Key: (((((w3.o * a.weight))::numeric * (0.5 + (500.0 / ((500 + COALESCE(((abs((a.latitude - r.latitude)) / 15000) + (LEAST((abs(((a.longitude - r.longitude) + 180000000)) % 180000000), (abs(((r.longitude - a.longitude) + 180000000) (...)
                    Sort Method: top-N heapsort  Memory: 1237kB
                    Buffers: shared hit=66377, local read=3114
                    ->  Nested Loop  (cost=134215.02..897669.48 rows=666396 width=29) (actual time=1113.853..12785.772 rows=703708 loops=1)
                          Output: a.is_sale, a.org_id, ((((w3.o * a.weight))::numeric * (0.5 + (500.0 / ((500 + COALESCE(((abs((a.latitude - r.latitude)) / 15000) + (LEAST((abs(((a.longitude - r.longitude) + 180000000)) % 180000000), (abs(((r.longitude - a (...)
                          Buffers: shared hit=66377, local read=3114
                          ->  Index Scan using org_address_org_search on msn.org_address r  (cost=0.43..4.54 rows=1 width=8) (actual time=0.031..0.035 rows=1 loops=1)
                                Output: r.id, r.org_id, r.latitude, r.longitude, r.is_sale, r.name, r.others, r.date_in, r.user_in, r.weight
                                Index Cond: (r.org_id = 1)
                                Filter: (r.id = ANY ('{997192}'::integer[]))
                                Buffers: shared hit=4
                          ->  Merge Join  (cost=134214.58..796039.54 rows=666396 width=21) (actual time=1113.768..7651.042 rows=703708 loops=1)
                                Output: a.is_sale, a.org_id, a.weight, a.latitude, a.longitude, w3.o
                                Merge Cond: (a.org_id = w3.z)
                                Buffers: shared hit=66373, local read=3114
                                ->  Index Only Scan using org_address_org_search on msn.org_address a  (cost=0.43..579252.15 rows=5040664 width=17) (actual time=0.020..3134.594 rows=5040093 loops=1)
                                      Output: a.org_id, a.is_sale, a.latitude, a.longitude, a.weight
                                      Heap Fetches: 0
                                      Buffers: shared hit=66373
                                ->  Sort  (cost=134214.15..135880.14 rows=666396 width=8) (actual time=1113.734..1482.823 rows=703710 loops=1)
                                      Output: w3.o, w3.z
                                      Sort Key: w3.z
                                      Sort Method: quicksort  Memory: 50054kB
                                      Buffers: local read=3114
                                      ->  Seq Scan on pg_temp_8.tmp_seller_allorg w3  (cost=0.00..69753.60 rows=666396 width=8) (actual time=0.036..381.671 rows=703710 loops=1)
                                            Output: w3.o, w3.z
                                            Buffers: local read=3114
Total runtime: 13702.426 ms

Итого, общее время 20955 мс (собрана 10к статистика по колонкам, сделан analyze)
Здорово, что сейчас оптимизатор почти не ошибается при подсчете строк при union all, и знает, какой план будет быстрее!

Материализованные вьюхи нам не подходят, т.к. много входных переменных. Если видите, где можно вставить - подскажите...
Пробовал избавиться от сортировки на 50МБ, "set enable_sort to off;": вместо Merge стало Nested и время увеличилось на 5733 мс.

Как еще можно повлиять на общее время выполнения запросов? Есть предложения по оптимизации? :)

Мне кажется, что "org_address a" склеивается с "org_address r" слишком долго - целых 11 секунд. А в первом запросе присоединение (union all) источника на 0.499мс (2 записи) занимает целых 427мс (попробовал переставить источники местами, план поменялся, время - нет. видимо, производится копирование всех строк обоих источников данных, а не Append)

...

Рейтинг:

0 / 0

15.01.2014, 13:24:26

| Ответить | Цитировать | Написать

На пути к идеальному плану

#38526903

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

R_Only <>Далее можно было бы сделать CREATE INDEX tmp_seller_allorg_idx ON tmp_seller_allorg USING btree (z,o); vacuum tmp_seller_allorg; что позволило бы сохранить память и не выполнять операцию сортировки (будет использоваться Index Only Scan и ускорится на 1с) в дальнейшем, но увеличит общее время выполнения на 1с. Конечно, temp_buffers надо было бы также увеличить на размер индекса.
Но к сожалению vacuum не делается из функции, а у нас все эти действия выполняются в одном месте - в функции pg/sql.
<>
зачем вам вакуум в ф-ии ?? Вам (вероятно) нужен ANALYZE (без какого либо "вакуум") - а тот нормально выполняется внутри блока транзакций. Хотя, сразу после создания индекса и он, пожалуй не нужен.

ну а если вы времянку используете без многочисленных апдейтов в ней и прочей логики "многократно-доступа-изменения" - то вам достаточно CTE.
с теми же параметрами (т.е. SET) транзакции CTE не должен ничем отличаться от времянки, за искючением того, что его можно и на стендбае выполнить.

...

Рейтинг:

0 / 0

15.01.2014, 13:48:09

| Ответить | Цитировать | Написать

На пути к идеальному плану

#38527060

R_Only

Гость

qwwqзачем вам вакуум в ф-ии ?? Вам (вероятно) нужен ANALYZE (без какого либо "вакуум") - а тот нормально выполняется внутри блока транзакций. Хотя, сразу после создания индекса и он, пожалуй не нужен.Одного analyze не хватает(проверено) - index only scan не будет использоваться, т.к. не заполнен bitmap таблицы, а если он не используется, то и индекс не нужен совсем.
qwwqну а если вы времянку используете без многочисленных апдейтов в ней и прочей логики "многократно-доступа-изменения" - то вам достаточно CTE.посмотрите предыдущие запросы. там именно CTE. только это замедляло выполнение.
qwwqс теми же параметрами (т.е. SET) транзакции CTE не должен ничем отличаться от времянки, за искючением того, что его можно и на стендбае выполнить.тем не менее - отличается, обратите внимание на EXPLAIN полного запроса "EXPLAIN PLAN полный, 28286мс". оптимизатор считает, что w3 вернет 200 строк. как его в этом переубедить?

...

Рейтинг:

0 / 0

15.01.2014, 15:02:33

| Ответить | Цитировать | Написать

На пути к идеальному плану

#38527127

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

R_Only <>тем не менее - отличается, обратите внимание на EXPLAIN полного запроса "EXPLAIN PLAN полный, 28286мс". оптимизатор считает, что w3 вернет 200 строк. как его в этом переубедить?
а можно, я не буду вчитываться ?

я только замечу, что странно вы, на поверхностный, cte используете. агрегат - у вас аккурат и не cte, а подзапрос. а cte - это некие (вероятно очень объёмные) фильтры. Что в комбинации со знанием, что cte в постгресе на сегодня - это материализация (а не алиас "вьюхи") - выглядит более чем странно.

лично у меня паттерны такие: там где вам надо фильтрануть до группировки - обычно кладёте фильтр в подзапрос (с тем неудобством, что выписывать его надо столько раз, сколько он встретится в), а там, где что-то реально раз и навсегда посчитали (или необходимо материализовать ещё по каким соображениям) -- можно и в cte пихать -- хуже не станет. Это развязывает руки планировщику (нет необходимости материализовывать 100500 записей, получаемых до группировки из фильтров), но добавляет гемора с собственно написанием -- при множественном вхождении одной и той же конструкции.

...

Рейтинг:

0 / 0

15.01.2014, 15:37:00

| Ответить | Цитировать | Написать

12 сообщений из 12, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / На пути к идеальному плану

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=38527127&tid=1998894]:	0ms
get settings:	10ms
get forum list:	20ms
check forum access:	5ms
check topic access:	5ms
track hit:	212ms
get topic data:	12ms
get forum data:	3ms
get page messages:	77ms
get tp. blocked users:	2ms
others:	223ms

total:	569ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы