explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Borq : Optimization for: plan #C6yd

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 1,816.381 ↑ 1.0 20 1

Limit (cost=396,383.60..396,383.65 rows=20 width=917) (actual time=1,816.374..1,816.381 rows=20 loops=1)

2. 0.348 1,816.379 ↑ 2,392.3 20 1

Sort (cost=396,383.60..396,503.22 rows=47,847 width=917) (actual time=1,816.373..1,816.379 rows=20 loops=1)

  • Sort Key: ads.post_date DESC
  • Sort Method: top-N heapsort Memory: 52kB
3. 545.308 1,816.031 ↑ 310.7 154 1

GroupAggregate (cost=392,239.59..395,110.41 rows=47,847 width=917) (actual time=1,071.130..1,816.031 rows=154 loops=1)

  • Group Key: ads.post_id, ai.publish_time
  • Filter: (fn_division_change(((json_agg(adst.total_comments ORDER BY adst.statistical_time) ->> (json_array_length(json_agg(adst.total_comments ORDER BY adst.statistical_time)) - 1)))::integer, ((json_agg(adst.total_comments ORDER BY adst.statistical_time) ->> (json_array_length(json_agg(adst.total_comments ORDER BY adst.statistical_time)) - 2)))::integer, 2, false) > '10'::double precision)
  • Rows Removed by Filter: 20598
4. 734.751 1,270.723 ↓ 2.3 109,612 1

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

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

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

6. 20.747 187.403 ↑ 1.0 21,004 1

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

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

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

8. 111.495 111.495 ↑ 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.142..111.495 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.005 0.015 ↓ 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.014..0.015 rows=4 loops=1)

  • Recheck Cond: (user_id = 68)
  • Heap Blocks: exact=3
12. 0.010 0.010 ↓ 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.010..0.010 rows=6 loops=1)

  • Index Cond: (user_id = 68)
13. 315.060 315.060 ↓ 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.015 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))