explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1G64

Settings
# exclusive inclusive rows x rows loops node
1. 2.893 2,951.321 ↑ 72.9 5,571 1

Finalize GroupAggregate (cost=959,898.10..1,115,555.46 rows=406,363 width=64) (actual time=2,313.431..2,951.321 rows=5,571 loops=1)

  • 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=41295, temp read=6693 written=6706
2. 0.000 2,948.428 ↑ 55.4 14,677 1

Gather Merge (cost=959,898.10..1,091,173.68 rows=812,726 width=64) (actual time=2,313.189..2,948.428 rows=14,677 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=80340, temp read=19115 written=19152
3. 795.252 8,556.006 ↑ 83.1 4,892 3

Partial GroupAggregate (cost=958,898.08..996,364.91 rows=406,363 width=64) (actual time=2,297.069..2,852.002 rows=4,892 loops=3)

  • 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=80340, temp read=19115 written=19152
4. 3,645.924 7,760.754 ↑ 1.3 1,040,344 3

Sort (cost=958,898.08..962,158.03 rows=1,303,981 width=64) (actual time=2,296.317..2,586.918 rows=1,040,344 loops=3)

  • Sort 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)
  • Sort Method: external merge Disk: 53544kB
  • Buffers: shared hit=80340, temp read=19115 written=19152
5. 2,164.641 4,114.830 ↑ 1.3 1,040,344 3

Result (cost=68,181.28..798,433.52 rows=1,303,981 width=64) (actual time=242.939..1,371.610 rows=1,040,344 loops=3)

  • Buffers: shared hit=80310
6. 247.515 1,950.189 ↑ 1.3 1,040,344 3

Append (cost=68,181.28..772,353.90 rows=1,303,981 width=97) (actual time=242.931..650.063 rows=1,040,344 loops=3)

  • Buffers: shared hit=80310
7. 1,470.454 1,702.674 ↑ 1.3 1,040,344 3

Parallel Bitmap Heap Scan on tracking_display_201907 td (cost=68,181.28..772,353.90 rows=1,303,981 width=97) (actual time=242.930..567.558 rows=1,040,344 loops=3)

  • Recheck Cond: ((date >= '2019-07-10'::date) AND (date <= '2019-07-11'::date) AND (date >= '2019-07-09 07:15:48'::timestamp without time zone) AND (date < '2019-07-11 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=21025
  • Buffers: shared hit=80310
8. 232.220 232.220 ↑ 1.0 3,121,031 1

Bitmap Index Scan on idx_tracking_display_date_201907 (cost=0.00..67,398.89 rows=3,129,555 width=0) (actual time=232.220..232.220 rows=3,121,031 loops=1)

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