explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Bz

Settings
# exclusive inclusive rows x rows loops node
1. 1.394 1,184.295 ↑ 1.0 100 1

Limit (cost=0.41..248,067.86 rows=100 width=37) (actual time=642.004..1,184.295 rows=100 loops=1)

2. 2.183 1,182.901 ↑ 59.9 100 1

Nested Loop (cost=0.41..14,864,202.11 rows=5,992 width=37) (actual time=641.993..1,182.901 rows=100 loops=1)

3. 6.108 807.888 ↑ 98.0 115 1

Seq Scan on _reference198 t26 (cost=0.00..7,810,298.49 rows=11,274 width=20) (actual time=624.665..807.888 rows=115 loops=1)

  • Filter: ((_description IS NOT NULL) AND (_fld610 = '0'::numeric) AND (SubPlan 5))
  • Rows Removed by Filter: 715
4.          

SubPlan (forSeq Scan)

5. 78.443 801.780 ↓ 0.0 0 830

Nested Loop Semi Join (cost=137.09..4,664.09 rows=22 width=0) (actual time=0.966..0.966 rows=0 loops=830)

  • 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))
  • Rows Removed by Join Filter: 5
6. 19.090 33.200 ↑ 43.0 1 830

Nested Loop Left Join (cost=0.29..43.16 rows=43 width=20) (actual time=0.028..0.040 rows=1 loops=830)

7. 7.470 7.470 ↑ 1.0 1 830

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.005..0.009 rows=1 loops=830)

8. 6.640 6.640 ↓ 0.0 0 830

Index Only Scan using _reference118_5 on _reference118 t27 (cost=0.29..42.71 rows=43 width=20) (actual time=0.008..0.008 rows=0 loops=830)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld2939rref = t26._idrref))
  • Heap Fetches: 1
9. 25.575 644.910 ↑ 4.2 5 830

Materialize (cost=136.81..453.91 rows=21 width=20) (actual time=0.139..0.777 rows=5 loops=830)

10. 1.609 619.335 ↑ 3.5 6 1

Nested Loop Semi Join (cost=136.81..453.80 rows=21 width=20) (actual time=111.383..619.335 rows=6 loops=1)

11. 2.397 6.346 ↓ 5.9 154 1

Nested Loop (cost=0.97..88.90 rows=26 width=40) (actual time=0.346..6.346 rows=154 loops=1)

12. 1.546 2.717 ↓ 5.9 154 1

Nested Loop (cost=0.82..84.61 rows=26 width=20) (actual time=0.291..2.717 rows=154 loops=1)

13. 0.236 0.236 ↑ 1.0 1 1

Index Only Scan using _reference62_7 on _reference62 t28 (cost=0.41..4.42 rows=1 width=20) (actual time=0.219..0.236 rows=1 loops=1)

  • Index Cond: (_fld1638 = 'Справочник.ФизическиеЛица'::mvarchar)
  • Heap Fetches: 1
14. 0.935 0.935 ↓ 1.8 154 1

Index Only Scan using _inforg14646_1 on _inforg14646 t30 (cost=0.42..79.33 rows=86 width=40) (actual time=0.054..0.935 rows=154 loops=1)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld14647rref = t28._idrref))
  • Heap Fetches: 154
15. 1.232 1.232 ↑ 1.0 1 154

Index Only Scan using _reference48_s_hpk on _reference48 t29 (cost=0.14..0.17 rows=1 width=20) (actual time=0.008..0.008 rows=1 loops=154)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_idrref = t30._fld14648rref))
  • Heap Fetches: 154
16. 303.538 611.380 ↓ 0.0 0 154

Hash Join (cost=135.84..149.58 rows=2 width=17) (actual time=3.970..3.970 rows=0 loops=154)

  • Hash Cond: ((t31._fld1294_type = t32._fld14470_type) AND (t31._fld1294_rtref = t32._fld14470_rtref) AND (t31._fld1294_rrref = t32._fld14470_rrref))
17. 307.692 307.692 ↑ 1.3 372 154

Index Scan using _reference48_vt1292_sk on _reference48_vt1292 t31 (cost=0.42..10.28 rows=492 width=41) (actual time=0.015..1.998 rows=372 loops=154)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_reference48_idrref = t30._fld14648rref))
18. 0.031 0.150 ↑ 54.0 3 1

Hash (cost=132.59..132.59 rows=162 width=33) (actual time=0.141..0.150 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.119 0.119 ↑ 54.0 3 1

Index Only Scan using _inforg14469_2 on _inforg14469 t32 (cost=0.41..132.59 rows=162 width=33) (actual time=0.084..0.119 rows=3 loops=1)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld14471_type = '\\x08'::bytea) AND (_fld14471_rtref = '\\x00000076'::bytea) AND (_fld14471_rrref = '\\x80c7000e1e51267211e75a7005501a87'::bytea))
  • Heap Fetches: 3
20.          

SubPlan (forNested Loop Semi Join)

21. 0.000 0.000 ↓ 0.0 0

Index Only Scan using _inforg12729_2 on _inforg12729 t33 (cost=0.28..4.31 rows=1 width=0) (never executed)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld12731_type = CASE WHEN (t26._fld3987rref IS NOT NULL) THEN '\\x08'::bytea ELSE NULL::bytea END) AND (_fld12731_rtref = CASE WHEN (t26._fld3987rref IS NOT NULL) THEN '\\x00000031'::bytea ELSE NULL::bytea END) AND (_fld12731_rrref = t26._fld3987rref) AND (_fld12730rref = t29._idrref))
  • Heap Fetches: 0
22. 44.334 44.334 ↓ 1.1 8 821

Index Only Scan using _inforg12729_2 on _inforg12729 t33_1 (cost=0.28..9.71 rows=7 width=20) (actual time=0.011..0.054 rows=8 loops=821)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld12731_type = CASE WHEN (t26._fld3987rref IS NOT NULL) THEN '\\x08'::bytea ELSE NULL::bytea END) AND (_fld12731_rtref = CASE WHEN (t26._fld3987rref IS NOT NULL) THEN '\\x00000031'::bytea ELSE NULL::bytea END) AND (_fld12731_rrref = t26._fld3987rref))
  • Heap Fetches: 6490
23. 0.000 0.000 ↓ 0.0 0

Index Scan using _inforg12736_1 on _inforg12736 t34 (cost=0.28..4.30 rows=1 width=0) (never executed)

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

Index Scan using _inforg12736_2 on _inforg12736 t34_1 (cost=0.28..30.59 rows=58 width=20) (actual time=0.046..0.893 rows=136 loops=1)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld12738_type = '\\x08'::bytea) AND (_fld12738_rtref = '\\x000000c6'::bytea))
  • Filter: (NOT _fld12739)
  • Rows Removed by Filter: 20
25. 2.330 372.830 ↑ 1.0 1 115

Index Only Scan using _reference146_4 on _reference146 t24 (cost=0.41..625.67 rows=1 width=37) (actual time=3.237..3.242 rows=1 loops=115)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld3450rref = t26._idrref) AND (_fld3450rref IS NOT NULL))
  • Filter: (SubPlan 13)
  • Heap Fetches: 100
26.          

SubPlan (forIndex Only Scan)

27. 1.500 370.500 ↑ 1.0 1 100

Nested Loop (cost=137.22..625.06 rows=1 width=0) (actual time=3.705..3.705 rows=1 loops=100)

28. 2.900 368.500 ↑ 1.0 1 100

Nested Loop Semi Join (cost=137.22..625.03 rows=1 width=0) (actual time=3.685..3.685 rows=1 loops=100)

  • 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)
29. 1.000 1.000 ↑ 2.0 1 100

Index Only Scan using _inforg35359_1 on _inforg35359 t35 (cost=0.41..5.33 rows=2 width=20) (actual time=0.010..0.010 rows=1 loops=100)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld35360rref = t24._idrref))
  • Heap Fetches: 100
30. 1.100 362.700 ↑ 1.0 1 100

Materialize (cost=136.81..602.45 rows=1 width=20) (actual time=3.627..3.627 rows=1 loops=100)

31. 1.600 361.600 ↑ 1.0 1 100

Nested Loop Semi Join (cost=136.81..602.45 rows=1 width=20) (actual time=3.616..3.616 rows=1 loops=100)

32. 17.100 157.900 ↑ 1.0 1 100

Nested Loop (cost=0.97..452.85 rows=1 width=40) (actual time=1.579..1.579 rows=1 loops=100)

33. 16.400 27.200 ↑ 1.6 16 100

Nested Loop (cost=0.82..84.61 rows=26 width=20) (actual time=0.044..0.272 rows=16 loops=100)

34. 1.200 1.200 ↑ 1.0 1 100

Index Only Scan using _reference62_7 on _reference62 t36 (cost=0.41..4.42 rows=1 width=20) (actual time=0.011..0.012 rows=1 loops=100)

  • Index Cond: (_fld1638 = 'Справочник.Сотрудники'::mvarchar)
  • Heap Fetches: 100
35. 9.600 9.600 ↑ 5.4 16 100

Index Only Scan using _inforg14646_1 on _inforg14646 t38 (cost=0.42..79.33 rows=86 width=40) (actual time=0.017..0.096 rows=16 loops=100)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld14647rref = t36._idrref))
  • Heap Fetches: 1600
36. 28.800 113.600 ↓ 0.0 0 1,600

Index Only Scan using _reference48_s_hpk on _reference48 t37 (cost=0.14..13.64 rows=1 width=20) (actual time=0.071..0.071 rows=0 loops=1,600)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_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: 1600
37.          

SubPlan (forIndex Only Scan)

38. 32.000 70.400 ↓ 0.0 0 1,600

Nested Loop (cost=0.70..9.17 rows=1 width=0) (actual time=0.044..0.044 rows=0 loops=1,600)

39. 24.000 24.000 ↑ 1.0 1 1,600

Index Only Scan using _inforg12318_1 on _inforg12318 t44 (cost=0.42..4.44 rows=1 width=33) (actual time=0.010..0.015 rows=1 loops=1,600)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld12319_type = '\\x08'::bytea) AND (_fld12319_rtref = '\\x000000c6'::bytea) AND (_fld12319_rrref = t24._fld3450rref))
  • Heap Fetches: 1600
40. 14.400 14.400 ↓ 0.0 0 1,600

Index Only Scan using _inforg12729_2 on _inforg12729 t43 (cost=0.28..4.31 rows=1 width=33) (actual time=0.009..0.009 rows=0 loops=1,600)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld12731_type = t44._fld12320_type) AND (_fld12731_rtref = t44._fld12320_rtref) AND (_fld12731_rrref = t44._fld12320_rrref) AND (_fld12730rref = t37._idrref))
  • Heap Fetches: 100
41. 14.400 14.400 ↑ 1.0 1 1,600

Index Scan using _inforg12736_1 on _inforg12736 t45 (cost=0.28..4.30 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1,600)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld12737rref = t37._idrref) AND (_fld12738_type = '\\x08'::bytea) AND (_fld12738_rtref = '\\x000000c6'::bytea))
  • Filter: (NOT _fld12739)
42. 0.000 0.000 ↓ 0.0 0

Index Scan using _inforg12736_2 on _inforg12736 t45_1 (cost=0.28..30.59 rows=58 width=20) (never executed)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld12738_type = '\\x08'::bytea) AND (_fld12738_rtref = '\\x000000c6'::bytea))
  • Filter: (NOT _fld12739)
43. 101.294 202.100 ↑ 2.0 1 100

Hash Join (cost=135.84..149.58 rows=2 width=17) (actual time=2.021..2.021 rows=1 loops=100)

  • Hash Cond: ((t39._fld1294_type = t40._fld14470_type) AND (t39._fld1294_rtref = t40._fld14470_rtref) AND (t39._fld1294_rrref = t40._fld14470_rrref))
44. 100.700 100.700 ↑ 2.6 190 100

Index Scan using _reference48_vt1292_sk on _reference48_vt1292 t39 (cost=0.42..10.28 rows=492 width=41) (actual time=0.015..1.007 rows=190 loops=100)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_reference48_idrref = t38._fld14648rref))
45. 0.031 0.106 ↑ 54.0 3 1

Hash (cost=132.59..132.59 rows=162 width=33) (actual time=0.096..0.106 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.075 0.075 ↑ 54.0 3 1

Index Only Scan using _inforg14469_2 on _inforg14469 t40 (cost=0.41..132.59 rows=162 width=33) (actual time=0.046..0.075 rows=3 loops=1)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld14471_type = '\\x08'::bytea) AND (_fld14471_rtref = '\\x00000076'::bytea) AND (_fld14471_rrref = '\\x80c7000e1e51267211e75a7005501a87'::bytea))
  • Heap Fetches: 3
47.          

SubPlan (forNested Loop Semi Join)

48. 1.000 1.000 ↑ 1.0 1 100

Index Only Scan using _inforg12729_2 on _inforg12729 t41 (cost=0.28..4.31 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=100)

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

Index Only Scan using _inforg12729_2 on _inforg12729 t41_1 (cost=0.28..48.70 rows=391 width=40) (never executed)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld12731_type = '\\x08'::bytea) AND (_fld12731_rtref = '\\x00000051'::bytea))
  • Heap Fetches: 0
50. 0.900 0.900 ↑ 1.0 1 100

Index Scan using _inforg12736_1 on _inforg12736 t42 (cost=0.28..4.30 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=100)

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

Index Scan using _inforg12736_2 on _inforg12736 t42_1 (cost=0.28..30.14 rows=51 width=20) (never executed)

  • Index Cond: ((_fld610 = '0'::numeric) AND (_fld12738_type = '\\x08'::bytea) AND (_fld12738_rtref = '\\x00000051'::bytea))
  • Filter: (NOT _fld12739)
52. 0.500 0.500 ↑ 1.0 1 100

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

Planning time : 15.254 ms
Execution time : 1,187.034 ms