explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9aA9 : Optimization for: plan #Vsc3

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 190.511 ↑ 14.3 3 1

Limit (cost=732,801.95..732,802.05 rows=43 width=454) (actual time=190.510..190.511 rows=3 loops=1)

  • Buffers: shared hit=164,396 read=6
2. 0.014 190.509 ↑ 14.3 3 1

Sort (cost=732,801.95..732,802.05 rows=43 width=454) (actual time=190.509..190.509 rows=3 loops=1)

  • Sort Key: t1._date_time DESC
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=164,396 read=6
3. 0.011 190.495 ↑ 14.3 3 1

Nested Loop Left Join (cost=505,370.55..732,800.78 rows=43 width=454) (actual time=97.229..190.495 rows=3 loops=1)

  • Buffers: shared hit=164,396 read=6
4. 0.007 190.310 ↑ 14.3 3 1

Nested Loop Left Join (cost=505,352.40..730,882.06 rows=43 width=411) (actual time=97.125..190.310 rows=3 loops=1)

  • Buffers: shared hit=164,332 read=6
5. 0.006 190.279 ↑ 14.3 3 1

Nested Loop Left Join (cost=505,351.84..730,827.36 rows=43 width=372) (actual time=97.115..190.279 rows=3 loops=1)

  • Join Filter: (t2._fld7983 = t16._fld20243)
  • Buffers: shared hit=164,317 read=6
6. 3.209 190.246 ↑ 14.3 3 1

Hash Join (cost=505,351.28..730,551.84 rows=43 width=355) (actual time=97.101..190.246 rows=3 loops=1)

  • Hash Cond: ((t2._fld17407rref = t4._document300_idrref) AND (t2._fld17352 = t4._fld7862))
  • Buffers: shared hit=164,302 read=6
7. 2.837 175.914 ↓ 1.1 15,583 1

Nested Loop Left Join (cost=1.67..220,719.92 rows=14,342 width=303) (actual time=0.073..175.914 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=160,600
8. 21.384 95.162 ↓ 1.1 15,583 1

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

  • Buffers: shared hit=86,783
9. 11.894 11.894 ↓ 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.037..11.894 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=9,056
10. 61.884 61.884 ↑ 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.004..0.004 rows=1 loops=15,471)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_document302_idrref = t1._idrref))
  • Buffers: shared hit=77,727
11. 77.915 77.915 ↑ 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.004..0.005 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=73,817
12. 0.244 11.123 ↑ 31.5 325 1

Hash (cost=505,196.00..505,196.00 rows=10,241 width=91) (actual time=11.123..11.123 rows=325 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 168kB
  • Buffers: shared hit=3,702 read=6
13. 0.380 10.879 ↑ 31.5 325 1

Nested Loop Semi Join (cost=18.70..505,196.00 rows=10,241 width=91) (actual time=0.745..10.879 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=3,702 read=6
14. 0.154 4.237 ↑ 63.0 325 1

Nested Loop (cost=0.99..119,360.34 rows=20,481 width=108) (actual time=0.129..4.237 rows=325 loops=1)

  • Buffers: shared hit=1,616 read=6
15. 1.158 1.158 ↑ 63.0 325 1

Index Scan using _document300_vt7842_11022020_a on _document300_vt7842 t4 (cost=0.56..7.58 rows=20,481 width=77) (actual time=0.114..1.158 rows=325 loops=1)

  • Index Cond: ((_fld717 = '0'::numeric) AND (_fld17170 >= 'AWKAA'::mvarchar COLLATE "default") AND (_fld17170 < 'AWKAB'::mvarchar COLLATE "default"))
  • Filter: (_fld17170 ~~ 'AWKAA%'::mvarchar)
  • Buffers: shared hit=314 read=6
16. 2.925 2.925 ↑ 1.0 1 325

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

  • Index Cond: ((_fld717 = '0'::numeric) AND (_idrref = t4._document300_idrref))
  • Buffers: shared hit=1,302
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.002 0.093 ↑ 1.0 1 1

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

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

Nested Loop (cost=17.45..24.52 rows=1 width=37) (actual time=0.080..0.080 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.002 0.060 ↑ 1.0 1 1

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

  • Buffers: shared hit=12
22. 0.007 0.045 ↑ 1.0 1 1

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

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

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

  • Buffers: shared hit=8
24. 0.014 0.014 ↑ 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.013..0.014 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.020 0.020 ↑ 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.010 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.013 0.013 ↑ 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.013..0.013 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 5.850 ↓ 0.0 0 325

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

  • Buffers: shared hit=1,970
30. 3.575 3.575 ↑ 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.011..0.011 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.950 1.950 ↓ 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.006..0.006 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.412 0.412 ↓ 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.013..0.412 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.027 0.027 ↑ 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.008..0.009 rows=1 loops=3)

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

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

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

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

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

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

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

  • Buffers: shared hit=20
39. 0.000 0.056 ↑ 1.0 1 1

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

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

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

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

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

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

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

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

  • Buffers: shared hit=8
45. 0.008 0.008 ↑ 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.008..0.008 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.006..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.006 0.006 ↑ 1.0 1 1

Index Only Scan using _reference73hpk on _reference73 t20 (cost=0.27..0.29 rows=1 width=17) (actual time=0.006..0.006 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.054 ↓ 0.0 0 3

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

  • Buffers: shared hit=20
51. 0.033 0.033 ↑ 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.010..0.011 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.018 0.018 ↓ 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.006..0.006 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.024 0.024 ↓ 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.008..0.008 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 : 80.454 ms
Execution time : 191.068 ms