explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 78ng

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 3.922 ↑ 1.2 8 1

Limit (cost=22,204.08..22,206.79 rows=10 width=591) (actual time=3.669..3.922 rows=8 loops=1)

2. 0.109 3.919 ↑ 278.8 8 1

Result (cost=22,204.08..22,808.41 rows=2,230 width=591) (actual time=3.667..3.919 rows=8 loops=1)

3. 0.019 3.594 ↑ 278.8 8 1

Sort (cost=22,204.08..22,209.65 rows=2,230 width=747) (actual time=3.592..3.594 rows=8 loops=1)

  • Sort Key: (CASE WHEN (count(DISTINCT si.postid) >= 5) THEN 1 ELSE 0 END) DESC, (CASE WHEN (count(DISTINCT si.postid) = 0) THEN '0'::numeric ELSE round((((sum(CASE WHEN (si.pfindex >= 500) THEN 1 ELSE 0 END) * 100) / count(DISTINCT si.postid)))::numeric, 0) END) DESC, (round(avg(si.pfindex), 0)) DESC
  • Sort Method: quicksort Memory: 33kB
4. 0.037 3.575 ↑ 278.8 8 1

WindowAgg (cost=4,090.95..22,155.89 rows=2,230 width=747) (actual time=3.567..3.575 rows=8 loops=1)

5. 0.010 3.538 ↑ 278.8 8 1

Merge Join (cost=4,090.95..22,055.54 rows=2,230 width=749) (actual time=1.436..3.538 rows=8 loops=1)

  • Merge Cond: ((viewpoststattotalmonth.domainid = a.domainid) AND ((unnest(viewpoststattotalmonth.authors)) = a.authorid))
6. 2.435 3.467 ↑ 1,428.6 14 1

GroupAggregate (cost=4,090.53..4,320.85 rows=20,000 width=716) (actual time=1.421..3.467 rows=14 loops=1)

  • Group Key: viewpoststattotalmonth.domainid, (unnest(viewpoststattotalmonth.authors))
7. 0.048 1.032 ↑ 5.7 71 1

Sort (cost=4,090.53..4,091.54 rows=404 width=271) (actual time=1.019..1.032 rows=71 loops=1)

  • Sort Key: viewpoststattotalmonth.domainid, (unnest(viewpoststattotalmonth.authors))
  • Sort Method: quicksort Memory: 45kB
8. 0.053 0.984 ↑ 5.7 71 1

Nested Loop Left Join (cost=2,532.49..4,073.04 rows=404 width=271) (actual time=0.394..0.984 rows=71 loops=1)

9. 0.022 0.831 ↑ 8.1 50 1

Nested Loop Left Join (cost=2,532.05..3,672.65 rows=404 width=180) (actual time=0.382..0.831 rows=50 loops=1)

10. 0.377 0.709 ↑ 8.1 50 1

GroupAggregate (cost=2,531.62..2,595.25 rows=404 width=156) (actual time=0.372..0.709 rows=50 loops=1)

  • Group Key: viewpoststattotalmonth.domainid, viewpoststattotalmonth.postid, viewpoststattotalmonth.createdate, viewpoststattotalmonth.authors
11. 0.042 0.332 ↑ 4.1 98 1

Sort (cost=2,531.62..2,532.63 rows=404 width=112) (actual time=0.323..0.332 rows=98 loops=1)

  • Sort Key: viewpoststattotalmonth.postid, viewpoststattotalmonth.createdate, viewpoststattotalmonth.authors
  • Sort Method: quicksort Memory: 51kB
12. 0.225 0.290 ↑ 4.1 98 1

Bitmap Heap Scan on viewpoststattotalmonth (cost=39.94..2,514.13 rows=404 width=112) (actual time=0.089..0.290 rows=98 loops=1)

  • Recheck Cond: (domainid = 5)
  • Filter: ((createdate >= '2018-12-08'::date) AND (createdate <= '2019-01-07'::date) AND (active = 1))
  • Rows Removed by Filter: 838
  • Heap Blocks: exact=56
13. 0.065 0.065 ↑ 2.5 936 1

Bitmap Index Scan on viewpoststattotalmonth_new_domainid_longtail_idx (cost=0.00..39.84 rows=2,322 width=0) (actual time=0.065..0.065 rows=936 loops=1)

  • Index Cond: (domainid = 5)
14. 0.100 0.100 ↑ 1.0 1 50

Index Scan using sectionindex_pkey on sectionindex si (cost=0.43..2.65 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=50)

  • Index Cond: ((viewpoststattotalmonth.domainid = domainid) AND (viewpoststattotalmonth.postid = postid) AND (sectionid = 0))
15. 0.100 0.100 ↑ 1.0 1 50

Index Scan using post_tmp_pkey on post p (cost=0.43..0.98 rows=1 width=117) (actual time=0.002..0.002 rows=1 loops=50)

  • Index Cond: ((si.domainid = domainid) AND (si.postid = postid))
16. 0.061 0.061 ↑ 17,451.7 20 1

Index Scan using author_tmp_pkey on author a (cost=0.42..15,667.22 rows=349,034 width=41) (actual time=0.012..0.061 rows=20 loops=1)

  • Filter: ((status = 1) AND (parentid = 0))
  • Rows Removed by Filter: 16
17.          

SubPlan (forResult)

18. 0.112 0.216 ↑ 1.0 1 8

Aggregate (cost=0.18..0.19 rows=1 width=32) (actual time=0.027..0.027 rows=1 loops=8)

19. 0.008 0.104 ↑ 2.0 5 8

Subquery Scan on item (cost=0.00..0.15 rows=10 width=24) (actual time=0.010..0.013 rows=5 loops=8)

20. 0.008 0.096 ↑ 2.0 5 8

Limit (cost=0.00..0.05 rows=10 width=192) (actual time=0.010..0.012 rows=5 loops=8)

21. 0.088 0.088 ↑ 20.0 5 8

Result (cost=0.00..0.54 rows=100 width=192) (actual time=0.009..0.011 rows=5 loops=8)