explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LJ8t : Optimization for: plan #pzdG

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.083 771.607 ↓ 0.0 0 1

Insert on view_activity_data (cost=267,819.69..409,090.49 rows=3,730 width=24) (actual time=771.607..771.607 rows=0 loops=1)

2. 1.041 771.524 ↑ 169.5 22 1

Subquery Scan on z (cost=267,819.69..409,053.19 rows=3,730 width=24) (actual time=771.428..771.524 rows=22 loops=1)

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

CTE oor_min_time

4. 158.761 310.145 ↑ 1.0 272 1

GroupAggregate (cost=0.43..43,637.43 rows=274 width=8) (actual time=2.247..310.145 rows=272 loops=1)

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

Index Only Scan using eh_index_oor on enginehistory enginehistory_1 (cost=0.43..34,594.46 rows=1,808,047 width=8) (actual time=0.025..151.384 rows=1,827,001 loops=1)

  • Heap Fetches: 264
6. 5.232 770.483 ↑ 1.0 11,170 1

WindowAgg (cost=224,182.26..365,182.64 rows=11,190 width=12) (actual time=210.861..770.483 rows=11,170 loops=1)

7. 1.853 765.251 ↑ 1.0 11,170 1

Subquery Scan on y (cost=224,182.26..364,958.84 rows=11,190 width=10) (actual time=210.859..765.251 rows=11,170 loops=1)

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

WindowAgg (cost=224,182.26..364,735.03 rows=22,381 width=12) (actual time=210.858..763.398 rows=19,093 loops=1)

9. 10.989 319.014 ↑ 1.2 19,093 1

Subquery Scan on x (cost=224,182.26..226,308.54 rows=22,381 width=11) (actual time=210.841..319.014 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. 46.617 308.025 ↓ 5.3 118,991 1

WindowAgg (cost=224,182.26..225,972.81 rows=22,382 width=13) (actual time=210.839..308.025 rows=118,991 loops=1)

11. 24.263 261.408 ↓ 5.3 118,991 1

Unique (cost=224,182.26..225,301.35 rows=22,382 width=11) (actual time=210.834..261.408 rows=118,991 loops=1)

12. 112.567 237.145 ↑ 1.0 216,216 1

Sort (cost=224,182.26..224,741.80 rows=223,819 width=11) (actual time=210.829..237.145 rows=216,216 loops=1)

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

Append (cost=1,344.62..201,834.48 rows=223,819 width=11) (actual time=13.707..124.578 rows=216,216 loops=1)

14. 50.701 60.407 ↑ 1.0 111,068 1

Bitmap Heap Scan on enginehistory (cost=1,344.62..89,912.43 rows=113,250 width=11) (actual time=13.706..60.407 rows=111,068 loops=1)

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

Bitmap Index Scan on enginehistory_pkey (cost=0.00..1,316.31 rows=113,250 width=0) (actual time=9.706..9.706 rows=111,068 loops=1)

  • Index Cond: (eh_nl_id = 1013)
16. 35.112 42.015 ↑ 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.959..42.015 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.903 6.903 ↑ 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.903..6.903 rows=105,168 loops=1)

  • Index Cond: (tmi_nl_mobile_id = 1013)
18.          

SubPlan (forWindowAgg)

19. 435.765 435.765 ↑ 1.0 1 7,923

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

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