explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8lPf

Settings
# exclusive inclusive rows x rows loops node
1. 0.126 4,248,526.536 ↓ 847.0 847 1

Limit (cost=562.16..562.16 rows=1 width=334) (actual time=4,248,526.341..4,248,526.536 rows=847 loops=1)

  • Output: t1._idrref, t1._marked, (CASE WHEN (t1._predefinedid > '\\x00000000000000000000000000000000'::bytea) THEN true ELSE false END), t1._parentidrref, (CASE WHEN (NOT t1._folder) THEN true ELSE false END), t1._code, t1._description, t1._fld55865rref, t1._fld55866, t1._fld60163rref, t1._fld55868rref, t1._fld55870rref, t1._fld55871, t3._fld53637rref, t3._fld53638rref, t11._offbalance, t3._fld53640rref, t3._fld53641_type, t3._fld53641_s, t3._fld53641_rrref
  • Buffers: shared hit=4,858,141,538 read=29
  • I/O Timings: read=1.033
2. 6.342 4,248,526.410 ↓ 847.0 847 1

Sort (cost=562.16..562.16 rows=1 width=334) (actual time=4,248,526.340..4,248,526.410 rows=847 loops=1)

  • Output: t1._idrref, t1._marked, (CASE WHEN (t1._predefinedid > '\\x00000000000000000000000000000000'::bytea) THEN true ELSE false END), t1._parentidrref, (CASE WHEN (NOT t1._folder) THEN true ELSE false END), t1._code, t1._description, t1._fld55865rref, t1._fld55866, t1._fld60163rref, t1._fld55868rref, t1._fld55870rref, t1._fld55871, t3._fld53637rref, t3._fld53638rref, t11._offbalance, t3._fld53640rref, t3._fld53641_type, t3._fld53641_s, t3._fld53641_rrref
  • Sort Key: (CASE WHEN (NOT t1._folder) THEN true ELSE false END), t1._description DESC, t1._idrref DESC
  • Sort Method: quicksort Memory: 636kB
  • Buffers: shared hit=4,858,141,538 read=29
  • I/O Timings: read=1.033
3. 4.451 4,248,520.068 ↓ 847.0 847 1

Nested Loop Left Join (cost=31.08..562.12 rows=1 width=334) (actual time=108.905..4,248,520.068 rows=847 loops=1)

  • Output: t1._idrref, t1._marked, CASE WHEN (t1._predefinedid > '\\x00000000000000000000000000000000'::bytea) THEN true ELSE false END, t1._parentidrref, CASE WHEN (NOT t1._folder) THEN true ELSE false END, t1._code, t1._description, t1._fld55865rref, t1._fld55866, t1._fld60163rref, t1._fld55868rref, t1._fld55870rref, t1._fld55871, t3._fld53637rref, t3._fld53638rref, t11._offbalance, t3._fld53640rref, t3._fld53641_type, t3._fld53641_s, t3._fld53641_rrref
  • Inner Unique: true
  • Buffers: shared hit=4,858,141,538 read=29
  • I/O Timings: read=1.033
4. 259.639 4,248,507.994 ↓ 847.0 847 1

Nested Loop Left Join (cost=30.91..553.92 rows=1 width=349) (actual time=108.885..4,248,507.994 rows=847 loops=1)

  • Output: t1._idrref, t1._marked, t1._predefinedid, t1._parentidrref, t1._folder, t1._code, t1._description, t1._fld55865rref, t1._fld55866, t1._fld60163rref, t1._fld55868rref, t1._fld55870rref, t1._fld55871, t3._fld53637rref, t3._fld53638rref, t3._fld53640rref, t3._fld53641_type, t3._fld53641_s, t3._fld53641_rrref
  • Join Filter: (t3._fld53636rref = t1._idrref)
  • Rows Removed by Join Filter: 876,677
  • Buffers: shared hit=4,858,138,278 read=29
  • I/O Timings: read=1.033
5. 6.588 8.051 ↓ 847.0 847 1

Bitmap Heap Scan on public._reference36583 t1 (cost=21.91..526.67 rows=1 width=277) (actual time=1.500..8.051 rows=847 loops=1)

  • Output: t1._idrref, t1._version, t1._marked, t1._predefinedid, t1._parentidrref, t1._folder, t1._code, t1._description, t1._fld55865rref, t1._fld55866, t1._fld55868rref, t1._fld55869rref, t1._fld60163rref, t1._fld55870rref, t1._fld55871, t1._fld55872, t1._fld55873, t1._fld55874, t1._fld55875, t1._fld55877, t1._fld60357rref, t1._fld55880, t1._fld55881rref, t1._fld55882, t1._fld55867, t1._fld55876, t1._fld55878, t1._fld55879, t1._fld60164rref, t1._fld1215
  • Recheck Cond: (((t1._fld1215 = '8726'::numeric) AND (t1._parentidrref = '\\x00000000000000000000000000000000'::bytea) AND (NOT t1._folder)) OR (((t1._fld1215 = '8726'::numeric) AND (t1._parentidrref = '\\x00000000000000000000000000000000'::bytea) AND t1._folder AND (t1._description < 'Насос погружной скважинный Vodotok""БЦПЭ-100-0,5-140м'::mvarchar)) OR ((t1._fld1215 = '8726'::numeric) AND (t1._description = 'Насос погружной скважинный Vodotok""БЦПЭ-100-0,5-140м'::mvarchar) AND (t1._idrref <= '\\x8a9d001a4b0103ef11eaa62f139b067a'::bytea))))
  • Filter: ((t1._parentidrref = '\\x00000000000000000000000000000000'::bytea) AND ((NOT t1._folder) OR ((t1._fld55868rref = ANY ('{""\\\\xa3e267addd2656224138d8934ff8a3fb"",""\\\\xa88ffc6e5eb6ca2f4a3fcb3f7326676d"",""\\\\x8159dfe3717fab584d1c0850de8d065f""}'::bytea[])) AND (t1._fld55865rref = '\\x529a005056031e8d11e934d77972bbb6'::bytea))) AND ((NOT t1._folder) OR (t1._folder AND ((t1._description < 'Насос погружной скважинный Vodotok""БЦПЭ-100-0,5-140м'::mvarchar) OR ((t1._description = 'Насос погружной скважинный Vodotok""БЦПЭ-100-0,5-140м'::mvarchar) AND (t1._idrref <= '\\x8a9d001a4b0103ef11eaa62f139b067a'::bytea))))))
  • Heap Blocks: exact=362
  • Buffers: shared hit=365 read=29
  • I/O Timings: read=1.033
6. 0.039 1.463 ↓ 0.0 0 1

BitmapOr (cost=21.91..21.91 rows=134 width=0) (actual time=1.463..1.463 rows=0 loops=1)

  • Buffers: shared hit=3 read=29
  • I/O Timings: read=1.033
7. 0.010 0.010 ↓ 0.0 0 1

Bitmap Index Scan on _reference36583_6 (cost=0.00..4.19 rows=2 width=0) (actual time=0.009..0.010 rows=0 loops=1)

  • Index Cond: ((t1._fld1215 = '8726'::numeric) AND (t1._parentidrref = '\\x00000000000000000000000000000000'::bytea) AND (t1._folder = false))
  • Buffers: shared hit=3
8. 0.001 1.414 ↓ 0.0 0 1

BitmapOr (cost=17.63..17.63 rows=131 width=0) (actual time=1.414..1.414 rows=0 loops=1)

  • Buffers: shared read=29
  • I/O Timings: read=1.033
9. 0.970 0.970 ↓ 6.5 846 1

Bitmap Index Scan on _reference36583_3 (cost=0.00..13.40 rows=131 width=0) (actual time=0.970..0.970 rows=846 loops=1)

  • Index Cond: ((t1._fld1215 = '8726'::numeric) AND (t1._parentidrref = '\\x00000000000000000000000000000000'::bytea) AND (t1._folder = true) AND (t1._description < 'Насос погружной скважинный Vodotok""БЦПЭ-100-0,5-140м'::mvarchar))
  • Buffers: shared read=25
  • I/O Timings: read=0.611
10. 0.443 0.443 ↑ 1.0 1 1

Bitmap Index Scan on _reference36583_5 (cost=0.00..4.23 rows=1 width=0) (actual time=0.443..0.443 rows=1 loops=1)

  • Index Cond: ((t1._fld1215 = '8726'::numeric) AND (t1._description = 'Насос погружной скважинный Vodotok""БЦПЭ-100-0,5-140м'::mvarchar) AND (t1._idrref <= '\\x8a9d001a4b0103ef11eaa62f139b067a'::bytea))
  • Buffers: shared read=4
  • I/O Timings: read=0.422
11. 2,144.594 4,248,240.304 ↓ 1,036.0 1,036 847

Nested Loop Semi Join (cost=9.00..27.23 rows=1 width=89) (actual time=4.849..5,015.632 rows=1,036 loops=847)

  • Output: t3._fld53637rref, t3._fld53638rref, t3._fld53640rref, t3._fld53641_type, t3._fld53641_s, t3._fld53641_rrref, t3._fld53636rref
  • Join Filter: (CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN true ELSE false END = CASE WHEN (alternatives: SubPlan 3 or hashed SubPlan 4) THEN true ELSE false END)
  • Buffers: shared hit=4,858,137,913
12. 789.404 789.404 ↓ 1,036.0 1,036 847

Index Scan using _inforgsl53644_1 on public._inforgsl53644 t3 (cost=0.17..8.18 rows=1 width=106) (actual time=0.018..0.932 rows=1,036 loops=847)

  • Output: t3._period, t3._recordertref, t3._recorderrref, t3._fld53635rref, t3._fld53636rref, t3._fld53637rref, t3._fld53638rref, t3._fld53639rref, t3._fld53640rref, t3._fld53641_type, t3._fld53641_s, t3._fld53641_rrref, t3._fld62715rref, t3._fld1215
  • Index Cond: ((t3._fld1215 = '8726'::numeric) AND (t3._fld53635rref = '\\x529a005056031e8d11e934d77972bbb6'::bytea))
  • Buffers: shared hit=342,188
13. 0.000 4,241,796.328 ↑ 1.0 1 877,492

Nested Loop (cost=8.83..13.72 rows=1 width=17) (actual time=4.834..4.834 rows=1 loops=877,492)

  • Output: t5._idrref
  • Inner Unique: true
  • Buffers: shared hit=4,855,163,248
14. 81,606.756 2,479,792.392 ↓ 1,089.0 1,089 877,492

Nested Loop (cost=8.72..13.49 rows=1 width=34) (actual time=0.008..2.826 rows=1,089 loops=877,492)

  • Output: t5._idrref, t6._fld24899_rrref
  • Inner Unique: true
  • Buffers: shared hit=1,984,009,424
15. 182,518.336 487,008.060 ↓ 217.8 1,089 877,492

Nested Loop (cost=8.61..12.76 rows=5 width=51) (actual time=0.006..0.555 rows=1,089 loops=877,492)

  • Output: t6._fld24899_rrref, t6._fld24900rref, t7._reference99_idrref
  • Buffers: shared hit=72,831,847
16. 877.492 877.492 ↓ 2.0 2 877,492

Unique (cost=8.38..8.39 rows=1 width=17) (actual time=0.000..0.001 rows=2 loops=877,492)

  • Output: t7._reference99_idrref
  • Buffers: shared hit=11
17. 0.000 0.000 ↓ 2.0 2 877,492

Sort (cost=8.38..8.39 rows=1 width=17) (actual time=0.000..0.000 rows=2 loops=877,492)

  • Output: t7._reference99_idrref
  • Sort Key: t7._reference99_idrref
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=11
18. 0.004 0.030 ↓ 3.0 3 1

Nested Loop (cost=0.33..8.38 rows=1 width=17) (actual time=0.028..0.030 rows=3 loops=1)

  • Output: t7._reference99_idrref
  • Buffers: shared hit=11
19. 0.012 0.012 ↓ 2.0 2 1

Index Only Scan using _inforg24559_2 on public._inforg24559 t8 (cost=0.17..4.19 rows=1 width=24) (actual time=0.011..0.012 rows=2 loops=1)

  • Output: t8._fld1215, t8._fld24561_type, t8._fld24561_rtref, t8._fld24561_rrref, t8._fld24560_type, t8._fld24560_rtref, t8._fld24560_rrref
  • Index Cond: ((t8._fld1215 = '8726'::numeric) AND (t8._fld24561_type = '\\x08'::bytea) AND (t8._fld24561_rtref = '\\x000000ff'::bytea) AND (t8._fld24561_rrref = '\\x9684005056031e8711e935a92db5040e'::bytea))
  • Heap Fetches: 0
  • Buffers: shared hit=4
20. 0.014 0.014 ↓ 2.0 2 2

Index Only Scan using _reference99_vt28711_1 on public._reference99_vt28711 t7 (cost=0.16..4.18 rows=1 width=41) (actual time=0.007..0.007 rows=2 loops=2)

  • Output: t7._fld1215, t7._fld28713_type, t7._fld28713_rtref, t7._fld28713_rrref, t7._reference99_idrref
  • Index Cond: ((t7._fld1215 = '8726'::numeric) AND (t7._fld28713_type = t8._fld24560_type) AND (t7._fld28713_rtref = t8._fld24560_rtref) AND (t7._fld28713_rrref = t8._fld24560_rrref))
  • Heap Fetches: 0
  • Buffers: shared hit=7
21. 303,612.232 303,612.232 ↓ 108.8 544 1,754,984

Index Only Scan using _inforg24898_2 on public._inforg24898 t6 (cost=0.22..4.32 rows=5 width=34) (actual time=0.005..0.173 rows=544 loops=1,754,984)

  • Output: t6._fld1215, t6._fld24900rref, t6._fld24899_type, t6._fld24899_rtref, t6._fld24899_rrref
  • Index Cond: ((t6._fld1215 = '8726'::numeric) AND (t6._fld24900rref = t7._reference99_idrref) AND (t6._fld24899_type = '\\x08'::bytea) AND (t6._fld24899_rtref = '\\x00000088'::bytea))
  • Heap Fetches: 0
  • Buffers: shared hit=72,831,836
22. 1,911,177.576 1,911,177.576 ↑ 1.0 1 955,588,788

Index Only Scan using _reference99_s_hpk on public._reference99 t5 (cost=0.12..0.15 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=955,588,788)

  • Output: t5._fld1215, t5._idrref
  • Index Cond: ((t5._fld1215 = '8726'::numeric) AND (t5._idrref = t6._fld24900rref))
  • Heap Fetches: 0
  • Buffers: shared hit=1,911,177,577
23. 1,911,177.576 1,911,177.576 ↓ 0.0 0 955,588,788

Index Scan using _reference136ng_pkey1 on public._reference136 t4 (cost=0.11..0.17 rows=1 width=17) (actual time=0.002..0.002 rows=0 loops=955,588,788)

  • Output: t4._idrref, t4._version, t4._marked, t4._predefinedid, t4._parentidrref, t4._description, t4._fld29547, t4._fld29548, t4._fld29549, t4._fld29550, t4._fld29551, t4._fld29552, t4._fld29553_type, t4._fld29553_rtref, t4._fld29553_rrref, t4._fld29554, t4._fld29555rref
  • Index Cond: (t4._idrref = t6._fld24899_rrref)
  • Filter: (t4._fld29550 = 'РегистрСведений.УчетныеДанныеОбъектаОС'::mvarchar)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=2,871,153,824
24.          

SubPlan (for Nested Loop Semi Join)

25. 0.000 0.000 ↓ 0.0 0

Seq Scan on public._inforg20607 t9 (cost=0.00..1.14 rows=1 width=0) (never executed)

  • Filter: ((t9._fld1215 = '8726'::numeric) AND (t9._fld20608rref = t5._idrref) AND (t9._fld20609_type = '\\x08'::bytea) AND (t9._fld20609_rtref = '\\x000000c1'::bytea) AND (t9._fld20609_rrref = t3._fld53635rref))
26. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on public._inforg20607 t9_1 (cost=0.00..1.12 rows=1 width=34) (actual time=0.009..0.010 rows=1 loops=1)

  • Output: t9_1._fld20608rref, t9_1._fld20609_rrref
  • Filter: ((t9_1._fld1215 = '8726'::numeric) AND (t9_1._fld20609_type = '\\x08'::bytea) AND (t9_1._fld20609_rtref = '\\x000000c1'::bytea))
  • Rows Removed by Filter: 8
  • Buffers: shared hit=1
27. 3,509.968 3,509.968 ↓ 0.0 0 877,492

Index Only Scan using _inforg20614_5 on public._inforg20614 t10 (cost=0.17..4.19 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=877,492)

  • Index Cond: ((t10._fld1215 = '8726'::numeric) AND (t10._fld20617 = false) AND (t10._fld20615rref = t5._idrref) AND (t10._fld20616_type = '\\x08'::bytea) AND (t10._fld20616_rtref = '\\x000000c1'::bytea))
  • Filter: (NOT t10._fld20617)
  • Heap Fetches: 0
  • Buffers: shared hit=2,632,476
28. 0.000 0.000 ↓ 0.0 0

Index Only Scan using _inforg20614_5 on public._inforg20614 t10_1 (cost=0.17..20.06 rows=7 width=17) (never executed)

  • Output: t10_1._fld20615rref
  • Index Cond: ((t10_1._fld1215 = '8726'::numeric) AND (t10_1._fld20617 = false) AND (t10_1._fld20616_type = '\\x08'::bytea) AND (t10_1._fld20616_rtref = '\\x000000c1'::bytea))
  • Filter: (NOT t10_1._fld20617)
  • Heap Fetches: 0
29. 7.623 7.623 ↑ 1.0 1 847

Index Scan using _acc36456_s_hpk on public._acc36456 t11 (cost=0.17..8.18 rows=1 width=18) (actual time=0.009..0.009 rows=1 loops=847)

  • Output: t11._idrref, t11._version, t11._marked, t11._predefinedid, t11._parentidrref, t11._code, t11._description, t11._orderfield, t11._kind, t11._offbalance, t11._fld57729, t11._fld57730rref, t11._fld57731rref, t11._fld57732rref, t11._fld57733, t11._fld57734, t11._fld57735, t11._fld57736, t11._fld57737rref, t11._fld57738, t11._fld57739, t11._fld57740, t11._fld1215, t11._fld57741, t11._fld57742, t11._fld57743, t11._fld57744, t11._fld57745, t11._fld57746, t11._fld57747, t11._fld57748
  • Index Cond: ((t11._fld1215 = '8726'::numeric) AND (t3._fld53637rref = t11._idrref))
  • Buffers: shared hit=3,260
Planning time : 5.356 ms
Execution time : 4,248,527.434 ms