explain.depesz.com

PostgreSQL's explain analyze made readable

Result: otg : Popular Keywords - location index

Settings
# exclusive inclusive rows x rows loops node
1. 14.761 3,474.434 ↑ 1.0 1 1

Aggregate (cost=210,758.48..210,758.49 rows=1 width=128) (actual time=3,474.433..3,474.434 rows=1 loops=1)

  • Buffers: shared hit=158817, temp read=2006 written=2006
2. 19.973 3,459.673 ↓ 168.0 33,604 1

Subquery Scan on stats (cost=210,745.97..210,752.97 rows=200 width=149) (actual time=3,410.403..3,459.673 rows=33,604 loops=1)

  • Buffers: shared hit=158817, temp read=2006 written=2006
3. 21.027 3,439.700 ↓ 168.0 33,604 1

WindowAgg (cost=210,745.97..210,750.97 rows=200 width=77) (actual time=3,410.393..3,439.700 rows=33,604 loops=1)

  • Buffers: shared hit=158817, temp read=2006 written=2006
4. 147.125 3,418.673 ↓ 168.0 33,604 1

Sort (cost=210,745.97..210,746.47 rows=200 width=69) (actual time=3,410.385..3,418.673 rows=33,604 loops=1)

  • Sort Key: stats_1.total_mentions DESC, stats_1.short
  • Sort Method: quicksort Memory: 3783kB
  • Buffers: shared hit=158817, temp read=2006 written=2006
5. 30.399 3,271.548 ↓ 168.0 33,604 1

WindowAgg (cost=210,732.82..210,738.32 rows=200 width=69) (actual time=3,228.271..3,271.548 rows=33,604 loops=1)

  • Buffers: shared hit=158817, temp read=2006 written=2006
6. 141.597 3,241.149 ↓ 168.0 33,604 1

Sort (cost=210,732.82..210,733.32 rows=200 width=61) (actual time=3,228.262..3,241.149 rows=33,604 loops=1)

  • Sort Key: (CASE WHEN (stats_1.avg_sentiment >= 0.25) THEN 'positive'::text WHEN (stats_1.avg_sentiment <= '-0.25'::numeric) THEN 'negative'::text ELSE 'mixed'::text END), stats_1.total_mentions DESC, stats_1.short
  • Sort Method: quicksort Memory: 3615kB
  • Buffers: shared hit=158817, temp read=2006 written=2006
7. 18.157 3,099.552 ↓ 168.0 33,604 1

Subquery Scan on stats_1 (cost=208,744.22..210,725.18 rows=200 width=61) (actual time=1,779.384..3,099.552 rows=33,604 loops=1)

  • Buffers: shared hit=158817, temp read=2006 written=2006
8. 153.545 3,081.395 ↓ 168.0 33,604 1

GroupAggregate (cost=208,744.22..210,722.18 rows=200 width=29) (actual time=1,779.380..3,081.395 rows=33,604 loops=1)

  • Group Key: fact_review_keywords.short
  • Buffers: shared hit=158817, temp read=2006 written=2006
9. 343.986 2,927.850 ↓ 6.4 317,007 1

GroupAggregate (cost=208,744.22..209,855.13 rows=49,374 width=64) (actual time=1,779.363..2,927.850 rows=317,007 loops=1)

  • Group Key: fact_review_keywords.short, fact_review_keywords.review_id
  • Buffers: shared hit=158817, temp read=2006 written=2006
10. 2,116.299 2,583.864 ↓ 6.7 332,217 1

Sort (cost=208,744.22..208,867.65 rows=49,374 width=37) (actual time=1,779.347..2,583.864 rows=332,217 loops=1)

  • Sort Key: fact_review_keywords.short, fact_review_keywords.review_id
  • Sort Method: external merge Disk: 15984kB
  • Buffers: shared hit=158817, temp read=2006 written=2006
11. 164.880 467.565 ↓ 6.7 332,217 1

Nested Loop (cost=53.27..204,895.15 rows=49,374 width=37) (actual time=1.813..467.565 rows=332,217 loops=1)

  • Buffers: shared hit=158817
12. 2.087 2.941 ↑ 1.0 1,972 1

HashAggregate (cost=52.58..72.30 rows=1,972 width=25) (actual time=1.761..2.941 rows=1,972 loops=1)

  • Group Key: (location_index.location_id)::text
  • Buffers: shared hit=23
13. 0.854 0.854 ↑ 1.0 1,972 1

Seq Scan on location_index (cost=0.00..47.65 rows=1,972 width=25) (actual time=0.013..0.854 rows=1,972 loops=1)

  • Filter: ((token)::text = '5e42be42bbde3d452edfc28d'::text)
  • Buffers: shared hit=23
14. 299.744 299.744 ↓ 6.7 168 1,972

Index Scan using index_fact_review_keywords_on_account_and_location_and_publish on fact_review_keywords (cost=0.69..103.62 rows=25 width=62) (actual time=0.018..0.152 rows=168 loops=1,972)

  • Index Cond: (((account_id)::text = '5c0fbe3b2ef4cf738f616dc2'::text) AND ((location_id)::text = (location_index.location_id)::text) AND (published_at > '2019-01-01 00:00:00+00'::timestamp with time zone) AND (published_at < '2020-03-01 00:00:00+00'::timestamp with time zone))
  • Buffers: shared hit=158794