explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6lGM

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.006 191.548 ↓ 6.0 6 1

Nested Loop (cost=235,675.18..235,683.23 rows=1 width=601) (actual time=191.510..191.548 rows=6 loops=1)

2.          

CTE r

3. 182.314 182.314 ↓ 4.6 101 1

Index Scan using outfit_hashtag_idx on rel_outfit_hashtag (cost=0.43..235,456.02 rows=22 width=8) (actual time=2.273..182.314 rows=101 loops=1)

  • Index Cond: (id_hashtag = 55149)
  • Filter: ((deleted_at IS NULL) AND (date(created_at) > (('now'::cstring)::date - '21 days'::interval)))
4.          

CTE outfits_data

5. 0.224 191.004 ↓ 75.0 75 1

GroupAggregate (cost=190.01..190.03 rows=1 width=536) (actual time=190.683..191.004 rows=75 loops=1)

  • Group Key: r.id_hashtag, pl.country, r.id_outfit
6. 1.028 190.780 ↓ 1,444.0 1,444 1

Sort (cost=190.01..190.01 rows=1 width=528) (actual time=190.672..190.780 rows=1,444 loops=1)

  • Sort Key: r.id_hashtag, pl.country, r.id_outfit
  • Sort Method: quicksort Memory: 161kB
7. 0.251 189.752 ↓ 1,444.0 1,444 1

Nested Loop (cost=0.99..190.00 rows=1 width=528) (actual time=2.302..189.752 rows=1,444 loops=1)

8. 0.091 183.651 ↓ 75.0 75 1

Nested Loop (cost=0.56..188.73 rows=1 width=528) (actual time=2.290..183.651 rows=75 loops=1)

9. 0.108 183.410 ↓ 5.4 75 1

Nested Loop (cost=0.43..186.45 rows=14 width=40) (actual time=2.286..183.410 rows=75 loops=1)

10. 182.393 182.393 ↓ 4.6 101 1

CTE Scan on r (cost=0.00..0.44 rows=22 width=8) (actual time=2.275..182.393 rows=101 loops=1)

11. 0.909 0.909 ↑ 1.0 1 101

Index Scan using outfit_pkey on outfit o (cost=0.43..8.45 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=101)

  • Index Cond: (id_outfit = r.id_outfit)
  • Filter: ((NOT private_flag) AND (deleted_at IS NULL))
  • Rows Removed by Filter: 0
12. 0.150 0.150 ↑ 1.0 1 75

Index Scan using placement_pkey on placement pl (cost=0.14..0.15 rows=1 width=1,032) (actual time=0.002..0.002 rows=1 loops=75)

  • Index Cond: ((id_google_placement)::text = (o.place_id)::text)
  • Filter: (deleted_at IS NULL)
13. 5.850 5.850 ↓ 2.7 19 75

Index Scan using star_outfit_user_idx on post_star_history p (cost=0.43..1.20 rows=7 width=4) (actual time=0.011..0.078 rows=19 loops=75)

  • Index Cond: (id_outfit = o.id_outfit)
  • Filter: ((deleted_at IS NULL) AND (date(tap_star_datetime) > (('now'::cstring)::date - '14 days'::interval)))
14.          

CTE partitioned_data

15. 0.032 191.413 ↓ 69.0 69 1

WindowAgg (cost=28.60..28.62 rows=1 width=544) (actual time=191.383..191.413 rows=69 loops=1)

16. 0.052 191.381 ↓ 69.0 69 1

Sort (cost=28.60..28.60 rows=1 width=536) (actual time=191.379..191.381 rows=69 loops=1)

  • Sort Key: outfits_data.countr, outfits_data.gend, outfits_data.c DESC
  • Sort Method: quicksort Memory: 30kB
17. 191.116 191.329 ↓ 69.0 69 1

CTE Scan on outfits_data (cost=0.00..28.59 rows=1 width=536) (actual time=190.714..191.329 rows=69 loops=1)

  • Filter: ((NOT (SubPlan 3)) AND ((SubPlan 4) < 200))
  • Rows Removed by Filter: 6
18.          

SubPlan (for CTE Scan)

19. 0.075 0.075 ↑ 14.0 1 75

Seq Scan on hashtag_ranking_temp (cost=0.00..19.00 rows=14 width=4) (actual time=0.001..0.001 rows=1 loops=75)

  • Filter: ((gender = outfits_data.gend) AND ((region)::text = (outfits_data.countr)::text))
  • Rows Removed by Filter: 5
20. 0.069 0.138 ↑ 1.0 1 69

Aggregate (cost=19.04..19.05 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=69)

21. 0.069 0.069 ↑ 14.0 1 69

Seq Scan on hashtag_ranking_temp hashtag_ranking_temp_1 (cost=0.00..19.00 rows=14 width=0) (actual time=0.001..0.001 rows=1 loops=69)

  • Filter: (((region)::text = (outfits_data.countr)::text) AND (gender = outfits_data.gend))
  • Rows Removed by Filter: 5
22.          

CTE random_ten_data

23. 0.013 191.508 ↓ 37.0 37 1

WindowAgg (cost=0.04..0.06 rows=1 width=560) (actual time=191.489..191.508 rows=37 loops=1)

24. 0.053 191.495 ↓ 37.0 37 1

Sort (cost=0.04..0.04 rows=1 width=552) (actual time=191.487..191.495 rows=37 loops=1)

  • Sort Key: partitioned_data.countr, partitioned_data.gend, (random())
  • Sort Method: quicksort Memory: 28kB
25. 191.442 191.442 ↓ 37.0 37 1

CTE Scan on partitioned_data (cost=0.00..0.02 rows=1 width=552) (actual time=191.389..191.442 rows=37 loops=1)

  • Filter: ("row" <= 10)
  • Rows Removed by Filter: 32
26.          

CTE data

27. 191.517 191.517 ↓ 6.0 6 1

CTE Scan on random_ten_data (cost=0.00..0.02 rows=1 width=552) (actual time=191.490..191.517 rows=6 loops=1)

  • Filter: (random_rank = 1)
  • Rows Removed by Filter: 31
28. 191.524 191.524 ↓ 6.0 6 1

CTE Scan on data (cost=0.00..0.02 rows=1 width=528) (actual time=191.493..191.524 rows=6 loops=1)

29. 0.018 0.018 ↑ 1.0 1 6

Index Scan using hashtag_pkey on hashtag (cost=0.42..8.44 rows=1 width=61) (actual time=0.003..0.003 rows=1 loops=6)

  • Index Cond: (id_hashtag = data.id_hashtag)