explain.depesz.com

PostgreSQL's explain analyze made readable

Result: grqH

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

Insert on acquisition.facebook_status_score (cost=110,569.34..110,629.80 rows=3,023 width=12) (actual rows= loops=)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: uq_facebook_status_score_status_id
  • Conflict Filter: (excluded.score <> facebook_status_score.score)
2.          

CTE factored_streams

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..3,436.16 rows=135,515 width=40) (actual rows= loops=)

  • Output: stream.id, "*VALUES*".column1, "*VALUES*".column2
4. 0.000 0.000 ↓ 0.0

Seq Scan on acquisition.stream (cost=0.00..1,742.15 rows=27,103 width=4) (actual rows= loops=)

  • Output: stream.id, stream.discriminator, stream.publisher_id, stream.country_ids, stream.created_at, stream.modified_at, stream.disabled_at, stream.locked_at, stream.last_queued_at, stream.last_polled_at, stream.last_error, stream.error_count, stream.timezone, stream.language_ids
  • Filter: ((stream.disabled_at IS NULL) AND (stream.locked_at IS NULL) AND (stream.discriminator = 1))
5. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..0.09 rows=5 width=36) (actual rows= loops=)

  • Output: "*VALUES*".column1, "*VALUES*".column2
6. 0.000 0.000 ↓ 0.0

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

  • Output: "*VALUES*".column1, "*VALUES*".column2
7.          

CTE stream_scores

8. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=8,073.70..8,086.77 rows=200 width=8) (actual rows= loops=)

  • Output: factored_streams.id, weight_stream(array_agg(stream_factor.factor ORDER BY factored_streams.factor_type), array_agg(factored_streams.factor_weight ORDER BY factored_streams.factor_type))
  • Group Key: factored_streams.id
9. 0.000 0.000 ↓ 0.0

Sort (cost=8,073.70..8,075.71 rows=807 width=44) (actual rows= loops=)

  • Output: factored_streams.id, stream_factor.factor, factored_streams.factor_type, factored_streams.factor_weight
  • Sort Key: factored_streams.id
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,267.28..8,034.73 rows=807 width=44) (actual rows= loops=)

  • Output: factored_streams.id, stream_factor.factor, factored_streams.factor_type, factored_streams.factor_weight
  • Hash Cond: ((factored_streams.id = stream_factor.stream_id) AND (factored_streams.factor_type = stream_factor.factor_type))
11. 0.000 0.000 ↓ 0.0

CTE Scan on factored_streams (cost=0.00..2,710.30 rows=135,515 width=40) (actual rows= loops=)

  • Output: factored_streams.id, factored_streams.factor_type, factored_streams.factor_weight
12. 0.000 0.000 ↓ 0.0

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

  • Output: stream_factor.factor, stream_factor.stream_id, stream_factor.factor_type
  • Buckets: 131072 Batches: 1 Memory Usage: 3894kB
13. 0.000 0.000 ↓ 0.0

Seq Scan on acquisition.stream_factor (cost=0.00..1,151.11 rows=74,411 width=12) (actual rows= loops=)

  • Output: stream_factor.factor, stream_factor.stream_id, stream_factor.factor_type
14.          

CTE status_score

15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.86..99,046.41 rows=3,023 width=36) (actual rows= loops=)

  • Output: facebook_status.id, score_source(facebook_status.discovered_at, facebook_status.last_polled_at, 1575022121, stream_scores.weight, COALESCE(facebook_status.useless_polls, 0), facebook_status.last_metric, 60, 3600, 86400, 86400, '-60'::integer, 1000), facebook_status.discovered_at, facebook_status.last_polled_at, 1575022121, stream_scores.weight, facebook_status.useless_polls, facebook_status.last_metric
  • Filter: ((facebook_status_score_1.as_at IS NULL) OR (facebook_status_score_1.as_at < 1575022061))
16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..92,898.37 rows=12,935 width=28) (actual rows= loops=)

  • Output: facebook_status.id, facebook_status.discovered_at, facebook_status.last_polled_at, facebook_status.useless_polls, facebook_status.last_metric, stream_scores.weight
17. 0.000 0.000 ↓ 0.0

CTE Scan on stream_scores (cost=0.00..4.00 rows=200 width=8) (actual rows= loops=)

  • Output: stream_scores.id, stream_scores.weight
18. 0.000 0.000 ↓ 0.0

Index Scan using uq_facebook_status_stream_id_id on acquisition.facebook_status (cost=0.43..463.82 rows=65 width=28) (actual rows= loops=)

  • Output: facebook_status.id, facebook_status.discovered_at, facebook_status.last_polled_at, facebook_status.useless_polls, facebook_status.last_metric, facebook_status.stream_id
  • Index Cond: (facebook_status.stream_id = stream_scores.id)
  • Filter: (((facebook_status.last_polled_at IS NULL) OR (facebook_status.last_polled_at < 1575018521)) AND (facebook_status.discovered_at > 1574762921))
19. 0.000 0.000 ↓ 0.0

Index Scan using uq_facebook_status_score_status_id on acquisition.facebook_status_score facebook_status_score_1 (cost=0.42..0.46 rows=1 width=8) (actual rows= loops=)

  • Output: facebook_status_score_1.status_id, facebook_status_score_1.score, facebook_status_score_1.as_at
  • Index Cond: (facebook_status_score_1.status_id = facebook_status.id)
20. 0.000 0.000 ↓ 0.0

CTE Scan on status_score (cost=0.00..60.46 rows=3,023 width=12) (actual rows= loops=)

  • Output: status_score.status_id, status_score.score, status_score.as_at