explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.160 350.453 ↓ 0.0 0 1

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

2. 1.039 350.293 ↑ 169.5 22 1

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

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

4. 1.649 343.986 ↑ 1.0 11,170 1

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

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

WindowAgg (cost=224,267.91..226,841.82 rows=22,381 width=12) (actual time=222.450..342.337 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 oor (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.816 334.522 ↑ 1.2 19,093 1

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

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

13. 27.039 276.233 ↓ 5.3 118,991 1

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

14. 108.379 249.194 ↑ 1.0 216,216 1

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

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

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

16. 51.917 81.142 ↑ 1.0 111,068 1

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

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

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

  • Index Cond: (eh_nl_id = 1013)
18. 32.126 39.145 ↑ 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.008..39.145 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.019 7.019 ↑ 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.019..7.019 rows=105,168 loops=1)

  • Index Cond: (tmi_nl_mobile_id = 1013)