explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MTpr : Optimization for: Optimization for: Optimization for: plans; plan #h5s7; plan #h9Scj; plan #olKE

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.128 277.853 ↓ 15.2 607 1

Unique (cost=9,328.85..9,334.35 rows=40 width=1,446) (actual time=276.681..277.853 rows=607 loops=1)

2. 3.318 276.725 ↓ 15.2 607 1

Sort (cost=9,328.85..9,328.95 rows=40 width=1,446) (actual time=276.674..276.725 rows=607 loops=1)

  • Sort Key: p.private, l.id, l.start_price, l.subject, ((((to_char(l.date_end_registration, 'YYYY-MM-DD"T"HH24:MI:SS.MS'::text) || to_char(date_part('timezone_hour'::text, l.date_end_registration), 'S00'::text)) || ':'::text) || to_char(date_part('timezone_minute'::text, l.date_end_registration), 'FM00'::text))), ((((to_char(l.date_fulfilled, 'YYYY-MM-DD"T"HH24:MI:SS.MS'::text) || to_char(date_part('timezone_hour'::text, l.date_fulfilled), 'S00'::text)) || ':'::text) || to_char(date_part('timezone_minute'::text, l.date_fulfilled), 'FM00'::text))), ((((to_char(l.date_placed, 'YYYY-MM-DD"T"HH24:MI:SS.MS'::text) || to_char(date_part('timezone_hour'::text, l.date_placed), 'S00'::text)) || ':'::text) || to_char(date_part('timezone_minute'::text, l.date_placed), 'FM00'::text))), l.okved_codes, l.okei_codes, l.procedure_id, l.current_step, l.number, p.remote_id, p.registry_number, p.title, p.procedure_type, p.stage, p.frm, p.application_stages, p.peretorg_possible, p.organizer_department_id, p.send_to_oos, p.oos_publish_status, p.oos_changes_status, p.is_money_equivalent, (CASE WHEN (p.version > 1) THEN date_part('epoch'::text, p.date_last_update) WHEN (p.version = 1) THEN date_part('epoch'::text, p.date_added) ELSE NULL::double precision END), (date_part('epoch'::text, p.date_last_edited)), ((((to_char(p.date_published, 'YYYY-MM-DD"T"HH24:MI:SS.MS'::text) || to_char(date_part('timezone_hour'::text, p.date_published), 'S00'::text)) || ':'::text) || to_char(date_part('timezone_minute'::text, p.date_published), 'FM00'::text))), (concat(u.last_name, u.first_name, u.middle_name)), po_proc.id_in_schedule_oos, po_proc.can_be_joint, po_proc.basis_for_change, po_proc.basis_for_single_supplier, polots.purchase_purpose, polots.supply_periodicity, polots.mass_discussion, org.full_name, org.short_name, cur.name, cur.description, cur.id, stat.start_price, stat.doc_explain_requests, stat.doc_pend_explain_requests, stat.applic_pend_explain_requests, stat.result_explain_requests, psteps.step_id, psteps.order_number, vpsteps.pseudo, vpsteps.full_name, (CASE WHEN (p.organizer_contragent_id = 200) THEN stat.applics_count ELSE NULL::integer END)
  • Sort Method: quicksort Memory: 1298kB
3. 14.936 273.407 ↓ 15.2 607 1

Hash Join (cost=8,471.22..9,327.78 rows=40 width=1,446) (actual time=243.483..273.407 rows=607 loops=1)

  • Hash Cond: (l.id = lh.record_id)
  • Join Filter: (('in_scheduled_plan'::text = ANY (((array_agg(ps.step_id)))::text[])) OR ((('in_scheduled_plan_oos'::text = ANY (((array_agg(ps.step_id)))::text[])) OR ('in_scheduled_plan_oos_sended'::text = ANY (((array_agg(ps.step_id)))::text[]))) AND (s.id <> 6)))
4. 0.713 35.052 ↓ 3.8 615 1

Hash Left Join (cost=1,018.94..1,858.12 rows=164 width=1,454) (actual time=19.502..35.052 rows=615 loops=1)

  • Hash Cond: ((psteps.step_id)::text = (vpsteps.pseudo)::text)
5. 0.838 34.232 ↓ 3.8 615 1

Nested Loop (cost=1,011.43..1,848.36 rows=164 width=1,369) (actual time=19.370..34.232 rows=615 loops=1)

6. 0.795 31.549 ↓ 3.8 615 1

Nested Loop (cost=1,011.15..1,785.83 rows=164 width=1,347) (actual time=19.326..31.549 rows=615 loops=1)

7. 1.111 26.458 ↓ 4.3 716 1

Hash Join (cost=1,010.86..1,470.91 rows=165 width=1,324) (actual time=19.224..26.458 rows=716 loops=1)

  • Hash Cond: (po_proc.time_period_id = period.id)
  • Join Filter: ((s.id = 6) OR (SubPlan 1))
8. 0.546 23.859 ↑ 1.1 716 1

Hash Left Join (cost=1,003.04..1,199.46 rows=806 width=1,500) (actual time=18.939..23.859 rows=716 loops=1)

  • Hash Cond: (p.currency = cur.id)
9. 0.585 23.275 ↑ 1.1 716 1

Hash Left Join (cost=1,001.23..1,186.56 rows=806 width=1,468) (actual time=18.884..23.275 rows=716 loops=1)

  • Hash Cond: (p.organizer_contragent_id = org.id)
10. 0.656 22.635 ↑ 1.1 716 1

Hash Left Join (cost=994.49..1,168.74 rows=806 width=1,376) (actual time=18.805..22.635 rows=716 loops=1)

  • Hash Cond: (p.organizer_user_id = u.id)
11. 1.811 21.564 ↑ 1.1 716 1

Hash Join (cost=967.46..1,130.63 rows=806 width=1,327) (actual time=18.360..21.564 rows=716 loops=1)

  • Hash Cond: (polots.lot_id = l.id)
12. 1.822 1.822 ↓ 1.0 2,198 1

Seq Scan on po_lots polots (cost=0.00..146.90 rows=2,190 width=189) (actual time=0.022..1.822 rows=2,198 loops=1)

13. 1.687 17.931 ↑ 1.1 716 1

Hash (cost=957.38..957.38 rows=806 width=1,138) (actual time=17.931..17.931 rows=716 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 709kB
14. 1.128 16.244 ↑ 1.1 716 1

Hash Join (cost=740.42..957.38 rows=806 width=1,138) (actual time=12.479..16.244 rows=716 loops=1)

  • Hash Cond: (l.procedure_id = p.id)
15. 2.675 2.675 ↓ 1.0 2,201 1

Seq Scan on lots l (cost=0.00..200.89 rows=2,189 width=487) (actual time=0.004..2.675 rows=2,201 loops=1)

  • Filter: (actual AND actual)
16. 0.940 12.441 ↑ 1.1 716 1

Hash (cost=730.34..730.34 rows=806 width=651) (actual time=12.441..12.441 rows=716 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 351kB
17. 0.914 11.501 ↑ 1.1 716 1

Hash Right Join (cost=660.51..730.34 rows=806 width=651) (actual time=10.780..11.501 rows=716 loops=1)

  • Hash Cond: (pp.procedure_id = p.id)
18. 0.330 0.330 ↑ 1.0 2,181 1

Seq Scan on po_procedures pp (cost=0.00..53.81 rows=2,181 width=4) (actual time=0.004..0.330 rows=2,181 loops=1)

19. 0.952 10.257 ↑ 1.1 716 1

Hash (cost=650.44..650.44 rows=806 width=651) (actual time=10.257..10.257 rows=716 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 351kB
20. 1.052 9.305 ↑ 1.1 716 1

Hash Join (cost=580.39..650.44 rows=806 width=651) (actual time=8.391..9.305 rows=716 loops=1)

  • Hash Cond: (po_proc.procedure_id = p.id)
21. 0.630 0.630 ↑ 1.0 2,181 1

Seq Scan on po_procedures po_proc (cost=0.00..53.81 rows=2,181 width=49) (actual time=0.016..0.630 rows=2,181 loops=1)

22. 1.114 7.623 ↑ 1.2 716 1

Hash (cost=570.02..570.02 rows=829 width=602) (actual time=7.623..7.623 rows=716 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 340kB
23. 6.509 6.509 ↑ 1.2 716 1

Seq Scan on procedures p (cost=0.00..570.02 rows=829 width=602) (actual time=0.016..6.509 rows=716 loops=1)

  • Filter: (send_to_oos AND actual AND (date_archived IS NULL) AND (organizer_contragent_id = 200))
  • Rows Removed by Filter: 1527
24. 0.133 0.415 ↑ 1.0 268 1

Hash (cost=23.68..23.68 rows=268 width=57) (actual time=0.415..0.415 rows=268 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
25. 0.282 0.282 ↑ 1.0 268 1

Seq Scan on users u (cost=0.00..23.68 rows=268 width=57) (actual time=0.006..0.282 rows=268 loops=1)

26. 0.002 0.055 ↑ 1.0 1 1

Hash (cost=6.72..6.72 rows=1 width=96) (actual time=0.055..0.055 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
27. 0.053 0.053 ↑ 1.0 1 1

Seq Scan on contragents org (cost=0.00..6.72 rows=1 width=96) (actual time=0.053..0.053 rows=1 loops=1)

  • Filter: (id = 200)
  • Rows Removed by Filter: 57
28. 0.015 0.038 ↑ 1.0 36 1

Hash (cost=1.36..1.36 rows=36 width=36) (actual time=0.038..0.038 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
29. 0.023 0.023 ↑ 1.0 36 1

Seq Scan on vocab_currency cur (cost=0.00..1.36 rows=36 width=36) (actual time=0.016..0.023 rows=36 loops=1)

30. 0.004 0.178 ↑ 1.0 8 1

Hash (cost=7.72..7.72 rows=8 width=16) (actual time=0.178..0.178 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
31. 0.035 0.174 ↑ 1.0 8 1

Hash Join (cost=5.61..7.72 rows=8 width=16) (actual time=0.150..0.174 rows=8 loops=1)

  • Hash Cond: (s.po_budget_id = bud.id)
32. 0.016 0.016 ↑ 1.0 75 1

Seq Scan on po_schedules s (cost=0.00..1.75 rows=75 width=8) (actual time=0.010..0.016 rows=75 loops=1)

33. 0.004 0.123 ↑ 1.0 8 1

Hash (cost=5.51..5.51 rows=8 width=20) (actual time=0.123..0.123 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
34. 0.021 0.119 ↑ 1.0 8 1

Hash Join (cost=4.17..5.51 rows=8 width=20) (actual time=0.113..0.119 rows=8 loops=1)

  • Hash Cond: (period.id = bud2.po_period_id)
35. 0.023 0.023 ↑ 1.0 19 1

Seq Scan on po_vocab_time_periods period (cost=0.00..1.19 rows=19 width=4) (actual time=0.021..0.023 rows=19 loops=1)

36. 0.002 0.075 ↑ 1.0 8 1

Hash (cost=4.07..4.07 rows=8 width=16) (actual time=0.075..0.075 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
37. 0.031 0.073 ↑ 1.0 8 1

Hash Join (cost=2.00..4.07 rows=8 width=16) (actual time=0.051..0.073 rows=8 loops=1)

  • Hash Cond: (bud2.id = bud.id)
38. 0.015 0.015 ↓ 1.0 75 1

Seq Scan on po_budget bud2 (cost=0.00..1.72 rows=72 width=8) (actual time=0.005..0.015 rows=75 loops=1)

39. 0.005 0.027 ↑ 1.0 8 1

Hash (cost=1.90..1.90 rows=8 width=8) (actual time=0.027..0.027 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
40. 0.022 0.022 ↑ 1.0 8 1

Seq Scan on po_budget bud (cost=0.00..1.90 rows=8 width=8) (actual time=0.012..0.022 rows=8 loops=1)

  • Filter: (contragent_id = 200)
  • Rows Removed by Filter: 67
41.          

SubPlan (forHash Join)

42. 1.310 1.310 ↑ 1.0 1 262

Function Scan on jsonb_array_elements jae (cost=0.00..1.50 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=262)

  • Filter: ((value @> '{"year": 2019}'::jsonb) OR (value @> '{"year": "2019"}'::jsonb))
  • Rows Removed by Filter: 1
43. 4.296 4.296 ↑ 1.0 1 716

Index Scan using procedure_steps_pkey on procedure_steps psteps (cost=0.29..1.90 rows=1 width=31) (actual time=0.006..0.006 rows=1 loops=716)

  • Index Cond: (id = l.current_step)
  • Filter: ((step_id)::text <> ALL ('{on_delete_from_plan,deleted_from_plan}'::text[]))
  • Rows Removed by Filter: 0
44. 1.845 1.845 ↑ 1.0 1 615

Index Scan using procedure_stats_pkey on procedure_stats stat (cost=0.28..0.37 rows=1 width=30) (actual time=0.003..0.003 rows=1 loops=615)

  • Index Cond: (procedure_id = l.procedure_id)
45. 0.042 0.107 ↑ 1.0 67 1

Hash (cost=6.67..6.67 rows=67 width=85) (actual time=0.107..0.107 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
46. 0.065 0.065 ↑ 1.0 67 1

Seq Scan on vocab_procedure_steps vpsteps (cost=0.00..6.67 rows=67 width=85) (actual time=0.004..0.065 rows=67 loops=1)

47. 0.999 223.419 ↓ 3.8 2,015 1

Hash (cost=7,445.59..7,445.59 rows=536 width=40) (actual time=223.419..223.419 rows=2,015 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 312kB
48. 3.933 222.420 ↓ 3.8 2,015 1

GroupAggregate (cost=7,191.33..7,440.23 rows=536 width=27) (actual time=217.803..222.420 rows=2,015 loops=1)

  • Group Key: lh.record_id
  • Filter: ((array_agg(ps.step_id) IS NOT NULL) AND (('in_scheduled_plan'::text = ANY ((array_agg(ps.step_id))::text[])) OR ('in_scheduled_plan_oos'::text = ANY ((array_agg(ps.step_id))::text[])) OR ('in_scheduled_plan_oos_sended'::text = ANY ((array_agg(ps.step_id))::text[]))))
49. 3.111 218.487 ↑ 1.7 7,151 1

Sort (cost=7,191.33..7,222.29 rows=12,385 width=27) (actual time=217.758..218.487 rows=7,151 loops=1)

  • Sort Key: lh.record_id
  • Sort Method: quicksort Memory: 751kB
50. 23.999 215.376 ↑ 1.7 7,151 1

Merge Join (cost=5,893.67..6,349.38 rows=12,385 width=27) (actual time=188.435..215.376 rows=7,151 loops=1)

  • Merge Cond: (((ps.id)::text) = lh."to")
51. 27.259 38.518 ↑ 1.0 5,932 1

Sort (cost=1,344.15..1,359.24 rows=6,037 width=27) (actual time=37.945..38.518 rows=5,932 loops=1)

  • Sort Key: ((ps.id)::text)
  • Sort Method: quicksort Memory: 656kB
52. 9.830 11.259 ↑ 1.0 5,932 1

Bitmap Heap Scan on procedure_steps ps (cost=200.03..965.03 rows=6,037 width=27) (actual time=1.578..11.259 rows=5,932 loops=1)

  • Recheck Cond: ((step_id)::text = ANY ('{in_scheduled_plan,in_scheduled_plan_oos,in_scheduled_plan_oos_sended}'::text[]))
  • Heap Blocks: exact=674
53. 1.429 1.429 ↓ 1.0 6,149 1

Bitmap Index Scan on procedure_steps_idx_step_id (cost=0.00..198.52 rows=6,037 width=0) (actual time=1.429..1.429 rows=6,149 loops=1)

  • Index Cond: ((step_id)::text = ANY ('{in_scheduled_plan,in_scheduled_plan_oos,in_scheduled_plan_oos_sended}'::text[]))
54. 125.852 152.859 ↑ 1.0 26,975 1

Sort (cost=4,549.52..4,617.39 rows=27,149 width=29) (actual time=150.458..152.859 rows=26,975 loops=1)

  • Sort Key: lh."to
  • Sort Method: quicksort Memory: 2035kB
55. 22.447 27.007 ↑ 1.0 27,018 1

Bitmap Heap Scan on lots_history lh (cost=694.82..2,550.18 rows=27,149 width=29) (actual time=4.909..27.007 rows=27,018 loops=1)

  • Recheck Cond: ((field)::text = 'current_step'::text)
  • Heap Blocks: exact=1469
56. 4.560 4.560 ↑ 1.0 27,058 1

Bitmap Index Scan on lots_history_idx_field (cost=0.00..688.03 rows=27,149 width=0) (actual time=4.560..4.560 rows=27,058 loops=1)

  • Index Cond: ((field)::text = 'current_step'::text)
Planning time : 27.814 ms
Execution time : 280.175 ms