explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P4Xy : popular keywords - venga - index

Settings
# exclusive inclusive rows x rows loops node
1. 143.327 27,204.736 ↑ 1.0 1 1

Aggregate (cost=2,116,127.62..2,116,127.63 rows=1 width=128) (actual time=27,204.736..27,204.736 rows=1 loops=1)

  • Buffers: shared hit=128 read=1,016,337, temp read=5,823 written=5,823
2. 203.127 27,061.409 ↓ 1,666.4 333,285 1

Subquery Scan on stats (cost=2,116,115.11..2,116,122.11 rows=200 width=150) (actual time=26,229.242..27,061.409 rows=333,285 loops=1)

  • Buffers: shared hit=128 read=1,016,337, temp read=5,823 written=5,823
3. 211.496 26,858.282 ↓ 1,666.4 333,285 1

WindowAgg (cost=2,116,115.11..2,116,120.11 rows=200 width=78) (actual time=26,229.233..26,858.282 rows=333,285 loops=1)

  • Buffers: shared hit=128 read=1,016,337, temp read=5,823 written=5,823
4. 1,307.268 26,646.786 ↓ 1,666.4 333,285 1

Sort (cost=2,116,115.11..2,116,115.61 rows=200 width=70) (actual time=26,229.228..26,646.786 rows=333,285 loops=1)

  • Sort Key: stats_1.total_mentions DESC, stats_1.short
  • Sort Method: external merge Disk: 19,408kB
  • Buffers: shared hit=128 read=1,016,337, temp read=5,823 written=5,823
5. 228.576 25,339.518 ↓ 1,666.4 333,285 1

WindowAgg (cost=2,116,101.96..2,116,107.46 rows=200 width=70) (actual time=24,748.358..25,339.518 rows=333,285 loops=1)

  • Buffers: shared hit=128 read=1,016,337, temp read=3,388 written=3,388
6. 2,393.093 25,110.942 ↓ 1,666.4 333,285 1

Sort (cost=2,116,101.96..2,116,102.46 rows=200 width=62) (actual time=24,748.351..25,110.942 rows=333,285 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: external merge Disk: 16,696kB
  • Buffers: shared hit=128 read=1,016,337, temp read=3,388 written=3,388
7. 176.168 22,717.849 ↓ 1,666.4 333,285 1

Subquery Scan on stats_1 (cost=2,115,990.39..2,116,094.32 rows=200 width=62) (actual time=21,185.964..22,717.849 rows=333,285 loops=1)

  • Buffers: shared hit=128 read=1,016,337, temp read=1,295 written=1,295
8. 396.371 22,541.681 ↓ 1,666.4 333,285 1

GroupAggregate (cost=2,115,990.39..2,116,091.32 rows=200 width=30) (actual time=21,185.959..22,541.681 rows=333,285 loops=1)

  • Group Key: m_keywords.short
  • Buffers: shared hit=128 read=1,016,337, temp read=1,295 written=1,295
9. 2,327.391 22,145.310 ↓ 34.0 333,285 1

Sort (cost=2,115,990.39..2,116,014.87 rows=9,793 width=40) (actual time=21,185.940..22,145.310 rows=333,285 loops=1)

  • Sort Key: m_keywords.short
  • Sort Method: external merge Disk: 10,296kB
  • Buffers: shared hit=128 read=1,016,337, temp read=1,295 written=1,295
10. 3,321.312 19,817.919 ↓ 34.0 333,285 1

HashAggregate (cost=2,115,120.89..2,115,243.30 rows=9,793 width=40) (actual time=19,493.107..19,817.919 rows=333,285 loops=1)

  • Group Key: m_keywords.short
  • Buffers: shared hit=128 read=1,016,337
11. 3,426.006 16,496.607 ↓ 3.7 5,557,166 1

Hash Semi Join (cost=179.86..2,107,672.45 rows=1,489,688 width=14) (actual time=2.774..16,496.607 rows=5,557,166 loops=1)

  • Hash Cond: ((m_keywords.location_id)::text = (location_index.location_id)::text)
  • Buffers: shared hit=128 read=1,016,337
12. 13,067.880 13,067.880 ↑ 1.0 5,557,171 1

Seq Scan on m_keywords (cost=0.00..2,076,131.35 rows=5,633,699 width=39) (actual time=0.043..13,067.880 rows=5,557,171 loops=1)

  • Filter: ((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) AND ((account_id)::text = '566c8f731379cc24b69ae33b'::text))
  • Rows Removed by Filter: 55,010,670
  • Buffers: shared hit=64 read=1,016,337
13. 1.181 2.721 ↑ 1.0 3,648 1

Hash (cost=134.26..134.26 rows=3,648 width=25) (actual time=2.721..2.721 rows=3,648 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 236kB
  • Buffers: shared hit=64
14. 1.540 1.540 ↑ 1.0 3,648 1

Seq Scan on location_index (cost=0.00..134.26 rows=3,648 width=25) (actual time=0.248..1.540 rows=3,648 loops=1)

  • Filter: ((token)::text = '5e42be42bbde3d452edfc287'::text)
  • Rows Removed by Filter: 1,973
  • Buffers: shared hit=64