explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y1kT

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 32,482.563 ↑ 1.0 20 1

Limit (cost=231,332.96..231,333.01 rows=20 width=908) (actual time=32,482.553..32,482.563 rows=20 loops=1)

2. 20.195 32,482.558 ↑ 915.9 20 1

Sort (cost=231,332.96..231,378.75 rows=18,318 width=908) (actual time=32,482.551..32,482.558 rows=20 loops=1)

  • Sort Key: ads.post_date DESC
  • Sort Method: top-N heapsort Memory: 63kB
3. 215.787 32,462.363 ↑ 2.0 9,296 1

GroupAggregate (cost=85,738.85..230,845.52 rows=18,318 width=908) (actual time=11,968.785..32,462.363 rows=9,296 loops=1)

  • Group Key: ads.post_id
4. 31.396 32,246.576 ↓ 2.8 52,121 1

Nested Loop (cost=85,738.85..230,204.39 rows=18,318 width=796) (actual time=11,968.677..32,246.576 rows=52,121 loops=1)

5. 12.706 11,996.657 ↑ 1.0 9,313 1

Merge Left Join (cost=85,738.29..85,788.58 rows=9,426 width=764) (actual time=11,964.922..11,996.657 rows=9,313 loops=1)

  • Merge Cond: ((ads.post_id)::text = (adf.post_id)::text)
6. 63.546 11,961.300 ↑ 1.0 9,313 1

Sort (cost=85,683.21..85,706.78 rows=9,426 width=748) (actual time=11,942.534..11,961.300 rows=9,313 loops=1)

  • Sort Key: ads.post_id
  • Sort Method: external merge Disk: 7080kB
7. 10.644 11,897.754 ↑ 1.0 9,313 1

Merge Join (cost=81,600.85..81,935.48 rows=9,426 width=748) (actual time=11,862.161..11,897.754 rows=9,313 loops=1)

  • Merge Cond: ((ads.domain_non_www)::text = (replace((ud.domain_name)::text, 'www.'::text, ''::text)))
8. 114.073 11,872.101 ↑ 1.1 20,734 1

Sort (cost=80,862.57..80,919.13 rows=22,624 width=748) (actual time=11,847.155..11,872.101 rows=20,734 loops=1)

  • Sort Key: ads.domain_non_www
  • Sort Method: external merge Disk: 15008kB
9. 11,758.028 11,758.028 ↑ 1.1 20,735 1

Seq Scan on wp_pidi_fb_ads ads (cost=0.00..71,722.23 rows=22,624 width=748) (actual time=0.175..11,758.028 rows=20,735 loops=1)

  • Filter: (total_comments >= 300)
  • Rows Removed by Filter: 324926
10. 11.816 15.009 ↓ 2.8 11,641 1

Sort (cost=738.28..748.50 rows=4,089 width=16) (actual time=13.699..15.009 rows=11,641 loops=1)

  • Sort Key: (replace((ud.domain_name)::text, 'www.'::text, ''::text))
  • Sort Method: quicksort Memory: 423kB
11. 3.193 3.193 ↑ 1.0 4,089 1

Seq Scan on wp_pidi_user_domain ud (cost=0.00..492.99 rows=4,089 width=16) (actual time=0.021..3.193 rows=4,089 loops=1)

  • Filter: (user_id = 42)
  • Rows Removed by Filter: 17388
12. 1.295 22.651 ↑ 1.0 602 1

Sort (cost=55.08..56.59 rows=602 width=16) (actual time=22.382..22.651 rows=602 loops=1)

  • Sort Key: adf.post_id
  • Sort Method: quicksort Memory: 62kB
13. 21.356 21.356 ↑ 1.0 602 1

Seq Scan on wp_pidi_fb_ads_favorite adf (cost=0.00..27.29 rows=602 width=16) (actual time=1.927..21.356 rows=602 loops=1)

  • Filter: (user_id = 42)
  • Rows Removed by Filter: 678
14. 20,218.523 20,218.523 ↓ 2.0 6 9,313

Index Scan using wp_pidi_fb_ads_tracking_pkey on wp_pidi_fb_ads_tracking adst (cost=0.56..15.29 rows=3 width=48) (actual time=2.115..2.171 rows=6 loops=9,313)

  • Index Cond: (((post_id)::text = (ads.post_id)::text) AND (statistical_time >= (ads.statistical_time - '6 days'::interval)) AND (statistical_time <= ads.statistical_time))
Planning time : 3.966 ms
Execution time : 32,487.245 ms