explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vsc3

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 2,086.067 ↑ 14.3 3 1

Limit (cost=1,244,902.12..1,244,902.22 rows=43 width=454) (actual time=2,086.067..2,086.067 rows=3 loops=1)

  • Buffers: shared hit=614746
2. 0.013 2,086.067 ↑ 14.3 3 1

Sort (cost=1,244,902.12..1,244,902.22 rows=43 width=454) (actual time=2,086.067..2,086.067 rows=3 loops=1)

  • Sort Key: t1._date_time DESC
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=614746
3. 0.007 2,086.054 ↑ 14.3 3 1

Nested Loop Left Join (cost=1,017,470.72..1,244,900.95 rows=43 width=454) (actual time=2,016.462..2,086.054 rows=3 loops=1)

  • Buffers: shared hit=614746
4. 0.005 2,085.900 ↑ 14.3 3 1

Nested Loop Left Join (cost=1,017,452.57..1,242,982.23 rows=43 width=411) (actual time=2,016.362..2,085.900 rows=3 loops=1)

  • Buffers: shared hit=614682
5. 0.005 2,085.877 ↑ 14.3 3 1

Nested Loop Left Join (cost=1,017,452.01..1,242,927.53 rows=43 width=372) (actual time=2,016.352..2,085.877 rows=3 loops=1)

  • Join Filter: (t2._fld7983 = t16._fld20243)
  • Buffers: shared hit=614667
6. 2.353 2,085.851 ↑ 14.3 3 1

Hash Join (cost=1,017,451.45..1,242,652.01 rows=43 width=355) (actual time=2,016.341..2,085.851 rows=3 loops=1)

  • Hash Cond: ((t2._fld17407rref = t4._document300_idrref) AND (t2._fld17352 = t4._fld7862))
  • Buffers: shared hit=614652
7. 8.709 118.164 ↓ 1.1 15,583 1

Nested Loop Left Join (cost=1.67..220,719.92 rows=14,342 width=303) (actual time=0.076..118.164 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=160600
8. 8.627 62.706 ↓ 1.1 15,583 1

Nested Loop (cost=1.11..116,149.48 rows=14,342 width=298) (actual time=0.060..62.706 rows=15,583 loops=1)

  • Buffers: shared hit=86783
9. 7.666 7.666 ↓ 1.1 15,471 1

Index Scan Backward using _document302_bydocdate_trl on _document302 t1 (cost=0.56..22,026.60 rows=14,175 width=140) (actual time=0.040..7.666 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=9056
10. 46.413 46.413 ↑ 1.0 1 15,471

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

  • Index Cond: ((_fld717 = '0'::numeric) AND (_document302_idrref = t1._idrref))
  • Buffers: shared hit=77727
11. 46.749 46.749 ↑ 1.0 1 15,583

Index Scan using _accumrg15660_bydims17975_rtrn on _accumrg15660 t3 (cost=0.56..7.27 rows=1 width=78) (actual time=0.003..0.003 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=73817
12. 0.339 1,965.334 ↑ 31.5 325 1

Hash (cost=1,017,296.17..1,017,296.17 rows=10,241 width=91) (actual time=1,965.334..1,965.334 rows=325 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 168kB
  • Buffers: shared hit=454052
13. 0.847 1,964.995 ↑ 31.5 325 1

Nested Loop Semi Join (cost=18.15..1,017,296.17 rows=10,241 width=91) (actual time=52.805..1,964.995 rows=325 loops=1)

  • 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=454052
14. 0.460 1,959.594 ↑ 63.0 325 1

Nested Loop (cost=0.43..631,460.51 rows=20,481 width=108) (actual time=52.295..1,959.594 rows=325 loops=1)

  • Buffers: shared hit=451966
15. 1,956.859 1,956.859 ↑ 63.0 325 1

Seq Scan on _document300_vt7842 t4 (cost=0.00..512,107.75 rows=20,481 width=77) (actual time=52.269..1,956.859 rows=325 loops=1)

  • Filter: ((_fld17170 ~~ 'AWKAA%'::mvarchar) AND (_fld717 = '0'::numeric))
  • Rows Removed by Filter: 4095925
  • Buffers: shared hit=450664
16. 2.275 2.275 ↑ 1.0 1 325

Index Scan using _document300hpk on _document300 t6 (cost=0.43..5.82 rows=1 width=37) (actual time=0.007..0.007 rows=1 loops=325)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_idrref = t4._document300_idrref))
  • Buffers: shared hit=1302
17. 0.000 0.000 ↑ 1.0 1 325

Materialize (cost=17.72..24.82 rows=1 width=17) (actual time=0.000..0.000 rows=1 loops=325)

  • Buffers: shared hit=20
18. 0.003 0.090 ↑ 1.0 1 1

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

  • Buffers: shared hit=20
19. 0.002 0.076 ↑ 1.0 1 1

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

  • Buffers: shared hit=16
20. 0.018 0.018 ↑ 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.018..0.018 rows=1 loops=1)

  • Index Cond: (_fld2789 = 'Документ.ЗаказКлиента'::mvarchar)
  • Heap Fetches: 0
  • Buffers: shared hit=4
21. 0.003 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
22. 0.008 0.043 ↑ 1.0 1 1

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

  • Group Key: t10._reference73_idrref
  • Buffers: shared hit=8
23. 0.004 0.035 ↓ 3.0 3 1

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

  • Buffers: shared hit=8
24. 0.013 0.013 ↑ 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.011..0.013 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 (...)
  • Heap Fetches: 0
  • Buffers: shared hit=3
25. 0.018 0.018 ↑ 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.009..0.009 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
26. 0.010 0.010 ↑ 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.010..0.010 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
27. 0.011 0.011 ↑ 1.0 1 1

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

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

SubPlan (for Nested Loop Semi Join)

29. 0.325 4.225 ↓ 0.0 0 325

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

  • Buffers: shared hit=1970
30. 2.600 2.600 ↑ 1.0 1 325

Index Only Scan using _inforg13761_bydims_rrn on _inforg13761 t13 (cost=0.42..7.45 rows=1 width=24) (actual time=0.007..0.008 rows=1 loops=325)

  • 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: 11
  • Buffers: shared hit=995
31. 1.300 1.300 ↓ 0.0 0 325

Index Only Scan using _inforg13931_bydims13935_rr on _inforg13931 t12 (cost=0.41..3.93 rows=1 width=27) (actual time=0.004..0.004 rows=0 loops=325)

  • 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._idrre (...)
  • Heap Fetches: 0
  • Buffers: shared hit=975
32. 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)
33. 0.329 0.329 ↓ 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.012..0.329 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
34. 0.021 0.021 ↑ 1.0 1 3

Index Scan using _inforgs17524_bydims_rn on _inforgsl17524 t16 (cost=0.56..6.39 rows=1 width=38) (actual time=0.006..0.007 rows=1 loops=3)

  • Index Cond: ((_fld717 = '0'::numeric) AND (t1._idrref = _fld17495rref))
  • Buffers: shared hit=15
35. 0.018 0.018 ↑ 1.0 1 3

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

  • Index Cond: (t2._fld7979rref = _idrref)
  • Buffers: shared hit=15
36. 0.012 0.147 ↑ 1.0 1 3

Nested Loop Semi Join (cost=18.15..44.61 rows=1 width=34) (actual time=0.049..0.049 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=64
37. 0.018 0.018 ↑ 1.0 1 3

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

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

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

  • Buffers: shared hit=20
39. 0.001 0.057 ↑ 1.0 1 1

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

  • Buffers: shared hit=20
40. 0.002 0.049 ↑ 1.0 1 1

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

  • Buffers: shared hit=16
41. 0.011 0.011 ↑ 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.011..0.011 rows=1 loops=1)

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

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

  • Buffers: shared hit=12
43. 0.005 0.026 ↑ 1.0 1 1

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

  • Group Key: t23._reference73_idrref
  • Buffers: shared hit=8
44. 0.002 0.021 ↓ 3.0 3 1

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

  • Buffers: shared hit=8
45. 0.007 0.007 ↑ 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.007..0.007 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\\03 (...)
  • Heap Fetches: 0
  • Buffers: shared hit=3
46. 0.012 0.012 ↑ 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.005..0.006 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
47. 0.008 0.008 ↑ 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.008..0.008 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
48. 0.007 0.007 ↑ 1.0 1 1

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

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

SubPlan (for Nested Loop Semi Join)

50. 0.003 0.042 ↓ 0.0 0 3

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

  • Buffers: shared hit=20
51. 0.024 0.024 ↑ 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.008..0.008 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
52. 0.015 0.015 ↓ 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.005..0.005 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
53. 0.018 0.018 ↓ 0.0 0 3

Index Scan using _inforg13938_bydims_rr on _inforg13938 t27 (cost=0.41..7.43 rows=1 width=0) (actual time=0.006..0.006 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=12
54. 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 : 78.800 ms
Execution time : 2,086.605 ms