explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YXGm : Optimization for: plan #D3uI

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 354.782 ↓ 0.0 0 1

Insert on view_activity_data (cost=225,538.01..238,960.45 rows=3,761 width=24) (actual time=354.782..354.782 rows=0 loops=1)

2. 1.183 354.781 ↓ 0.0 0 1

Subquery Scan on z (cost=225,538.01..238,960.45 rows=3,761 width=24) (actual time=354.781..354.781 rows=0 loops=1)

  • Filter: ((z.eh_networktime >= 1547142034) AND ((z.eh_mainengine IS DISTINCT FROM z.prev_main) OR (z.eh_auxengine IS DISTINCT FROM z.prev_aux)))
  • Rows Removed by Filter: 11147
3. 5.957 353.598 ↑ 1.0 11,147 1

WindowAgg (cost=225,538.01..238,725.37 rows=11,284 width=12) (actual time=216.531..353.598 rows=11,147 loops=1)

4. 1.964 347.641 ↑ 1.0 11,147 1

Subquery Scan on y (cost=225,538.01..238,499.69 rows=11,284 width=10) (actual time=216.528..347.641 rows=11,147 loops=1)

  • Filter: (y.collapse IS NOT TRUE)
  • Rows Removed by Filter: 7921
5. 13.910 345.677 ↑ 1.2 19,068 1

WindowAgg (cost=225,538.01..238,274.00 rows=22,569 width=12) (actual time=216.527..345.677 rows=19,068 loops=1)

6. 11.286 323.846 ↑ 1.2 19,068 1

Subquery Scan on x (cost=225,538.01..227,682.13 rows=22,569 width=11) (actual time=216.511..323.846 rows=19,068 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: 99874
7. 48.070 312.560 ↓ 5.3 118,942 1

WindowAgg (cost=225,538.01..227,343.58 rows=22,570 width=13) (actual time=216.507..312.560 rows=118,942 loops=1)

8. 23.680 264.490 ↓ 5.3 118,942 1

Unique (cost=225,538.01..226,666.48 rows=22,570 width=11) (actual time=216.501..264.490 rows=118,942 loops=1)

9. 107.831 240.810 ↑ 1.0 216,150 1

Sort (cost=225,538.01..226,102.24 rows=225,695 width=11) (actual time=216.500..240.810 rows=216,150 loops=1)

  • Sort Key: enginehistory.eh_networktime, enginehistory.eh_mainengine
  • Sort Method: external merge Disk: 4224kB
10. 18.053 132.979 ↑ 1.0 216,150 1

Append (cost=1,353.62..202,987.44 rows=225,695 width=11) (actual time=17.482..132.979 rows=216,150 loops=1)

11. 62.297 76.255 ↑ 1.0 111,020 1

Bitmap Heap Scan on enginehistory (cost=1,353.62..90,264.40 rows=114,024 width=11) (actual time=17.481..76.255 rows=111,020 loops=1)

  • Recheck Cond: (eh_nl_id = 1013)
  • Heap Blocks: exact=22562
12. 13.958 13.958 ↑ 1.0 111,020 1

Bitmap Index Scan on enginehistory_pkey (cost=0.00..1,325.12 rows=114,024 width=0) (actual time=13.958..13.958 rows=111,020 loops=1)

  • Index Cond: (eh_nl_id = 1013)
13. 32.334 38.671 ↑ 1.1 105,130 1

Bitmap Heap Scan on trackingmessagesindexed (cost=1,326.51..111,606.33 rows=111,671 width=11) (actual time=9.920..38.671 rows=105,130 loops=1)

  • Recheck Cond: (tmi_nl_mobile_id = 1013)
  • Filter: (tmi_disconnect IS NOT NULL)
  • Heap Blocks: exact=13498
14. 6.337 6.337 ↑ 1.1 105,131 1

Bitmap Index Scan on tmi_indextrackingmobile (cost=0.00..1,298.59 rows=111,671 width=0) (actual time=6.337..6.337 rows=105,131 loops=1)

  • Index Cond: (tmi_nl_mobile_id = 1013)
15.          

SubPlan (forWindowAgg)

16. 0.000 7.921 ↑ 1.0 1 7,921

Limit (cost=0.43..0.45 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=7,921)

17. 7.921 7.921 ↑ 6,612.0 1 7,921

Index Only Scan using eh_index_oor on enginehistory oor (cost=0.43..144.64 rows=6,612 width=4) (actual time=0.001..0.001 rows=1 loops=7,921)

  • Index Cond: (eh_nl_id = x.eh_nl_id)
  • Heap Fetches: 0