explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xd5T : 11111111111

Settings
# exclusive inclusive rows x rows loops node
1. 2.406 4,485,303.328 ↓ 262.0 262 1

"Insert on pg_temp_23.tt18 (cost=110,022.18..110,022.51 rows=1 width=49) (actual time=4,485,291.507..4,485,303.328 rows=262 loops=1)

  • Output: 0
  • Buffers: shared hit=557800044 read=29, local hit=575 read=8 dirtied=6
2. 0.110 4,485,300.922 ↓ 262.0 262 1

Subquery Scan on "*SELECT*" (cost=110,022.18..110,022.51 rows=1 width=49) (actual time=4,485,290.864..4,485,300.922 rows=262 loops=1)

  • Output: ""*SELECT*"".fld8226rref, ""*SELECT*"".max, ""*SELECT*"".max_1
  • Buffers: shared hit=557800044 read=29, local hit=14
3. 13.509 4,485,300.812 ↓ 262.0 262 1

GroupAggregate (cost=110,022.18..110,022.50 rows=1 width=81) (actual time=4,485,290.861..4,485,300.812 rows=262 loops=1)

  • Output: t8._fld8226rref, max((t8._fld8228 + (datediff('DAY'::character varying, t8._period, '2020-02-12 08:00:00'::timestamp without time zone))::numeric(15,0))), max(t8._fld8233)
  • Group Key: t8._fld8226rref
  • Filter: ((max((t8._fld8228 + (datediff('DAY'::character varying, t8._period, '2020-02-12 08:00:00'::timestamp without time zone))::numeric(15,0))) > '7'::numeric) AND (max((t8._fld8228 + (datediff('DAY'::character varying, t8._period, '2020-02-12 08:00:00'::timestamp without time zone))::numeric(15,0))) < '41'::numeric))
  • Rows Removed by Filter: 511
  • Buffers: shared hit=557800044 read=29, local hit=14
4. 2.771 4,485,287.303 ↓ 773.0 773 1

Sort (cost=110,022.18..110,022.18 rows=1 width=31) (actual time=4,485,287.133..4,485,287.303 rows=773 loops=1)

  • Output: t8._fld8226rref, t8._fld8228, t8._period, t8._fld8233
  • Sort Key: t8._fld8226rref
  • Sort Method: quicksort Memory: 85kB
  • Buffers: shared hit=557799315 read=29, local hit=14
5. 36.507 4,485,284.532 ↓ 773.0 773 1

Nested Loop (cost=110,018.18..110,022.17 rows=1 width=31) (actual time=7,754.328..4,485,284.532 rows=773 loops=1)

  • Output: t8._fld8226rref, t8._fld8228, t8._period, t8._fld8233
  • Join Filter: ((t13._fld5798rref = t15._fld5798rref) AND (t13._fld5799rref = t15._fld5799rref) AND ((max(t13._period)) = t15._period) AND ((substr(max((t14._recordertref || t14._recorderrref)), 1, 4)) = t15._recordertref) AND ((substr(max((t14._recordertref || t14._recorderrref)), 5, 16)) = t15._recorderrref))
  • Rows Removed by Join Filter: 7
  • Buffers: shared hit=557799315 read=29, local hit=14
6. 7,493.164 4,485,248.025 ↓ 773.0 773 1

Nested Loop (cost=110,017.96..110,021.26 rows=1 width=177) (actual time=7,754.286..4,485,248.025 rows=773 loops=1)

  • Output: t4._fld8226rref, t8._fld8226rref, t8._fld8228, t8._period, t8._fld8233, t13._fld5798rref, t13._fld5799rref, t13._fld5800rref, (max(t13._period)), (substr(max((t14._recordertref || t14._recorderrref)), 1, 4)), (substr(max((t14._recordertref || t14._recorderrref)), 5, 16))
  • Join Filter: (t4._fld8226rref = t13._fld5799rref)
  • Rows Removed by Join Filter: 37121006
  • Buffers: shared hit=557788738 read=29, local hit=14
7. 294.658 810.686 ↓ 48,023.0 48,023 1

GroupAggregate (cost=73,308.94..73,309.16 rows=1 width=129) (actual time=467.925..810.686 rows=48,023 loops=1)

  • Output: t13._fld5798rref, t13._fld5799rref, t13._fld5800rref, (max(t13._period)), substr(max((t14._recordertref || t14._recorderrref)), 1, 4), substr(max((t14._recordertref || t14._recorderrref)), 5, 16)
  • Group Key: t13._fld5798rref, t13._fld5799rref, t13._fld5800rref, (max(t13._period))
  • Buffers: shared hit=213113
8. 104.598 516.028 ↓ 48,023.0 48,023 1

Sort (cost=73,308.94..73,308.94 rows=1 width=91) (actual time=467.863..516.028 rows=48,023 loops=1)

  • Output: t13._fld5798rref, t13._fld5799rref, t13._fld5800rref, (max(t13._period)), t14._recordertref, t14._recorderrref
  • Sort Key: t13._fld5798rref, t13._fld5799rref, (max(t13._period))
  • Sort Method: quicksort Memory: 8290kB
  • Buffers: shared hit=213113
9. 21.931 411.430 ↓ 48,023.0 48,023 1

Nested Loop (cost=30,435.09..73,308.94 rows=1 width=91) (actual time=79.810..411.430 rows=48,023 loops=1)

  • Output: t13._fld5798rref, t13._fld5799rref, t13._fld5800rref, (max(t13._period)), t14._recordertref, t14._recorderrref
  • Buffers: shared hit=213110
10. 52.712 101.361 ↓ 1.1 48,023 1

HashAggregate (cost=30,434.87..30,875.50 rows=44,063 width=65) (actual time=79.627..101.361 rows=48,023 loops=1)

  • Output: t13._fld5798rref, t13._fld5799rref, t13._fld5800rref, max(t13._period)
  • Group Key: t13._fld5798rref, t13._fld5799rref, t13._fld5800rref
  • Buffers: shared hit=19381
11. 34.865 48.649 ↑ 1.0 51,115 1

Bitmap Heap Scan on public._inforg5797 t13 (cost=1,292.50..30,223.27 rows=52,899 width=65) (actual time=16.937..48.649 rows=51,115 loops=1)

  • Output: t13._period, t13._recordertref, t13._recorderrref, t13._lineno, t13._active, t13._fld5798rref, t13._fld5799rref, t13._fld5800rref, t13._fld5801_type, t13._fld5801_l, t13._fld5801_n, t13._fld5801_t, t13._fld5801_s, t13._fld5801_rtref, t13._fld5801_rrref
  • Recheck Cond: ((t13._fld5800rref = '\\xb7f21c6f65c554b111e62d59ada33aab'::bytea) AND (t13._period <= '2020-02-12 08:00:01'::timestamp without time zone))
  • Filter: t13._active
  • Heap Blocks: exact=18789
  • Buffers: shared hit=19381
12. 13.784 13.784 ↑ 1.0 51,115 1

Bitmap Index Scan on _inforg5797_bydims5804 (cost=0.00..1,287.21 rows=52,899 width=0) (actual time=13.784..13.784 rows=51,115 loops=1)

  • Index Cond: ((t13._fld5800rref = '\\xb7f21c6f65c554b111e62d59ada33aab'::bytea) AND (t13._period <= '2020-02-12 08:00:01'::timestamp without time zone) AND (t13._active = true))
  • Buffers: shared hit=592
13. 288.138 288.138 ↑ 1.0 1 48,023

Index Only Scan using _inforg5797_bydims on public._inforg5797 t14 (cost=0.22..0.94 rows=1 width=91) (actual time=0.006..0.006 rows=1 loops=48,023)

  • Output: t14._fld5798rref, t14._fld5799rref, t14._fld5800rref, t14._period, t14._recordertref, t14._recorderrref, t14._lineno, t14._active
  • Index Cond: ((t14._fld5798rref = t13._fld5798rref) AND (t14._fld5799rref = t13._fld5799rref) AND (t14._fld5800rref = '\\xb7f21c6f65c554b111e62d59ada33aab'::bytea) AND (t14._period = (max(t13._period))) AND (t14._active = true))
  • Filter: t14._active
  • Heap Fetches: 6
  • Buffers: shared hit=193729
14. 4,476,944.175 4,476,944.175 ↓ 773.0 773 48,023

Nested Loop (cost=36,709.01..36,712.07 rows=1 width=48) (actual time=0.195..93.225 rows=773 loops=48,023)

  • Output: t4._fld8226rref, t8._fld8226rref, t8._fld8228, t8._period, t8._fld8233
  • Buffers: shared hit=557575625 read=29, local hit=14