Всем привет.
После восстановления базы, пользователи стали жаловаться что все тормозит. Виновником оказался такой запрос:
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.
SELECT
t1.*,
COALESCE(av.purchase_price_average, CAST(10000 - ecd1_extra_discount AS BIGINT) * b_base_price / 10000) AS purchase_price_average
FROM (
SELECT
ab.application_id,
ab.book_id,
ab.sale_price,
ab.sale_price_wd,
ab.purchase_price AS purchase_cost_app,
CAST(10000 - ecd.extra_discount AS BIGINT) * b.base_price / 10000 AS purchase_cost_now,
CAST(10000 - ecd1.extra_discount AS BIGINT) * b.base_price / 10000 AS purchase_cost_org,
ecd1.extra_discount AS ecd1_extra_discount,
b.base_price AS b_base_price
FROM application_books ab
JOIN applications a ON (a.application_id = ab.application_id)
JOIN books b ON (b.book_id = ab.book_id)
JOIN publishers p ON (p.publisher_id = b.publisher_id)
JOIN mv_extra_charge_for_all_departments ecd ON (ecd.target_department_id = a.homedep_id and ecd.publisher_id = p.publisher_id)
JOIN mv_extra_charge_for_all_departments ecd1 ON (ecd1.target_department_id = 1 and ecd1.publisher_id = p.publisher_id)
WHERE ab.application_id IN (46071)
) t1
LEFT JOIN (
SELECT book_id, SUM(purchase_price) / COUNT(*) AS purchase_price_average FROM book_instances
WHERE book_status = 100
AND book_id IN (SELECT book_id FROM application_books WHERE application_id IN (46071) GROUP BY book_id)
GROUP BY book_id
) av ON (av.book_id = t1.book_id)
Время его выполнения 29531 мс, причем если запустить то же запрос на старой базы, то время выполнения 70мс. Старая база поднята на том же PostgreSQL сервере, но под другим именем.
Новый план выгядит вот так: 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.
"Nested Loop (cost=826.53..938.52 rows=1 width=40) (actual time=4716.526..29507.218 rows=13 loops=1)"
" Output: ab.application_id, ab.book_id, ab.sale_price, ab.sale_price_wd, ab.purchase_price, ((((10000 - ecd.extra_discount))::bigint * b.base_price) / 10000), ((((10000 - ecd1.extra_discount))::bigint * b.base_price) / 10000), ecd1.extra_discount, b.base_ (...)"
" Join Filter: ((ab.book_id = b.book_id) AND (p.publisher_id = b.publisher_id))"
" Rows Removed by Join Filter: 328328"
" Buffers: shared hit=1813618"
" -> Nested Loop Left Join (cost=826.24..930.58 rows=1 width=48) (actual time=39.111..29415.222 rows=754 loops=1)"
" Output: ab.application_id, ab.book_id, ab.sale_price, ab.sale_price_wd, ab.purchase_price, ecd.extra_discount, ecd.publisher_id, p.publisher_id, ecd1.extra_discount, ecd1.publisher_id, ((sum(book_instances.purchase_price) / count(*)))"
" Join Filter: (book_instances.book_id = ab.book_id)"
" Rows Removed by Join Filter: 5568"
" Buffers: shared hit=1740090"
" -> Nested Loop (cost=0.85..61.51 rows=1 width=40) (actual time=0.251..3.092 rows=754 loops=1)"
" Output: ab.application_id, ab.book_id, ab.sale_price, ab.sale_price_wd, ab.purchase_price, ecd.extra_discount, ecd.publisher_id, p.publisher_id, ecd1.extra_discount, ecd1.publisher_id"
" Buffers: shared hit=1645"
" -> Nested Loop (cost=0.43..49.51 rows=1 width=24) (actual time=0.237..1.180 rows=58 loops=1)"
" Output: a.application_id, ecd.extra_discount, ecd.publisher_id, p.publisher_id, ecd1.extra_discount, ecd1.publisher_id"
" Buffers: shared hit=145"
" -> Nested Loop (cost=0.29..49.36 rows=1 width=20) (actual time=0.231..0.899 rows=58 loops=1)"
" Output: a.application_id, ecd.extra_discount, ecd.publisher_id, ecd1.extra_discount, ecd1.publisher_id"
" Join Filter: (ecd.publisher_id = ecd1.publisher_id)"
" Rows Removed by Join Filter: 3306"
" Buffers: shared hit=29"
" -> Nested Loop (cost=0.29..29.48 rows=9 width=12) (actual time=0.169..0.309 rows=58 loops=1)"
" Output: a.application_id, ecd.extra_discount, ecd.publisher_id"
" Join Filter: (a.homedep_id = ecd.target_department_id)"
" Rows Removed by Join Filter: 1682"
" Buffers: shared hit=16"
" -> Index Scan using applications_idx_application_id on public.applications a (cost=0.29..8.29 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)"
" Output: a.application_id, a.application_status, a.homedep_id, a.customer_id, a.manager_id, a.creation_date, a.delivery_date, a.discount_percent, a.absolute_cost, a.comments, a.application_priority, a.activation_date, a (...)"
" Index Cond: (a.application_id = 46071)"
" Buffers: shared hit=3"
" -> Seq Scan on public.mv_extra_charge_for_all_departments ecd (cost=0.00..14.82 rows=1820 width=12) (actual time=0.004..0.142 rows=1740 loops=1)"
" Output: ecd.target_department_id, ecd.publisher_id, ecd.discount, ecd.extra_charge, ecd.ecdep_id, ecd.extra_discount, ecd.disdep_id"
" Buffers: shared hit=13"
" -> Materialize (cost=0.00..19.42 rows=9 width=8) (actual time=0.001..0.005 rows=58 loops=58)"
" Output: ecd1.extra_discount, ecd1.publisher_id"
" Buffers: shared hit=13"
" -> Seq Scan on public.mv_extra_charge_for_all_departments ecd1 (cost=0.00..19.37 rows=9 width=8) (actual time=0.057..0.122 rows=58 loops=1)"
" Output: ecd1.extra_discount, ecd1.publisher_id"
" Filter: (ecd1.target_department_id = 1)"
" Rows Removed by Filter: 1682"
" Buffers: shared hit=13"
" -> Index Only Scan using publishers_idx_publisher_id on public.publishers p (cost=0.14..0.15 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=58)"
" Output: p.publisher_id"
" Index Cond: (p.publisher_id = ecd.publisher_id)"
" Heap Fetches: 58"
" Buffers: shared hit=116"
" -> Index Scan using application_books_idx_application_id on public.application_books ab (cost=0.42..11.99 rows=7 width=20) (actual time=0.010..0.026 rows=13 loops=58)"
" Output: ab.application_id, ab.book_id, ab.base_price, ab.sale_price, ab.paid_count, ab.free_count, ab.purchase_price, ab.purchase_price_date, ab.sale_price_wd, ab.log_create_date, ab.log_create_manager, ab.log_update_date, ab.log_update (...)"
" Index Cond: (ab.application_id = 46071)"
" Buffers: shared hit=1500"
" -> GroupAggregate (cost=825.40..869.03 rows=9 width=8) (actual time=5.779..39.004 rows=8 loops=754)"
" Output: book_instances.book_id, (sum(book_instances.purchase_price) / count(*))"
" Buffers: shared hit=1738445"
" -> Nested Loop (cost=825.40..868.93 rows=9 width=8) (actual time=2.890..38.981 rows=99 loops=754)"
" Output: book_instances.book_id, book_instances.purchase_price"
" Buffers: shared hit=1738445"
" -> Group (cost=0.42..8.47 rows=1 width=4) (actual time=0.004..0.031 rows=13 loops=754)"
" Output: application_books.book_id"
" Buffers: shared hit=16309"
" -> Index Only Scan using application_books_idx_application_id_book_id on public.application_books (cost=0.42..8.45 rows=7 width=4) (actual time=0.004..0.024 rows=13 loops=754)"
" Output: application_books.application_id, application_books.book_id"
" Index Cond: (application_books.application_id = 46071)"
" Heap Fetches: 14800"
" Buffers: shared hit=16309"
" -> Bitmap Heap Scan on public.book_instances (cost=824.98..860.46 rows=9 width=8) (actual time=2.989..2.991 rows=8 loops=9802)"
" Output: book_instances.book_instance_id, book_instances.book_id, book_instances.invoice_id, book_instances.storage_id, book_instances.book_status, book_instances.application_id, book_instances.purchase_price, book_instances.sale_p (...)"
" Recheck Cond: ((book_instances.book_id = application_books.book_id) AND (book_instances.book_status = 100))"
" Buffers: shared hit=1722136"
" -> BitmapAnd (cost=824.98..824.98 rows=9 width=0) (actual time=2.987..2.987 rows=0 loops=9802)"
" Buffers: shared hit=1704794"
" -> Bitmap Index Scan on book_instances_idx_book_id (cost=0.00..5.30 rows=292 width=0) (actual time=0.133..0.133 rows=2547 loops=9802)"
" Index Cond: (book_instances.book_id = application_books.book_id)"
" Buffers: shared hit=97266"
" -> Bitmap Index Scan on book_instances_idx_book_status (cost=0.00..805.04 rows=57536 width=0) (actual time=2.825..2.825 rows=58858 loops=9802)"
" Index Cond: (book_instances.book_status = 100)"
" Buffers: shared hit=1607528"
" -> Index Scan using books_idx_publisher_id on public.books b (cost=0.29..5.25 rows=443 width=12) (actual time=0.006..0.094 rows=435 loops=754)"
" Output: b.book_id, b.publisher_id, b.article, b.book_name, b.book_name_original, b.edition, b.edition_year, b.base_price, b.not_published, b.is_manual, b.manual_id, b.weight, b.units_in_box, b.create_date, b.bit_flags, b.rating, b.book_name_short, (...)"
" Index Cond: (b.publisher_id = ecd.publisher_id)"
" Buffers: shared hit=73528"
"Total runtime: 29507.333 ms"
Старый план такой:
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.
"Nested Loop (cost=1074.01..1151.83 rows=1 width=40) (actual time=15.955..52.837 rows=13 loops=1)"
" Output: ab.application_id, ab.book_id, ab.sale_price, ab.sale_price_wd, ab.purchase_price, ((((10000 - ecd.extra_discount))::bigint * b.base_price) / 10000), ((((10000 - ecd1.extra_discount))::bigint * b.base_price) / 10000), COALESCE(av.purchase_price_a (...)"
" Buffers: shared hit=10718"
" -> Nested Loop (cost=1073.87..1151.65 rows=1 width=52) (actual time=15.939..52.771 rows=13 loops=1)"
" Output: ab.application_id, ab.book_id, ab.sale_price, ab.sale_price_wd, ab.purchase_price, b.base_price, b.publisher_id, ecd.extra_discount, ecd.publisher_id, ecd1.extra_discount, ecd1.publisher_id, av.purchase_price_average"
" Join Filter: (ab.book_id = b.book_id)"
" Rows Removed by Join Filter: 327600"
" Buffers: shared hit=10692"
" -> Nested Loop (cost=0.57..55.37 rows=52 width=32) (actual time=0.262..10.927 rows=25201 loops=1)"
" Output: a.application_id, ecd.extra_discount, ecd.publisher_id, b.base_price, b.book_id, b.publisher_id, ecd1.extra_discount, ecd1.publisher_id"
" Buffers: shared hit=6140"
" -> Nested Loop (cost=0.29..49.36 rows=1 width=20) (actual time=0.253..0.665 rows=58 loops=1)"
" Output: a.application_id, ecd.extra_discount, ecd.publisher_id, ecd1.extra_discount, ecd1.publisher_id"
" Join Filter: (ecd.publisher_id = ecd1.publisher_id)"
" Rows Removed by Join Filter: 3306"
" Buffers: shared hit=29"
" -> Nested Loop (cost=0.29..29.48 rows=9 width=12) (actual time=0.191..0.220 rows=58 loops=1)"
" Output: a.application_id, ecd.extra_discount, ecd.publisher_id"
" Join Filter: (a.homedep_id = ecd.target_department_id)"
" Rows Removed by Join Filter: 1682"
" Buffers: shared hit=16"
" -> Index Scan using applications_idx_application_id on public.applications a (cost=0.29..8.29 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)"
" Output: a.application_id, a.application_status, a.homedep_id, a.customer_id, a.manager_id, a.creation_date, a.delivery_date, a.discount_percent, a.absolute_cost, a.comments, a.application_priority, a.activation_date, a.appli (...)"
" Index Cond: (a.application_id = 46071)"
" Buffers: shared hit=3"
" -> Seq Scan on public.mv_extra_charge_for_all_departments ecd (cost=0.00..14.82 rows=1820 width=12) (actual time=0.003..0.093 rows=1740 loops=1)"
" Output: ecd.target_department_id, ecd.publisher_id, ecd.discount, ecd.extra_charge, ecd.ecdep_id, ecd.extra_discount, ecd.disdep_id"
" Buffers: shared hit=13"
" -> Materialize (cost=0.00..19.42 rows=9 width=8) (actual time=0.001..0.004 rows=58 loops=58)"
" Output: ecd1.extra_discount, ecd1.publisher_id"
" Buffers: shared hit=13"
" -> Seq Scan on public.mv_extra_charge_for_all_departments ecd1 (cost=0.00..19.37 rows=9 width=8) (actual time=0.056..0.123 rows=58 loops=1)"
" Output: ecd1.extra_discount, ecd1.publisher_id"
" Filter: (ecd1.target_department_id = 1)"
" Rows Removed by Filter: 1682"
" Buffers: shared hit=13"
" -> Index Scan using books_idx_publisher_id on public.books b (cost=0.29..5.56 rows=451 width=12) (actual time=0.004..0.106 rows=435 loops=58)"
" Output: b.book_id, b.publisher_id, b.article, b.book_name, b.book_name_original, b.edition, b.edition_year, b.base_price, b.not_published, b.is_manual, b.manual_id, b.weight, b.units_in_box, b.create_date, b.bit_flags, b.rating, b.book_ (...)"
" Index Cond: (b.publisher_id = ecd.publisher_id)"
" Buffers: shared hit=6111"
" -> Materialize (cost=1073.30..1094.11 rows=7 width=28) (actual time=0.000..0.001 rows=13 loops=25201)"
" Output: ab.application_id, ab.book_id, ab.sale_price, ab.sale_price_wd, ab.purchase_price, av.purchase_price_average"
" Buffers: shared hit=4552"
" -> Merge Left Join (cost=1073.30..1094.08 rows=7 width=28) (actual time=7.850..7.862 rows=13 loops=1)"
" Output: ab.application_id, ab.book_id, ab.sale_price, ab.sale_price_wd, ab.purchase_price, av.purchase_price_average"
" Merge Cond: (ab.book_id = av.book_id)"
" Buffers: shared hit=4552"
" -> Index Scan using application_books_idx_application_id_book_id on public.application_books ab (cost=0.42..21.13 rows=7 width=20) (actual time=0.005..0.012 rows=13 loops=1)"
" Output: ab.application_id, ab.book_id, ab.base_price, ab.sale_price, ab.paid_count, ab.free_count, ab.purchase_price, ab.purchase_price_date, ab.sale_price_wd, ab.log_create_date, ab.log_create_manager, ab.log_update_date, ab.log_ (...)"
" Index Cond: (ab.application_id = 46071)"
" Buffers: shared hit=11"
" -> Sort (cost=1072.88..1072.90 rows=11 width=12) (actual time=7.841..7.842 rows=9 loops=1)"
" Output: av.purchase_price_average, av.book_id"
" Sort Key: av.book_id"
" Sort Method: quicksort Memory: 25kB"
" Buffers: shared hit=4541"
" -> Subquery Scan on av (cost=1072.64..1072.69 rows=11 width=12) (actual time=7.831..7.833 rows=9 loops=1)"
" Output: av.purchase_price_average, av.book_id"
" Buffers: shared hit=4541"
" -> HashAggregate (cost=1072.64..1072.67 rows=11 width=8) (actual time=7.830..7.831 rows=9 loops=1)"
" Output: book_instances.book_id, (sum(book_instances.purchase_price) / count(*))"
" Buffers: shared hit=4541"
" -> Nested Loop (cost=17.63..1072.55 rows=11 width=8) (actual time=0.677..7.791 rows=161 loops=1)"
" Output: book_instances.book_id, book_instances.purchase_price"
" Buffers: shared hit=4541"
" -> HashAggregate (cost=12.34..12.34 rows=1 width=4) (actual time=0.018..0.022 rows=13 loops=1)"
" Output: application_books.book_id"
" Buffers: shared hit=11"
" -> Index Scan using application_books_idx_application_id on public.application_books (cost=0.42..12.32 rows=7 width=4) (actual time=0.005..0.012 rows=13 loops=1)"
" Output: application_books.application_id, application_books.book_id, application_books.base_price, application_books.sale_price, application_books.paid_count, application_books.free_count, application (...)"
" Index Cond: (application_books.application_id = 46071)"
" Buffers: shared hit=11"
" -> Bitmap Heap Scan on public.book_instances (cost=5.29..1060.20 rows=11 width=8) (actual time=0.459..0.593 rows=12 loops=13)"
" Output: book_instances.book_instance_id, book_instances.book_id, book_instances.invoice_id, book_instances.storage_id, book_instances.book_status, book_instances.application_id, book_instances.purchase_pric (...)"
" Recheck Cond: (book_instances.book_id = application_books.book_id)"
" Filter: (book_instances.book_status = 100)"
" Rows Removed by Filter: 2481"
" Buffers: shared hit=4530"
" -> Bitmap Index Scan on book_instances_idx_book_id (cost=0.00..5.29 rows=288 width=0) (actual time=0.134..0.134 rows=2494 loops=13)"
" Index Cond: (book_instances.book_id = application_books.book_id)"
" Buffers: shared hit=129"
" -> Index Only Scan using publishers_idx_publisher_id on public.publishers p (cost=0.14..0.15 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=13)"
" Output: p.publisher_id"
" Index Cond: (p.publisher_id = b.publisher_id)"
" Heap Fetches: 13"
" Buffers: shared hit=26"
"Total runtime: 53.005 ms"
Запрос один и тот же, сервер тотже повторяемость 100%
Причем если в новой базе выполнить кажды подзапрос отдельно, то общее время будет такое же как и запроса на старой базе
План первого подзапроса "FROM"
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.
"Nested Loop (cost=12.74..67.82 rows=1 width=32) (actual time=2.702..10.925 rows=13 loops=1)"
" Output: ab.application_id, ab.book_id, ab.sale_price, ab.sale_price_wd, ab.purchase_price, ((((10000 - ecd.extra_discount))::bigint * b.base_price) / 10000), ((((10000 - ecd1.extra_discount))::bigint * b.base_price) / 10000), ecd1.extra_discount, b.base_ (...)"
" Buffers: shared hit=5725"
" -> Hash Join (cost=12.60..67.65 rows=1 width=44) (actual time=2.691..10.888 rows=13 loops=1)"
" Output: ab.application_id, ab.book_id, ab.sale_price, ab.sale_price_wd, ab.purchase_price, b.base_price, b.publisher_id, ecd.extra_discount, ecd.publisher_id, ecd1.extra_discount, ecd1.publisher_id"
" Hash Cond: (b.book_id = ab.book_id)"
" Buffers: shared hit=5699"
" -> Nested Loop (cost=0.57..55.05 rows=51 width=32) (actual time=0.294..9.509 rows=25257 loops=1)"
" Output: a.application_id, ecd.extra_discount, ecd.publisher_id, b.base_price, b.book_id, b.publisher_id, ecd1.extra_discount, ecd1.publisher_id"
" Buffers: shared hit=5685"
" -> Nested Loop (cost=0.29..49.36 rows=1 width=20) (actual time=0.288..0.693 rows=58 loops=1)"
" Output: a.application_id, ecd.extra_discount, ecd.publisher_id, ecd1.extra_discount, ecd1.publisher_id"
" Join Filter: (ecd.publisher_id = ecd1.publisher_id)"
" Rows Removed by Join Filter: 3306"
" Buffers: shared hit=29"
" -> Nested Loop (cost=0.29..29.48 rows=9 width=12) (actual time=0.218..0.255 rows=58 loops=1)"
" Output: a.application_id, ecd.extra_discount, ecd.publisher_id"
" Join Filter: (a.homedep_id = ecd.target_department_id)"
" Rows Removed by Join Filter: 1682"
" Buffers: shared hit=16"
" -> Index Scan using applications_idx_application_id on public.applications a (cost=0.29..8.29 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)"
" Output: a.application_id, a.application_status, a.homedep_id, a.customer_id, a.manager_id, a.creation_date, a.delivery_date, a.discount_percent, a.absolute_cost, a.comments, a.application_priority, a.activation_date, a.appli (...)"
" Index Cond: (a.application_id = 46071)"
" Buffers: shared hit=3"
" -> Seq Scan on public.mv_extra_charge_for_all_departments ecd (cost=0.00..14.82 rows=1820 width=12) (actual time=0.004..0.142 rows=1740 loops=1)"
" Output: ecd.target_department_id, ecd.publisher_id, ecd.discount, ecd.extra_charge, ecd.ecdep_id, ecd.extra_discount, ecd.disdep_id"
" Buffers: shared hit=13"
" -> Materialize (cost=0.00..19.42 rows=9 width=8) (actual time=0.001..0.004 rows=58 loops=58)"
" Output: ecd1.extra_discount, ecd1.publisher_id"
" Buffers: shared hit=13"
" -> Seq Scan on public.mv_extra_charge_for_all_departments ecd1 (cost=0.00..19.37 rows=9 width=8) (actual time=0.066..0.125 rows=58 loops=1)"
" Output: ecd1.extra_discount, ecd1.publisher_id"
" Filter: (ecd1.target_department_id = 1)"
" Rows Removed by Filter: 1682"
" Buffers: shared hit=13"
" -> Index Scan using books_idx_publisher_id on public.books b (cost=0.29..5.25 rows=443 width=12) (actual time=0.003..0.085 rows=435 loops=58)"
" Output: b.book_id, b.publisher_id, b.article, b.book_name, b.book_name_original, b.edition, b.edition_year, b.base_price, b.not_published, b.is_manual, b.manual_id, b.weight, b.units_in_box, b.create_date, b.bit_flags, b.rating, b.book_ (...)"
" Index Cond: (b.publisher_id = ecd.publisher_id)"
" Buffers: shared hit=5656"
" -> Hash (cost=12.00..12.00 rows=7 width=20) (actual time=0.020..0.020 rows=13 loops=1)"
" Output: ab.application_id, ab.book_id, ab.sale_price, ab.sale_price_wd, ab.purchase_price"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" Buffers: shared hit=14"
" -> Index Scan using application_books_idx_application_id on public.application_books ab (cost=0.42..12.00 rows=7 width=20) (actual time=0.006..0.018 rows=13 loops=1)"
" Output: ab.application_id, ab.book_id, ab.sale_price, ab.sale_price_wd, ab.purchase_price"
" Index Cond: (ab.application_id = 46071)"
" Buffers: shared hit=14"
" -> Index Only Scan using publishers_idx_publisher_id on public.publishers p (cost=0.14..0.15 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=13)"
" Output: p.publisher_id"
" Index Cond: (p.publisher_id = b.publisher_id)"
" Heap Fetches: 13"
" Buffers: shared hit=26"
"Total runtime: 11.004 ms"
План вторго подзапроса "LEFT JOIN"
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.
"GroupAggregate (cost=825.40..871.11 rows=9 width=8) (actual time=5.806..38.066 rows=8 loops=1)"
" Output: book_instances.book_id, (sum(book_instances.purchase_price) / count(*))"
" Buffers: shared hit=2298"
" -> Nested Loop (cost=825.40..871.02 rows=9 width=8) (actual time=2.981..38.038 rows=99 loops=1)"
" Output: book_instances.book_id, book_instances.purchase_price"
" Buffers: shared hit=2298"
" -> Group (cost=0.42..10.55 rows=1 width=4) (actual time=0.073..0.096 rows=13 loops=1)"
" Output: application_books.book_id"
" Buffers: shared hit=14"
" -> Index Only Scan using application_books_idx_application_id_book_id on public.application_books (cost=0.42..10.53 rows=7 width=4) (actual time=0.071..0.088 rows=13 loops=1)"
" Output: application_books.application_id, application_books.book_id"
" Index Cond: (application_books.application_id = 46071)"
" Heap Fetches: 11"
" Buffers: shared hit=14"
" -> Bitmap Heap Scan on public.book_instances (cost=824.98..860.46 rows=9 width=8) (actual time=2.912..2.914 rows=8 loops=13)"
" Output: book_instances.book_instance_id, book_instances.book_id, book_instances.invoice_id, book_instances.storage_id, book_instances.book_status, book_instances.application_id, book_instances.purchase_price, book_instances.sale_price, book_i (...)"
" Recheck Cond: ((book_instances.book_id = application_books.book_id) AND (book_instances.book_status = 100))"
" Buffers: shared hit=2284"
" -> BitmapAnd (cost=824.98..824.98 rows=9 width=0) (actual time=2.909..2.909 rows=0 loops=13)"
" Buffers: shared hit=2261"
" -> Bitmap Index Scan on book_instances_idx_book_id (cost=0.00..5.30 rows=292 width=0) (actual time=0.136..0.136 rows=2547 loops=13)"
" Index Cond: (book_instances.book_id = application_books.book_id)"
" Buffers: shared hit=129"
" -> Bitmap Index Scan on book_instances_idx_book_status (cost=0.00..805.04 rows=57536 width=0) (actual time=2.731..2.731 rows=58858 loops=13)"
" Index Cond: (book_instances.book_status = 100)"
" Buffers: shared hit=2132"
"Total runtime: 38.109 ms"
Причем первый подзапрос возвращает 13 строк, а второй 8 строк и этот чертов LEFT JOIN выполняется 29 секунд!
Подскажите куда копать, ничего понять не могу почему изменился план и почему пересечь 13 строк с 8 так медлено работает. |
