explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.102 826.246 ↓ 0.0 0 1

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

2. 0.976 826.144 ↑ 169.5 22 1

Subquery Scan on z (cost=267,819.69..409,053.19 rows=3,730 width=24) (actual time=826.059..826.144 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. 167.498 327.263 ↑ 1.0 272 1

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

  • Group Key: enginehistory_1.eh_nl_id
5. 159.765 159.765 ↓ 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.036..159.765 rows=1,827,001 loops=1)

  • Heap Fetches: 264
6. 5.044 825.168 ↑ 1.0 11,170 1

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

7. 1.748 820.124 ↑ 1.0 11,170 1

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

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

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

9. 10.428 356.701 ↑ 1.2 19,093 1

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

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

11. 23.573 302.158 ↓ 5.3 118,991 1

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

12. 113.069 278.585 ↑ 1.0 216,216 1

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

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

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

14. 69.050 97.892 ↑ 1.0 111,068 1

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

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

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

  • Index Cond: (eh_nl_id = 1013)
16. 38.756 46.535 ↑ 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.792..46.535 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. 7.779 7.779 ↑ 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.779..7.779 rows=105,168 loops=1)

  • Index Cond: (tmi_nl_mobile_id = 1013)
18.          

SubPlan (forWindowAgg)

19. 451.611 451.611 ↑ 1.0 1 7,923

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

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