explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C6yd

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 1,624.534 ↑ 1.0 20 1

Limit (cost=395,426.66..395,426.71 rows=20 width=917) (actual time=1,624.524..1,624.534 rows=20 loops=1)

2. 18.511 1,624.530 ↑ 2,392.3 20 1

Sort (cost=395,426.66..395,546.28 rows=47,847 width=917) (actual time=1,624.523..1,624.530 rows=20 loops=1)

  • Sort Key: ads.post_date DESC
  • Sort Method: top-N heapsort Memory: 54kB
3. 300.171 1,606.019 ↑ 2.3 20,752 1

GroupAggregate (cost=392,239.59..394,153.47 rows=47,847 width=917) (actual time=1,053.823..1,606.019 rows=20,752 loops=1)

  • Group Key: ads.post_id, ai.publish_time
4. 754.715 1,305.848 ↓ 2.3 109,612 1

Sort (cost=392,239.59..392,359.21 rows=47,847 width=805) (actual time=1,053.754..1,305.848 rows=109,612 loops=1)

  • Sort Key: ads.post_id, ai.publish_time
  • Sort Method: external merge Disk: 85584kB
5. 24.207 551.133 ↓ 2.3 109,612 1

Nested Loop (cost=5.00..371,510.41 rows=47,847 width=805) (actual time=0.203..551.133 rows=109,612 loops=1)

6. 20.431 190.862 ↑ 1.0 21,004 1

Nested Loop Left Join (cost=4.44..43,906.44 rows=21,447 width=773) (actual time=0.181..190.862 rows=21,004 loops=1)

  • Join Filter: ((ads.post_id)::text = (adf.post_id)::text)
  • Rows Removed by Join Filter: 84012
7. 15.714 170.431 ↑ 1.0 21,004 1

Nested Loop Left Join (cost=0.14..42,929.30 rows=21,447 width=757) (actual time=0.149..170.431 rows=21,004 loops=1)

8. 112.709 112.709 ↑ 1.0 21,004 1

Seq Scan on wp_pidi_fb_ads ads (cost=0.00..39,267.31 rows=21,447 width=749) (actual time=0.138..112.709 rows=21,004 loops=1)

  • Filter: (total_comments >= 300)
  • Rows Removed by Filter: 331181
9. 42.008 42.008 ↓ 0.0 0 21,004

Index Scan using wp_pidi_ai_picked_ads_pkey on wp_pidi_ai_picked_ads ai (cost=0.14..0.16 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=21,004)

  • Index Cond: ((ads.post_id)::text = (post_id)::text)
10. 0.000 0.000 ↓ 1.3 4 21,004

Materialize (cost=4.30..12.03 rows=3 width=16) (actual time=0.000..0.000 rows=4 loops=21,004)

11. 0.007 0.019 ↓ 1.3 4 1

Bitmap Heap Scan on wp_pidi_fb_ads_favorite adf (cost=4.30..12.01 rows=3 width=16) (actual time=0.018..0.019 rows=4 loops=1)

  • Recheck Cond: (user_id = 68)
  • Heap Blocks: exact=3
12. 0.012 0.012 ↓ 2.0 6 1

Bitmap Index Scan on wp_pidi_fb_ads_favorite_pkey (cost=0.00..4.30 rows=3 width=0) (actual time=0.012..0.012 rows=6 loops=1)

  • Index Cond: (user_id = 68)
13. 336.064 336.064 ↓ 1.7 5 21,004

Index Scan using wp_pidi_fb_ads_tracking_pkey on wp_pidi_fb_ads_tracking adst (cost=0.56..15.25 rows=3 width=48) (actual time=0.010..0.016 rows=5 loops=21,004)

  • Index Cond: (((post_id)::text = (ads.post_id)::text) AND (statistical_time >= (ads.statistical_time - '6 days'::interval)) AND (statistical_time <= ads.statistical_time))