Всем привет.
Есть некая sql функция - там простой селект с группировкой.
1.
2.
3.
4.
explain ANALYZE
select * from PE.FF_Payments_Balance() AS D
where 439438381 = D.LINK
AND 1 = D.F_Division
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
HashAggregate (cost=52.49..52.55 rows=2 width=316) (actual time=0.224..0.228 rows=1 loops=1)
" Group Key: d.f_division, d.f_subdivision, d.link, d.f_subscr, d.f_sale_categories, d.f_doc_registers, d.f_doc_types, d.f_operation_types, d.f_status, d.f_debts, d.f_payments, d.c_number, d.c_destination, d.b_closed, st.b_closed, st.b_reversed, d.n_amount, d.n_amount_sec, d.d_reverse_date, d.d_date, d.d_post_date, d.n_period, d.n_dt_period, d.b_import, d.s_sequenceid, d.s_owner, d.s_create_date, d.s_modif_date, d.s_creator, d.link_imp"
-> Nested Loop (cost=0.85..52.32 rows=2 width=214) (actual time=0.176..0.189 rows=2 loops=1)
Join Filter: (d.f_status = st.link)
Rows Removed by Join Filter: 22
-> Seq Scan on fs_status st (cost=0.00..1.12 rows=12 width=6) (actual time=0.012..0.015 rows=12 loops=1)
-> Materialize (cost=0.85..50.84 rows=2 width=212) (actual time=0.006..0.013 rows=2 loops=12)
-> Nested Loop Left Join (cost=0.85..50.83 rows=2 width=212) (actual time=0.068..0.138 rows=2 loops=1)
-> Append (cost=0.42..16.92 rows=2 width=201) (actual time=0.035..0.048 rows=1 loops=1)
-> Index Scan using pk_fd_payments_d1_o on fd_payments_d1_o d (cost=0.42..8.45 rows=1 width=223) (actual time=0.034..0.035 rows=1 loops=1)
Index Cond: (link = 439438381)
" Filter: (b_transaction AND (1 = f_division) AND (d_post_date <= (to_date('20790606'::text, 'YYYYMMDD'::text))::timestamp with time zone))"
-> Index Scan using pk_fd_payments_d1_w on fd_payments_d1_w d_1 (cost=0.43..8.46 rows=1 width=179) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: (link = 439438381)
" Filter: (b_transaction AND (1 = f_division) AND (d_post_date <= (to_date('20790606'::text, 'YYYYMMDD'::text))::timestamp with time zone))"
-> Append (cost=0.43..16.94 rows=2 width=26) (actual time=0.029..0.083 rows=2 loops=1)
-> Index Scan using fd_applications_d1_o_f_payments_d_post_date_f_finances_ex_n_idx on fd_applications_d1_o apl (cost=0.43..8.46 rows=1 width=26) (actual time=0.020..0.057 rows=2 loops=1)
" Index Cond: ((f_payments = d.link) AND (f_payments = 439438381) AND (d_post_date <= (to_date('20790606'::text, 'YYYYMMDD'::text))::timestamp with time zone))"
Filter: ((f_division = 1) AND (f_division = d.f_division) AND (f_subscr = d.f_subscr))
-> Index Scan using fd_applications_d1_w_f_payments_d_post_date_f_finances_ex_n_idx on fd_applications_d1_w apl_1 (cost=0.44..8.47 rows=1 width=26) (actual time=0.013..0.013 rows=0 loops=1)
" Index Cond: ((f_payments = d.link) AND (f_payments = 439438381) AND (d_post_date <= (to_date('20790606'::text, 'YYYYMMDD'::text))::timestamp with time zone))"
Filter: ((f_division = 1) AND (f_division = d.f_division) AND (f_subscr = d.f_subscr))
Planning Time: 5.523 ms
Execution Time: 0.471 ms
Все отлично - cardinality estimation правильное, везде где надо index scan.
Если джойнить эту функцию с таблицей, по которой ожидается небольшое кол-во строк (index scan), то происходит fullscan с hash join, хотя ожидается везде index scan с nested loop:
1.
2.
3.
4.
5.
6.
7.
EXPLAIN
SELECT *
from PE.FD_Debts_Transfers_Documents AS TD
INNER JOIN PE.FF_Payments_Balance() AS D ON TD.F_Payments = D.LINK
AND TD.F_Division = D.F_Division
where TD.F_Division = 1
AND TD.F_Debts_Transfers = 268021
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.
Hash Join (cost=7262597.60..7745501.15 rows=45 width=473)
Hash Cond: (d.link = td.f_payments)
-> GroupAggregate (cost=7262546.99..7691794.19 rows=3577060 width=298)
" Group Key: d.f_debts, d.b_closed, st.b_closed, d.f_subdivision, st.b_reversed, d.b_import, d.f_sale_categories, d.f_status, d.f_operation_types, d.f_doc_types, d.s_sequenceid, d.s_creator, d.s_owner, d.n_period, d.n_dt_period, d.d_reverse_date, d.s_modif_date, d.d_post_date, d.d_date, d.f_payments, d.s_create_date, d.n_amount, d.n_amount_sec, d.f_doc_registers, d.c_destination, d.c_number, d.f_subscr, d.link_imp, d.link, d.f_division"
-> Sort (cost=7262546.99..7271489.64 rows=3577060 width=196)
" Sort Key: d.f_debts, d.b_closed, st.b_closed, d.f_subdivision, st.b_reversed, d.b_import, d.f_sale_categories, d.f_status, d.f_operation_types, d.f_doc_types, d.s_sequenceid, d.s_creator, d.s_owner, d.n_period, d.n_dt_period, d.d_reverse_date, d.s_modif_date, d.d_post_date, d.d_date, d.f_payments, d.s_create_date, d.n_amount, d.n_amount_sec, d.f_doc_registers, d.c_destination, d.c_number, d.f_subscr, d.link_imp, d.link"
-> Hash Join (cost=362938.98..978086.14 rows=3577060 width=196)
Hash Cond: (d.f_status = st.link)
-> Hash Right Join (cost=362937.71..965378.86 rows=3577060 width=194)
Hash Cond: ((apl.f_division = d.f_division) AND (apl.f_payments = d.link) AND (apl.f_subscr = d.f_subscr))
-> Append (cost=0.00..281980.56 rows=6348436 width=26)
-> Seq Scan on fd_applications_d1_o apl (cost=0.00..37135.30 rows=895346 width=26)
" Filter: ((f_division = 1) AND (d_post_date <= (to_date('20790606'::text, 'YYYYMMDD'::text))::timestamp with time zone))"
-> Seq Scan on fd_applications_d1_w apl_1 (cost=0.00..213103.08 rows=5453090 width=26)
" Filter: ((f_division = 1) AND (d_post_date <= (to_date('20790606'::text, 'YYYYMMDD'::text))::timestamp with time zone))"
-> Hash (cost=209515.16..209515.16 rows=3577060 width=184)
-> Append (cost=0.00..209515.16 rows=3577060 width=184)
-> Seq Scan on fd_payments_d1_o d (cost=0.00..24043.76 rows=389427 width=223)
" Filter: (b_transaction AND (f_division = 1) AND (d_post_date <= (to_date('20790606'::text, 'YYYYMMDD'::text))::timestamp with time zone))"
-> Seq Scan on fd_payments_d1_w d_1 (cost=0.00..167586.10 rows=3187633 width=179)
" Filter: (b_transaction AND (f_division = 1) AND (d_post_date <= (to_date('20790606'::text, 'YYYYMMDD'::text))::timestamp with time zone))"
-> Hash (cost=1.12..1.12 rows=12 width=6)
-> Seq Scan on fs_status st (cost=0.00..1.12 rows=12 width=6)
-> Hash (cost=50.59..50.59 rows=2 width=175)
-> Bitmap Heap Scan on fd_debts_transfers_documents td (cost=4.51..50.59 rows=2 width=175)
Recheck Cond: (f_debts_transfers = 268021)
Filter: (f_division = 1)
-> Bitmap Index Scan on idx_fd_debts_transfers_documents_pe_f_debts_transfers (cost=0.00..4.51 rows=12 width=0)
Index Cond: (f_debts_transfers = 268021)
Пробовал через lateral, но все равно - план такой же как и при обычном джойне:
1.
2.
3.
4.
5.
6.
7.
8.
9.
EXPLAIN
SELECT *
from PE.FD_Debts_Transfers_Documents AS TD
INNER JOIN LATERAL
(
select * from PE.FF_Payments_Balance() AS d where d.link = TD.F_Payments and D.F_Division = TD.F_Division
) d on TRUE
where TD.F_Division = 1
AND TD.F_Debts_Transfers = 268021
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.
Hash Join (cost=7262597.60..7745501.15 rows=45 width=473)
Hash Cond: (d.link = td.f_payments)
-> GroupAggregate (cost=7262546.99..7691794.19 rows=3577060 width=298)
" Group Key: d.f_debts, d.b_closed, st.b_closed, d.f_subdivision, st.b_reversed, d.b_import, d.f_sale_categories, d.f_status, d.f_operation_types, d.f_doc_types, d.s_sequenceid, d.s_creator, d.s_owner, d.n_period, d.n_dt_period, d.d_reverse_date, d.s_modif_date, d.d_post_date, d.d_date, d.f_payments, d.s_create_date, d.n_amount, d.n_amount_sec, d.f_doc_registers, d.c_destination, d.c_number, d.f_subscr, d.link_imp, d.link, d.f_division"
-> Sort (cost=7262546.99..7271489.64 rows=3577060 width=196)
" Sort Key: d.f_debts, d.b_closed, st.b_closed, d.f_subdivision, st.b_reversed, d.b_import, d.f_sale_categories, d.f_status, d.f_operation_types, d.f_doc_types, d.s_sequenceid, d.s_creator, d.s_owner, d.n_period, d.n_dt_period, d.d_reverse_date, d.s_modif_date, d.d_post_date, d.d_date, d.f_payments, d.s_create_date, d.n_amount, d.n_amount_sec, d.f_doc_registers, d.c_destination, d.c_number, d.f_subscr, d.link_imp, d.link"
-> Hash Join (cost=362938.98..978086.14 rows=3577060 width=196)
Hash Cond: (d.f_status = st.link)
-> Hash Right Join (cost=362937.71..965378.86 rows=3577060 width=194)
Hash Cond: ((apl.f_division = d.f_division) AND (apl.f_payments = d.link) AND (apl.f_subscr = d.f_subscr))
-> Append (cost=0.00..281980.56 rows=6348436 width=26)
-> Seq Scan on fd_applications_d1_o apl (cost=0.00..37135.30 rows=895346 width=26)
" Filter: ((f_division = 1) AND (d_post_date <= (to_date('20790606'::text, 'YYYYMMDD'::text))::timestamp with time zone))"
-> Seq Scan on fd_applications_d1_w apl_1 (cost=0.00..213103.08 rows=5453090 width=26)
" Filter: ((f_division = 1) AND (d_post_date <= (to_date('20790606'::text, 'YYYYMMDD'::text))::timestamp with time zone))"
-> Hash (cost=209515.16..209515.16 rows=3577060 width=184)
-> Append (cost=0.00..209515.16 rows=3577060 width=184)
-> Seq Scan on fd_payments_d1_o d (cost=0.00..24043.76 rows=389427 width=223)
" Filter: (b_transaction AND (f_division = 1) AND (d_post_date <= (to_date('20790606'::text, 'YYYYMMDD'::text))::timestamp with time zone))"
-> Seq Scan on fd_payments_d1_w d_1 (cost=0.00..167586.10 rows=3187633 width=179)
" Filter: (b_transaction AND (f_division = 1) AND (d_post_date <= (to_date('20790606'::text, 'YYYYMMDD'::text))::timestamp with time zone))"
-> Hash (cost=1.12..1.12 rows=12 width=6)
-> Seq Scan on fs_status st (cost=0.00..1.12 rows=12 width=6)
-> Hash (cost=50.59..50.59 rows=2 width=175)
-> Bitmap Heap Scan on fd_debts_transfers_documents td (cost=4.51..50.59 rows=2 width=175)
Recheck Cond: (f_debts_transfers = 268021)
Filter: (f_division = 1)
-> Bitmap Index Scan on idx_fd_debts_transfers_documents_pe_f_debts_transfers (cost=0.00..4.51 rows=12 width=0)
Index Cond: (f_debts_transfers = 268021)
Что делать то ? Ведущей таблицей в плане должна быть PE.FD_Debts_Transfers_Documents с небольшим кол-вом искомых строк (в плане кстати cardinality estimation для нее правильный) + должен быть nested loop c запросом из PE.FF_Payments_Balance(). |
