explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IcrO

Settings
# exclusive inclusive rows x rows loops node
1. 8,183.953 162,817.683 ↓ 5.5 49,816 1

Gather (cost=3,067,286.61..7,791,898.53 rows=8,979 width=90) (actual time=117,761.101..162,817.683 rows=49,816 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
2. 54,839.030 154,633.730 ↓ 4.7 24,908 2

Parallel Hash Join (cost=3,066,286.61..7,790,000.63 rows=5,282 width=90) (actual time=116,896.714..154,633.730 rows=24,908 loops=2)

  • Hash Cond: (pe."CommandGUID" = dc."CommandGUID")
3. 68,640.017 68,640.017 ↑ 1.2 45,421,156 2

Parallel Index Only Scan using "PolicyEvents_idx4" on "PolicyEvents" pe (cost=0.57..4,005,943.46 rows=52,888,551 width=16) (actual time=80.746..68,640.017 rows=45,421,156 loops=2)

  • Index Cond: (("EventTime" >= '2017-07-01 00:00:00'::timestamp without time zone) AND ("EventTime" < '2019-07-04 00:00:00'::timestamp without time zone))
  • Heap Fetches: 56
4. 116.289 31,154.683 ↑ 127.7 1,560 2

Parallel Hash (cost=3,060,878.06..3,060,878.06 rows=199,198 width=90) (actual time=31,154.683..31,154.683 rows=1,560 loops=2)

  • Buckets: 131072 Batches: 4 Memory Usage: 1184kB
5. 0.209 31,038.394 ↑ 127.7 1,560 2

Nested Loop (cost=737.45..3,060,878.06 rows=199,198 width=90) (actual time=29,936.548..31,038.394 rows=1,560 loops=2)

6. 1,499.765 30,850.825 ↑ 1,575.0 1 2

Parallel Bitmap Heap Scan on "DataTaskFiles" dtf (cost=736.75..10,836.77 rows=1,575 width=90) (actual time=29,760.705..30,850.825 rows=1 loops=2)

  • Recheck Cond: ("BatchFilePath" ~ 'F:\\FOMS\\AutoLoad\\Gateway\\Input\\B45000-FEDEDEBE-B0BA-C0BA-B0DA-BCEBCAD00031\.dat'::text)
  • Rows Removed by Index Recheck: 94
  • Heap Blocks: exact=46
7. 29,351.060 29,351.060 ↑ 14.1 190 1

Bitmap Index Scan on "DataTaskFiles_BatchFilePath_gin_idx" (cost=0.00..736.08 rows=2,677 width=0) (actual time=29,351.060..29,351.060 rows=190 loops=1)

  • Index Cond: ("BatchFilePath" ~ 'F:\\FOMS\\AutoLoad\\Gateway\\Input\\B45000-FEDEDEBE-B0BA-C0BA-B0DA-BCEBCAD00031\.dat'::text)
8. 187.360 187.360 ↑ 16.8 1,560 2

Index Only Scan using "DataCommands_idx2" on "DataCommands" dc (cost=0.70..1,674.90 rows=26,163 width=34) (actual time=175.816..187.360 rows=1,560 loops=2)

  • Index Cond: ("TaskGUID" = dtf."TaskGUID")
  • Heap Fetches: 0
Planning time : 1,556.705 ms
Execution time : 162,819.443 ms