explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VSnj : Optimization for: Optimization for: plan #WZf; plan #oamF

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2,274.032 101,620.815 ↑ 1.0 106,536 1

WindowAgg (cost=253,994.22..312,844.77 rows=107,001 width=612) (actual time=7,186.782..101,620.815 rows=106,536 loops=1)

2. 5,575.257 99,346.783 ↑ 1.0 106,536 1

WindowAgg (cost=253,994.22..295,457.11 rows=107,001 width=420) (actual time=7,185.523..99,346.783 rows=106,536 loops=1)

3. 86,355.311 93,771.526 ↑ 1.0 106,536 1

WindowAgg (cost=253,994.22..278,604.45 rows=107,001 width=412) (actual time=7,183.915..93,771.526 rows=106,536 loops=1)

4. 943.906 7,416.215 ↑ 1.0 106,536 1

Sort (cost=253,994.22..254,261.72 rows=107,001 width=144) (actual time=7,181.953..7,416.215 rows=106,536 loops=1)

  • Sort Key: fbpostmetrics.page_id, fbpostmetrics.type, fbpostmetrics.weekday, fbpostmetrics.subdomain, (CASE WHEN ((focus_calc.avg_eng_score >= '-1'::double precision) AND (focus_calc.avg_click_score >= '-1'::double precision) AND (focus_calc.avg_eng_score <= '2'::double precision) AND (focus_calc.avg_click_score <= '2'::double precision)) THEN 'Click/Engagement'::text ELSE CASE WHEN ((focus_calc.avg_eng_score >= '-1'::double precision) AND (focus_calc.avg_eng_score <= '2'::double precision)) THEN 'Engagement'::text ELSE CASE WHEN ((focus_calc.avg_click_score >= '-1'::double precision) AND (focus_calc.avg_click_score <= '2'::double precision)) THEN 'Click'::text ELSE 'Undefined'::text END END END), fbpostmetrics.published_at
  • Sort Method: external merge Disk: 15128kB
5. 187.878 6,472.309 ↑ 1.0 106,536 1

Hash Right Join (cost=218,349.17..237,373.25 rows=107,001 width=144) (actual time=5,546.898..6,472.309 rows=106,536 loops=1)

  • Hash Cond: (focus_calc.article_id = fbpostmetrics.article_id)
6. 38.318 5,879.637 ↓ 1.9 105,225 1

Subquery Scan on focus_calc (cost=142,973.78..152,121.90 rows=56,047 width=82) (actual time=5,141.857..5,879.637 rows=105,225 loops=1)

7. 360.085 5,841.319 ↓ 1.9 105,225 1

GroupAggregate (cost=142,973.78..150,440.49 rows=56,047 width=66) (actual time=5,141.843..5,841.319 rows=105,225 loops=1)

  • Group Key: sub.article_id
8. 2,080.235 5,481.234 ↑ 1.0 165,021 1

Sort (cost=142,973.78..143,387.91 rows=165,650 width=186) (actual time=5,141.771..5,481.234 rows=165,021 loops=1)

  • Sort Key: sub.article_id
  • Sort Method: external merge Disk: 18520kB
9. 21.602 3,400.999 ↑ 1.0 165,021 1

Subquery Scan on sub (cost=94,276.02..113,325.77 rows=165,650 width=186) (actual time=832.597..3,400.999 rows=165,021 loops=1)

10. 2,344.618 3,379.397 ↑ 1.0 165,021 1

WindowAgg (cost=94,276.02..111,669.27 rows=165,650 width=234) (actual time=832.595..3,379.397 rows=165,021 loops=1)

11. 790.196 1,034.779 ↑ 1.0 165,021 1

Sort (cost=94,276.02..94,690.14 rows=165,650 width=106) (actual time=832.449..1,034.779 rows=165,021 loops=1)

  • Sort Key: fbpostmetrics_1.page_id, fbpostmetrics_1.type, fbpostmetrics_1.weekday, fbpostmetrics_1.subdomain, fbpostmetrics_1.published_at
  • Sort Method: external merge Disk: 19672kB
12. 244.583 244.583 ↑ 1.0 165,021 1

Seq Scan on fbpostmetrics fbpostmetrics_1 (cost=0.00..70,287.50 rows=165,650 width=106) (actual time=0.009..244.583 rows=165,021 loops=1)

13. 57.142 404.794 ↑ 1.0 106,536 1

Hash (cost=71,529.88..71,529.88 rows=107,001 width=162) (actual time=404.794..404.794 rows=106,536 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 6039kB
14. 347.652 347.652 ↑ 1.0 106,536 1

Seq Scan on fbpostmetrics (cost=0.00..71,529.88 rows=107,001 width=162) (actual time=0.025..347.652 rows=106,536 loops=1)

  • Filter: (published_at > (now() - '1 year 10 mons'::interval))
  • Rows Removed by Filter: 58485
Planning time : 1.722 ms
Execution time : 101,644.891 ms