explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oR1 : Optimization for: plan #8ZbH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.542 61,950.463 ↓ 157.0 157 1

GroupAggregate (cost=3,880.92..3,880.95 rows=1 width=27) (actual time=61,949.601..61,950.463 rows=157 loops=1)

  • Output: (date_trunc('week'::text, a.connectedat)), b.country, sum(a.durationseconds)
  • Group Key: b.country, (date_trunc('week'::text, a.connectedat))
  • Buffers: shared hit=18687373
2. 2.744 61,949.921 ↓ 553.0 553 1

Sort (cost=3,880.92..3,880.93 rows=1 width=23) (actual time=61,949.588..61,949.921 rows=553 loops=1)

  • Sort Method: quicksort Memory: 68kB
  • Sort Key: b.country, (date_trunc('week'::text, a.connectedat))
  • Output: (date_trunc('week'::text, a.connectedat)), b.country, a.durationseconds
  • Buffers: shared hit=18687373
3. 9,811.587 61,947.177 ↓ 553.0 553 1

Nested Loop (cost=310.33..3,880.91 rows=1 width=23) (actual time=17.254..61,947.177 rows=553 loops=1)

  • Rows Removed by Join Filter: 11164713
  • Output: date_trunc('week'::text, a.connectedat), b.country, a.durationseconds
  • Join Filter: ((a.uri)::text = (b.uri)::text)
  • Buffers: shared hit=18687373
4. 64.942 64.942 ↓ 10,178.0 10,178 1

Seq Scan on public.describers b (cost=0.00..819.45 rows=1 width=40) (actual time=0.017..64.942 rows=10,178 loops=1)

5. 40,915.560 52,070.648 ↑ 3.7 1,097 10,178

Bitmap Heap Scan on public.participantcalldata a (cost=310.33..3,010.37 rows=4,087 width=43) (actual time=1.372..5.116 rows=1,097 loops=10,178)

  • Rows Removed by Filter: 13465
  • Recheck Cond: ((a.durationseconds >= 300) AND (a.durationseconds <= 18000))
  • Output: b.alltags, b.client, b.country, b.createdtimestamp, b.customtags, b.deleted, b.department, b.external, b.id, b.lastupdatedat, b.location, b.managedbyautotagger, b.modifiedtimestamp, b.name, b.nodestoragepointid, b.owner, b.path, b.revisionsignature, b.shared, b.timeinvariantrevisionsignature, b.uri
  • Output: a.alltargeturis, a.connectedat, a.disconnectedat, a.durationseconds, a.hour, a.hourindex, a.id, a.lastupdatedat, a.networkpeers__list, a.path, a.primarytargeturi, a.quarter, a.recordmodifiedat, a.uri, a.vendors, a.week, a.weekday, a.weekdayindex, a.weekdayisoindex
  • Heap Blocks: exact=18259332
  • Filter: ((a.connectedat >= '2017-08-08 01:22:49.098+00'::timestamp with time zone) AND (a.connectedat <= '2017-12-12 07:53:17.599+00'::timestamp with time zone))
  • Filter: (('{all}'::text[] && (array_prepend('all'::character varying, b.alltags))::text[]) AND ('{all}'::text[] && CASE WHEN ((b.country)::text = 'Undefined'::text) THEN ('{all,Undefined}'::text[] || (b.alltags)::text[]) ELSE (array_prepend('all'::character varying, b.alltags))::text[] END))
  • Buffers: shared hit=565
  • Buffers: shared hit=18686808
6. 11,155.088 11,155.088 ↓ 1.0 14,562 10,178

Bitmap Index Scan on durationseconds_participantcalldata (cost=0.00..309.31 rows=14,502 width=0) (actual time=1.096..1.096 rows=14,562 loops=10,178)

  • Index Cond: ((a.durationseconds >= 300) AND (a.durationseconds <= 18000))
  • Buffers: shared hit=427476