explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KTKq

Settings
# exclusive inclusive rows x rows loops node
1. 7,557.019 291,703.965 ↓ 20,620.0 20,620 1

Nested Loop Left Join (cost=853,220.91..1,162,972.47 rows=1 width=1,032) (actual time=53,887.692..291,703.965 rows=20,620 loops=1)

  • Join Filter: (st.stotv_id = decis.court_decis_stotv_id)
  • Rows Removed by Join Filter: 9,222,898
2.          

CTE decis

3. 99.730 105,493.676 ↑ 3.6 20,620 1

Nested Loop Left Join (cost=204,610.75..853,215.77 rows=73,971 width=1,908) (actual time=53,873.868..105,493.676 rows=20,620 loops=1)

  • Filter: ((court_decis.delo_id IS NOT NULL) OR (vozv.delo_id IS NOT NULL))
  • Rows Removed by Filter: 37,545
4. 101.716 54,038.666 ↑ 1.3 58,160 1

Merge Left Join (cost=204,610.18..205,041.02 rows=73,971 width=1,780) (actual time=53,873.805..54,038.666 rows=58,160 loops=1)

  • Merge Cond: (decis_1.delo_id = vozv.delo_id)
5. 110.878 50,184.048 ↑ 1.3 58,152 1

Sort (cost=191,164.52..191,349.45 rows=73,971 width=12) (actual time=50,138.915..50,184.048 rows=58,152 loops=1)

  • Sort Key: decis_1.delo_id
  • Sort Method: quicksort Memory: 4,262kB
6. 50,073.170 50,073.170 ↑ 1.3 58,152 1

Index Scan using decis_det_ogai_kod on decis decis_1 (cost=0.58..185,182.24 rows=73,971 width=12) (actual time=214.066..50,073.170 rows=58,152 loops=1)

  • Index Cond: ((decis_dat >= date_trunc('year'::text, (('now'::cstring)::date)::timestamp with time zone)) AND (decis_dat <= (('now'::cstring)::date - '1 day'::interval)) AND ((ogai_sost_kod)::text = '45597'::text) AND (decis_kod = '95'::numeric))
7. 5.783 3,752.902 ↓ 2.1 16,172 1

Materialize (cost=13,445.66..13,485.09 rows=7,886 width=1,768) (actual time=3,734.874..3,752.902 rows=16,172 loops=1)

8. 133.368 3,747.119 ↓ 2.1 16,172 1

Sort (cost=13,445.66..13,465.37 rows=7,886 width=1,768) (actual time=3,734.870..3,747.119 rows=16,172 loops=1)

  • Sort Key: vozv.delo_id
  • Sort Method: quicksort Memory: 6,693kB
9. 3,613.751 3,613.751 ↓ 2.1 16,172 1

Index Scan using decis_ispoln_kod_dat on decis_ispoln vozv (cost=0.56..9,310.64 rows=7,886 width=1,768) (actual time=1.799..3,613.751 rows=16,172 loops=1)

  • Index Cond: (stad_ispoln_kod = '46'::numeric)
10. 51,355.280 51,355.280 ↓ 0.0 0 58,160

Index Scan using decis_delo_id on decis court_decis (cost=0.56..8.75 rows=1 width=128) (actual time=0.712..0.883 rows=0 loops=58,160)

  • Index Cond: (decis_1.delo_id = delo_id)
  • Filter: (organ_sost_kod IS NULL)
  • Rows Removed by Filter: 1
11. 64.132 257,863.682 ↓ 20,620.0 20,620 1

Nested Loop Left Join (cost=5.13..309,673.71 rows=1 width=1,013) (actual time=53,881.570..257,863.682 rows=20,620 loops=1)

12. 61.589 248,499.930 ↓ 20,620.0 20,620 1

Nested Loop Left Join (cost=4.70..309,672.10 rows=1 width=1,006) (actual time=53,880.960..248,499.930 rows=20,620 loops=1)

13. 76.717 247,613.541 ↓ 20,620.0 20,620 1

Nested Loop Left Join (cost=4.27..309,670.50 rows=1 width=1,002) (actual time=53,880.958..247,613.541 rows=20,620 loops=1)

14. 60.409 241,948.804 ↓ 20,620.0 20,620 1

Nested Loop (cost=3.84..309,668.11 rows=1 width=1,010) (actual time=53,880.935..241,948.804 rows=20,620 loops=1)

15. 92.547 231,330.955 ↓ 763.7 20,620 1

Nested Loop Left Join (cost=3.42..309,624.97 rows=27 width=936) (actual time=53,880.345..231,330.955 rows=20,620 loops=1)

  • Join Filter: (p.doc_n IS NOT NULL)
16. 73.374 223,980.168 ↓ 763.7 20,620 1

Nested Loop Left Join (cost=2.98..309,558.39 rows=27 width=907) (actual time=53,880.341..223,980.168 rows=20,620 loops=1)

17. 80.775 194,069.654 ↓ 763.7 20,620 1

Nested Loop (cost=2.42..309,510.23 rows=27 width=909) (actual time=53,878.936..194,069.654 rows=20,620 loops=1)

18. 82.093 183,369.579 ↓ 736.4 20,620 1

Nested Loop (cost=1.98..309,464.30 rows=28 width=877) (actual time=53,878.484..183,369.579 rows=20,620 loops=1)

19. 82.708 155,739.166 ↓ 42.4 20,620 1

Nested Loop (cost=1.42..308,602.56 rows=486 width=883) (actual time=53,878.186..155,739.166 rows=20,620 loops=1)

20. 135.635 143,160.738 ↓ 2.4 20,620 1

Nested Loop (cost=0.99..294,567.62 rows=8,578 width=864) (actual time=53,877.611..143,160.738 rows=20,620 loops=1)

  • Join Filter: (decis.delo_id = delo_s.delo_id)
21. 78.899 118,095.523 ↑ 3.6 20,620 1

Nested Loop (cost=0.43..152,167.42 rows=73,971 width=822) (actual time=53,875.963..118,095.523 rows=20,620 loops=1)

22. 105,562.144 105,562.144 ↑ 3.6 20,620 1

CTE Scan on decis (cost=0.00..1,479.42 rows=73,971 width=790) (actual time=53,873.870..105,562.144 rows=20,620 loops=1)

23. 12,454.480 12,454.480 ↑ 1.0 1 20,620

Index Scan using docs_prot_delo_id on docs_prot prot (cost=0.43..2.03 rows=1 width=32) (actual time=0.598..0.604 rows=1 loops=20,620)

  • Index Cond: (delo_id = decis.delo_id)
24. 24,929.580 24,929.580 ↑ 1.0 1 20,620

Index Scan using delo_pkey on delo delo_s (cost=0.56..1.91 rows=1 width=42) (actual time=1.193..1.209 rows=1 loops=20,620)

  • Index Cond: (delo_id = prot.delo_id)
25. 12,495.720 12,495.720 ↑ 1.0 1 20,620

Index Scan using viol_pk on viol (cost=0.43..1.63 rows=1 width=19) (actual time=0.603..0.606 rows=1 loops=20,620)

  • Index Cond: (delo_id = delo_s.delo_main_id)
26. 27,548.320 27,548.320 ↑ 1.0 1 20,620

Index Scan using delo_pkey on delo delo_f (cost=0.56..1.76 rows=1 width=14) (actual time=1.330..1.336 rows=1 loops=20,620)

  • Index Cond: (delo_id = delo_s.delo_main_id)
27. 10,619.300 10,619.300 ↑ 1.0 1 20,620

Index Scan using docs_post_pkey on docs_post p (cost=0.43..1.63 rows=1 width=38) (actual time=0.513..0.515 rows=1 loops=20,620)

  • Index Cond: (docs_id = viol.docs_post_id)
28. 29,837.140 29,837.140 ↑ 1.0 1 20,620

Index Scan using decis_docs_post_id on decis decis_f (cost=0.56..1.77 rows=1 width=10) (actual time=1.442..1.447 rows=1 loops=20,620)

  • Index Cond: (docs_post_id = p.docs_id)
  • Filter: (decis_kod = '65'::numeric)
29. 7,258.240 7,258.240 ↑ 1.0 1 20,620

Index Scan using docs_post_pkey on docs_post pt (cost=0.43..2.46 rows=1 width=67) (actual time=0.348..0.352 rows=1 loops=20,620)

  • Index Cond: (docs_id = decis.court_decis_docs_post_id)
30. 10,557.440 10,557.440 ↑ 1.0 1 20,620

Index Scan using case_delo_id_un on "case" c (cost=0.42..1.59 rows=1 width=124) (actual time=0.505..0.512 rows=1 loops=20,620)

  • Index Cond: (delo_id = delo_s.delo_id)
31. 5,588.020 5,588.020 ↑ 1.0 1 20,620

Index Scan using uchast_pkey on uchast uch (cost=0.43..2.38 rows=1 width=24) (actual time=0.271..0.271 rows=1 loops=20,620)

  • Index Cond: (uchast_id = decis.uchast_id)
32. 824.800 824.800 ↓ 0.0 0 20,620

Index Scan using kart_ul_pkey on kart_ul ul (cost=0.42..1.59 rows=1 width=17) (actual time=0.039..0.040 rows=0 loops=20,620)

  • Index Cond: (id = uch.ul_id)
33. 9,299.620 9,299.620 ↑ 1.0 1 20,620

Index Scan using kart_fl_pkey on kart_fl fl (cost=0.43..1.60 rows=1 width=19) (actual time=0.450..0.451 rows=1 loops=20,620)

  • Index Cond: (id = uch.fl_id)
34. 4,515.780 4,515.780 ↑ 1.0 448 20,620

Seq Scan on s_stotv st (cost=0.00..61.49 rows=449 width=143) (actual time=0.004..0.219 rows=448 loops=20,620)

35.          

SubPlan (for Nested Loop Left Join)

36. 124.196 20,558.140 ↑ 1.0 1 20,620

Nested Loop (cost=0.87..5.71 rows=1 width=12) (actual time=0.995..0.997 rows=1 loops=20,620)

37. 15,526.860 15,526.860 ↑ 1.0 1 20,620

Index Scan using vehs_pkey on vehs v_1 (cost=0.43..2.85 rows=1 width=6) (actual time=0.752..0.753 rows=1 loops=20,620)

  • Index Cond: (vehs_id = viol.vehs_id)
38. 4,907.084 4,907.084 ↑ 1.0 1 20,618

Index Scan using kart_docs_pkey on kart_docs kdr (cost=0.43..2.85 rows=1 width=18) (actual time=0.237..0.238 rows=1 loops=20,618)

  • Index Cond: (id = v_1.regno_id)
39. 371.160 371.160 ↑ 1.0 1 20,620

Index Scan using s_stotv_pkey on s_stotv stotv (cost=0.27..2.69 rows=1 width=15) (actual time=0.018..0.018 rows=1 loops=20,620)

  • Index Cond: (stotv_id = p.stotv_id)
40. 144.340 144.340 ↑ 1.0 1 20,620

Seq Scan on s_sstad_delo sd (cost=0.00..1.09 rows=1 width=118) (actual time=0.006..0.007 rows=1 loops=20,620)

  • Filter: (delo_s.stad_delo_kod = stad_delo_kod)
  • Rows Removed by Filter: 6
41. 123.720 123.720 ↑ 1.0 1 20,620

Seq Scan on s_interaction_type it (cost=0.00..1.02 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=20,620)

  • Filter: (id = c.interaction_type)
  • Rows Removed by Filter: 1
42. 123.720 123.720 ↑ 1.0 1 20,620

Seq Scan on s_suchast_status status (cost=0.00..1.04 rows=1 width=118) (actual time=0.005..0.006 rows=1 loops=20,620)

  • Filter: (uchast_status = uch.uchast_status)
  • Rows Removed by Filter: 2
43. 103.100 103.100 ↑ 1.0 1 20,620

Seq Scan on s_case_status cs (cost=0.00..1.23 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=20,620)

  • Filter: (id = c.status)
  • Rows Removed by Filter: 17
44. 178.344 178.344 ↑ 1.0 1 14,862

Seq Scan on s_sdecis ssd (cost=0.00..1.46 rows=1 width=54) (actual time=0.011..0.012 rows=1 loops=14,862)

  • Filter: (decis.court_decis_decis_kod = decis_kod)
  • Rows Removed by Filter: 36
45. 164.960 164.960 ↓ 0.0 0 20,620

Seq Scan on s_return_reason ret (cost=0.00..1.35 rows=1 width=32) (actual time=0.007..0.008 rows=0 loops=20,620)

  • Filter: (id = decis.vozv_court_return_reason_id)
  • Rows Removed by Filter: 28
Planning time : 5.283 ms
Execution time : 291,709.495 ms