explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qJpv

Settings
# exclusive inclusive rows x rows loops node
1. 28,985.704 122,936.903 ↓ 0.0 0 1

Insert on answers__user_topic_ratings (cost=4,781,596.52..5,582,641.55 rows=13,931,218 width=61) (actual time=122,936.903..122,936.903 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: answers__user_topic_ratings_user_topic_pkey
  • Tuples Inserted: 0
  • Conflicting Tuples: 1380955
2. 234.703 93,951.199 ↑ 10.1 1,380,955 1

Subquery Scan on *SELECT* (cost=4,781,596.52..5,582,641.55 rows=13,931,218 width=61) (actual time=66,258.868..93,951.199 rows=1,380,955 loops=1)

3. 19,591.037 93,716.496 ↑ 10.1 1,380,955 1

GroupAggregate (cost=4,781,596.52..5,338,845.24 rows=13,931,218 width=65) (actual time=66,258.866..93,716.496 rows=1,380,955 loops=1)

  • Group Key: answers.author_id, questions_tag.tag_id
4. 44,116.219 74,125.459 ↓ 2.3 31,904,184 1

Sort (cost=4,781,596.52..4,816,424.56 rows=13,931,218 width=53) (actual time=66,258.850..74,125.459 rows=31,904,184 loops=1)

  • Sort Key: answers.author_id, questions_tag.tag_id
  • Sort Method: external merge Disk: 1862176kB
5. 8,023.256 30,009.240 ↓ 2.3 31,904,184 1

Hash Join (cost=1,299,847.47..2,312,242.86 rows=13,931,218 width=53) (actual time=8,216.526..30,009.240 rows=31,904,184 loops=1)

  • Hash Cond: ((answers.question_id)::text = (questions.id)::text)
6. 7,590.181 18,124.277 ↓ 1.3 9,173,651 1

Hash Join (cost=811,708.51..1,494,901.52 rows=6,857,843 width=51) (actual time=4,353.927..18,124.277 rows=9,173,651 loops=1)

  • Hash Cond: ((votes.answer_id)::text = (answers.id)::text)
  • Join Filter: ((answers.author_id)::text <> (votes.user_id)::text)
  • Rows Removed by Join Filter: 52146
7. 6,181.605 6,181.605 ↓ 1.0 14,434,992 1

Seq Scan on answers__answer_votes votes (cost=0.00..347,208.20 rows=14,421,233 width=49) (actual time=0.019..6,181.605 rows=14,434,992 loops=1)

  • Filter: (status = 0)
  • Rows Removed by Filter: 354569
8. 443.626 4,352.491 ↑ 1.1 1,281,347 1

Hash (cost=777,685.25..777,685.25 rows=1,404,821 width=67) (actual time=4,352.491..4,352.491 rows=1,281,347 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2506kB
9. 3,908.865 3,908.865 ↑ 1.1 1,281,347 1

Seq Scan on answers__answers answers (cost=0.00..777,685.25 rows=1,404,821 width=67) (actual time=0.103..3,908.865 rows=1,281,347 loops=1)

  • Filter: ((answer_id IS NULL) AND (NOT anonymous) AND (status = 0))
  • Rows Removed by Filter: 1672628
10. 513.791 3,861.707 ↓ 1.0 2,081,930 1

Hash (cost=441,281.76..441,281.76 rows=2,016,016 width=59) (actual time=3,861.707..3,861.707 rows=2,081,930 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 3309kB
11. 1,237.159 3,347.916 ↓ 1.0 2,081,930 1

Hash Join (cost=355,701.38..441,281.76 rows=2,016,016 width=59) (actual time=1,777.962..3,347.916 rows=2,081,930 loops=1)

  • Hash Cond: ((questions_tag.question_id)::text = (questions.id)::text)
12. 333.241 333.241 ↓ 1.0 2,190,063 1

Seq Scan on answers__question_tags questions_tag (cost=0.00..40,259.90 rows=2,189,890 width=36) (actual time=0.017..333.241 rows=2,190,063 loops=1)

13. 194.079 1,777.516 ↑ 1.0 913,616 1

Hash (cost=338,927.16..338,927.16 rows=913,617 width=23) (actual time=1,777.516..1,777.516 rows=913,616 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 3581kB
14. 1,583.437 1,583.437 ↑ 1.0 913,616 1

Seq Scan on answers__questions questions (cost=0.00..338,927.16 rows=913,617 width=23) (actual time=0.013..1,583.437 rows=913,616 loops=1)

  • Filter: (status = 0)
  • Rows Removed by Filter: 78797
Planning time : 419.848 ms
Execution time : 123,256.085 ms