explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J1VG

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 342,273.939 ↓ 3.0 3 1

Limit (cost=39.64..505,425.89 rows=1 width=454) (actual time=89,596.611..342,273.939 rows=3 loops=1)

  • Buffers: shared hit=56114124 read=5778 dirtied=4089
2. 0.031 342,273.934 ↓ 3.0 3 1

Nested Loop Left Join (cost=39.64..505,425.89 rows=1 width=454) (actual time=89,596.611..342,273.934 rows=3 loops=1)

  • Buffers: shared hit=56114124 read=5778 dirtied=4089
3. 0.031 342,084.333 ↓ 3.0 3 1

Nested Loop Left Join (cost=21.49..505,381.27 rows=1 width=411) (actual time=89,407.227..342,084.333 rows=3 loops=1)

  • Buffers: shared hit=56114064 read=5774 dirtied=4089
4. 0.046 341,799.326 ↓ 3.0 3 1

Nested Loop Left Join (cost=20.93..505,379.99 rows=1 width=372) (actual time=89,290.059..341,799.326 rows=3 loops=1)

  • Join Filter: (t2._fld7983 = t16._fld20243)
  • Buffers: shared hit=56114058 read=5765 dirtied=4089
5. 1,171.492 341,561.650 ↓ 3.0 3 1

Nested Loop (cost=20.37..505,372.41 rows=1 width=355) (actual time=89,251.688..341,561.650 rows=3 loops=1)

  • Join Filter: ((t2._fld17407rref = t4._document300_idrref) AND (t2._fld17352 = t4._fld7862))
  • Rows Removed by Join Filter: 5064472
  • Buffers: shared hit=56114049 read=5755 dirtied=4084
6. 51.209 206,204.945 ↓ 15,583.0 15,583 1

Nested Loop Left Join (cost=1.67..22.79 rows=1 width=303) (actual time=0.059..206,204.945 rows=15,583 loops=1)

  • Join Filter: ((t2._fld7983 = t3._fld15664) AND (t2._fld7979rref = t3._fld15662rref) AND (t2._fld7981rref = t3._fld15663rref))
  • Rows Removed by Join Filter: 230
  • Buffers: shared hit=155385 read=5749 dirtied=4084
7. 52.988 204,486.355 ↓ 15,583.0 15,583 1

Nested Loop (cost=1.11..15.16 rows=1 width=298) (actual time=0.046..204,486.355 rows=15,583 loops=1)

  • Buffers: shared hit=81616 read=5701 dirtied=4035
8. 149,170.955 149,170.955 ↓ 15,471.0 15,471 1

Index Scan Backward using _document302_bydocdate_trl on _document302 t1 (cost=0.56..7.58 rows=1 width=140) (actual time=0.027..149,170.955 rows=15,471 loops=1)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_date_time >= '2020-02-05 00:00:00'::timestamp without time zone) AND (_date_time <= '2020-02-11 23:59:59'::timestamp without time zone) AND (_marked = false))
  • Filter: (NOT _marked)
  • Buffers: shared hit=5122 read=4029 dirtied=3566
9. 55,262.412 55,262.412 ↑ 1.0 1 15,471

Index Scan using _document302_vt7973_intkeyind on _document302_vt7973 t2 (cost=0.56..7.57 rows=1 width=181) (actual time=3.563..3.572 rows=1 loops=15,471)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_document302_idrref = t1._idrref))
  • Buffers: shared hit=76494 read=1672 dirtied=469
10. 1,667.381 1,667.381 ↑ 1.0 1 15,583

Index Scan using _accumrg15660_bydims17975_rtrn on _accumrg15660 t3 (cost=0.56..7.61 rows=1 width=78) (actual time=0.107..0.107 rows=1 loops=15,583)

  • Index Cond: ((_fld717 = '0'::numeric) AND (t1._idrref = _fld15661rref) AND (_recordertref = '\\000\\000\\001W'::bytea))
  • Filter: (_recordkind = '1'::numeric)
  • Buffers: shared hit=73769 read=48 dirtied=49
11. 5,765.398 134,185.213 ↑ 31.5 325 15,583

Nested Loop Semi Join (cost=18.70..505,196.00 rows=10,241 width=91) (actual time=0.087..8.611 rows=325 loops=15,583)

  • Join Filter: (CASE WHEN (SubPlan 1) THEN true ELSE false END = CASE WHEN (alternatives: SubPlan 2 or hashed SubPlan 3) THEN true ELSE false END)
  • Buffers: shared hit=55958664 read=6
12. 5,750.127 47,387.903 ↑ 63.0 325 15,583

Nested Loop (cost=0.99..119,360.34 rows=20,481 width=108) (actual time=0.063..3.041 rows=325 loops=15,583)

  • Buffers: shared hit=25275620 read=6
13. 11,250.926 11,250.926 ↑ 63.0 325 15,583

Index Scan using _document300_vt7842_11022020_a on _document300_vt7842 t4 (cost=0.56..7.58 rows=20,481 width=77) (actual time=0.054..0.722 rows=325 loops=15,583)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld17170 >= 'AWKAA'::mvarchar COLLATE "default") AND (_fld17170 < 'AWKAB'::mvarchar COLLATE "default"))
  • Filter: (_fld17170 ~~ 'AWKAA%'::mvarchar)
  • Buffers: shared hit=4986554 read=6
14. 30,386.850 30,386.850 ↑ 1.0 1 5,064,475

Index Scan using _document300hpk on _document300 t6 (cost=0.43..5.82 rows=1 width=37) (actual time=0.006..0.006 rows=1 loops=5,064,475)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_idrref = t4._document300_idrref))
  • Buffers: shared hit=20289066
15. 0.000 0.000 ↑ 1.0 1 5,064,475

Materialize (cost=17.72..24.82 rows=1 width=17) (actual time=0.000..0.000 rows=1 loops=5,064,475)

  • Buffers: shared hit=20
16. 0.003 0.085 ↑ 1.0 1 1

Nested Loop (cost=17.72..24.82 rows=1 width=17) (actual time=0.085..0.085 rows=1 loops=1)

  • Buffers: shared hit=20
17. 0.001 0.072 ↑ 1.0 1 1

Nested Loop (cost=17.45..24.52 rows=1 width=37) (actual time=0.072..0.072 rows=1 loops=1)

  • Buffers: shared hit=16
18. 0.015 0.015 ↑ 1.0 1 1

Index Only Scan using _reference105_byfield2798_sr on _reference105 t9 (cost=0.41..3.92 rows=1 width=19) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (_fld2789 = 'Документ.ЗаказКлиента'::mvarchar)
  • Heap Fetches: 0
  • Buffers: shared hit=4
19. 0.006 0.056 ↑ 1.0 1 1

Nested Loop (cost=17.04..20.59 rows=1 width=55) (actual time=0.056..0.056 rows=1 loops=1)

  • Buffers: shared hit=12
20. 0.005 0.039 ↑ 1.0 1 1

HashAggregate (cost=16.63..16.64 rows=1 width=19) (actual time=0.039..0.039 rows=1 loops=1)

  • Group Key: t10._reference73_idrref
  • Buffers: shared hit=8
21. 0.009 0.034 ↓ 3.0 3 1

Nested Loop (cost=0.56..16.62 rows=1 width=19) (actual time=0.033..0.034 rows=3 loops=1)

  • Buffers: shared hit=8
22. 0.011 0.011 ↑ 1.0 2 1

Index Only Scan using _inforg14726_bydims14730_rr on _inforg14726 t11 (cost=0.28..8.86 rows=2 width=24) (actual time=0.010..0.011 rows=2 loops=1)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld14728_type = '\\010'::bytea) AND (_fld14728_rtref = '\\000\\000\\000\\254'::bytea) AND (_fld14728_rrref = 'P\\2140Z:wL\\341\\021\\347\\ (...)
  • Heap Fetches: 0
  • Buffers: shared hit=3
23. 0.014 0.014 ↑ 1.5 2 2

Index Only Scan using _reference73_vt2103_byfield2106_rr on _reference73_vt2103 t10 (cost=0.28..3.85 rows=3 width=49) (actual time=0.007..0.007 rows=2 loops=2)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld2105_type = t11._fld14727_type) AND (_fld2105_rtref = t11._fld14727_rtref) AND (_fld2105_rrref = t11._fld14727_rrref))
  • Heap Fetches: 0
  • Buffers: shared hit=5
24. 0.011 0.011 ↑ 1.0 1 1

Index Only Scan using _inforg14845_bydims14850_rr on _inforg14845 t8 (cost=0.42..3.94 rows=1 width=36) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld14847rref = t10._reference73_idrref) AND (_fld14846rref = t9._idrref))
  • Heap Fetches: 0
  • Buffers: shared hit=4
25. 0.010 0.010 ↑ 1.0 1 1

Index Only Scan using _reference73hpk on _reference73 t7 (cost=0.27..0.29 rows=1 width=17) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_idrref = t8._fld14847rref))
  • Heap Fetches: 1
  • Buffers: shared hit=4
26.          

SubPlan (for Nested Loop Semi Join)

27. 5,064.475 81,031.600 ↓ 0.0 0 5,064,475

Nested Loop (cost=0.83..11.39 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=5,064,475)

  • Buffers: shared hit=30682928
28. 45,580.275 45,580.275 ↑ 1.0 1 5,064,475

Index Only Scan using _inforg13761_bydims_rrn on _inforg13761 t13 (cost=0.42..7.45 rows=1 width=24) (actual time=0.009..0.009 rows=1 loops=5,064,475)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld13762_type = '\\010'::bytea) AND (_fld13762_rtref = '\\000\\000\\000\\242'::bytea) AND (_fld13762_rrref = t6._fld7777rref))
  • Heap Fetches: 171413
  • Buffers: shared hit=15489503
29. 30,386.850 30,386.850 ↓ 0.0 0 5,064,475

Index Only Scan using _inforg13931_bydims13935_rr on _inforg13931 t12 (cost=0.41..3.93 rows=1 width=27) (actual time=0.006..0.006 rows=0 loops=5,064,475)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld13933_type = t13._fld13763_type) AND (_fld13933_rtref = t13._fld13763_rtref) AND (_fld13933_rrref = t13._fld13763_rrref) AND (_fld13932rref = t7._idrref))
  • Heap Fetches: 0
  • Buffers: shared hit=15193425
30. 0.000 0.000 ↓ 0.0 0

Index Scan using _inforg13938_bydims_rr on _inforg13938 t14 (cost=0.41..7.43 rows=1 width=0) (never executed)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld13939rref = t7._idrref) AND (_fld13940_type = '\\010'::bytea) AND (_fld13940_rtref = '\\000\\000\\000\\242'::bytea))
  • Filter: (NOT _fld13941)
31. 0.312 0.312 ↓ 3.9 148 1

Index Only Scan using _inforg13938_byresource13945_lrr on _inforg13938 t14_1 (cost=0.41..189.35 rows=38 width=17) (actual time=0.011..0.312 rows=148 loops=1)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld13941 = false) AND (_fld13940_type = '\\010'::bytea) AND (_fld13940_rtref = '\\000\\000\\000\\242'::bytea))
  • Filter: (NOT _fld13941)
  • Heap Fetches: 2
  • Buffers: shared hit=96
32. 237.630 237.630 ↑ 1.0 1 3

Index Scan using _inforgs17524_bydims_rn on _inforgsl17524 t16 (cost=0.56..7.57 rows=1 width=38) (actual time=68.262..79.210 rows=1 loops=3)

  • Index Cond: ((_fld717 = '0'::numeric) AND (t1._idrref = _fld17495rref))
  • Buffers: shared hit=9 read=10 dirtied=5
33. 284.976 284.976 ↑ 1.0 1 3

Index Scan using _reference140ng_pkey1 on _reference140 t17 (cost=0.56..1.26 rows=1 width=56) (actual time=94.991..94.992 rows=1 loops=3)

  • Index Cond: (t2._fld7979rref = _idrref)
  • Buffers: shared hit=6 read=9
34. 0.057 189.570 ↑ 1.0 1 3

Nested Loop Semi Join (cost=18.15..44.61 rows=1 width=34) (actual time=63.188..63.190 rows=1 loops=3)

  • Join Filter: (CASE WHEN (SubPlan 4) THEN true ELSE false END = CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6) THEN true ELSE false END)
  • Buffers: shared hit=60 read=4
35. 0.072 0.072 ↑ 1.0 1 3

Index Scan using _document300hpk on _document300 t19 (cost=0.43..0.95 rows=1 width=51) (actual time=0.022..0.024 rows=1 loops=3)

  • Index Cond: ((_fld717 = '0'::numeric) AND (t2._fld17407rref = _idrref))
  • Buffers: shared hit=12
36. 0.005 0.153 ↑ 1.0 1 3

Materialize (cost=17.72..24.82 rows=1 width=17) (actual time=0.051..0.051 rows=1 loops=3)

  • Buffers: shared hit=20
37. 0.005 0.148 ↑ 1.0 1 1

Nested Loop (cost=17.72..24.82 rows=1 width=17) (actual time=0.148..0.148 rows=1 loops=1)

  • Buffers: shared hit=20
38. 0.003 0.129 ↑ 1.0 1 1

Nested Loop (cost=17.45..24.52 rows=1 width=37) (actual time=0.129..0.129 rows=1 loops=1)

  • Buffers: shared hit=16
39. 0.027 0.027 ↑ 1.0 1 1

Index Only Scan using _reference105_byfield2798_sr on _reference105 t22 (cost=0.41..3.92 rows=1 width=19) (actual time=0.027..0.027 rows=1 loops=1)

  • Index Cond: (_fld2789 = 'Документ.ЗаказКлиента'::mvarchar)
  • Heap Fetches: 0
  • Buffers: shared hit=4
40. 0.007 0.099 ↑ 1.0 1 1

Nested Loop (cost=17.04..20.59 rows=1 width=55) (actual time=0.099..0.099 rows=1 loops=1)

  • Buffers: shared hit=12
41. 0.010 0.074 ↑ 1.0 1 1

HashAggregate (cost=16.63..16.64 rows=1 width=19) (actual time=0.074..0.074 rows=1 loops=1)

  • Group Key: t23._reference73_idrref
  • Buffers: shared hit=8
42. 0.017 0.064 ↓ 3.0 3 1

Nested Loop (cost=0.56..16.62 rows=1 width=19) (actual time=0.059..0.064 rows=3 loops=1)

  • Buffers: shared hit=8
43. 0.019 0.019 ↑ 1.0 2 1

Index Only Scan using _inforg14726_bydims14730_rr on _inforg14726 t24 (cost=0.28..8.86 rows=2 width=24) (actual time=0.018..0.019 rows=2 loops=1)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld14728_type = '\\010'::bytea) AND (_fld14728_rtref = '\\000\\000\\000\\254'::bytea) AND (_fld14728_rrref = 'P\\2140Z:wL\\341\\021\\347\\024\\222\\030#\\25 (...)
  • Heap Fetches: 0
  • Buffers: shared hit=3
44. 0.028 0.028 ↑ 1.5 2 2

Index Only Scan using _reference73_vt2103_byfield2106_rr on _reference73_vt2103 t23 (cost=0.28..3.85 rows=3 width=49) (actual time=0.013..0.014 rows=2 loops=2)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld2105_type = t24._fld14727_type) AND (_fld2105_rtref = t24._fld14727_rtref) AND (_fld2105_rrref = t24._fld14727_rrref))
  • Heap Fetches: 0
  • Buffers: shared hit=5
45. 0.018 0.018 ↑ 1.0 1 1

Index Only Scan using _inforg14845_bydims14850_rr on _inforg14845 t21 (cost=0.42..3.94 rows=1 width=36) (actual time=0.018..0.018 rows=1 loops=1)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld14847rref = t23._reference73_idrref) AND (_fld14846rref = t22._idrref))
  • Heap Fetches: 0
  • Buffers: shared hit=4
46. 0.014 0.014 ↑ 1.0 1 1

Index Only Scan using _reference73hpk on _reference73 t20 (cost=0.27..0.29 rows=1 width=17) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_idrref = t21._fld14847rref))
  • Heap Fetches: 1
  • Buffers: shared hit=4
47.          

SubPlan (for Nested Loop Semi Join)

48. 0.030 0.135 ↓ 0.0 0 3

Nested Loop (cost=0.83..11.39 rows=1 width=0) (actual time=0.045..0.045 rows=0 loops=3)

  • Buffers: shared hit=20
49. 0.060 0.060 ↑ 1.0 1 3

Index Only Scan using _inforg13761_bydims_rrn on _inforg13761 t26 (cost=0.42..7.45 rows=1 width=24) (actual time=0.019..0.020 rows=1 loops=3)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld13762_type = '\\010'::bytea) AND (_fld13762_rtref = '\\000\\000\\000\\242'::bytea) AND (_fld13762_rrref = t19._fld7777rref))
  • Heap Fetches: 1
  • Buffers: shared hit=11
50. 0.045 0.045 ↓ 0.0 0 3

Index Only Scan using _inforg13931_bydims13935_rr on _inforg13931 t25 (cost=0.41..3.93 rows=1 width=27) (actual time=0.015..0.015 rows=0 loops=3)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld13933_type = t26._fld13763_type) AND (_fld13933_rtref = t26._fld13763_rtref) AND (_fld13933_rrref = t26._fld13763_rrref) AND (_fld13932rref = t20._idrref))
  • Heap Fetches: 0
  • Buffers: shared hit=9
51. 189.153 189.153 ↓ 0.0 0 3

Index Scan using _inforg13938_bydims_rr on _inforg13938 t27 (cost=0.41..7.43 rows=1 width=0) (actual time=63.051..63.051 rows=0 loops=3)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld13939rref = t20._idrref) AND (_fld13940_type = '\\010'::bytea) AND (_fld13940_rtref = '\\000\\000\\000\\242'::bytea))
  • Filter: (NOT _fld13941)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=8 read=4
52. 0.000 0.000 ↓ 0.0 0

Index Only Scan using _inforg13938_byresource13945_lrr on _inforg13938 t27_1 (cost=0.41..189.35 rows=38 width=17) (never executed)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld13941 = false) AND (_fld13940_type = '\\010'::bytea) AND (_fld13940_rtref = '\\000\\000\\000\\242'::bytea))
  • Filter: (NOT _fld13941)
  • Heap Fetches: 0
Planning time : 54.300 ms
Execution time : 342,274.713 ms