explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rq7P

Settings
# exclusive inclusive rows x rows loops node
1. 9,431.419 38,554.255 ↓ 0.0 0 1

Insert on facebook_status_score (cost=110,537.04..110,790.26 rows=12,661 width=12) (actual time=38,554.255..38,554.255 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: uq_facebook_status_score_status_id
  • Conflict Filter: (excluded.score <> facebook_status_score.score)
  • Rows Removed by Conflict Filter: 151060
  • Tuples Inserted: 0
  • Conflicting Tuples: 725231
2.          

CTE factored_streams

3. 69.922 138.991 ↓ 1.1 142,910 1

Nested Loop (cost=0.00..3,436.16 rows=135,515 width=40) (actual time=0.038..138.991 rows=142,910 loops=1)

4. 40.487 40.487 ↓ 1.1 28,582 1

Seq Scan on stream (cost=0.00..1,742.15 rows=27,103 width=4) (actual time=0.027..40.487 rows=28,582 loops=1)

  • Filter: ((disabled_at IS NULL) AND (locked_at IS NULL) AND (discriminator = 1))
  • Rows Removed by Filter: 28898
5. 28.567 28.582 ↑ 1.0 5 28,582

Materialize (cost=0.00..0.09 rows=5 width=36) (actual time=0.000..0.001 rows=5 loops=28,582)

6. 0.015 0.015 ↑ 1.0 5 1

Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=36) (actual time=0.004..0.015 rows=5 loops=1)

7.          

CTE stream_scores

8. 414.822 935.763 ↓ 142.9 28,582 1

GroupAggregate (cost=8,073.70..8,086.77 rows=200 width=8) (actual time=504.673..935.763 rows=28,582 loops=1)

  • Group Key: factored_streams.id
9. 52.237 520.941 ↓ 41.2 33,233 1

Sort (cost=8,073.70..8,075.71 rows=807 width=44) (actual time=504.474..520.941 rows=33,233 loops=1)

  • Sort Key: factored_streams.id
  • Sort Method: quicksort Memory: 4133kB
10. 135.787 468.704 ↓ 41.2 33,233 1

Hash Join (cost=2,267.28..8,034.73 rows=807 width=44) (actual time=77.251..468.704 rows=33,233 loops=1)

  • Hash Cond: ((factored_streams.id = stream_factor.stream_id) AND (factored_streams.factor_type = stream_factor.factor_type))
11. 255.930 255.930 ↓ 1.1 142,910 1

CTE Scan on factored_streams (cost=0.00..2,710.30 rows=135,515 width=40) (actual time=0.041..255.930 rows=142,910 loops=1)

12. 45.604 76.987 ↑ 1.1 66,777 1

Hash (cost=1,151.11..1,151.11 rows=74,411 width=12) (actual time=76.987..76.987 rows=66,777 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 3894kB
13. 31.383 31.383 ↑ 1.1 66,777 1

Seq Scan on stream_factor (cost=0.00..1,151.11 rows=74,411 width=12) (actual time=0.018..31.383 rows=66,777 loops=1)

14.          

CTE status_score

15. 15,416.167 28,393.000 ↓ 57.3 725,231 1

Nested Loop Left Join (cost=0.86..99,014.11 rows=12,661 width=36) (actual time=505.390..28,393.000 rows=725,231 loops=1)

  • Filter: ((facebook_status_score_1.as_at IS NULL) OR (facebook_status_score_1.as_at < 1575026670))
16. 147.797 10,075.909 ↓ 57.3 725,231 1

Nested Loop (cost=0.43..92,894.37 rows=12,661 width=28) (actual time=505.033..10,075.909 rows=725,231 loops=1)

17. 953.364 953.364 ↓ 142.9 28,582 1

CTE Scan on stream_scores (cost=0.00..4.00 rows=200 width=8) (actual time=504.677..953.364 rows=28,582 loops=1)

18. 8,974.748 8,974.748 ↑ 2.5 25 28,582

Index Scan using uq_facebook_status_stream_id_id on facebook_status (cost=0.43..463.82 rows=63 width=28) (actual time=0.272..0.314 rows=25 loops=28,582)

  • Index Cond: (stream_id = stream_scores.id)
  • Filter: (((last_polled_at IS NULL) OR (last_polled_at < 1575023130)) AND (discovered_at > 1574767530))
  • Rows Removed by Filter: 222
19. 2,900.924 2,900.924 ↑ 1.0 1 725,231

Index Scan using uq_facebook_status_score_status_id on facebook_status_score facebook_status_score_1 (cost=0.42..0.46 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=725,231)

  • Index Cond: (status_id = facebook_status.id)
20. 29,122.836 29,122.836 ↓ 57.3 725,231 1

CTE Scan on status_score (cost=0.00..253.22 rows=12,661 width=12) (actual time=505.396..29,122.836 rows=725,231 loops=1)

Planning time : 1.522 ms
Execution time : 38,564.637 ms