explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mfqT

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 919.682 ↓ 0.0 0 1

Insert on view_activity_data (cost=269,232.03..411,689.48 rows=3,761 width=24) (actual time=919.682..919.682 rows=0 loops=1)

2. 1.181 919.682 ↓ 0.0 0 1

Subquery Scan on z (cost=269,232.03..411,651.87 rows=3,761 width=24) (actual time=919.682..919.682 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.          

CTE oor_min_time

4. 281.905 456.190 ↑ 1.0 272 1

HashAggregate (cost=43,691.29..43,694.03 rows=274 width=8) (actual time=456.159..456.190 rows=272 loops=1)

  • Group Key: enginehistory_1.eh_nl_id
5. 174.285 174.285 ↓ 1.0 1,826,741 1

Index Only Scan using eh_index_oor on enginehistory enginehistory_1 (cost=0.43..34,632.70 rows=1,811,718 width=8) (actual time=0.016..174.285 rows=1,826,741 loops=1)

  • Heap Fetches: 0
6. 5.978 918.501 ↑ 1.0 11,147 1

WindowAgg (cost=225,538.01..367,722.77 rows=11,284 width=12) (actual time=200.580..918.501 rows=11,147 loops=1)

7. 1.973 912.523 ↑ 1.0 11,147 1

Subquery Scan on y (cost=225,538.01..367,497.09 rows=11,284 width=10) (actual time=200.577..912.523 rows=11,147 loops=1)

  • Filter: (y.collapse IS NOT TRUE)
  • Rows Removed by Filter: 7921
8. 7.664 910.550 ↑ 1.2 19,068 1

WindowAgg (cost=225,538.01..367,271.40 rows=22,569 width=12) (actual time=200.577..910.550 rows=19,068 loops=1)

9. 11.380 308.811 ↑ 1.2 19,068 1

Subquery Scan on x (cost=225,538.01..227,682.13 rows=22,569 width=11) (actual time=200.560..308.811 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
10. 48.250 297.431 ↓ 5.3 118,942 1

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

11. 23.615 249.181 ↓ 5.3 118,942 1

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

12. 107.157 225.566 ↑ 1.0 216,150 1

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

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

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

14. 53.426 60.791 ↑ 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.066..60.791 rows=111,020 loops=1)

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

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

  • Index Cond: (eh_nl_id = 1013)
16. 33.165 39.360 ↑ 1.1 105,130 1

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

  • Recheck Cond: (tmi_nl_mobile_id = 1013)
  • Filter: (tmi_disconnect IS NOT NULL)
  • Heap Blocks: exact=13498
17. 6.195 6.195 ↑ 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.195..6.195 rows=105,131 loops=1)

  • Index Cond: (tmi_nl_mobile_id = 1013)
18.          

SubPlan (forWindowAgg)

19. 594.075 594.075 ↑ 1.0 1 7,921

CTE Scan on oor_min_time (cost=0.00..6.17 rows=1 width=4) (actual time=0.064..0.075 rows=1 loops=7,921)

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