explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZHks : Optimization for: plan #jaod

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.078 531.453 ↓ 8.0 8 1

Unique (cost=11,408.12..11,408.18 rows=1 width=1,057) (actual time=531.374..531.453 rows=8 loops=1)

2. 0.248 531.375 ↓ 18.0 18 1

Sort (cost=11,408.12..11,408.13 rows=1 width=1,057) (actual time=531.373..531.375 rows=18 loops=1)

  • Sort Key: p.id, p.posttypeid, p.acceptedanswerid, p.parentid, p.creationdate, p.score, p.viewcount, p.body, p.owneruserid, p.ownerdisplayname, p.lasteditoruserid, p.lasteditordisplayname, p.lasteditdate, p.lastactivitydate, p.title, p.tags, p.answercount, p.commentcount, p.favoritecount, p.closeddate, p.communityowneddate
  • Sort Method: quicksort Memory: 49kB
3. 0.121 531.127 ↓ 18.0 18 1

Nested Loop (cost=7,860.03..11,408.11 rows=1 width=1,057) (actual time=103.113..531.127 rows=18 loops=1)

4. 3.437 160.026 ↓ 18.0 18 1

Hash Join (cost=7,859.74..8,440.59 rows=1 width=4) (actual time=80.012..160.026 rows=18 loops=1)

  • Hash Cond: (b.userid = u.id)
5. 82.966 88.115 ↓ 1.0 14,843 1

Bitmap Heap Scan on badges b (cost=102.01..629.73 rows=14,167 width=4) (actual time=5.369..88.115 rows=14,843 loops=1)

  • Recheck Cond: ((name ~ '^(?:Autobiographer)$'::text) OR (name ~ '^(?:Teacher)$'::text))
  • Filter: ((name ~ '^(?:Autobiographer)$'::text) OR (name ~ '^(?:Teacher)$'::text))
  • Heap Blocks: exact=454
6. 0.004 5.149 ↓ 0.0 0 1

BitmapOr (cost=102.01..102.01 rows=4,048 width=0) (actual time=5.149..5.149 rows=0 loops=1)

7. 3.605 3.605 ↓ 3.7 10,776 1

Bitmap Index Scan on badges_name_btree (cost=0.00..66.27 rows=2,931 width=0) (actual time=3.605..3.605 rows=10,776 loops=1)

  • Index Cond: (name = 'Autobiographer'::text)
8. 1.540 1.540 ↓ 3.6 4,067 1

Bitmap Index Scan on badges_name_btree (cost=0.00..28.65 rows=1,116 width=0) (actual time=1.540..1.540 rows=4,067 loops=1)

  • Index Cond: (name = 'Teacher'::text)
9. 0.015 68.474 ↓ 15.0 15 1

Hash (cost=7,757.72..7,757.72 rows=1 width=12) (actual time=68.474..68.474 rows=15 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 12.748 68.459 ↓ 15.0 15 1

Hash Join (cost=1,443.83..7,757.72 rows=1 width=12) (actual time=26.909..68.459 rows=15 loops=1)

  • Hash Cond: ((h.userid = u.id) AND (h.userdisplayname = (u.displayname)::text))
11. 33.368 33.368 ↑ 1.0 66,528 1

Seq Scan on posthistory h (cost=0.00..5,964.16 rows=66,616 width=16) (actual time=0.032..33.368 rows=66,528 loops=1)

12. 11.022 22.343 ↑ 1.0 24,913 1

Hash (cost=1,070.13..1,070.13 rows=24,913 width=14) (actual time=22.343..22.343 rows=24,913 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,384kB
13. 11.321 11.321 ↑ 1.0 24,913 1

Seq Scan on users u (cost=0.00..1,070.13 rows=24,913 width=14) (actual time=0.012..11.321 rows=24,913 loops=1)

14. 0.396 370.980 ↑ 1.0 1 18

Index Scan using posts_pkey on posts p (cost=0.29..2,967.51 rows=1 width=1,057) (actual time=20.610..20.610 rows=1 loops=18)

  • Index Cond: (id = h.postid)
  • Filter: ((SubPlan 1) >= ((SubPlan 2))::numeric)
15.          

SubPlan (for Index Scan)

16. 100.260 238.734 ↑ 1.0 1 18

Aggregate (cost=1,153.17..1,153.18 rows=1 width=32) (actual time=13.263..13.263 rows=1 loops=18)

17. 138.474 138.474 ↓ 2.5 20,545 18

Seq Scan on users u2 (cost=0.00..1,132.41 rows=8,304 width=4) (actual time=0.481..7.693 rows=20,545 loops=18)

  • Filter: (creationdate > p.creationdate)
  • Rows Removed by Filter: 4,368
18. 0.180 131.850 ↑ 1.0 1 18

Aggregate (cost=1,813.81..1,813.82 rows=1 width=8) (actual time=7.325..7.325 rows=1 loops=18)

19. 105.156 131.670 ↓ 0.0 0 18

Bitmap Heap Scan on votes v (cost=275.55..1,813.80 rows=1 width=0) (actual time=7.315..7.315 rows=0 loops=18)

  • Recheck Cond: (votetypeid = 3)
  • Filter: (postid = h.postid)
  • Rows Removed by Filter: 14,999
  • Heap Blocks: exact=23,382
20. 26.514 26.514 ↓ 1.0 14,999 18

Bitmap Index Scan on votetypeid_btree (cost=0.00..275.55 rows=14,817 width=0) (actual time=1.473..1.473 rows=14,999 loops=18)

  • Index Cond: (votetypeid = 3)