explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7wKi

Settings
# exclusive inclusive rows x rows loops node
1. 4,744.031 18,153.797 ↓ 0.0 0 1

Update on forum_post_history_backup (cost=1,298,377.60..1,810,460.09 rows=2,194,859 width=391) (actual time=18,153.797..18,153.797 rows=0 loops=1)

2.          

CTE subquery

3. 367.425 6,191.766 ↑ 8.7 252,635 1

WindowAgg (cost=844,364.63..888,261.81 rows=2,194,859 width=24) (actual time=5,722.385..6,191.766 rows=252,635 loops=1)

4. 380.093 5,824.341 ↑ 8.7 252,635 1

Sort (cost=844,364.63..849,851.78 rows=2,194,859 width=16) (actual time=5,722.379..5,824.341 rows=252,635 loops=1)

  • Sort Key: b.post_id, b.change_time
  • Sort Method: external sort Disk: 6424kB
5. 2,538.409 5,444.248 ↑ 8.7 252,635 1

Hash Join (cost=119,953.63..538,157.46 rows=2,194,859 width=16) (actual time=4,951.630..5,444.248 rows=252,635 loops=1)

  • Hash Cond: (b.post_id = forum_post_history_backup_1.post_id)
6. 2,454.198 2,454.198 ↑ 1.6 5,505,024 1

Seq Scan on forum_post_history_backup b (cost=0.00..308,035.89 rows=8,726,189 width=16) (actual time=0.004..2,454.198 rows=5,505,024 loops=1)

7. 48.469 451.641 ↑ 1.0 100,000 1

Hash (cost=118,703.63..118,703.63 rows=100,000 width=4) (actual time=451.641..451.641 rows=100,000 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 2856kB
8. 63.480 403.172 ↑ 1.0 100,000 1

Limit (cost=0.43..117,703.63 rows=100,000 width=4) (actual time=0.036..403.172 rows=100,000 loops=1)

9. 339.692 339.692 ↑ 4.0 100,000 1

Unique (cost=0.43..467,957.74 rows=397,574 width=4) (actual time=0.035..339.692 rows=100,000 loops=1)

  • -> Index Only Scan Backward using forum_post_history_backup_post_id_idx on forum_post_history_backup forum_post_history_backup_1 (cost=0.43..446142
  • Heap Fetches: 188657
10. 2,213.016 13,409.766 ↑ 8.7 252,635 1

Merge Join (cost=410,115.79..922,198.27 rows=2,194,859 width=391) (actual time=12,458.590..13,409.766 rows=252,635 loops=1)

  • Merge Cond: (forum_post_history_backup.id = subquery.id)
11. 4,099.608 4,099.608 ↑ 1.6 5,505,024 1

Index Scan using forum_post_history_backup_pkey on forum_post_history_backup (cost=0.43..446,370.27 rows=8,726,189 width=351) (actual time=0.088..4,099.608 rows=5,505,024 loops=1)

12. 180.178 7,097.142 ↑ 8.7 252,635 1

Materialize (cost=410,115.35..421,089.65 rows=2,194,859 width=48) (actual time=6,798.123..7,097.142 rows=252,635 loops=1)

13. 397.539 6,916.964 ↑ 8.7 252,635 1

Sort (cost=410,115.35..415,602.50 rows=2,194,859 width=48) (actual time=6,798.120..6,916.964 rows=252,635 loops=1)

  • Sort Key: subquery.id
  • Sort Method: external sort Disk: 14328kB
14. 6,519.425 6,519.425 ↑ 8.7 252,635 1

CTE Scan on subquery (cost=0.00..43,897.18 rows=2,194,859 width=48) (actual time=5,722.390..6,519.425 rows=252,635 loops=1)