explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1WTB

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 49,439.451 ↑ 1.0 20 1

Limit (cost=1,177,897.24..1,177,897.29 rows=20 width=911) (actual time=49,439.443..49,439.451 rows=20 loops=1)

2. 244.706 49,439.445 ↑ 12,295.9 40 1

Sort (cost=1,177,897.19..1,179,126.79 rows=491,837 width=911) (actual time=49,439.435..49,439.445 rows=40 loops=1)

  • Sort Key: ads.post_date DESC
  • Sort Method: top-N heapsort Memory: 65kB
3. 4,073.097 49,194.739 ↑ 1.6 301,437 1

GroupAggregate (cost=1,142,676.92..1,162,350.40 rows=491,837 width=911) (actual time=41,633.815..49,194.739 rows=301,437 loops=1)

  • Group Key: ads.post_id, ai.publish_time
4. 37,305.044 45,121.642 ↓ 3.1 1,542,470 1

Sort (cost=1,142,676.92..1,143,906.51 rows=491,837 width=799) (actual time=41,633.740..45,121.642 rows=1,542,470 loops=1)

  • Sort Key: ads.post_id, ai.publish_time
  • Sort Method: external merge Disk: 1159456kB
5. 318.696 7,816.598 ↓ 3.1 1,542,470 1

Hash Left Join (cost=65,056.27..749,875.09 rows=491,837 width=799) (actual time=357.434..7,816.598 rows=1,542,470 loops=1)

  • Hash Cond: ((ads.post_id)::text = (adf.post_id)::text)
6. 344.315 7,497.883 ↓ 3.1 1,542,470 1

Hash Left Join (cost=65,046.93..747,406.54 rows=491,837 width=783) (actual time=357.410..7,497.883 rows=1,542,470 loops=1)

  • Hash Cond: ((ads.post_id)::text = (ai.post_id)::text)
7. 4,904.890 7,153.528 ↓ 3.1 1,542,470 1

Hash Join (cost=65,042.19..745,555.34 rows=491,837 width=775) (actual time=357.354..7,153.528 rows=1,542,470 loops=1)

  • Hash Cond: ((adst.post_id)::text = (ads.post_id)::text)
  • Join Filter: ((adst.statistical_time <= ads.statistical_time) AND (adst.statistical_time >= (ads.statistical_time - '6 days'::interval)))
  • Rows Removed by Join Filter: 2887569
8. 1,894.314 1,894.314 ↓ 1.0 4,430,039 1

Seq Scan on wp_pidi_fb_ads_tracking adst (cost=0.00..481,311.33 rows=4,426,533 width=48) (actual time=0.095..1,894.314 rows=4,430,039 loops=1)

9. 296.062 354.324 ↓ 1.0 304,965 1

Hash (cost=34,072.75..34,072.75 rows=291,475 width=743) (actual time=354.324..354.324 rows=304,965 loops=1)

  • Buckets: 8192 Batches: 64 Memory Usage: 3706kB
10. 58.262 58.262 ↓ 1.0 304,965 1

Seq Scan on wp_pidi_fb_ads ads (cost=0.00..34,072.75 rows=291,475 width=743) (actual time=0.084..58.262 rows=304,965 loops=1)

11. 0.022 0.040 ↑ 1.2 102 1

Hash (cost=3.22..3.22 rows=122 width=24) (actual time=0.040..0.040 rows=102 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
12. 0.018 0.018 ↑ 1.2 102 1

Seq Scan on wp_pidi_ai_picked_ads ai (cost=0.00..3.22 rows=122 width=24) (actual time=0.005..0.018 rows=102 loops=1)

13. 0.005 0.019 ↓ 2.0 4 1

Hash (cost=9.32..9.32 rows=2 width=16) (actual time=0.019..0.019 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.004 0.014 ↓ 2.0 4 1

Bitmap Heap Scan on wp_pidi_fb_ads_favorite adf (cost=4.29..9.32 rows=2 width=16) (actual time=0.014..0.014 rows=4 loops=1)

  • Recheck Cond: (user_id = 43)
  • Heap Blocks: exact=1
15. 0.010 0.010 ↓ 2.0 4 1

Bitmap Index Scan on wp_pidi_fb_ads_favorite_pkey (cost=0.00..4.29 rows=2 width=0) (actual time=0.010..0.010 rows=4 loops=1)

  • Index Cond: (user_id = 43)
Planning time : 0.697 ms
Execution time : 49,613.442 ms