explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pzdG

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.044 2,073.625 ↓ 0.0 0 1

Insert on view_activity_data (cost=599,241.55..740,512.36 rows=3,730 width=24) (actual time=2,073.625..2,073.625 rows=0 loops=1)

2. 1.029 2,073.581 ↑ 3,730.0 1 1

Subquery Scan on z (cost=599,241.55..740,475.06 rows=3,730 width=24) (actual time=2,073.580..2,073.581 rows=1 loops=1)

  • Filter: ((z.eh_networktime >= 1547142034) AND ((z.eh_mainengine IS DISTINCT FROM z.prev_main) OR (z.eh_auxengine IS DISTINCT FROM z.prev_aux)))
  • Rows Removed by Filter: 11169
3.          

CTE oor_min_time

4. 278.236 1,595.464 ↑ 1.0 272 1

HashAggregate (cost=375,056.44..375,059.18 rows=274 width=8) (actual time=1,595.433..1,595.464 rows=272 loops=1)

  • Group Key: enginehistory_1.eh_nl_id
5. 1,317.228 1,317.228 ↓ 1.0 1,827,001 1

Seq Scan on enginehistory enginehistory_1 (cost=0.00..366,016.17 rows=1,808,054 width=8) (actual time=0.191..1,317.228 rows=1,827,001 loops=1)

  • Filter: eh_isoutofrange
  • Rows Removed by Filter: 21931835
6. 5.140 2,072.552 ↑ 1.0 11,170 1

WindowAgg (cost=224,182.37..365,182.76 rows=11,190 width=12) (actual time=218.734..2,072.552 rows=11,170 loops=1)

7. 2.113 2,067.412 ↑ 1.0 11,170 1

Subquery Scan on y (cost=224,182.37..364,958.96 rows=11,190 width=10) (actual time=218.730..2,067.412 rows=11,170 loops=1)

  • Filter: (y.collapse IS NOT TRUE)
  • Rows Removed by Filter: 7923
8. 6.805 2,065.299 ↑ 1.2 19,093 1

WindowAgg (cost=224,182.37..364,735.15 rows=22,381 width=12) (actual time=218.730..2,065.299 rows=19,093 loops=1)

9. 10.593 323.357 ↑ 1.2 19,093 1

Subquery Scan on x (cost=224,182.37..226,308.66 rows=22,381 width=11) (actual time=218.712..323.357 rows=19,093 loops=1)

  • Filter: ((x.eh_mainengine IS DISTINCT FROM x.prev_main) OR (x.eh_auxengine IS DISTINCT FROM x.prev_aux))
  • Rows Removed by Filter: 99898
10. 44.699 312.764 ↓ 5.3 118,991 1

WindowAgg (cost=224,182.37..225,972.93 rows=22,382 width=13) (actual time=218.710..312.764 rows=118,991 loops=1)

11. 23.511 268.065 ↓ 5.3 118,991 1

Unique (cost=224,182.37..225,301.47 rows=22,382 width=11) (actual time=218.705..268.065 rows=118,991 loops=1)

12. 106.843 244.554 ↑ 1.0 216,216 1

Sort (cost=224,182.37..224,741.92 rows=223,820 width=11) (actual time=218.703..244.554 rows=216,216 loops=1)

  • Sort Key: enginehistory.eh_networktime, enginehistory.eh_mainengine
  • Sort Method: external merge Disk: 4224kB
13. 20.296 137.711 ↑ 1.0 216,216 1

Append (cost=1,344.63..201,834.50 rows=223,820 width=11) (actual time=32.509..137.711 rows=216,216 loops=1)

14. 51.095 78.373 ↑ 1.0 111,068 1

Bitmap Heap Scan on enginehistory (cost=1,344.63..89,912.45 rows=113,251 width=11) (actual time=32.508..78.373 rows=111,068 loops=1)

  • Recheck Cond: (eh_nl_id = 1013)
  • Heap Blocks: exact=22567
15. 27.278 27.278 ↑ 1.0 111,068 1

Bitmap Index Scan on enginehistory_pkey (cost=0.00..1,316.32 rows=113,251 width=0) (actual time=27.278..27.278 rows=111,068 loops=1)

  • Index Cond: (eh_nl_id = 1013)
16. 32.222 39.042 ↑ 1.1 105,148 1

Bitmap Heap Scan on trackingmessagesindexed (cost=1,313.47..110,816.36 rows=110,569 width=11) (actual time=8.807..39.042 rows=105,148 loops=1)

  • Recheck Cond: (tmi_nl_mobile_id = 1013)
  • Filter: (tmi_disconnect IS NOT NULL)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=13499
17. 6.820 6.820 ↑ 1.1 105,168 1

Bitmap Index Scan on tmi_indextrackingmobile (cost=0.00..1,285.83 rows=110,569 width=0) (actual time=6.820..6.820 rows=105,168 loops=1)

  • Index Cond: (tmi_nl_mobile_id = 1013)
18.          

SubPlan (forWindowAgg)

19. 1,735.137 1,735.137 ↑ 1.0 1 7,923

CTE Scan on oor_min_time (cost=0.00..6.17 rows=1 width=4) (actual time=0.208..0.219 rows=1 loops=7,923)

  • Filter: (x.eh_nl_id = oor_nl_id)
  • Rows Removed by Filter: 271