explain.depesz.com

PostgreSQL's explain analyze made readable

Result: APM7 : Optimization for: Optimization for: plan #Obq7; plan #Ld2K

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,063.715 2,805.252 ↑ 33.3 6 1

GroupAggregate (cost=369,252.52..369,352.87 rows=200 width=19) (actual time=1,768.618..2,805.252 rows=6 loops=1)

  • Output: events_2019_11_28.type, count(DISTINCT (events_2019_11_28.value -> 'sessionID'::text))
  • Group Key: events_2019_11_28.type
2. 675.508 1,741.537 ↓ 32.4 318,388 1

Sort (cost=369,252.52..369,277.11 rows=9,835 width=901) (actual time=1,595.642..1,741.537 rows=318,388 loops=1)

  • Output: events_2019_11_28.type, events_2019_11_28.value
  • Sort Key: events_2019_11_28.type
  • Sort Method: external merge Disk: 325664kB
3. 24.793 1,066.029 ↓ 32.4 318,388 1

Append (cost=0.30..368,600.28 rows=9,835 width=901) (actual time=0.022..1,066.029 rows=318,388 loops=1)

  • Subplans Removed: 57
4. 240.624 240.624 ↓ 32.7 64,644 1

Index Scan using events_2019_11_28_timestamp_idx on part.events_2019_11_28 (cost=0.43..76,108.88 rows=1,974 width=911) (actual time=0.022..240.624 rows=64,644 loops=1)

  • Output: events_2019_11_28.type, events_2019_11_28.value
  • Index Cond: (events_2019_11_28."timestamp" >= (now() - '24:00:00'::interval))
  • Filter: (((events_2019_11_28.value ->> 'baseAID'::text) = ANY ('{bandsintown}'::text[])) AND (events_2019_11_28.type = ANY ('{PAGE_LOAD,PAGE_VIEW,TRUE_PAGE_VIEW,INTERACTION,PIN_HOVER,PIN_CLICK}'::text[])))
  • Rows Removed by Filter: 436725
5. 800.605 800.605 ↓ 32.5 253,744 1

Seq Scan on part.events_2019_11_29 (cost=0.00..292,344.14 rows=7,803 width=899) (actual time=0.025..800.605 rows=253,744 loops=1)

  • Output: events_2019_11_29.type, events_2019_11_29.value
  • Filter: (((events_2019_11_29.value ->> 'baseAID'::text) = ANY ('{bandsintown}'::text[])) AND (events_2019_11_29."timestamp" >= (now() - '24:00:00'::interval)) AND (events_2019_11_29.type = ANY ('{PAGE_LOAD,PAGE_VIEW,TRUE_PAGE_VIEW,INTERACTION,PIN_HOVER,PIN_CLICK}'::text[])))
  • Rows Removed by Filter: 1679691
6. 0.002 0.007 ↓ 0.0 0 1

Bitmap Heap Scan on part.events_2019_11_30 (cost=4.80..9.09 rows=1 width=64) (actual time=0.007..0.007 rows=0 loops=1)

  • Output: events_2019_11_30.type, events_2019_11_30.value
  • Recheck Cond: ((events_2019_11_30.type = ANY ('{PAGE_LOAD,PAGE_VIEW,TRUE_PAGE_VIEW,INTERACTION,PIN_HOVER,PIN_CLICK}'::text[])) AND (events_2019_11_30."timestamp" >= (now() - '24:00:00'::interval)))
  • Filter: ((events_2019_11_30.value ->> 'baseAID'::text) = ANY ('{bandsintown}'::text[]))
7. 0.000 0.005 ↓ 0.0 0 1

BitmapAnd (cost=4.80..4.80 rows=5 width=0) (actual time=0.005..0.005 rows=0 loops=1)

8. 0.005 0.005 ↓ 0.0 0 1

Bitmap Index Scan on events_2019_11_30_type_idx (cost=0.00..2.08 rows=15 width=0) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (events_2019_11_30.type = ANY ('{PAGE_LOAD,PAGE_VIEW,TRUE_PAGE_VIEW,INTERACTION,PIN_HOVER,PIN_CLICK}'::text[]))
9. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on events_2019_11_30_timestamp_idx (cost=0.00..2.47 rows=163 width=0) (never executed)

  • Index Cond: (events_2019_11_30."timestamp" >= (now() - '24:00:00'::interval))
Planning time : 3.582 ms
Execution time : 2,843.117 ms