explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tP4h : sort-1M-materialized-view-with-many-columns-no-order-by-index

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 157,773.327 ↑ 1.0 1 1

Result (cost=146,240.43..146,240.44 rows=1 width=40) (actual time=157,773.311..157,773.327 rows=1 loops=1)

  • Buffers: shared hit=7537 read=68392
2.          

CTE __local_0__

3. 0.180 150,500.659 ↑ 1.0 10 1

Limit (cost=102,069.72..102,069.74 rows=10 width=72) (actual time=150,500.422..150,500.659 rows=10 loops=1)

  • Buffers: shared hit=3755 read=34212
4. 8,459.079 150,500.479 ↑ 99,997.0 10 1

Sort (cost=102,069.72..104,569.64 rows=999,970 width=72) (actual time=150,500.403..150,500.479 rows=10 loops=1)

  • Sort Key: __local_1__.mls DESC
  • Sort Method: top-N heapsort Memory: 67kB
  • Buffers: shared hit=3755 read=34212
5. 133,656.383 142,041.400 ↓ 1.0 999,999 1

WindowAgg (cost=0.00..80,460.73 rows=999,970 width=72) (actual time=1.075..142,041.400 rows=999,999 loops=1)

  • Buffers: shared hit=3752 read=34212
6. 8,385.017 8,385.017 ↓ 1.0 999,999 1

Seq Scan on premium __local_1__ (cost=0.00..47,961.70 rows=999,970 width=318) (actual time=0.598..8,385.017 rows=999,999 loops=1)

  • Buffers: shared hit=3750 read=34212
7.          

CTE __local_2__

8. 0.178 150,501.051 ↑ 1.0 1 1

Aggregate (cost=0.25..0.26 rows=1 width=32) (actual time=150,501.036..150,501.051 rows=1 loops=1)

  • Buffers: shared hit=3755 read=34212
9. 150,500.873 150,500.873 ↑ 1.0 10 1

CTE Scan on __local_0__ (cost=0.00..0.20 rows=10 width=24) (actual time=150,500.475..150,500.873 rows=10 loops=1)

  • Buffers: shared hit=3755 read=34212
10.          

Initplan (forResult)

11. 150,501.152 150,501.152 ↑ 1.0 1 1

CTE Scan on __local_2__ (cost=0.00..0.02 rows=1 width=32) (actual time=150,501.075..150,501.152 rows=1 loops=1)

  • Buffers: shared hit=3755 read=34212
12. 0.000 7,272.158 ↑ 1.0 1 1

Finalize Aggregate (cost=44,170.39..44,170.40 rows=1 width=8) (actual time=7,272.142..7,272.158 rows=1 loops=1)

  • Buffers: shared hit=3782 read=34180
13. 13.267 7,274.310 ↓ 1.5 3 1

Gather (cost=44,170.18..44,170.39 rows=2 width=8) (actual time=7,270.136..7,274.310 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=3782 read=34180
14. 3,674.014 7,261.043 ↑ 1.0 1 3

Partial Aggregate (cost=43,170.18..43,170.19 rows=1 width=8) (actual time=7,260.972..7,261.043 rows=1 loops=3)

  • Buffers: shared hit=3782 read=34180
15. 3,587.029 3,587.029 ↑ 1.2 333,333 3

Parallel Seq Scan on premium __local_1___1 (cost=0.00..42,128.54 rows=416,654 width=0) (actual time=0.073..3,587.029 rows=333,333 loops=3)

  • Buffers: shared hit=3782 read=34180