explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FqgW : sentiment groups, group by score

Settings
# exclusive inclusive rows x rows loops node
1. 0.035 45.944 ↑ 1.9 15 1

Sort (cost=2,093.11..2,093.18 rows=29 width=27) (actual time=45.939..45.944 rows=15 loops=1)

  • Sort Key: ((SubPlan 1))
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=12778
2. 9.933 45.909 ↑ 1.9 15 1

GroupAggregate (cost=1,845.21..2,092.40 rows=29 width=27) (actual time=33.684..45.909 rows=15 loops=1)

  • Group Key: unnested_mailing_groups.mailing_group_id
  • Buffers: shared hit=12778
3. 7.046 35.916 ↓ 131.9 13,322 1

Sort (cost=1,845.21..1,845.46 rows=101 width=27) (actual time=33.620..35.916 rows=13,322 loops=1)

  • Sort Key: unnested_mailing_groups.mailing_group_id
  • Sort Method: quicksort Memory: 1425kB
  • Buffers: shared hit=12733
4. 6.591 28.870 ↓ 131.9 13,322 1

Nested Loop (cost=683.36..1,841.85 rows=101 width=27) (actual time=5.259..28.870 rows=13,322 loops=1)

  • Buffers: shared hit=12733
5. 1.465 7.019 ↓ 12.0 2,180 1

Hash Join (cost=682.80..977.14 rows=181 width=8) (actual time=3.555..7.019 rows=2,180 loops=1)

  • Hash Cond: (mailings.mailing_group_id = mailing_groups.id)
  • Buffers: shared hit=193
6. 2.038 2.038 ↓ 1.4 4,081 1

Index Only Scan using mailings_covering_index on mailings (cost=0.42..282.28 rows=2,845 width=8) (actual time=0.024..2.038 rows=4,081 loops=1)

  • Index Cond: ((project_id = 20006) AND (active = true))
  • Filter: ((deleted IS NOT TRUE) AND (active IS TRUE))
  • Rows Removed by Filter: 1
  • Heap Fetches: 6
  • Buffers: shared hit=62
7. 0.022 3.516 ↑ 15.2 63 1

Hash (cost=670.42..670.42 rows=957 width=12) (actual time=3.516..3.516 rows=63 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
  • Buffers: shared hit=131
8. 1.832 3.494 ↑ 15.2 63 1

Hash Join (cost=456.42..670.42 rows=957 width=12) (actual time=0.626..3.494 rows=63 loops=1)

  • Hash Cond: (mailing_groups.id = unnested_mailing_groups.id)
  • Buffers: shared hit=131
9. 1.439 1.439 ↑ 1.0 6,722 1

Seq Scan on mailing_groups (cost=0.00..179.22 rows=6,722 width=4) (actual time=0.005..1.439 rows=6,722 loops=1)

  • Buffers: shared hit=112
10. 0.029 0.223 ↑ 15.2 63 1

Hash (cost=444.46..444.46 rows=957 width=8) (actual time=0.223..0.223 rows=63 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
  • Buffers: shared hit=19
11. 0.050 0.194 ↑ 15.2 63 1

Subquery Scan on unnested_mailing_groups (cost=4.79..444.46 rows=957 width=8) (actual time=0.060..0.194 rows=63 loops=1)

  • Filter: (unnested_mailing_groups.mailing_group_id = ANY ('{6429,6398,7698,5478,5471,5472,5473,5474,5475,5476,5477,5479,5480,5481,5482,5483,5484,5485,5486,5487,5582,7699,7602,7700,7690,7691,7692,7693,7697}'::integer[]))
  • Rows Removed by Filter: 63
  • Buffers: shared hit=19
12. 0.108 0.144 ↑ 52.4 126 1

Bitmap Heap Scan on mailing_groups mailing_groups_1 (cost=4.79..139.21 rows=6,600 width=40) (actual time=0.051..0.144 rows=126 loops=1)

  • Recheck Cond: (project_id = 20006)
  • Heap Blocks: exact=17
  • Buffers: shared hit=19
13. 0.036 0.036 ↑ 1.0 66 1

Bitmap Index Scan on mailing_groups_project_id_index (cost=0.00..4.78 rows=66 width=0) (actual time=0.036..0.036 rows=66 loops=1)

  • Index Cond: (project_id = 20006)
  • Buffers: shared hit=2
14. 15.260 15.260 ↑ 1.0 6 2,180

Index Only Scan using form_responses_covering_mailing_id_first_index on form_responses (cost=0.56..4.72 rows=6 width=27) (actual time=0.003..0.007 rows=6 loops=2,180)

  • Index Cond: ((project_id = 20006) AND (mailing_id = mailings.id) AND (created_time >= '2019-03-10 23:00:00+00'::timestamp with time zone) AND (created_time <= '2019-04-09 21:59:59+00'::timestamp with time zone))
  • Filter: ((deleted IS NOT TRUE) AND (is_unanswered IS NOT TRUE))
  • Rows Removed by Filter: 0
  • Heap Fetches: 150
  • Buffers: shared hit=12540
15.          

SubPlan (forGroupAggregate)

16. 0.060 0.060 ↑ 1.0 1 15

Index Scan using mailing_groups_pkey on mailing_groups mailing_groups_2 (cost=0.28..8.30 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=15)

  • Index Cond: (id = unnested_mailing_groups.mailing_group_id)
  • Buffers: shared hit=45