explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AkoE : Optimization for: plan #6lGM

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.008 201.339 ↓ 6.0 6 1

Nested Loop (cost=235,804.50..235,812.55 rows=1 width=601) (actual time=201.301..201.339 rows=6 loops=1)

2.          

CTE r

3. 183.964 183.964 ↓ 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=1.716..183.964 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.181 201.104 ↓ 69.0 69 1

GroupAggregate (cost=347.90..347.92 rows=1 width=536) (actual time=200.824..201.104 rows=69 loops=1)

  • Group Key: r.id_hashtag, pl.country, r.id_outfit
6. 0.933 200.923 ↓ 1,329.0 1,329 1

Sort (cost=347.90..347.90 rows=1 width=528) (actual time=200.816..200.923 rows=1,329 loops=1)

  • Sort Key: r.id_hashtag, pl.country, r.id_outfit
  • Sort Method: quicksort Memory: 152kB
7. 0.212 199.990 ↓ 1,329.0 1,329 1

Nested Loop (cost=0.99..347.89 rows=1 width=528) (actual time=1.755..199.990 rows=1,329 loops=1)

8. 0.262 191.114 ↓ 288.8 1,444 1

Nested Loop (cost=0.85..204.24 rows=5 width=44) (actual time=1.739..191.114 rows=1,444 loops=1)

9. 0.067 185.002 ↓ 5.4 75 1

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

10. 184.026 184.026 ↓ 4.6 101 1

CTE Scan on r (cost=0.00..0.44 rows=22 width=8) (actual time=1.718..184.026 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. 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.010..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)))
13. 4.562 8.664 ↑ 1.0 1 1,444

Index Scan using placement_pkey on placement pl (cost=0.14..28.72 rows=1 width=1,032) (actual time=0.005..0.006 rows=1 loops=1,444)

  • Index Cond: ((id_google_placement)::text = (o.place_id)::text)
  • Filter: ((deleted_at IS NULL) AND (NOT (SubPlan 2)) AND ((SubPlan 3) < 200))
  • Rows Removed by Filter: 0
14.          

SubPlan (for Index Scan)

15. 1.444 1.444 ↑ 14.0 1 1,444

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

  • Filter: ((gender = 0) AND ((region)::text = (pl.country)::text))
  • Rows Removed by Filter: 5
16. 1.329 2.658 ↑ 1.0 1 1,329

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

17. 1.329 1.329 ↑ 14.0 1 1,329

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=1,329)

  • Filter: (((region)::text = (pl.country)::text) AND (gender = 0))
  • Rows Removed by Filter: 5
18.          

CTE partitioned_data

19. 0.027 201.218 ↓ 69.0 69 1

WindowAgg (cost=0.03..0.06 rows=1 width=544) (actual time=201.182..201.218 rows=69 loops=1)

20. 0.059 201.191 ↓ 69.0 69 1

Sort (cost=0.03..0.04 rows=1 width=536) (actual time=201.178..201.191 rows=69 loops=1)

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

CTE Scan on outfits_data (cost=0.00..0.02 rows=1 width=536) (actual time=200.826..201.132 rows=69 loops=1)

22.          

CTE random_ten_data

23. 0.019 201.300 ↓ 37.0 37 1

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

24. 0.042 201.281 ↓ 37.0 37 1

Sort (cost=0.04..0.04 rows=1 width=552) (actual time=201.278..201.281 rows=37 loops=1)

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

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

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

CTE data

27. 201.311 201.311 ↓ 6.0 6 1

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

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

CTE Scan on data (cost=0.00..0.02 rows=1 width=528) (actual time=201.284..201.313 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)