explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zGir

Settings
# exclusive inclusive rows x rows loops node
1. 2.159 1,148,326.517 ↑ 1.0 100 1

Limit (cost=0.12..512,897.37 rows=100 width=37) (actual time=753,807.472..1,148,326.517 rows=100 loops=1)

  • Output: t24._idrref, t24._fld3450rref
  • Buffers: shared hit=44174120
2. 2.451 1,148,324.358 ↑ 59.1 100 1

Nested Loop (cost=0.12..30,317,356.67 rows=5,911 width=37) (actual time=753,807.456..1,148,324.358 rows=100 loops=1)

  • Output: t24._idrref, t24._fld3450rref
  • Inner Unique: true
  • Buffers: shared hit=44174120
3. 249.359 1,148,290.907 ↑ 119.4 100 1

Seq Scan on public._reference146 t24 (cost=0.00..13,691,020.54 rows=11,941 width=37) (actual time=753,803.488..1,148,290.907 rows=100 loops=1)

  • Output: t24._idrref, t24._version, t24._marked, t24._predefinedid, t24._code, t24._description, t24._fld3450rref, t24._fld3451rref, t24._fld3452rref, t24._fld3453, t24._fld3454, t24._fld3455, t24._fld3456rref, t24._fld610
  • Filter: ((t24._fld3450rref IS NOT NULL) AND (t24._fld610 = '0'::numeric) AND (SubPlan 13))
  • Rows Removed by Filter: 3536
  • Buffers: shared hit=44172937
4.          

SubPlan (forSeq Scan)

5. 271.900 1,148,041.548 ↓ 0.0 0 3,636

Nested Loop (cost=0.88..567.55 rows=1 width=0) (actual time=315.743..315.743 rows=0 loops=3,636)

  • Buffers: shared hit=44172815
6. 196.154 1,147,768.848 ↓ 0.0 0 3,636

Nested Loop Semi Join (cost=0.88..567.52 rows=1 width=0) (actual time=315.668..315.668 rows=0 loops=3,636)

  • Join Filter: (CASE WHEN (alternatives: SubPlan 6 or hashed SubPlan 7) THEN true ELSE false END = CASE WHEN (alternatives: SubPlan 8 or hashed SubPlan 9) THEN true ELSE false END)
  • Rows Removed by Join Filter: 0
  • Buffers: shared hit=44172815
7. 130.896 130.896 ↑ 1.0 2 3,636

Index Only Scan using _inforg35359_1 on public._inforg35359 t35 (cost=0.17..5.07 rows=2 width=20) (actual time=0.017..0.036 rows=2 loops=3,636)

  • Output: t35._fld610, t35._fld35360rref, t35._fld35361rref
  • Index Cond: ((t35._fld610 = '0'::numeric) AND (t35._fld35360rref = t24._idrref))
  • Heap Fetches: 7027
  • Buffers: shared hit=14675
8. 91.758 1,147,438.830 ↓ 0.0 0 7,027

Materialize (cost=0.72..545.90 rows=1 width=20) (actual time=163.242..163.290 rows=0 loops=7,027)

  • Output: t37._idrref
  • Buffers: shared hit=44157510
9. 3,335.828 1,147,347.072 ↓ 0.0 0 3,636

Nested Loop Semi Join (cost=0.72..545.90 rows=1 width=20) (actual time=315.458..315.552 rows=0 loops=3,636)

  • Output: t37._idrref
  • Buffers: shared hit=44157510
10. 18,633.618 155,359.008 ↓ 25.0 25 3,636

Nested Loop (cost=0.39..433.37 rows=1 width=40) (actual time=2.320..42.728 rows=25 loops=3,636)

  • Output: t38._fld14648rref, t37._idrref
  • Inner Unique: true
  • Buffers: shared hit=6095537
11. 16,940.124 25,830.144 ↓ 5.8 150 3,636

Nested Loop (cost=0.33..83.85 rows=26 width=20) (actual time=0.132..7.104 rows=150 loops=3,636)

  • Output: t38._fld14648rref
  • Buffers: shared hit=39699
12. 116.352 116.352 ↑ 1.0 1 3,636

Index Only Scan using _reference62_7 on public._reference62 t36 (cost=0.16..4.18 rows=1 width=20) (actual time=0.023..0.032 rows=1 loops=3,636)

  • Output: t36._fld1638, t36._idrref
  • Index Cond: (t36._fld1638 = 'Справочник.Сотрудники'::mvarchar)
  • Heap Fetches: 3636
  • Buffers: shared hit=14544
13. 8,773.668 8,773.668 ↓ 1.7 150 3,636

Index Only Scan using _inforg14646_1 on public._inforg14646 t38 (cost=0.17..78.82 rows=86 width=40) (actual time=0.083..2.413 rows=150 loops=3,636)

  • Output: t38._fld610, t38._fld14647rref, t38._fld14648rref
  • Index Cond: ((t38._fld610 = '0'::numeric) AND (t38._fld14647rref = t36._idrref))
  • Heap Fetches: 546282
  • Buffers: shared hit=25155
14. 27,314.100 110,895.246 ↓ 0.0 0 546,282

Index Only Scan using _reference48_s_hpk on public._reference48 t37 (cost=0.06..12.95 rows=1 width=20) (actual time=0.203..0.203 rows=0 loops=546,282)

  • Output: t37._fld610, t37._idrref
  • Index Cond: ((t37._fld610 = '0'::numeric) AND (t37._idrref = t38._fld14648rref))
  • Filter: (CASE WHEN (SubPlan 10) THEN true ELSE false END = CASE WHEN (alternatives: SubPlan 11 or hashed SubPlan 12) THEN true ELSE false END)
  • Rows Removed by Filter: 1
  • Heap Fetches: 546282
  • Buffers: shared hit=6055838
15.          

SubPlan (forIndex Only Scan)

16. 35,508.330 71,562.942 ↓ 0.0 0 546,282

Nested Loop (cost=0.28..8.74 rows=1 width=0) (actual time=0.131..0.131 rows=0 loops=546,282)

  • Inner Unique: true
  • Buffers: shared hit=3317255
17. 22,943.844 22,943.844 ↑ 1.0 1 546,282

Index Only Scan using _inforg12318_1 on public._inforg12318 t44 (cost=0.17..4.19 rows=1 width=33) (actual time=0.027..0.042 rows=1 loops=546,282)

  • Output: t44._fld610, t44._fld12319_type, t44._fld12319_rtref, t44._fld12319_rrref, t44._fld12320_type, t44._fld12320_rtref, t44._fld12320_rrref, t44._fld12321
  • Index Cond: ((t44._fld610 = '0'::numeric) AND (t44._fld12319_type = '\\x08'::bytea) AND (t44._fld12319_rtref = '\\x000000c6'::bytea) AND (t44._fld12319_rrref = t24._fld3450rref))
  • Heap Fetches: 546282
  • Buffers: shared hit=2198258
18. 13,110.768 13,110.768 ↓ 0.0 0 546,282

Index Only Scan using _inforg12729_2 on public._inforg12729 t43 (cost=0.11..4.13 rows=1 width=33) (actual time=0.024..0.024 rows=0 loops=546,282)

  • Output: t43._fld610, t43._fld12731_type, t43._fld12731_rtref, t43._fld12731_rrref, t43._fld12730rref
  • Index Cond: ((t43._fld610 = '0'::numeric) AND (t43._fld12731_type = t44._fld12320_type) AND (t43._fld12731_rtref = t44._fld12320_rtref) AND (t43._fld12731_rrref = t44._fld12320_rrref) AND (t43._fld12730rref = t37._idrref))
  • Heap Fetches: 26138
  • Buffers: shared hit=1118997
19. 12,018.204 12,018.204 ↑ 1.0 1 546,282

Index Scan using _inforg12736_1 on public._inforg12736 t45 (cost=0.11..4.13 rows=1 width=0) (actual time=0.022..0.022 rows=1 loops=546,282)

  • Index Cond: ((t45._fld610 = '0'::numeric) AND (t45._fld12737rref = t37._idrref) AND (t45._fld12738_type = '\\x08'::bytea) AND (t45._fld12738_rtref = '\\x000000c6'::bytea))
  • Filter: (NOT t45._fld12739)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1646019
20. 0.000 0.000 ↓ 0.0 0

Index Scan using _inforg12736_2 on public._inforg12736 t45_1 (cost=0.11..29.70 rows=58 width=20) (never executed)

  • Output: t45_1._fld12737rref
  • Index Cond: ((t45_1._fld610 = '0'::numeric) AND (t45_1._fld12738_type = '\\x08'::bytea) AND (t45_1._fld12738_rtref = '\\x000000c6'::bytea))
  • Filter: (NOT t45_1._fld12739)
21. 407,380.717 988,652.236 ↓ 0.0 0 89,804

Nested Loop (cost=0.33..112.51 rows=2 width=17) (actual time=11.009..11.009 rows=0 loops=89,804)

  • Output: t39._reference48_idrref
  • Inner Unique: true
  • Buffers: shared hit=38061973
22. 193,797.032 193,797.032 ↑ 3.5 139 89,804

Index Scan using _reference48_vt1292_sk on public._reference48_vt1292 t39 (cost=0.17..8.56 rows=492 width=41) (actual time=0.036..2.158 rows=139 loops=89,804)

  • Output: t39._reference48_idrref, t39._fld610, t39._keyfield, t39._lineno1293, t39._fld1294_type, t39._fld1294_rtref, t39._fld1294_rrref
  • Index Cond: ((t39._fld610 = '0'::numeric) AND (t39._reference48_idrref = t38._fld14648rref))
  • Buffers: shared hit=564337
23. 387,474.487 387,474.487 ↓ 0.0 0 12,499,177

Index Only Scan using _inforg14469_2 on public._inforg14469 t40 (cost=0.16..0.21 rows=1 width=33) (actual time=0.031..0.031 rows=0 loops=12,499,177)

  • Output: t40._fld610, t40._fld14471_type, t40._fld14471_rtref, t40._fld14471_rrref, t40._fld14470_type, t40._fld14470_rtref, t40._fld14470_rrref
  • Index Cond: ((t40._fld610 = '0'::numeric) AND (t40._fld14471_type = '\\x08'::bytea) AND (t40._fld14471_rtref = '\\x00000076'::bytea) AND (t40._fld14471_rrref = '\\x80c7000e1e51267211e75a7005501a87'::bytea) AND (t40._fld14470_type = t39._fld1294_type) AND (t40._fld14470_rtref = t39._fld1294_rtref) AND (t40._fld14470_rrref = t39._fld1294_rrref))
  • Heap Fetches: 105
  • Buffers: shared hit=37497636
24.          

SubPlan (forNested Loop Semi Join)

25. 1.590 1.590 ↑ 1.0 1 106

Index Only Scan using _inforg12729_2 on public._inforg12729 t41 (cost=0.11..4.13 rows=1 width=0) (actual time=0.014..0.015 rows=1 loops=106)

  • Index Cond: ((t41._fld610 = '0'::numeric) AND (t41._fld12731_type = '\\x08'::bytea) AND (t41._fld12731_rtref = '\\x00000051'::bytea) AND (t41._fld12731_rrref = t35._fld35361rref) AND (t41._fld12730rref = t37._idrref))
  • Heap Fetches: 100
  • Buffers: shared hit=312
26. 0.000 0.000 ↓ 0.0 0

Index Only Scan using _inforg12729_2 on public._inforg12729 t41_1 (cost=0.11..46.77 rows=391 width=40) (never executed)

  • Output: t41_1._fld12730rref, t41_1._fld12731_rrref
  • Index Cond: ((t41_1._fld610 = '0'::numeric) AND (t41_1._fld12731_type = '\\x08'::bytea) AND (t41_1._fld12731_rtref = '\\x00000051'::bytea))
  • Heap Fetches: 0
27. 1.378 1.378 ↑ 1.0 1 106

Index Scan using _inforg12736_1 on public._inforg12736 t42 (cost=0.11..4.13 rows=1 width=0) (actual time=0.013..0.013 rows=1 loops=106)

  • Index Cond: ((t42._fld610 = '0'::numeric) AND (t42._fld12737rref = t37._idrref) AND (t42._fld12738_type = '\\x08'::bytea) AND (t42._fld12738_rtref = '\\x00000051'::bytea))
  • Filter: (NOT t42._fld12739)
  • Buffers: shared hit=318
28. 0.000 0.000 ↓ 0.0 0

Index Scan using _inforg12736_2 on public._inforg12736 t42_1 (cost=0.11..29.34 rows=51 width=20) (never executed)

  • Output: t42_1._fld12737rref
  • Index Cond: ((t42_1._fld610 = '0'::numeric) AND (t42_1._fld12738_type = '\\x08'::bytea) AND (t42_1._fld12738_rtref = '\\x00000051'::bytea))
  • Filter: (NOT t42_1._fld12739)
29. 0.800 0.800 ↑ 1.0 1 100

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=100)

  • Output: NULL::integer
30. 3.200 31.000 ↑ 1.0 1 100

Index Scan using _reference198_s_hpk on public._reference198 t26 (cost=0.12..1,392.49 rows=1 width=20) (actual time=0.309..0.310 rows=1 loops=100)

  • Output: t26._idrref, t26._version, t26._marked, t26._predefinedid, t26._parentidrref, t26._folder, t26._code, t26._description, t26._fld3982, t26._fld3983rref, t26._fld3984, t26._fld3985, t26._fld3986, t26._fld3987rref, t26._fld3988, t26._fld3989, t26._fld3990, t26._fld3991, t26._fld3992, t26._fld3993, t26._fld3994rref, t26._fld3995, t26._fld3996, t26._fld3997rref, t26._fld31069rref, t26._fld3998rref, t26._fld3999, t26._fld25557, t26._fld25558, t26._fld25559, t26._fld31070, t26._fld610
  • Index Cond: ((t26._fld610 = '0'::numeric) AND (t26._idrref = t24._fld3450rref))
  • Filter: ((t26._description IS NOT NULL) AND (SubPlan 5))
  • Buffers: shared hit=1183
31.          

SubPlan (forIndex Scan)

32. 11.355 27.800 ↑ 22.0 1 100

Nested Loop Semi Join (cost=1,204.56..5,329.76 rows=22 width=0) (actual time=0.278..0.278 rows=1 loops=100)

  • Join Filter: ((NOT t26._folder) OR (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) OR (t27._idrref = '\\x80c7000e1e51267211e75a7005501a87'::bytea))
  • Buffers: shared hit=883
33. 2.400 4.500 ↑ 43.0 1 100

Nested Loop Left Join (cost=0.11..42.86 rows=43 width=20) (actual time=0.045..0.045 rows=1 loops=100)

  • Output: t27._idrref
  • Buffers: shared hit=200
34. 0.800 0.800 ↑ 1.0 1 100

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=100)

  • Output: NULL::integer
35. 1.300 1.300 ↓ 0.0 0 100

Index Only Scan using _reference118_5 on public._reference118 t27 (cost=0.11..42.41 rows=43 width=20) (actual time=0.013..0.013 rows=0 loops=100)

  • Output: t27._fld610, t27._fld2939rref, t27._idrref
  • Index Cond: ((t27._fld610 = '0'::numeric) AND (t27._fld2939rref = t26._idrref))
  • Heap Fetches: 0
  • Buffers: shared hit=200
36. 0.809 2.200 ↑ 21.0 1 100

Materialize (cost=1,204.45..1,290.10 rows=21 width=20) (actual time=0.022..0.022 rows=1 loops=100)

  • Output: t29._idrref
  • Buffers: shared hit=33
37. 0.011 1.391 ↑ 21.0 1 1

Nested Loop (cost=1,204.45..1,290.06 rows=21 width=20) (actual time=1.373..1.391 rows=1 loops=1)

  • Output: t29._idrref
  • Inner Unique: true
  • Buffers: shared hit=33
38. 0.129 1.315 ↑ 25.0 1 1

Hash Join (cost=1,204.39..1,288.18 rows=25 width=37) (actual time=1.299..1.315 rows=1 loops=1)

  • Output: t30._fld14648rref, t31._reference48_idrref
  • Hash Cond: (t30._fld14648rref = t31._reference48_idrref)
  • Buffers: shared hit=31
39. 0.214 0.447 ↑ 1.6 16 1

Nested Loop (cost=0.33..83.85 rows=26 width=20) (actual time=0.087..0.447 rows=16 loops=1)

  • Output: t30._fld14648rref
  • Buffers: shared hit=9
40. 0.039 0.039 ↑ 1.0 1 1

Index Only Scan using _reference62_7 on public._reference62 t28 (cost=0.16..4.18 rows=1 width=20) (actual time=0.024..0.039 rows=1 loops=1)

  • Output: t28._fld1638, t28._idrref
  • Index Cond: (t28._fld1638 = 'Справочник.ФизическиеЛица'::mvarchar)
  • Heap Fetches: 1
  • Buffers: shared hit=4
41. 0.194 0.194 ↑ 5.4 16 1

Index Only Scan using _inforg14646_1 on public._inforg14646 t30 (cost=0.17..78.82 rows=86 width=40) (actual time=0.043..0.194 rows=16 loops=1)

  • Output: t30._fld610, t30._fld14647rref, t30._fld14648rref
  • Index Cond: ((t30._fld610 = '0'::numeric) AND (t30._fld14647rref = t28._idrref))
  • Heap Fetches: 16
  • Buffers: shared hit=5
42. 0.062 0.739 ↑ 20.6 7 1

Hash (cost=1,202.48..1,202.48 rows=144 width=17) (actual time=0.724..0.739 rows=7 loops=1)

  • Output: t31._reference48_idrref
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=22
43. 0.110 0.677 ↑ 20.6 7 1

HashAggregate (cost=1,201.04..1,202.48 rows=144 width=17) (actual time=0.615..0.677 rows=7 loops=1)

  • Output: t31._reference48_idrref
  • Group Key: t31._reference48_idrref
  • Buffers: shared hit=22
44. 0.129 0.567 ↑ 50.9 7 1

Nested Loop (cost=0.33..1,200.68 rows=356 width=17) (actual time=0.403..0.567 rows=7 loops=1)

  • Output: t31._reference48_idrref
  • Buffers: shared hit=22
45. 0.171 0.171 ↑ 54.0 3 1

Index Only Scan using _inforg14469_2 on public._inforg14469 t32 (cost=0.16..131.37 rows=162 width=33) (actual time=0.137..0.171 rows=3 loops=1)

  • Output: t32._fld610, t32._fld14471_type, t32._fld14471_rtref, t32._fld14471_rrref, t32._fld14470_type, t32._fld14470_rtref, t32._fld14470_rrref
  • Index Cond: ((t32._fld610 = '0'::numeric) AND (t32._fld14471_type = '\\x08'::bytea) AND (t32._fld14471_rtref = '\\x00000076'::bytea) AND (t32._fld14471_rrref = '\\x80c7000e1e51267211e75a7005501a87'::bytea))
  • Heap Fetches: 3
  • Buffers: shared hit=6
46. 0.267 0.267 ↑ 3.5 2 3

Index Only Scan using _reference48_vt1292_1 on public._reference48_vt1292 t31 (cost=0.17..6.53 rows=7 width=41) (actual time=0.069..0.089 rows=2 loops=3)

  • Output: t31._fld610, t31._fld1294_type, t31._fld1294_rtref, t31._fld1294_rrref, t31._reference48_idrref
  • Index Cond: ((t31._fld610 = '0'::numeric) AND (t31._fld1294_type = t32._fld14470_type) AND (t31._fld1294_rtref = t32._fld14470_rtref) AND (t31._fld1294_rrref = t32._fld14470_rrref))
  • Heap Fetches: 7
  • Buffers: shared hit=16
47. 0.065 0.065 ↑ 1.0 1 1

Index Only Scan using _reference48_s_hpk on public._reference48 t29 (cost=0.06..0.08 rows=1 width=20) (actual time=0.049..0.065 rows=1 loops=1)

  • Output: t29._fld610, t29._idrref
  • Index Cond: ((t29._fld610 = '0'::numeric) AND (t29._idrref = t30._fld14648rref))
  • Heap Fetches: 1
  • Buffers: shared hit=2
48.          

SubPlan (forNested Loop Semi Join)

49. 0.000 0.000 ↓ 0.0 0

Index Only Scan using _inforg12729_2 on public._inforg12729 t33 (cost=0.11..4.13 rows=1 width=0) (never executed)

  • Index Cond: ((t33._fld610 = '0'::numeric) AND (t33._fld12731_type = CASE WHEN (t26._fld3987rref IS NOT NULL) THEN '\\x08'::bytea ELSE NULL::bytea END) AND (t33._fld12731_rtref = CASE WHEN (t26._fld3987rref IS NOT NULL) THEN '\\x00000031'::bytea ELSE NULL::bytea END) AND (t33._fld12731_rrref = t26._fld3987rref) AND (t33._fld12730rref = t29._idrref))
  • Heap Fetches: 0
50. 8.700 8.700 ↓ 1.3 9 100

Index Only Scan using _inforg12729_2 on public._inforg12729 t33_1 (cost=0.11..9.50 rows=7 width=20) (actual time=0.015..0.087 rows=9 loops=100)

  • Output: t33_1._fld12730rref
  • Index Cond: ((t33_1._fld610 = '0'::numeric) AND (t33_1._fld12731_type = CASE WHEN (t26._fld3987rref IS NOT NULL) THEN '\\x08'::bytea ELSE NULL::bytea END) AND (t33_1._fld12731_rtref = CASE WHEN (t26._fld3987rref IS NOT NULL) THEN '\\x00000031'::bytea ELSE NULL::bytea END) AND (t33_1._fld12731_rrref = t26._fld3987rref))
  • Heap Fetches: 856
  • Buffers: shared hit=623
51. 0.000 0.000 ↓ 0.0 0

Index Scan using _inforg12736_1 on public._inforg12736 t34 (cost=0.11..4.13 rows=1 width=0) (never executed)

  • Index Cond: ((t34._fld610 = '0'::numeric) AND (t34._fld12737rref = t29._idrref) AND (t34._fld12738_type = '\\x08'::bytea) AND (t34._fld12738_rtref = '\\x000000c6'::bytea))
  • Filter: (NOT t34._fld12739)
52. 1.045 1.045 ↓ 2.3 136 1

Index Scan using _inforg12736_2 on public._inforg12736 t34_1 (cost=0.11..29.70 rows=58 width=20) (actual time=0.057..1.045 rows=136 loops=1)

  • Output: t34_1._fld12737rref
  • Index Cond: ((t34_1._fld610 = '0'::numeric) AND (t34_1._fld12738_type = '\\x08'::bytea) AND (t34_1._fld12738_rtref = '\\x000000c6'::bytea))
  • Filter: (NOT t34_1._fld12739)
  • Rows Removed by Filter: 20
  • Buffers: shared hit=27