Добрый день!
На сервере порядка 10 баз данных,средний размер базы 10гб. Иногда на сервере поднимается iowait , доходит до 40% по zabbix
В логах пг иногда появляется:
temporary file: path "base/pgsql_tmp/pgsql_tmp27336.3", size 28202020
Вот запрос:
select * from Transactions T, DebitTokens D, CreditTokens C where (T.ID = D.ID) and (T.ID = C.ID) and (((C_Agreement in (38558956, 38558956)) or (D_Agreement in (38558956, 38558956))))

План запроса
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=31708.55..56241.31 rows=185 width=648) (actual time=569.994..637.500 rows=10 loops=1)
Workers Planned: 3
Workers Launched: 3
-> Nested Loop (cost=30708.55..55222.81 rows=60 width=648) (actual time=580.163..595.466 rows=2 loops=4)
-> Parallel Hash Join (cost=30708.12..55193.07 rows=64 width=568) (actual time=580.136..595.417 rows=2 loops=4)
Hash Cond: (c.id = d.id)
Join Filter: ((c.c_agreement = ANY ('{38558956,38558956}'::integer[])) OR (d.d_agreement = ANY ('{38558956,38558956}'::integer[])))
Rows Removed by Join Filter: 472558
-> Parallel Seq Scan on credittokens c (cost=0.00..22982.94 rows=572194 width=284) (actual time=0.012..47.908 rows=472560 loops=4)
-> Parallel Hash (cost=23563.61..23563.61 rows=571561 width=284) (actual time=232.142..232.143 rows=472560 loops=4)
Buckets: 2097152 Batches: 1 Memory Usage: 152448kB
-> Parallel Seq Scan on debittokens d (cost=0.00..23563.61 rows=571561 width=284) (actual time=0.021..56.768 rows=472560 loops=4)
-> Index Scan using pk_transactions on transactions t (cost=0.43..0.46 rows=1 width=80) (actual time=0.015..0.015 rows=1 loops=10)
Index Cond: (id = d.id)
Planning Time: 0.680 ms
Execution Time: 637.666 ms
(16 rows)

Настройки сервера:
max_connections = 1000
shared_buffers = 64GB
effective_cache_size = 195072MB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 256MB
min_wal_size = 2GB
max_wal_size = 8GB
max_worker_processes = 24
max_parallel_workers_per_gather = 4
max_parallel_workers = 24
max_parallel_maintenance_workers = 4
checkpoint_timeout = 1h

Возможно стоит поменять какие-то настройки пг? Пробовал играться с work_mem поднимая до 512мб - но ситуация не менялась

Maxim Boguk, благодарю за оперативность. Проверил на базе - запрос в 3 раза быстрее выполняется

Maxim Boguk, индексов не было. Добавил для теста и вот что получил:

Это старый запрос:
"Gather (cost=30891.98..54785.63 rows=204 width=648) (actual time=690.605..749.054 rows=10 loops=1)"
" Workers Planned: 3"
" Workers Launched: 3"
" -> Nested Loop (cost=29891.98..53765.23 rows=66 width=648) (actual time=682.893..701.664 rows=2 loops=4)"
" -> Parallel Hash Join (cost=29891.55..53734.74 rows=66 width=568) (actual time=682.819..701.582 rows=2 loops=4)"
" Hash Cond: (d.id = c.id)"
" Join Filter: ((c.c_agreement = ANY ('{38558956,38558956}'::integer[])) OR (d.d_agreement = ANY ('{38558956,38558956}'::integer[])))"
" Rows Removed by Join Filter: 472560"
" -> Parallel Seq Scan on debittokens d (cost=0.00..22242.58 rows=609758 width=284) (actual time=0.018..70.371 rows=472562 loops=4)"
" -> Parallel Hash (cost=22269.58..22269.58 rows=609758 width=284) (actual time=280.334..280.336 rows=472562 loops=4)"
" Buckets: 2097152 Batches: 1 Memory Usage: 152640kB"
" -> Parallel Seq Scan on credittokens c (cost=0.00..22269.58 rows=609758 width=284) (actual time=0.019..76.657 rows=472562 loops=4)"
" -> Index Scan using pk_transactions on transactions t (cost=0.43..0.46 rows=1 width=80) (actual time=0.026..0.026 rows=1 loops=10)"
" Index Cond: (id = d.id)"
"Planning Time: 0.874 ms"
"Execution Time: 749.202 ms"


Это новый:

"HashAggregate (cost=791.09..793.13 rows=204 width=648) (actual time=0.293..0.309 rows=10 loops=1)"
" Group Key: t.id, t.amount, t.quantity, t.balanstypecls, t.classno, t.credit, t.debet, t.description, t.docdetail, t.document, t.eicls, t.finsource, t.firstnum, t.opdate, t.orgtypecls, t.params, t.parentid, t.userid, d.id, d.d_agreement, d.d_bankaccount, d.d_basedoc, d.d_calcrange, d.d_clselement, d.d_clsprogram, d.d_custom, d.d_deficitsource, d.d_depart, d.d_directioncls, d.d_estateadmin, d.d_expensekind, d.d_facialaccount, d.d_factcls, d.d_fintypecls, d.d_fss, d.d_functionalea, d.d_fundsmovement, d.d_gosprogramcls, d.d_incomeadmin, d.d_incomecode, d.d_incomeprice, d.d_incometype, d.d_information, d.d_jointstock, d.d_mediatype, d.d_monthrange, d.d_nbo, d.d_nestingtype, d.d_nfa, d.d_nfagroup, d.d_objaccount, d.d_organization, d.d_paytype, d.d_person, d.d_place, d.d_protectiontype, d.d_realaccount, d.d_regioncls, d.d_rendering, d.d_stateoperation, d.d_stateoperation2016, d.d_stdohrash, d.d_subkesr, d.d_subsidycls, d.d_targetcodecls, d.d_targetea, d.d_targetea2016, d.d_tipsummy, d.d_tiptseny, d.d_usercategory, d.d_vehicle, d.d_nomerpartii, d.d_nomerserii, d.d_srokgodnosti, d.d_budallocationcls, d.d_contractcostscls, d.d_ndsopcode, d.d_kodba, d.d_kindofcosts, d.d_targetfunds, d.d_tselycode, d.d_clselement125, d.d_estateadmin125, d.d_oktmo, d.d_recipientmc, d.d_statusnfa, d.d_tselevajafunktsija, d.d_ndo, d.d_actdir, c.id, c.c_agreement, c.c_bankaccount, c.c_basedoc, c.c_calcrange, c.c_clselement, c.c_clsprogram, c.c_custom, c.c_deficitsource, c.c_depart, c.c_directioncls, c.c_estateadmin, c.c_expensekind, c.c_facialaccount, c.c_factcls, c.c_fintypecls, c.c_fss, c.c_functionalea, c.c_fundsmovement, c.c_gosprogramcls, c.c_incomeadmin, c.c_incomecode, c.c_incomeprice, c.c_incometype, c.c_information, c.c_jointstock, c.c_mediatype, c.c_monthrange, c.c_nbo, c.c_nestingtype, c.c_nfa, c.c_nfagroup, c.c_objaccount, c.c_organization, c.c_paytype, c.c_person, c.c_place, c.c_protectiontype, c.c_realaccount, c.c_regioncls, c.c_rendering, c.c_stateoperation, c.c_stateoperation2016, c.c_stdohrash, c.c_subkesr, c.c_subsidycls, c.c_targetcodecls, c.c_targetea, c.c_targetea2016, c.c_tipsummy, c.c_tiptseny, c.c_usercategory, c.c_vehicle, c.c_nomerpartii, c.c_nomerserii, c.c_srokgodnosti, c.c_budallocationcls, c.c_contractcostscls, c.c_ndsopcode, c.c_kodba, c.c_kindofcosts, c.c_targetfunds, c.c_tselycode, c.c_clselement125, c.c_estateadmin125, c.c_oktmo, c.c_recipientmc, c.c_statusnfa, c.c_tselevajafunktsija, c.c_ndo, c.c_actdir"
" -> Append (cost=1.28..709.49 rows=204 width=648) (actual time=0.047..0.217 rows=20 loops=1)"
" -> Nested Loop (cost=1.28..373.19 rows=108 width=648) (actual time=0.047..0.126 rows=10 loops=1)"
" -> Nested Loop (cost=0.85..323.79 rows=108 width=364) (actual time=0.033..0.079 rows=10 loops=1)"
" -> Index Scan using ix_credittokens_agreement on credittokens c (cost=0.43..39.23 rows=108 width=284) (actual time=0.020..0.031 rows=10 loops=1)"
" Index Cond: (c_agreement = ANY ('{38558956,38558956}'::integer[]))"
" -> Index Scan using pk_transactions on transactions t (cost=0.43..2.63 rows=1 width=80) (actual time=0.003..0.003 rows=1 loops=10)"
" Index Cond: (id = c.id)"
" -> Index Scan using pk_debittokens on debittokens d (cost=0.43..0.46 rows=1 width=284) (actual time=0.003..0.003 rows=1 loops=10)"
" Index Cond: (id = t.id)"
" -> Nested Loop (cost=1.28..333.24 rows=96 width=648) (actual time=0.041..0.088 rows=10 loops=1)"
" -> Nested Loop (cost=0.85..289.33 rows=96 width=364) (actual time=0.031..0.054 rows=10 loops=1)"
" -> Index Scan using ix_debittokens_agreement on debittokens d_1 (cost=0.43..35.41 rows=96 width=284) (actual time=0.026..0.029 rows=10 loops=1)"
" Index Cond: (d_agreement = ANY ('{38558956,38558956}'::integer[]))"
" -> Index Scan using pk_transactions on transactions t_1 (cost=0.43..2.65 rows=1 width=80) (actual time=0.001..0.001 rows=1 loops=10)"
" Index Cond: (id = d_1.id)"
" -> Index Scan using pk_credittokens on credittokens c_1 (cost=0.43..0.46 rows=1 width=284) (actual time=0.002..0.002 rows=1 loops=10)"
" Index Cond: (id = t_1.id)"
"Planning Time: 1.695 ms"
"Execution Time: 0.583 ms"

Guzya, на сервере 256гб. А запрос отловил включением log_temp_files=1.