explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qhAF

Settings
# exclusive inclusive rows x rows loops node
1. 0.352 13,641.267 ↑ 18.0 50 1

Unique (cost=470,175.07..470,206.57 rows=900 width=515) (actual time=13,640.784..13,641.267 rows=50 loops=1)

  • Output: it.id, ms.id, ms.id_version, it.description, xrv.version_number, mit.version_mr, mit.registration_date, mit.purpose, mit.application, (CASE WHEN ((mit.application IS NOT NULL) AND (mit.application = 1)) THEN 'Межведомственное взаимодействие'::cha (...)
  • Buffers: shared hit=23034, temp read=1377045 written=354
2. 2.334 13,640.915 ↓ 1.0 935 1

Sort (cost=470,175.07..470,177.32 rows=900 width=515) (actual time=13,640.783..13,640.915 rows=935 loops=1)

  • Output: it.id, ms.id, ms.id_version, it.description, xrv.version_number, mit.version_mr, mit.registration_date, mit.purpose, mit.application, (CASE WHEN ((mit.application IS NOT NULL) AND (mit.application = 1)) THEN 'Межведомственное взаимодействие (...)
  • Sort Key: mit.registration_date DESC, it.id, ms.id, ms.id_version, it.description, xrv.version_number, mit.version_mr, mit.purpose, mit.application, (CASE WHEN ((mit.application IS NOT NULL) AND (mit.application = 1)) THEN 'Межведомственное взаимод (...)
  • Sort Method: quicksort Memory: 910kB
  • Buffers: shared hit=23034, temp read=1377045 written=354
3. 0.000 13,638.581 ↓ 1.0 935 1

Nested Loop (cost=2,229.47..470,130.91 rows=900 width=515) (actual time=10.435..13,638.581 rows=935 loops=1)

  • Output: it.id, ms.id, ms.id_version, it.description, xrv.version_number, mit.version_mr, mit.registration_date, mit.purpose, mit.application, CASE WHEN ((mit.application IS NOT NULL) AND (mit.application = 1)) THEN 'Межведомственное взаимодей (...)
  • Join Filter: (it.id = mit.inquiry_type_id)
  • Buffers: shared hit=23034, temp read=1377045 written=354
4. 2.437 13,628.127 ↑ 1.1 5,349 1

Hash Left Join (cost=2,229.19..467,141.18 rows=5,829 width=301) (actual time=10.422..13,628.127 rows=5,349 loops=1)

  • Output: ms.id, ms.id_version, ms.namespace_uri, ms.inquiry_type_id, it.id, it.description, it.interaction_type, xrv.version_number, mlp.type
  • Hash Cond: (xd.resource_version_id = xrv.id)
  • Buffers: shared hit=6981, temp read=1377045 written=354
5. 2.922 13,623.464 ↑ 1.1 5,349 1

Hash Left Join (cost=1,937.68..466,769.52 rows=5,829 width=304) (actual time=8.149..13,623.464 rows=5,349 loops=1)

  • Output: ms.id, ms.id_version, ms.namespace_uri, ms.inquiry_type_id, it.id, it.description, it.interaction_type, xd.resource_version_id, mlp.type
  • Hash Cond: ((ms.namespace_uri)::text = (xd.public_id)::text)
  • Buffers: shared hit=6902, temp read=1377045 written=354
6. 1.740 13,616.534 ↑ 1.1 5,349 1

Merge Join (cost=352.96..465,097.36 rows=5,829 width=296) (actual time=4.091..13,616.534 rows=5,349 loops=1)

  • Output: ms.id, ms.id_version, ms.namespace_uri, ms.inquiry_type_id, it.id, it.description, it.interaction_type, mlp.type
  • Merge Cond: (lp.id = ip.owner_id)
  • Buffers: shared hit=5577, temp read=1377045 written=354
7. 1.987 1.987 ↑ 1.0 4,500 1

Index Only Scan using legal_person_pkey on public.legal_person lp (cost=0.28..312.99 rows=4,572 width=8) (actual time=0.008..1.987 rows=4,500 loops=1)

  • Output: lp.id
  • Heap Fetches: 4500
  • Buffers: shared hit=1183
8. 1.999 13,612.807 ↑ 1.1 5,349 1

Materialize (cost=352.68..464,700.06 rows=5,831 width=312) (actual time=4.078..13,612.807 rows=5,349 loops=1)

  • Output: ms.id, ms.id_version, ms.namespace_uri, ms.inquiry_type_id, it.id, it.description, it.interaction_type, ip.owner_id, mlp.type, mlp.legal_person_id
  • Buffers: shared hit=4394, temp read=1377045 written=354
9. 7,271.937 13,610.808 ↑ 1.1 5,349 1

Nested Loop (cost=352.68..464,685.48 rows=5,831 width=312) (actual time=4.076..13,610.808 rows=5,349 loops=1)

  • Output: ms.id, ms.id_version, ms.namespace_uri, ms.inquiry_type_id, it.id, it.description, it.interaction_type, ip.owner_id, mlp.type, mlp.legal_person_id
  • Join Filter: ((ip.id = rte.destination) OR ((ip.id = it.responsible_part_id) AND (NOT (hashed SubPlan 1))))
  • Rows Removed by Join Filter: 38104011
  • Buffers: shared hit=4394, temp read=1377045 written=354
10. 2.544 10.195 ↑ 1.1 3,880 1

Merge Join (cost=0.56..857.35 rows=4,115 width=32) (actual time=0.018..10.195 rows=3,880 loops=1)

  • Output: ip.id, ip.owner_id, mlp.type, mlp.legal_person_id
  • Merge Cond: (mlp.legal_person_id = ip.owner_id)
  • Buffers: shared hit=4193
11. 2.958 2.958 ↑ 1.0 3,253 1

Index Scan using idx_meta_legal_person_lp_fk on public.meta_legal_person mlp (cost=0.28..330.81 rows=3,253 width=16) (actual time=0.006..2.958 rows=3,253 loops=1)

  • Output: mlp.id, mlp.legal_person_id, mlp.frgu_id, mlp.type, mlp.full_name
  • Filter: (mlp.type = ANY ('{2,4}'::bigint[]))
  • Rows Removed by Filter: 1318
  • Buffers: shared hit=1052
12. 4.693 4.693 ↑ 1.0 5,782 1

Index Scan using idx_interaction_part_lp_fk on public.interaction_participant ip (cost=0.28..462.80 rows=5,782 width=16) (actual time=0.003..4.693 rows=5,782 loops=1)

  • Output: ip.id, ip.name, ip.mnemonic, ip.owner_id, ip.use_separate_queues, ip.queue_basic_name, ip.vip, ip.mgmt_domain_mnemonic, ip.is_integrational, ip.redelivery_delay, ip.delivery_shard
  • Buffers: shared hit=3141
13. 6,323.089 6,328.280 ↓ 1.7 9,822 3,880

Materialize (cost=265.64..489.03 rows=5,629 width=304) (actual time=0.006..1.631 rows=9,822 loops=3,880)

  • Output: ms.id, ms.id_version, ms.namespace_uri, ms.inquiry_type_id, it.id, it.description, it.interaction_type, it.responsible_part_id, rte.destination
  • Buffers: shared hit=167, temp read=1377045 written=354
14. 2.193 5.191 ↓ 1.7 9,822 1

Hash Right Join (cost=265.64..460.88 rows=5,629 width=304) (actual time=2.752..5.191 rows=9,822 loops=1)

  • Output: ms.id, ms.id_version, ms.namespace_uri, ms.inquiry_type_id, it.id, it.description, it.interaction_type, it.responsible_part_id, rte.destination
  • Hash Cond: (rte.inquiry_type_id = ms.inquiry_type_id)
  • Buffers: shared hit=167
15. 0.272 0.272 ↑ 1.0 4,198 1

Seq Scan on public.routing_table_element rte (cost=0.00..75.98 rows=4,198 width=16) (actual time=0.010..0.272 rows=4,198 loops=1)

  • Output: rte.id, rte.inquiry_type_id, rte.pattern, rte.destination
  • Buffers: shared hit=34
16. 0.851 2.726 ↑ 1.0 2,279 1

Hash (cost=236.98..236.98 rows=2,293 width=296) (actual time=2.726..2.726 rows=2,279 loops=1)

  • Output: ms.id, ms.id_version, ms.namespace_uri, ms.inquiry_type_id, it.id, it.description, it.interaction_type, it.responsible_part_id
  • Buckets: 4096 Batches: 1 Memory Usage: 780kB
  • Buffers: shared hit=133
17. 0.654 1.875 ↑ 1.0 2,279 1

Hash Join (cost=105.51..236.98 rows=2,293 width=296) (actual time=0.772..1.875 rows=2,279 loops=1)

  • Output: ms.id, ms.id_version, ms.namespace_uri, ms.inquiry_type_id, it.id, it.description, it.interaction_type, it.responsible_part_id
  • Hash Cond: (ms.inquiry_type_id = it.id)
  • Buffers: shared hit=133
18. 0.470 0.470 ↑ 1.0 2,373 1

Seq Scan on public.message_suite ms (cost=0.00..99.64 rows=2,373 width=84) (actual time=0.013..0.470 rows=2,373 loops=1)

  • Output: ms.id, ms.inquiry_type_id, ms.namespace_uri, ms.req_root_elem_l_name, ms.resp_root_elem_l_name, ms.territory_code_xpath, ms.version_number, ms.disabled, ms.id_version, ms.is_ (...)
  • Filter: (ms.disabled = 0)
  • Rows Removed by Filter: 318
  • Buffers: shared hit=66
19. 0.391 0.751 ↑ 1.0 1,514 1

Hash (cost=86.59..86.59 rows=1,514 width=212) (actual time=0.751..0.751 rows=1,514 loops=1)

  • Output: it.id, it.description, it.interaction_type, it.responsible_part_id
  • Buckets: 2048 Batches: 1 Memory Usage: 381kB
  • Buffers: shared hit=67
20. 0.360 0.360 ↑ 1.0 1,514 1

Seq Scan on public.inquiry_type it (cost=0.00..86.59 rows=1,514 width=212) (actual time=0.006..0.360 rows=1,514 loops=1)

  • Output: it.id, it.description, it.interaction_type, it.responsible_part_id
  • Filter: ((it.interaction_type)::text <> 'BROADCAST'::text)
  • Rows Removed by Filter: 53
  • Buffers: shared hit=67
21.          

SubPlan (for Nested Loop)

22. 0.396 0.396 ↑ 1.0 4,198 1

Seq Scan on public.routing_table_element (cost=0.00..75.98 rows=4,198 width=8) (actual time=0.005..0.396 rows=4,198 loops=1)

  • Output: routing_table_element.inquiry_type_id
  • Buffers: shared hit=34
23. 1.336 4.008 ↑ 2.3 4,987 1

Hash (cost=1,440.43..1,440.43 rows=11,543 width=60) (actual time=4.008..4.008 rows=4,987 loops=1)

  • Output: xd.public_id, xd.resource_version_id
  • Buckets: 16384 Batches: 1 Memory Usage: 599kB
  • Buffers: shared hit=1325
24. 2.672 2.672 ↑ 1.0 11,543 1

Seq Scan on public.xml_document xd (cost=0.00..1,440.43 rows=11,543 width=60) (actual time=0.008..2.672 rows=11,543 loops=1)

  • Output: xd.public_id, xd.resource_version_id
  • Buffers: shared hit=1325
25. 1.210 2.226 ↑ 1.0 9,445 1

Hash (cost=173.45..173.45 rows=9,445 width=13) (actual time=2.226..2.226 rows=9,445 loops=1)

  • Output: xrv.version_number, xrv.id
  • Buckets: 16384 Batches: 1 Memory Usage: 571kB
  • Buffers: shared hit=79
26. 1.016 1.016 ↑ 1.0 9,445 1

Seq Scan on public.xml_resource_version xrv (cost=0.00..173.45 rows=9,445 width=13) (actual time=0.012..1.016 rows=9,445 loops=1)

  • Output: xrv.version_number, xrv.id
  • Buffers: shared hit=79
27. 10.698 10.698 ↓ 0.0 0 5,349

Index Scan using idx_meta_inquiry_type_it_fk on public.meta_inquiry_type mit (cost=0.28..0.50 rows=1 width=198) (actual time=0.002..0.002 rows=0 loops=5,349)

  • Output: mit.id, mit.inquiry_type_id, mit.frgu_id, mit.purpose, mit.application, mit.version_mr, mit.registration_date, mit.is_published, mit.manual_link, mit.sid, mit.incident_number, mit.legal_act
  • Index Cond: (mit.inquiry_type_id = ms.inquiry_type_id)
  • Filter: (mit.is_published = 1)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=16053
Planning time : 3.023 ms
Execution time : 13,643.753 ms