explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oP57

Settings
# exclusive inclusive rows x rows loops node
1. 2.184 356,418.026 ↑ 550.7 9 1

GroupAggregate (cost=2,663,584.80..2,665,852.13 rows=4,956 width=159) (actual time=356,416.914..356,418.026 rows=9 loops=1)

  • Group Key: ci.id, ci.name, ci.address
2.          

CTE intervals

3. 0.080 0.080 ↑ 166.7 6 1

Function Scan on generate_series start_date (cost=0.00..10.00 rows=1,000 width=8) (actual time=0.035..0.080 rows=6 loops=1)

4.          

CTE location_intervals

5. 0.142 9.306 ↑ 33.3 6 1

HashAggregate (cost=2,880.53..2,884.03 rows=200 width=54) (actual time=9.241..9.306 rows=6 loops=1)

  • Group Key: intervals_1.start_date, l.id
6. 0.139 9.164 ↑ 574.0 6 1

Hash Right Join (cost=52.56..2,820.26 rows=3,444 width=50) (actual time=9.122..9.164 rows=6 loops=1)

  • Hash Cond: ((f.rtx_location_id)::text = (l.rtx_id)::text)
  • Join Filter: ((((f.date_id)::text)::date >= intervals_1.start_date) AND (((f.date_id)::text)::date < (intervals_1.start_date + '1 mon'::interval)))
  • Rows Removed by Join Filter: 24
7. 0.436 6.596 ↑ 7.8 4 1

Hash Semi Join (cost=1.63..1,568.00 rows=31 width=41) (actual time=5.701..6.596 rows=4 loops=1)

  • Hash Cond: ((f.rtx_source_id)::text = (sources.rtx_source_id)::text)
8. 5.565 5.565 ↑ 19.4 44 1

Index Scan using index_fact_reviews_sync_on_rtx_location_id on fact_reviews_sync f (cost=0.56..1,564.34 rows=855 width=66) (actual time=3.312..5.565 rows=44 loops=1)

  • Index Cond: ((rtx_location_id)::text = '59badda0071ab23433727286'::text)
9. 0.049 0.595 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=32) (actual time=0.595..0.595 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.546 0.546 ↑ 1.0 3 1

Seq Scan on sources (cost=0.00..1.03 rows=3 width=32) (actual time=0.523..0.546 rows=3 loops=1)

11. 0.071 2.429 ↑ 166.7 6 1

Hash (cost=38.43..38.43 rows=1,000 width=63) (actual time=2.429..2.429 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.121 2.358 ↑ 166.7 6 1

Nested Loop (cost=0.41..38.43 rows=1,000 width=63) (actual time=2.118..2.358 rows=6 loops=1)

13. 2.093 2.093 ↑ 1.0 1 1

Index Scan using index_locations_on_rtx_id on locations l (cost=0.41..8.43 rows=1 width=55) (actual time=2.085..2.093 rows=1 loops=1)

  • Index Cond: ((rtx_id)::text = '59badda0071ab23433727286'::text)
14. 0.144 0.144 ↑ 166.7 6 1

CTE Scan on intervals intervals_1 (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.008..0.144 rows=6 loops=1)

15. 0.750 356,415.842 ↑ 917.8 54 1

Sort (cost=2,660,690.77..2,660,814.67 rows=49,559 width=143) (actual time=356,415.500..356,415.842 rows=54 loops=1)

  • Sort Key: ci.id, ci.name, ci.address
  • Sort Method: quicksort Memory: 39kB
16. 0.650 356,415.092 ↑ 917.8 54 1

Subquery Scan on ci (cost=2,650,912.40..2,653,266.45 rows=49,559 width=143) (actual time=356,381.558..356,415.092 rows=54 loops=1)

17. 19.054 356,414.442 ↑ 917.8 54 1

GroupAggregate (cost=2,650,912.40..2,652,770.86 rows=49,559 width=151) (actual time=356,381.542..356,414.442 rows=54 loops=1)

  • Group Key: intervals.start_date, c.id, li.name, li.total_reviews, li.avg_rating
18. 36.520 356,395.388 ↑ 19.6 2,529 1

Sort (cost=2,650,912.40..2,651,036.29 rows=49,559 width=147) (actual time=356,379.463..356,395.388 rows=2,529 loops=1)

  • Sort Key: intervals.start_date, c.id, li.name, li.total_reviews, li.avg_rating
  • Sort Method: quicksort Memory: 767kB
19. 172,896.426 356,358.868 ↑ 19.6 2,529 1

Hash Right Join (cost=997,067.49..2,643,320.07 rows=49,559 width=147) (actual time=162,716.406..356,358.868 rows=2,529 loops=1)

  • Hash Cond: (r.url_id = u.id)
  • Join Filter: ((r.published_at >= intervals.start_date) AND (r.published_at < (intervals.start_date + '1 mon'::interval)))
  • Rows Removed by Join Filter: 108686
20. 181,908.882 181,908.882 ↑ 1.0 27,021,452 1

Seq Scan on reviews r (cost=0.00..1,537,097.16 rows=27,026,616 width=28) (actual time=1.159..181,908.882 rows=27,021,452 loops=1)

  • Filter: (deleted_at IS NULL)
21. 0.867 1,553.560 ↑ 17.6 108 1

Hash (cost=997,043.72..997,043.72 rows=1,902 width=139) (actual time=1,553.560..1,553.560 rows=108 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 32kB
22. 5.184 1,552.693 ↑ 17.6 108 1

Nested Loop (cost=3,063.82..997,043.72 rows=1,902 width=139) (actual time=1,337.606..1,552.693 rows=108 loops=1)

  • Join Filter: (li.start_date = intervals.start_date)
  • Rows Removed by Join Filter: 540
23. 0.091 0.091 ↑ 166.7 6 1

CTE Scan on intervals (cost=0.00..20.00 rows=1,000 width=8) (actual time=0.051..0.091 rows=6 loops=1)

24. 5.067 1,547.418 ↑ 3.5 108 6

Materialize (cost=3,063.82..991,324.67 rows=380 width=139) (actual time=182.775..257.903 rows=108 loops=6)

25. 2.923 1,542.351 ↑ 3.5 108 1

Nested Loop (cost=3,063.82..991,322.77 rows=380 width=139) (actual time=1,096.601..1,542.351 rows=108 loops=1)

26. 2.206 1,530.140 ↑ 4.0 108 1

Nested Loop (cost=3,063.53..989,170.08 rows=428 width=76) (actual time=1,095.238..1,530.140 rows=108 loops=1)

27. 1.355 1,093.828 ↑ 12.4 54 1

Hash Join (cost=3,063.11..3,471.56 rows=670 width=68) (actual time=1,092.767..1,093.828 rows=54 loops=1)

  • Hash Cond: (li.id = ca.competable_id)
28. 9.407 9.407 ↑ 33.3 6 1

CTE Scan on location_intervals li (cost=0.00..4.00 rows=200 width=64) (actual time=9.258..9.407 rows=6 loops=1)

29. 542.912 1,083.066 ↑ 1.0 79,027 1

Hash (cost=1,689.27..1,689.27 rows=79,027 width=12) (actual time=1,083.066..1,083.066 rows=79,027 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 2710kB
30. 540.154 540.154 ↑ 1.0 79,027 1

Seq Scan on competitor_associations ca (cost=0.00..1,689.27 rows=79,027 width=12) (actual time=0.505..540.154 rows=79,027 loops=1)

31. 10.854 434.106 ↓ 2.0 2 54

Index Scan using index_urls_on_competitor_id on urls u (cost=0.42..1,471.18 rows=1 width=19) (actual time=2.091..8.039 rows=2 loops=54)

  • Index Cond: (competitor_id = ca.competitor_id)
  • Filter: ((deleted_at IS NULL) AND (SubPlan 3))
32.          

SubPlan (forIndex Scan)

33. 7.074 423.252 ↓ 2.0 2 108

Nested Loop (cost=0.00..2,932.07 rows=1 width=32) (actual time=0.443..3.919 rows=2 loops=108)

  • Join Filter: (us.id = es.source_id)
  • Rows Removed by Join Filter: 2
34. 412.020 412.020 ↓ 2.0 2 108

Seq Scan on enabled_sources es (cost=0.00..2,931.00 rows=1 width=4) (actual time=0.411..3.815 rows=2 loops=108)

  • Filter: ((location_id = ca.competable_id) AND (competitor_id = ca.competitor_id))
  • Rows Removed by Filter: 37536
35. 4.158 4.158 ↑ 1.5 2 198

Seq Scan on sources us (cost=0.00..1.03 rows=3 width=40) (actual time=0.009..0.021 rows=2 loops=198)

36. 9.288 9.288 ↑ 1.0 1 108

Index Scan using competitors_pkey on competitors c (cost=0.29..5.02 rows=1 width=75) (actual time=0.079..0.086 rows=1 loops=108)

  • Index Cond: (id = u.competitor_id)
Planning time : 34.450 ms