explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D3uI

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 353.260 ↓ 0.0 0 1

Insert on view_activity_data (cost=225,538.01..239,242.56 rows=3,761 width=24) (actual time=353.260..353.260 rows=0 loops=1)

2. 1.206 353.258 ↓ 0.0 0 1

Subquery Scan on z (cost=225,538.01..239,242.56 rows=3,761 width=24) (actual time=353.258..353.258 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. 6.093 352.052 ↑ 1.0 11,147 1

WindowAgg (cost=225,538.01..239,007.48 rows=11,284 width=12) (actual time=209.929..352.052 rows=11,147 loops=1)

4. 1.964 345.959 ↑ 1.0 11,147 1

Subquery Scan on y (cost=225,538.01..238,781.80 rows=11,284 width=10) (actual time=209.926..345.959 rows=11,147 loops=1)

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

WindowAgg (cost=225,538.01..238,556.11 rows=22,569 width=12) (actual time=209.926..343.995 rows=19,068 loops=1)

6. 11.298 318.449 ↑ 1.2 19,068 1

Subquery Scan on x (cost=225,538.01..227,682.13 rows=22,569 width=11) (actual time=209.907..318.449 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.604 307.151 ↓ 5.3 118,942 1

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

8. 23.675 258.547 ↓ 5.3 118,942 1

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

9. 109.504 234.872 ↑ 1.0 216,150 1

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

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

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

11. 53.717 62.361 ↑ 1.0 111,020 1

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

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

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

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

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

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

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

  • Index Cond: (tmi_nl_mobile_id = 1013)
15.          

SubPlan (forWindowAgg)

16. 0.000 15.842 ↑ 1.0 1 7,921

Result (cost=0.45..0.46 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=7,921)

17.          

Initplan (forResult)

18. 7.921 15.842 ↑ 1.0 1 7,921

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

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

Index Only Scan using eh_index_oor on enginehistory oor (cost=0.43..161.17 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) AND (eh_networktime IS NOT NULL))
  • Heap Fetches: 0