explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8ZbH

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.547 62,959.391 ↓ 157.0 157 1

GroupAggregate (cost=3,880.92..3,880.95 rows=1 width=27) (actual time=62,958.518..62,959.391 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=18684982 read=2397
2. 2.487 62,958.844 ↓ 553.0 553 1

Sort (cost=3,880.92..3,880.93 rows=1 width=23) (actual time=62,958.503..62,958.844 rows=553 loops=1)

  • Output: (date_trunc('week'::text, a.connectedat)), b.country, a.durationseconds
  • Sort Key: b.country, (date_trunc('week'::text, a.connectedat))
  • Sort Method: quicksort Memory: 68kB
  • Buffers: shared hit=18684982 read=2397
3. 9,954.851 62,956.357 ↓ 553.0 553 1

Nested Loop (cost=310.33..3,880.91 rows=1 width=23) (actual time=41.296..62,956.357 rows=553 loops=1)

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

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

  • 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
  • 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 read=565
5. 41,546.596 52,915.422 ↑ 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.399..5.199 rows=1,097 loops=10,178)

  • 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
  • Recheck Cond: ((a.durationseconds >= 300) AND (a.durationseconds <= 18000))
  • 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))
  • Rows Removed by Filter: 13465
  • Heap Blocks: exact=18259332
  • Buffers: shared hit=18684976 read=1832
6. 11,368.826 11,368.826 ↓ 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.117..1.117 rows=14,562 loops=10,178)

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