explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zYah

Settings
# exclusive inclusive rows x rows loops node
1. 1,244.046 4,402.873 ↑ 72.9 5,571 1

HashAggregate (cost=934,383.31..942,510.57 rows=406,363 width=64) (actual time=4,399.808..4,402.873 rows=5,571 loops=1)

  • Output: count(td.id), td.id_segment, td.id_newsletter, td.id_panel, td.insert_num, td.id_ad, td.id_campaign, (CASE WHEN (("substring"((td.user_hash)::text, 1, 1) >= '0'::text) AND ("substring"((td.user_hash)::text, 1, 1) <= '3'::text)) THEN 'b'::text ELSE 'a'::text END)
  • Group Key: td.id_segment, td.id_newsletter, td.id_panel, td.insert_num, td.id_ad, td.id_campaign, CASE WHEN (("substring"((td.user_hash)::text, 1, 1) >= '0'::text) AND ("substring"((td.user_hash)::text, 1, 1) <= '3'::text)) THEN 'b'::text ELSE 'a'::text END
  • Buffers: shared hit=80310
2. 1,973.061 3,158.827 ↑ 1.0 3,121,031 1

Result (cost=68,205.45..871,769.69 rows=3,130,681 width=64) (actual time=208.719..3,158.827 rows=3,121,031 loops=1)

  • Output: td.id_segment, td.id_newsletter, td.id_panel, td.insert_num, td.id_ad, td.id_campaign, CASE WHEN (("substring"((td.user_hash)::text, 1, 1) >= '0'::text) AND ("substring"((td.user_hash)::text, 1, 1) <= '3'::text)) THEN 'b'::text ELSE 'a'::text END, td.id
  • Buffers: shared hit=80310
3. 226.610 1,185.766 ↑ 1.0 3,121,031 1

Append (cost=68,205.45..809,156.07 rows=3,130,681 width=97) (actual time=208.707..1,185.766 rows=3,121,031 loops=1)

  • Buffers: shared hit=80310
4. 760.762 959.156 ↑ 1.0 3,121,031 1

Bitmap Heap Scan on public.tracking_display_201907 td (cost=68,205.45..809,156.07 rows=3,130,681 width=97) (actual time=208.706..959.156 rows=3,121,031 loops=1)

  • Output: td.id, td.id_segment, td.id_newsletter, td.id_panel, td.insert_num, td.id_ad, td.id_campaign, td.user_hash
  • Recheck Cond: ((td.date >= '2019-07-10'::date) AND (td.date <= '2019-07-11'::date) AND (td.date >= '2019-07-09 07:15:48'::timestamp without time zone) AND (td.date < '2019-07-11 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=60040
  • Buffers: shared hit=80310
5. 198.394 198.394 ↑ 1.0 3,121,031 1

Bitmap Index Scan on idx_tracking_display_date_201907 (cost=0.00..67,422.78 rows=3,130,681 width=0) (actual time=198.394..198.394 rows=3,121,031 loops=1)

  • Index Cond: ((td.date >= '2019-07-10'::date) AND (td.date <= '2019-07-11'::date) AND (td.date >= '2019-07-09 07:15:48'::timestamp without time zone) AND (td.date < '2019-07-11 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=20270
Planning time : 0.523 ms
Execution time : 4,406.559 ms