explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8zuw : Optimization for: Optimization for: Optimization for: plan #pzdG; plan #LJ8t; plan #xlHE

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.093 350.617 ↓ 0.0 0 1

Insert on view_activity_data (cost=224,267.91..227,522.55 rows=3,730 width=24) (actual time=350.617..350.617 rows=0 loops=1)

2. 0.982 350.524 ↑ 169.5 22 1

Subquery Scan on z (cost=224,267.91..227,522.55 rows=3,730 width=24) (actual time=350.457..350.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. 5.194 349.542 ↑ 1.0 11,170 1

WindowAgg (cost=224,267.91..227,289.43 rows=11,190 width=12) (actual time=229.255..349.542 rows=11,170 loops=1)

4. 1.602 344.348 ↑ 1.0 11,170 1

Subquery Scan on y (cost=224,267.91..227,065.63 rows=11,190 width=10) (actual time=229.252..344.348 rows=11,170 loops=1)

  • Filter: (y.collapse IS NOT TRUE)
  • Rows Removed by Filter: 7923
5. 7.466 342.746 ↑ 1.2 19,093 1

WindowAgg (cost=224,267.91..226,841.82 rows=22,381 width=12) (actual time=229.250..342.746 rows=19,093 loops=1)

6.          

Initplan (forWindowAgg)

7. 0.002 0.016 ↑ 1.0 1 1

Result (cost=85.65..85.66 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)

8.          

Initplan (forResult)

9. 0.001 0.014 ↑ 1.0 1 1

Limit (cost=0.56..85.65 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)

10. 0.013 0.013 ↑ 9,040.0 1 1

Index Scan Backward using eh_index_latest on enginehistory ehoor (cost=0.56..769,159.05 rows=9,040 width=4) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (eh_networktime IS NOT NULL)
  • Filter: ((eh_isoutofrange IS TRUE) AND (eh_nl_id = eh_nl_id))
11. 10.974 335.264 ↑ 1.2 19,093 1

Subquery Scan on x (cost=224,182.26..226,308.54 rows=22,381 width=11) (actual time=229.234..335.264 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
12. 45.076 324.290 ↓ 5.3 118,991 1

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

13. 23.990 279.214 ↓ 5.3 118,991 1

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

14. 107.869 255.224 ↑ 1.0 216,216 1

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

  • Sort Key: enginehistory.eh_networktime, enginehistory.eh_mainengine
  • Sort Method: external merge Disk: 4224kB
15. 21.755 147.355 ↑ 1.0 216,216 1

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

16. 55.234 85.281 ↑ 1.0 111,068 1

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

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

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

  • Index Cond: (eh_nl_id = 1013)
18. 33.476 40.319 ↑ 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.896..40.319 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
19. 6.843 6.843 ↑ 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.843..6.843 rows=105,168 loops=1)

  • Index Cond: (tmi_nl_mobile_id = 1013)