explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NpMb : Optimization for: plan #boRd

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.080 2,062.271 ↓ 8.0 8 1

Unique (cost=14,128.05..14,128.11 rows=1 width=1,057) (actual time=2,062.191..2,062.271 rows=8 loops=1)

2. 0.255 2,062.191 ↓ 18.0 18 1

Sort (cost=14,128.05..14,128.06 rows=1 width=1,057) (actual time=2,062.189..2,062.191 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. 32.570 2,061.936 ↓ 18.0 18 1

Nested Loop (cost=1,546.12..14,128.04 rows=1 width=1,057) (actual time=82.214..2,061.936 rows=18 loops=1)

  • Join Filter: (u.id = b.userid)
  • Rows Removed by Join Filter: 222627
4. 0.149 777.481 ↓ 15.0 15 1

Nested Loop (cost=1,444.11..13,321.23 rows=1 width=1,065) (actual time=74.073..777.481 rows=15 loops=1)

5. 13.459 67.172 ↓ 15.0 15 1

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

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

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

7. 9.781 20.033 ↑ 1.0 24,913 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1384kB
8. 10.252 10.252 ↑ 1.0 24,913 1

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

9. 0.495 710.160 ↑ 1.0 1 15

Index Scan using posts_pkey on posts p (cost=0.29..5,563.50 rows=1 width=1,057) (actual time=47.344..47.344 rows=1 loops=15)

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

SubPlan (for Index Scan)

11. 80.595 199.575 ↑ 1.0 1 15

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

12. 118.980 118.980 ↓ 2.3 18,735 15

Seq Scan on users u2 (cost=0.00..1,132.41 rows=8,304 width=4) (actual time=0.808..7.932 rows=18,735 loops=15)

  • Filter: (creationdate > p.creationdate)
  • Rows Removed by Filter: 6178
13. 0.120 510.090 ↑ 1.0 1 15

Aggregate (cost=4,409.80..4,409.81 rows=1 width=8) (actual time=34.006..34.006 rows=1 loops=15)

14. 509.970 509.970 ↓ 0.0 0 15

Seq Scan on votes v (cost=0.00..4,409.80 rows=1 width=0) (actual time=33.998..33.998 rows=0 loops=15)

  • Filter: ((votetypeid = 3) AND (postid = h.postid))
  • Rows Removed by Filter: 205313
15. 1,224.750 1,251.885 ↓ 1.0 14,843 15

Bitmap Heap Scan on badges b (cost=102.01..629.73 rows=14,167 width=4) (actual time=1.894..83.459 rows=14,843 loops=15)

  • Recheck Cond: ((name ~ '^(?:Autobiographer)$'::text) OR (name ~ '^(?:Teacher)$'::text))
  • Filter: ((name ~ '^(?:Autobiographer)$'::text) OR (name ~ '^(?:Teacher)$'::text))
  • Heap Blocks: exact=6810
16. 0.030 27.135 ↓ 0.0 0 15

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

17. 18.705 18.705 ↓ 3.7 10,776 15

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

  • Index Cond: (name = 'Autobiographer'::text)
18. 8.400 8.400 ↓ 3.6 4,067 15

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

  • Index Cond: (name = 'Teacher'::text)