explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jBR3 : Optimization for: Optimization for: Optimization for: plan #pzdG; plan #LJ8t; plan #xlHE

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.070 358.944 ↓ 0.0 0 1

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

2. 0.998 358.874 ↑ 169.5 22 1

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

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

4. 1.825 352.593 ↑ 1.0 11,170 1

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

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

WindowAgg (cost=224,267.91..226,841.82 rows=22,381 width=12) (actual time=235.112..350.768 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. 11.124 343.162 ↑ 1.2 19,093 1

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

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

13. 24.368 285.946 ↓ 5.3 118,991 1

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

14. 115.358 261.578 ↑ 1.0 216,216 1

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

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

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

16. 54.439 80.620 ↑ 1.0 111,068 1

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

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

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

  • Index Cond: (eh_nl_id = 1013)
18. 35.715 42.792 ↑ 1.1 105,148 1

Bitmap Heap Scan on trackingmessagesindexed (cost=1,313.47..110,816.36 rows=110,569 width=11) (actual time=9.150..42.792 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. 7.077 7.077 ↑ 1.1 105,168 1

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

  • Index Cond: (tmi_nl_mobile_id = 1013)