explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M6Jt : Optimization for: Optimization for: plan #D3uI; plan #YXGm

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4,243.656 152,213.523 ↓ 0.0 0 1

Insert on view_activity_data (cost=9,918,163.89..10,369,459.42 rows=20,000 width=24) (actual time=152,213.523..152,213.523 rows=0 loops=1)

2. 838.000 147,969.867 ↓ 77.9 1,557,564 1

Subquery Scan on z (cost=9,918,163.89..10,369,459.42 rows=20,000 width=24) (actual time=80,800.266..147,969.867 rows=1,557,564 loops=1)

  • Filter: ((z.eh_mainengine IS DISTINCT FROM z.prev_main) OR (z.eh_auxengine IS DISTINCT FROM z.prev_aux))
  • Rows Removed by Filter: 2665840
3. 2,376.264 147,131.867 ↓ 211.2 4,223,404 1

WindowAgg (cost=9,918,163.89..10,368,959.42 rows=20,000 width=12) (actual time=80,800.258..147,131.867 rows=4,223,404 loops=1)

4. 770.157 144,755.603 ↓ 211.2 4,223,404 1

Subquery Scan on y (cost=9,918,163.89..10,368,559.42 rows=20,000 width=10) (actual time=80,800.256..144,755.603 rows=4,223,404 loops=1)

  • Filter: (y.collapse IS NOT TRUE)
  • Rows Removed by Filter: 2803325
5. 4,356.880 143,985.446 ↓ 175.7 7,026,729 1

WindowAgg (cost=9,918,163.89..10,368,159.43 rows=39,999 width=12) (actual time=80,800.255..143,985.446 rows=7,026,729 loops=1)

6. 3,715.432 127,754.726 ↓ 175.7 7,026,729 1

Subquery Scan on x (cost=9,918,163.89..10,349,387.49 rows=39,999 width=11) (actual time=80,800.250..127,754.726 rows=7,026,729 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: 31617423
7. 16,641.786 124,039.294 ↓ 966.1 38,644,152 1

WindowAgg (cost=9,918,163.89..10,348,787.49 rows=40,000 width=13) (actual time=80,760.138..124,039.294 rows=38,644,152 loops=1)

8. 6,998.242 107,397.508 ↓ 966.1 38,644,152 1

Unique (cost=9,918,163.89..10,347,587.49 rows=40,000 width=11) (actual time=80,760.132..107,397.508 rows=38,644,152 loops=1)

9. 88,103.152 100,399.266 ↑ 1.0 57,256,430 1

Sort (cost=9,918,163.89..10,061,305.09 rows=57,256,480 width=11) (actual time=80,760.130..100,399.266 rows=57,256,430 loops=1)

  • Sort Key: enginehistory.eh_nl_id, enginehistory.eh_networktime, enginehistory.eh_mainengine
  • Sort Method: external merge Disk: 1109984kB
10. 4,383.551 12,296.114 ↑ 1.0 57,256,430 1

Append (cost=0.00..1,282,320.08 rows=57,256,480 width=11) (actual time=0.005..12,296.114 rows=57,256,430 loops=1)

11. 2,758.836 2,758.836 ↑ 1.0 23,755,052 1

Seq Scan on enginehistory (cost=0.00..365,956.52 rows=23,755,052 width=11) (actual time=0.005..2,758.836 rows=23,755,052 loops=1)

12. 5,153.727 5,153.727 ↑ 1.0 33,501,378 1

Seq Scan on trackingmessagesindexed (cost=0.00..581,349.28 rows=33,501,428 width=11) (actual time=0.070..5,153.727 rows=33,501,378 loops=1)

  • Filter: (tmi_disconnect IS NOT NULL)
  • Rows Removed by Filter: 45
13.          

SubPlan (forWindowAgg)

14. 0.000 11,873.840 ↑ 1.0 1 2,968,460

Limit (cost=0.43..0.45 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=2,968,460)

15. 11,873.840 11,873.840 ↑ 6,612.0 1 2,968,460

Index Only Scan using eh_index_oor on enginehistory oor (cost=0.43..144.64 rows=6,612 width=4) (actual time=0.004..0.004 rows=1 loops=2,968,460)

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