https://pastein.ru/t/r-8
Загрузка данных
CTE Scan on result r (cost=5047334.63..5048670.35 rows=66786 width=1144) (actual time=20747.715..22452.278 rows=200571 loops=1)
Buffers: shared hit=7660332 read=1805, temp read=13972 written=32406
CTE result
-> Result (cost=4916934.97..5047334.63 rows=66786 width=670) (actual time=20747.706..21911.278 rows=200571 loops=1)
Buffers: shared hit=7660332 read=1805, temp read=13972 written=13975
-> Sort (cost=4916934.97..4917101.93 rows=66786 width=1024) (actual time=20746.144..20953.648 rows=200571 loops=1)
Sort Key: ((td.canceled IS NOT NULL)), td.version DESC
Sort Method: external merge Disk: 111776kB
Buffers: shared hit=7660297 read=1805, temp read=13972 written=13975
-> Nested Loop (cost=21646.95..4891391.80 rows=66786 width=1024) (actual time=605.110..20276.698 rows=200571 loops=1)
Buffers: shared hit=7660297 read=1805
-> Seq Scan on npf360_settings (cost=0.00..1.30 rows=1 width=516) (actual time=0.028..0.031 rows=1 loops=1)
Filter: ((name)::text = 'TESSA_API'::text)
Rows Removed by Filter: 24
Buffers: shared hit=1
-> Nested Loop Left Join (cost=21646.95..4881539.56 rows=66786 width=803) (actual time=605.053..19682.326 rows=200571 loops=1)
Buffers: shared hit=7660296 read=1805
-> Nested Loop Left Join (cost=21646.51..4873920.52 rows=66786 width=793) (actual time=604.516..19563.116 rows=200571 loops=1)
Buffers: shared hit=7660240 read=1805
-> Nested Loop Left Join (cost=21646.22..4872246.14 rows=66786 width=797) (actual time=604.468..19425.506 rows=200571 loops=1)
Buffers: shared hit=7660205 read=1796
-> Hash Left Join (cost=21585.20..795721.40 rows=66786 width=781) (actual time=604.309..4112.702 rows=200571 loops=1)
Hash Cond: (atc.information_source_id = info.id)
Buffers: shared hit=2553347 read=362
-> Nested Loop Left Join (cost=21583.93..795482.90 rows=66786 width=367) (actual time=604.133..4024.466 rows=200571 loops=1)
Buffers: shared hit=2553346 read=362
-> Nested Loop Left Join (cost=21583.64..793811.12 rows=66786 width=317) (actual time=604.120..3905.778 rows=200571 loops=1)
Buffers: shared hit=2553328 read=362
-> Nested Loop Left Join (cost=21583.20..681912.75 rows=66786 width=271) (actual time=604.113..3071.212 rows=200571 loops=1)
Buffers: shared hit=1751463 read=15
-> Hash Join (cost=21582.76..679908.08 rows=66786 width=221) (actual time=603.673..2949.496 rows=200571 loops=1)
Hash Cond: (((contract.service_type)::text = ('NPO'::text)) AND (contract.client_type = (0)))
Buffers: shared hit=1751371 read=15
-> Nested Loop (cost=21582.70..677737.47 rows=200359 width=197) (actual time=603.144..2837.325 rows=200607 loops=1)
Buffers: shared hit=1751371 read=15
-> Nested Loop (cost=21582.26..579038.30 rows=200359 width=166) (actual time=603.113..2074.968 rows=200607 loops=1)
Buffers: shared hit=948958
-> Nested Loop (cost=21581.82..574022.12 rows=200435 width=142) (actual time=603.078..1672.516 rows=200607 loops=1)
Buffers: shared hit=829194
-> Hash Left Join (cost=21581.39..40865.02 rows=200435 width=138) (actual time=603.040..894.979 rows=200607 loops=1)
Hash Cond: (td.application_termination_contract_id = atc.id)
Buffers: shared hit=26766
-> Seq Scan on year_tax_deduction td (cost=0.00..18757.47 rows=200435 width=121) (actual time=0.033..148.741 rows=200607 loops=1)
Filter: ((type)::text <> 'NONE'::text)
Rows Removed by Filter: 284006
Buffers: shared hit=12699
-> Hash (cost=17406.73..17406.73 rows=333973 width=21) (actual time=602.043..602.044 rows=333954 loops=1)
Buckets: 524288 Batches: 1 Memory Usage: 20877kB
Buffers: shared hit=14067
-> Seq Scan on application_termination_contract atc (cost=0.00..17406.73 rows=333973 width=21) (actual time=0.024..157.561 rows=333954 loops=1)
Buffers: shared hit=14067
-> Limit (cost=0.42..2.64 rows=1 width=230) (actual time=0.003..0.003 rows=1 loops=200607)
Buffers: shared hit=802428
-> Index Scan using idx_atcd_contract_id on application_termination_contract_data (cost=0.42..2.64 rows=1 width=230) (actual time=0.003..0.003 rows=1 loops=200607)
Index Cond: (application_termination_contract_id = atc.id)
Buffers: shared hit=802428
-> Memoize (cost=0.45..2.66 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=200607)
Cache Key: application_termination_contract_data.sharer_id
Cache Mode: logical
Hits: 170666 Misses: 29941 Evictions: 0 Overflows: 0 Memory Usage: 3691kB
Buffers: shared hit=119764
-> Index Scan using sharer_pkey on sharer sh (cost=0.43..2.65 rows=1 width=28) (actual time=0.008..0.008 rows=1 loops=29941)
Index Cond: (id = application_termination_contract_data.sharer_id)
Buffers: shared hit=119764
-> Index Scan using contract_pkey on contract (cost=0.43..0.49 rows=1 width=39) (actual time=0.003..0.003 rows=1 loops=200607)
Index Cond: (id = sh.contract_id)
Buffers: shared hit=802413 read=15
-> Hash (cost=0.03..0.03 rows=2 width=68) (actual time=0.009..0.011 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Append (cost=0.00..0.03 rows=2 width=68) (actual time=0.003..0.005 rows=2 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=68) (actual time=0.002..0.002 rows=1 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=68) (actual time=0.000..0.001 rows=1 loops=1)
-> Memoize (cost=0.45..1.59 rows=1 width=58) (actual time=0.000..0.000 rows=0 loops=200571)
Cache Key: atc.application_personal_data_individual_id
Cache Mode: logical
Hits: 200548 Misses: 23 Evictions: 0 Overflows: 0 Memory Usage: 4kB
Buffers: shared hit=92
-> Index Scan using application_personal_data_individual_id_pk on application_personal_data_individual apdi (cost=0.44..1.58 rows=1 width=58) (actual time=0.012..0.012 rows=1 loops=23)
Index Cond: (id = atc.application_personal_data_individual_id)
Buffers: shared hit=91
-> Index Scan using individual_pkey on individual ind (cost=0.43..1.68 rows=1 width=54) (actual time=0.003..0.003 rows=1 loops=200571)
Index Cond: (id = COALESCE(td.individual_id, apdi.individual_id))
Buffers: shared hit=801865 read=347
-> Memoize (cost=0.30..0.31 rows=1 width=58) (actual time=0.000..0.000 rows=0 loops=200571)
Cache Key: td.agent_id
Cache Mode: logical
Hits: 200564 Misses: 7 Evictions: 0 Overflows: 0 Memory Usage: 1kB
Buffers: shared hit=18
-> Index Scan using agent_pkey on agent a (cost=0.29..0.30 rows=1 width=58) (actual time=0.011..0.011 rows=1 loops=7)
Index Cond: (id = td.agent_id)
Buffers: shared hit=18
-> Hash (cost=1.12..1.12 rows=12 width=422) (actual time=0.042..0.043 rows=12 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on information_source info (cost=0.00..1.12 rows=12 width=422) (actual time=0.033..0.035 rows=12 loops=1)
Buffers: shared hit=1
-> Limit (cost=61.02..61.02 rows=1 width=902) (actual time=0.076..0.076 rows=1 loops=200571)
Buffers: shared hit=5106858 read=1434
-> Sort (cost=61.02..61.15 rows=53 width=902) (actual time=0.075..0.075 rows=1 loops=200571)
Sort Key: fns_reference_request_journal.request_date DESC
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=5106858 read=1434
-> Index Scan using ix_fns_ref_req_journal_contract_number on fns_reference_request_journal (cost=0.43..60.75 rows=53 width=902) (actual time=0.007..0.050 rows=89 loops=200571)
Index Cond: (contract_number = (contract.number)::text)
Buffers: shared hit=5106858 read=1434
-> Memoize (cost=0.29..2.51 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=200571)
Cache Key: fns_reference_request_journal.model_file_to_verification_id
Cache Mode: logical
Hits: 200556 Misses: 15 Evictions: 0 Overflows: 0 Memory Usage: 2kB
Buffers: shared hit=35 read=9
-> Index Only Scan using idx_model_file_register on model_file_to_verification mfc (cost=0.28..2.50 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=15)
Index Cond: (id = fns_reference_request_journal.model_file_to_verification_id)
Heap Fetches: 16
Buffers: shared hit=35 read=9
-> Memoize (cost=0.44..2.57 rows=1 width=18) (actual time=0.000..0.000 rows=1 loops=200571)
Cache Key: mfc.register_id
Cache Mode: logical
Hits: 200556 Misses: 15 Evictions: 0 Overflows: 0 Memory Usage: 2kB
Buffers: shared hit=56
-> Index Scan using table_name_pkey on smev_register sr (cost=0.43..2.56 rows=1 width=18) (actual time=0.042..0.042 rows=1 loops=15)
Index Cond: (id = mfc.register_id)
Buffers: shared hit=56
SubPlan 2
-> Index Scan using ix_fns_ref_req_journal_response_received on fns_reference_request_journal fj (cost=0.43..1.55 rows=1 width=12) (actual time=0.033..0.039 rows=3 loops=1)
Index Cond: (response_received = true)
Buffers: shared hit=6
Planning:
Buffers: shared hit=84
Planning Time: 61.756 ms
Execution Time: 22486.473 ms