explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.069 351.438 ↓ 0.0 0 1

Insert on view_activity_data (cost=224,261.33..227,515.97 rows=3,730 width=24) (actual time=351.438..351.438 rows=0 loops=1)

2. 1.013 351.369 ↑ 169.5 22 1

Subquery Scan on z (cost=224,261.33..227,515.97 rows=3,730 width=24) (actual time=351.302..351.369 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.117 350.356 ↑ 1.0 11,170 1

WindowAgg (cost=224,261.33..227,282.85 rows=11,190 width=12) (actual time=229.426..350.356 rows=11,170 loops=1)

4. 1.803 345.239 ↑ 1.0 11,170 1

Subquery Scan on y (cost=224,261.33..227,059.05 rows=11,190 width=10) (actual time=229.422..345.239 rows=11,170 loops=1)

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

WindowAgg (cost=224,261.33..226,835.24 rows=22,381 width=12) (actual time=229.421..343.436 rows=19,093 loops=1)

6.          

Initplan (forWindowAgg)

7. 0.001 0.014 ↑ 1.0 1 1

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

8. 0.013 0.013 ↑ 9,040.0 1 1

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

  • Filter: ((eh_isoutofrange IS TRUE) AND (eh_nl_id = eh_nl_id))
9. 11.061 336.045 ↑ 1.2 19,093 1

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

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

11. 24.025 279.802 ↓ 5.3 118,991 1

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

12. 110.941 255.777 ↑ 1.0 216,216 1

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

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

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

14. 52.638 83.493 ↑ 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.661..83.493 rows=111,068 loops=1)

  • Recheck Cond: (eh_nl_id = 1013)
  • Heap Blocks: exact=22567
15. 30.855 30.855 ↑ 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.855..30.855 rows=111,068 loops=1)

  • Index Cond: (eh_nl_id = 1013)
16. 33.351 40.402 ↑ 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.030..40.402 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.051 7.051 ↑ 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.051..7.051 rows=105,168 loops=1)

  • Index Cond: (tmi_nl_mobile_id = 1013)