explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KB3c : Category Keywords - location index

Settings
# exclusive inclusive rows x rows loops node
1. 15.619 8,696.252 ↓ 1.2 5 1

GroupAggregate (cost=990,500.39..990,581.49 rows=4 width=100) (actual time=8,687.221..8,696.252 rows=5 loops=1)

  • Group Key: cr.rank, cr.category
  • Buffers: shared hit=298643, temp read=7561 written=7561
2. 43.871 8,680.633 ↓ 10.5 37,749 1

Sort (cost=990,500.39..990,509.39 rows=3,602 width=105) (actual time=8,669.323..8,680.633 rows=37,749 loops=1)

  • Sort Key: cr.rank, cr.category
  • Sort Method: external sort Disk: 3872kB
  • Buffers: shared hit=298639, temp read=7561 written=7561
3. 66.756 8,636.762 ↓ 10.5 37,749 1

Hash Right Join (cost=982,822.09..990,287.61 rows=3,602 width=105) (actual time=8,396.371..8,636.762 rows=37,749 loops=1)

  • Hash Cond: (lower((stats.category)::text) = lower((cr.category)::text))
  • Buffers: shared hit=298633, temp read=7077 written=7077
4. 63.145 8,569.869 ↑ 1.7 107,905 1

Subquery Scan on stats (cost=982,788.51..989,092.32 rows=180,109 width=75) (actual time=8,396.209..8,569.869 rows=107,905 loops=1)

  • Buffers: shared hit=298628, temp read=7077 written=7077
5. 74.432 8,506.724 ↑ 1.7 107,905 1

WindowAgg (cost=982,788.51..987,291.23 rows=180,109 width=33) (actual time=8,396.199..8,506.724 rows=107,905 loops=1)

  • Buffers: shared hit=298628, temp read=7077 written=7077
6. 563.672 8,432.292 ↑ 1.7 107,905 1

Sort (cost=982,788.51..983,238.78 rows=180,109 width=25) (actual time=8,396.192..8,432.292 rows=107,905 loops=1)

  • Sort Key: (CASE WHEN (rk.sentiment IS NOT NULL) THEN CASE WHEN (rk.sentiment >= 0.25) THEN 1 WHEN (rk.sentiment <= '-0.25'::numeric) THEN '-1'::integer ELSE 0 END WHEN (rk.rating >= 4.0) THEN 1 WHEN (rk.rating < 3.0) THEN '-1'::integer ELSE 0 END), rk.category, (count(*)) DESC, rk.short
  • Sort Method: external merge Disk: 4112kB
  • Buffers: shared hit=298628, temp read=7077 written=7077
7. 512.544 7,868.620 ↑ 1.7 107,905 1

GroupAggregate (cost=955,099.70..960,953.24 rows=180,109 width=25) (actual time=5,689.742..7,868.620 rows=107,905 loops=1)

  • Group Key: rk.category, (CASE WHEN (rk.sentiment IS NOT NULL) THEN CASE WHEN (rk.sentiment >= 0.25) THEN 1 WHEN (rk.sentiment <= '-0.25'::numeric) THEN '-1'::integer ELSE 0 END WHEN (rk.rating >= 4.0) THEN 1 WHEN (rk.rating < 3.0) THEN '-1'::integer ELSE 0 END), rk.short
  • Buffers: shared hit=298625, temp read=6562 written=6562
8. 5,434.582 7,356.076 ↓ 7.8 1,406,905 1

Sort (cost=955,099.70..955,549.97 rows=180,109 width=17) (actual time=5,689.731..7,356.076 rows=1,406,905 loops=1)

  • Sort Key: rk.category, (CASE WHEN (rk.sentiment IS NOT NULL) THEN CASE WHEN (rk.sentiment >= 0.25) THEN 1 WHEN (rk.sentiment <= '-0.25'::numeric) THEN '-1'::integer ELSE 0 END WHEN (rk.rating >= 4.0) THEN 1 WHEN (rk.rating < 3.0) THEN '-1'::integer ELSE 0 END), rk.short
  • Sort Method: external merge Disk: 32328kB
  • Buffers: shared hit=298625, temp read=6562 written=6562
9. 1,017.157 1,921.494 ↓ 7.8 1,406,905 1

Nested Loop (cost=53.27..935,681.52 rows=180,109 width=17) (actual time=1.830..1,921.494 rows=1,406,905 loops=1)

  • Buffers: shared hit=298625
10. 2.266 3.133 ↑ 1.0 1,972 1

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

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

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

  • Filter: ((token)::text = '5e42be42bbde3d452edfc28d'::text)
  • Buffers: shared hit=23
12. 901.204 901.204 ↓ 7.8 713 1,972

Index Scan using index_review_keywords_on_account_id_and_location_id on fact_review_keywords rk (cost=0.69..472.62 rows=91 width=48) (actual time=0.020..0.457 rows=713 loops=1,972)

  • Index Cond: (((account_id)::text = '5c0fbe3b2ef4cf738f616dc2'::text) AND ((location_id)::text = (location_index.location_id)::text))
  • Filter: ((keyword_confidence IS NULL) OR (keyword_confidence >= 0.5))
  • Buffers: shared hit=298602
13. 0.012 0.137 ↓ 1.2 5 1

Hash (cost=33.54..33.54 rows=4 width=36) (actual time=0.137..0.137 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=5
14. 0.015 0.125 ↓ 1.2 5 1

Sort (cost=33.49..33.50 rows=4 width=36) (actual time=0.124..0.125 rows=5 loops=1)

  • Sort Key: cr.rank
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=5
15. 0.018 0.110 ↓ 1.2 5 1

Hash Join (cost=9.59..33.45 rows=4 width=36) (actual time=0.104..0.110 rows=5 loops=1)

  • Hash Cond: (upper((cr.vertical_name)::text) = upper((dv.name)::text))
  • Buffers: shared hit=5
16. 0.009 0.009 ↑ 170.0 5 1

Seq Scan on dim_category_ranks cr (cost=0.00..18.50 rows=850 width=68) (actual time=0.008..0.009 rows=5 loops=1)

  • Buffers: shared hit=1
17. 0.037 0.083 ↑ 1.0 1 1

Hash (cost=9.58..9.58 rows=1 width=10) (actual time=0.083..0.083 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=4
18. 0.018 0.046 ↑ 1.0 1 1

Hash Join (cost=8.31..9.58 rows=1 width=10) (actual time=0.036..0.046 rows=1 loops=1)

  • Hash Cond: ((dv.id)::text = (av.vertical_id)::text)
  • Buffers: shared hit=4
19. 0.008 0.008 ↑ 1.0 19 1

Seq Scan on dim_verticals dv (cost=0.00..1.19 rows=19 width=29) (actual time=0.003..0.008 rows=19 loops=1)

  • Buffers: shared hit=1
20. 0.003 0.020 ↑ 1.0 1 1

Hash (cost=8.30..8.30 rows=1 width=19) (actual time=0.020..0.020 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
21. 0.017 0.017 ↑ 1.0 1 1

Index Scan using dim_account_verticals_pkey on dim_account_verticals av (cost=0.28..8.30 rows=1 width=19) (actual time=0.016..0.017 rows=1 loops=1)

  • Index Cond: ((account_id)::text = '5c0fbe3b2ef4cf738f616dc2'::text)
  • Buffers: shared hit=3
Planning time : 1.842 ms