explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 10NR

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.009 772,918.791 ↓ 45.0 45 1

ORDER BY (COALESCE(T3.Date_Time_,'0001-01-01 00:00:00'::timestamp)) DESC, (COALESCE(T3.IDRRef,'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea)) DESC, (CASE WHEN T3.IDRRef IS NULL THEN CAST(0 AS NUMERIC) ELSE CAST(1 AS NUMERIC) END) DESC LIMIT 45",planSQLText="Limit (cost=928,162.76..928,162.76 rows=1 width=522) (actual time=772,918.774..772,918.791 rows=45 loops=1)

2. 0.759 772,918.782 ↓ 45.0 45 1

Sort (cost=928,162.76..928,162.76 rows=1 width=522) (actual time=772,918.774..772,918.782 rows=45 loops=1)

  • Sort Key: (COALESCE(t4._date_time, '0001-01-01 00:00:00'::timestamp without time zone)) DESC, (COALESCE(t4._idrref, '\\x00000000000000000000000000000000'::bytea)) DESC, (CASE WHEN (t4._idrref IS NULL) THEN '0'::numeric ELSE '1'::numeric END) DESC
  • Sort Method: top-N heapsort Memory: 48kB
3. 95.761 772,918.023 ↓ 120.0 120 1

Nested Loop Semi Join (cost=0.77..928,162.75 rows=1 width=522) (actual time=42,149.845..772,918.023 rows=120 loops=1)

4. 165.807 772,620.676 ↓ 28,798.0 28,798 1

Nested Loop Left Join (cost=0.61..928,162.52 rows=1 width=389) (actual time=17.702..772,620.676 rows=28,798 loops=1)

5. 205.075 772,195.687 ↓ 28,798.0 28,798 1

Nested Loop Left Join (cost=0.50..928,162.39 rows=1 width=388) (actual time=17.687..772,195.687 rows=28,798 loops=1)

  • Join Filter: ((t4._fld8150_type = CASE WHEN (t11._fld2187rref IS NOT NULL) THEN '\\x08'::bytea ELSE NULL::bytea END) AND (t4._fld8150_rtref = CASE WHEN (t11._fld2187rref IS NOT NULL) THEN '\\x00000077'::bytea ELSE NULL::bytea END) AND (t4._fld8150_rrref = t11._fld2187rref))
6. 472,607.635 771,846.622 ↓ 28,798.0 28,798 1

Nested Loop Left Join (cost=0.50..928,161.24 rows=1 width=356) (actual time=17.674..771,846.622 rows=28,798 loops=1)

  • Join Filter: ((CASE WHEN (t4._idrref IS NOT NULL) THEN '\\x08'::bytea ELSE NULL::bytea END = t8._fld3233_type) AND (CASE WHEN (t4._idrref IS NOT NULL) THEN '\\x000001e9'::bytea ELSE NULL::bytea END = t8._fld3233_rtref) AND (t4._idrref = t8._fld3233_rrref))
  • Rows Removed by Join Filter: 1767929830
7. 99.990 5,182.609 ↓ 28,798.0 28,798 1

Nested Loop Left Join (cost=0.50..922,980.51 rows=1 width=339) (actual time=0.171..5,182.609 rows=28,798 loops=1)

8. 97.407 4,967.427 ↓ 28,798.0 28,798 1

Nested Loop (cost=0.39..922,980.37 rows=1 width=322) (actual time=0.164..4,967.427 rows=28,798 loops=1)

9. 103.406 4,265.262 ↓ 28,798.0 28,798 1

Nested Loop Left Join (cost=0.22..922,971.88 rows=1 width=339) (actual time=0.142..4,265.262 rows=28,798 loops=1)

  • Join Filter: (t4._idrref = t10._fld2022rref)
  • Rows Removed by Join Filter: 143993
  • Filter: ((t10._fld2027rref IS NULL) OR (t10._fld2027rref <> '\\xa3cff1f7a860096f4922ac09351a0674'::bytea))
  • Rows Removed by Filter: 3
10. 149.465 4,133.055 ↑ 1.9 28,801 1

Nested Loop (cost=0.17..919,502.56 rows=53,308 width=322) (actual time=0.103..4,133.055 rows=28,801 loops=1)

11. 208.490 208.490 ↓ 1.4 151,004 1

Seq Scan on _task489_vt8161 t1 (cost=0.00..6,455.79 rows=107,530 width=17) (actual time=0.022..208.490 rows=151,004 loops=1)

  • Filter: ((_fld8165_type = '\\x08'::bytea) AND ((_fld8165_rtref = '\\x00000037'::bytea) OR (_fld8165_rtref = '\\x00000035'::bytea) OR (_fld8165_rtref = '\\x00000050'::bytea)))
  • Rows Removed by Filter: 2456
12. 925.803 3,775.100 ↓ 0.0 0 151,004

Index Scan using _task489ng_pkey1 on _task489 t4 (cost=0.17..8.49 rows=1 width=305) (actual time=0.025..0.025 rows=0 loops=151,004)

  • Index Cond: (_idrref = t1._task489_idrref)
  • Filter: ((NOT _marked) AND (NOT _fld8136) AND (_fld8144rref = '\\x91c3774e6ccf7efa432dd180b77d748a'::bytea) AND (SubPlan 1))
  • Rows Removed by Filter: 1
13.          

SubPlan (forIndex Scan)

14. 149.963 2,849.297 ↓ 0.0 0 149,963

Nested Loop (cost=0.44..7.99 rows=1 width=0) (actual time=0.019..0.019 rows=0 loops=149,963)

15. 599.852 599.852 ↑ 2.0 1 149,963

Index Scan using _inforg3456_bymainfilter on _inforg3456 t6 (cost=0.16..3.78 rows=2 width=24) (actual time=0.003..0.004 rows=1 loops=149,963)

  • Index Cond: (_fld3457rref = '\\xa4370df76cc05e8149666a729c177dc6'::bytea)
  • Filter: (_fld3459 = ANY ('{"""",ПроцессыИЗадачи}'::mvarchar[]))
16. 2,099.482 2,099.482 ↓ 0.0 0 149,963

Index Only Scan using _inforg3832_bydims3838 on _inforg3832 t5 (cost=0.27..2.09 rows=1 width=30) (actual time=0.014..0.014 rows=0 loops=149,963)

  • Index Cond: ((_fld3834_type = t4._businessprocess_type) AND (_fld3834_rtref = t4._businessprocess_rtref) AND (_fld3834_rrref = t4._businessprocess_rrref) AND (_fld3833_type = t6._fld3458_type) AND (_fld3833_rtref = t6._fld3458_rtref) AND (_fld3833_rrref = t6._fld3458_rrref))
  • Heap Fetches: 243
17. 28.768 28.801 ↑ 1.0 5 28,801

Materialize (cost=0.05..4.30 rows=5 width=34) (actual time=0.001..0.001 rows=5 loops=28,801)

18. 0.033 0.033 ↑ 1.0 5 1

Index Only Scan using _inforg2021_byresource2030 on _inforg2021 t10 (cost=0.05..4.29 rows=5 width=34) (actual time=0.030..0.033 rows=5 loops=1)

  • Heap Fetches: 7
19. 172.788 604.758 ↑ 1.0 1 28,798

Index Scan using _task489ng_pkey1 on _task489 t2 (cost=0.17..8.49 rows=1 width=17) (actual time=0.021..0.021 rows=1 loops=28,798)

  • Index Cond: (_idrref = t1._task489_idrref)
  • Filter: (SubPlan 2)
20.          

SubPlan (forIndex Scan)

21. 57.596 431.970 ↑ 1.0 1 28,798

Nested Loop (cost=0.44..7.99 rows=1 width=0) (actual time=0.015..0.015 rows=1 loops=28,798)

22. 115.192 115.192 ↑ 2.0 1 28,798

Index Scan using _inforg3456_bymainfilter on _inforg3456 t14 (cost=0.16..3.78 rows=2 width=24) (actual time=0.004..0.004 rows=1 loops=28,798)

  • Index Cond: (_fld3457rref = '\\xa4370df76cc05e8149666a729c177dc6'::bytea)
  • Filter: (_fld3459 = ANY ('{"""",ПроцессыИЗадачи}'::mvarchar[]))
23. 259.182 259.182 ↑ 1.0 1 28,798

Index Only Scan using _inforg3832_bydims3838 on _inforg3832 t13 (cost=0.27..2.09 rows=1 width=30) (actual time=0.009..0.009 rows=1 loops=28,798)

  • Index Cond: ((_fld3834_type = t2._businessprocess_type) AND (_fld3834_rtref = t2._businessprocess_rtref) AND (_fld3834_rrref = t2._businessprocess_rrref) AND (_fld3833_type = t14._fld3458_type) AND (_fld3833_rtref = t14._fld3458_rtref) AND (_fld3833_rrref = t14._fld3458_rrref))
  • Heap Fetches: 243
24. 115.192 115.192 ↓ 0.0 0 28,798

Index Scan using _inforg3899_bydims3903 on _inforg3899 t7 (cost=0.11..0.13 rows=1 width=41) (actual time=0.004..0.004 rows=0 loops=28,798)

  • Index Cond: ((_fld3901rref = '\\xa4370df76cc05e8149666a729c177dc6'::bytea) AND (CASE WHEN (t4._idrref IS NOT NULL) THEN '\\x08'::bytea ELSE NULL::bytea END = _fld3900_type) AND (CASE WHEN (t4._idrref IS NOT NULL) THEN '\\x000001e9'::bytea ELSE NULL::bytea END = _fld3900_rtref) AND (t4._idrref = _fld3900_rrref))
25. 294,056.378 294,056.378 ↑ 1.4 61,391 28,798

Seq Scan on _inforg3232 t8 (cost=0.00..4,049.88 rows=86,988 width=41) (actual time=0.004..10.211 rows=61,391 loops=28,798)

26. 143.990 143.990 ↓ 0.0 0 28,798

Seq Scan on _inforg2185 t11 (cost=0.00..1.14 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=28,798)

  • Filter: (_fld2186rref = '\\xa4370df76cc05e8149666a729c177dc6'::bytea)
  • Rows Removed by Filter: 13
27. 259.182 259.182 ↑ 1.0 1 28,798

Index Scan using _reference132x1z_pkey on _reference132x1 t12 (cost=0.11..0.13 rows=1 width=18) (actual time=0.009..0.009 rows=1 loops=28,798)

  • Index Cond: (t4._fld8130rref = _idrref)
28. 201.586 201.586 ↓ 0.0 0 28,798

Index Only Scan using _inforg2198_bymainfilter on _inforg2198 t15 (cost=0.16..0.18 rows=1 width=24) (actual time=0.007..0.007 rows=0 loops=28,798)

  • Index Cond: ((_fld2199rref = '\\xa4370df76cc05e8149666a729c177dc6'::bytea) AND (_fld2200_type = t4._fld8150_type) AND (_fld2200_rtref = t4._fld8150_rtref) AND (_fld2200_rrref = t4._fld8150_rrref))
  • Filter: (_fld2203 = ANY ('{"""",ПроцессыИЗадачи}'::mvarchar[]))
  • Heap Fetches: 120