explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LXvG

Settings
# exclusive inclusive rows x rows loops node
1. 6,019.321 825,625.415 ↓ 3,919.3 23,516 1

Nested Loop Left Join (cost=2,961,859.68..3,782,398.93 rows=6 width=1,028) (actual time=107,293.682..825,625.415 rows=23,516 loops=1)

2.          

CTE decis

3. 449.118 427,113.125 ↑ 14.5 23,516 1

Nested Loop Left Join (cost=860,133.68..2,752,253.70 rows=340,334 width=1,895) (actual time=103,422.820..427,113.125 rows=23,516 loops=1)

  • Filter: ((court_decis.delo_id IS NOT NULL) OR (vozv.delo_id IS NOT NULL))
  • Rows Removed by Filter: 199,040
4. 268.216 109,313.411 ↑ 1.5 222,546 1

Hash Right Join (cost=860,133.12..875,401.00 rows=340,334 width=1,780) (actual time=103,420.403..109,313.411 rows=222,546 loops=1)

  • Hash Cond: (vozv.delo_id = decis_1.delo_id)
5. 5,679.091 5,679.091 ↓ 8.5 16,325 1

Index Scan using decis_ispoln_kod_dat on decis_ispoln vozv (cost=0.56..9,555.05 rows=1,922 width=1,768) (actual time=53.189..5,679.091 rows=16,325 loops=1)

  • Index Cond: (stad_ispoln_kod = '46'::numeric)
  • Filter: ((create_time <= (('now'::cstring)::date - '1 day'::interval)) AND (create_time >= date_trunc('year'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Rows Removed by Filter: 872
6. 86.126 103,366.104 ↑ 1.5 222,534 1

Hash (cost=854,216.38..854,216.38 rows=340,334 width=12) (actual time=103,366.104..103,366.104 rows=222,534 loops=1)

  • Buckets: 262,144 Batches: 4 Memory Usage: 4,551kB
7. 4,877.946 103,279.978 ↑ 1.5 222,534 1

Bitmap Heap Scan on decis decis_1 (cost=503,104.65..854,216.38 rows=340,334 width=12) (actual time=98,425.163..103,279.978 rows=222,534 loops=1)

  • Recheck Cond: ((decis_dat >= date_trunc('year'::text, (('now'::cstring)::date - '3 years'::interval))) AND (decis_dat <= (('now'::cstring)::date - '1 day'::interval)) AND ((ogai_sost_kod)::text = '45597'::text) AND (decis_kod = '95'::numeric))
  • Heap Blocks: exact=95,040
8. 98,402.032 98,402.032 ↑ 1.5 222,544 1

Bitmap Index Scan on decis_det_ogai_kod (cost=0.00..503,019.57 rows=340,334 width=0) (actual time=98,402.032..98,402.032 rows=222,544 loops=1)

  • Index Cond: ((decis_dat >= date_trunc('year'::text, (('now'::cstring)::date - '3 years'::interval))) AND (decis_dat <= (('now'::cstring)::date - '1 day'::interval)) AND ((ogai_sost_kod)::text = '45597'::text) AND (decis_kod = '95'::numeric))
9. 317,350.596 317,350.596 ↓ 0.0 0 222,546

Index Scan using decis_delo_id on decis court_decis (cost=0.56..5.50 rows=1 width=115) (actual time=1.353..1.426 rows=0 loops=222,546)

  • Index Cond: (decis_1.delo_id = delo_id)
  • Filter: ((organ_sost_kod IS NULL) AND (create_time <= (('now'::cstring)::date - '1 day'::interval)) AND (create_time >= date_trunc('year'::text, (('now'::cstring)::date)::timestamp with time zone)))
  • Rows Removed by Filter: 2
10. 83.187 746,257.474 ↓ 3,919.3 23,516 1

Nested Loop Left Join (cost=209,605.71..1,030,041.82 rows=6 width=1,009) (actual time=107,273.833..746,257.474 rows=23,516 loops=1)

11. 67.330 725,339.111 ↓ 3,919.3 23,516 1

Nested Loop Left Join (cost=209,605.28..1,030,032.11 rows=6 width=1,002) (actual time=107,273.830..725,339.111 rows=23,516 loops=1)

12. 94.104 723,366.985 ↓ 3,919.3 23,516 1

Nested Loop Left Join (cost=209,604.85..1,030,022.48 rows=6 width=998) (actual time=107,273.803..723,366.985 rows=23,516 loops=1)

13. 91.843 715,442.053 ↓ 3,919.3 23,516 1

Nested Loop (cost=209,604.42..1,030,011.02 rows=6 width=1,006) (actual time=107,273.211..715,442.053 rows=23,516 loops=1)

14. 115.295 697,760.242 ↓ 188.1 23,516 1

Nested Loop Left Join (cost=209,603.99..1,029,810.32 rows=125 width=932) (actual time=107,272.056..697,760.242 rows=23,516 loops=1)

  • Join Filter: (p.doc_n IS NOT NULL)
15. 94.307 675,728.035 ↓ 188.1 23,516 1

Nested Loop Left Join (cost=209,603.56..1,029,541.11 rows=125 width=908) (actual time=107,272.052..675,728.035 rows=23,516 loops=1)

16. 114.662 618,983.684 ↓ 188.1 23,516 1

Nested Loop (cost=209,602.99..1,029,317.43 rows=125 width=909) (actual time=107,269.840..618,983.684 rows=23,516 loops=1)

17. 99.182 585,523.334 ↓ 179.5 23,516 1

Nested Loop (cost=209,602.56..1,029,101.58 rows=131 width=877) (actual time=107,269.503..585,523.334 rows=23,516 loops=1)

18. 114.162 523,694.652 ↓ 10.1 23,516 1

Nested Loop (cost=209,602.00..1,024,937.23 rows=2,339 width=883) (actual time=107,267.325..523,694.652 rows=23,516 loops=1)

19. 151.786 479,934.794 ↑ 1.8 23,516 1

Nested Loop (cost=209,601.57..956,025.55 rows=41,925 width=864) (actual time=107,267.297..479,934.794 rows=23,516 loops=1)

  • Join Filter: (decis.delo_id = delo_s.delo_id)
20. 1,439.687 432,421.784 ↑ 14.5 23,516 1

Hash Join (cost=209,601.01..302,274.70 rows=340,334 width=822) (actual time=107,265.935..432,421.784 rows=23,516 loops=1)

  • Hash Cond: (decis.delo_id = prot.delo_id)
21. 427,299.605 427,299.605 ↑ 14.5 23,516 1

CTE Scan on decis (cost=0.00..6,806.68 rows=340,334 width=790) (actual time=103,422.824..427,299.605 rows=23,516 loops=1)

22. 529.755 3,682.492 ↓ 1.0 1,898,405 1

Hash (cost=172,943.78..172,943.78 rows=1,895,778 width=32) (actual time=3,682.491..3,682.492 rows=1,898,405 loops=1)

  • Buckets: 131,072 Batches: 32 Memory Usage: 4,832kB
23. 3,152.737 3,152.737 ↓ 1.0 1,898,405 1

Seq Scan on docs_prot prot (cost=0.00..172,943.78 rows=1,895,778 width=32) (actual time=0.911..3,152.737 rows=1,898,405 loops=1)

24. 47,361.224 47,361.224 ↑ 1.0 1 23,516

Index Scan using delo_pkey on delo delo_s (cost=0.56..1.91 rows=1 width=42) (actual time=2.002..2.014 rows=1 loops=23,516)

  • Index Cond: (delo_id = prot.delo_id)
25. 43,645.696 43,645.696 ↑ 1.0 1 23,516

Index Scan using viol_pk on viol (cost=0.43..1.63 rows=1 width=19) (actual time=1.847..1.856 rows=1 loops=23,516)

  • Index Cond: (delo_id = delo_s.delo_main_id)
26. 61,729.500 61,729.500 ↑ 1.0 1 23,516

Index Scan using delo_pkey on delo delo_f (cost=0.56..1.77 rows=1 width=14) (actual time=2.609..2.625 rows=1 loops=23,516)

  • Index Cond: (delo_id = delo_s.delo_main_id)
27. 33,345.688 33,345.688 ↑ 1.0 1 23,516

Index Scan using docs_post_pkey on docs_post p (cost=0.43..1.64 rows=1 width=38) (actual time=1.412..1.418 rows=1 loops=23,516)

  • Index Cond: (docs_id = viol.docs_post_id)
28. 56,650.044 56,650.044 ↑ 1.0 1 23,516

Index Scan using decis_docs_post_id on decis decis_f (cost=0.56..1.78 rows=1 width=11) (actual time=2.399..2.409 rows=1 loops=23,516)

  • Index Cond: (docs_post_id = p.docs_id)
  • Filter: (decis_kod = '65'::numeric)
29. 21,916.912 21,916.912 ↑ 1.0 1 23,516

Index Scan using docs_post_pkey on docs_post pt (cost=0.43..2.14 rows=1 width=62) (actual time=0.923..0.932 rows=1 loops=23,516)

  • Index Cond: (docs_id = decis.court_decis_docs_post_id)
30. 17,589.968 17,589.968 ↑ 1.0 1 23,516

Index Scan using case_delo_id_un on "case" c (cost=0.42..1.60 rows=1 width=124) (actual time=0.745..0.748 rows=1 loops=23,516)

  • Index Cond: (delo_id = delo_s.delo_id)
31. 7,830.828 7,830.828 ↑ 1.0 1 23,516

Index Scan using uchast_pkey on uchast uch (cost=0.43..1.90 rows=1 width=24) (actual time=0.333..0.333 rows=1 loops=23,516)

  • Index Cond: (uchast_id = decis.uchast_id)
32. 1,904.796 1,904.796 ↓ 0.0 0 23,516

Index Scan using kart_ul_pkey on kart_ul ul (cost=0.42..1.60 rows=1 width=17) (actual time=0.080..0.081 rows=0 loops=23,516)

  • Index Cond: (id = uch.ul_id)
33. 20,835.176 20,835.176 ↑ 1.0 1 23,516

Index Scan using kart_fl_pkey on kart_fl fl (cost=0.43..1.61 rows=1 width=19) (actual time=0.885..0.886 rows=1 loops=23,516)

  • Index Cond: (id = uch.fl_id)
34. 282.192 282.192 ↑ 1.0 1 23,516

Index Scan using s_stotv_pkey on s_stotv st (cost=0.27..1.34 rows=1 width=143) (actual time=0.011..0.012 rows=1 loops=23,516)

  • Index Cond: (stotv_id = decis.court_decis_stotv_id)
35.          

SubPlan (for Nested Loop Left Join)

36. 166.490 71,841.380 ↑ 1.0 1 23,516

Nested Loop (cost=0.87..5.71 rows=1 width=12) (actual time=3.042..3.055 rows=1 loops=23,516)

37. 49,595.244 49,595.244 ↑ 1.0 1 23,516

Index Scan using vehs_pkey on vehs v_1 (cost=0.43..2.85 rows=1 width=6) (actual time=2.101..2.109 rows=1 loops=23,516)

  • Index Cond: (vehs_id = viol.vehs_id)
38. 22,079.646 22,079.646 ↑ 1.0 1 23,514

Index Scan using kart_docs_pkey on kart_docs kdr (cost=0.43..2.85 rows=1 width=18) (actual time=0.936..0.939 rows=1 loops=23,514)

  • Index Cond: (id = v_1.regno_id)
39. 399.772 399.772 ↑ 1.0 1 23,516

Index Scan using s_stotv_pkey on s_stotv stotv (cost=0.27..2.69 rows=1 width=15) (actual time=0.016..0.017 rows=1 loops=23,516)

  • Index Cond: (stotv_id = p.stotv_id)
40. 117.580 117.580 ↑ 1.0 1 23,516

Seq Scan on s_sstad_delo sd (cost=0.00..1.09 rows=1 width=118) (actual time=0.004..0.005 rows=1 loops=23,516)

  • Filter: (delo_s.stad_delo_kod = stad_delo_kod)
  • Rows Removed by Filter: 6
41. 141.096 141.096 ↑ 1.0 1 23,516

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=23,516)

  • Filter: (id = c.interaction_type)
  • Rows Removed by Filter: 1
42. 117.580 117.580 ↑ 1.0 1 23,516

Seq Scan on s_suchast_status status (cost=0.00..1.04 rows=1 width=118) (actual time=0.004..0.005 rows=1 loops=23,516)

  • Filter: (uchast_status = uch.uchast_status)
  • Rows Removed by Filter: 2
43. 117.580 117.580 ↑ 1.0 1 23,516

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=23,516)

  • Filter: (id = c.status)
  • Rows Removed by Filter: 17
44. 190.344 190.344 ↑ 1.0 1 17,304

Seq Scan on s_sdecis ssd (cost=0.00..1.46 rows=1 width=54) (actual time=0.010..0.011 rows=1 loops=17,304)

  • Filter: (decis.court_decis_decis_kod = decis_kod)
  • Rows Removed by Filter: 36
45. 141.096 141.096 ↓ 0.0 0 23,516

Seq Scan on s_return_reason ret (cost=0.00..1.35 rows=1 width=32) (actual time=0.006..0.006 rows=0 loops=23,516)

  • Filter: (id = decis.vozv_court_return_reason_id)
  • Rows Removed by Filter: 28
Planning time : 43.368 ms
Execution time : 825,631.019 ms