explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7x2z : COLD

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 33.771 1,104,542.665 ↓ 3.1 107,564 1

Finalize GroupAggregate (cost=4,133,821.81..4,138,791.19 rows=35,093 width=60) (actual time=1,104,426.150..1,104,542.665 rows=107,564 loops=1)

  • Group Key: o.person_id, ('ALL'::text)
  • Buffers: shared hit=1381106 read=176710
2. 0.000 1,104,508.894 ↓ 3.9 136,629 1

Gather Merge (cost=4,133,821.81..4,138,177.07 rows=35,092 width=56) (actual time=1,104,426.143..1,104,508.894 rows=136,629 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=6949605 read=893911
3. 71.925 5,522,165.635 ↓ 3.1 27,326 5

Partial GroupAggregate (cost=4,132,821.75..4,132,997.21 rows=8,773 width=56) (actual time=1,104,412.192..1,104,433.127 rows=27,326 loops=5)

  • Group Key: o.person_id, 'ALL'::text
  • Buffers: shared hit=6949605 read=893911
4. 155.690 5,522,093.710 ↓ 6.1 53,293 5

Sort (cost=4,132,821.75..4,132,843.69 rows=8,773 width=56) (actual time=1,104,412.185..1,104,418.742 rows=53,293 loops=5)

  • Sort Key: o.person_id
  • Sort Method: quicksort Memory: 5729kB
  • Buffers: shared hit=6949605 read=893911
5. 29.845 5,521,938.020 ↓ 6.1 53,293 5

Result (cost=0.00..4,132,247.17 rows=8,773 width=56) (actual time=8.492..1,104,387.604 rows=53,293 loops=5)

  • Buffers: shared hit=6949577 read=893911
6. 23.450 5,521,908.175 ↓ 6.1 53,293 5

Append (cost=0.00..4,132,159.44 rows=8,773 width=24) (actual time=8.492..1,104,381.635 rows=53,293 loops=5)

  • Buffers: shared hit=6949577 read=893911
7. 0.000 0.000 ↓ 0.0 0 5

Parallel Seq Scan on event_page_ace8b421_634f_4060_9118_ffc36db704dc o (cost=0.00..0.00 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=5)

  • Filter: ((timestamp_ >= '2018-01-01 07:00:00'::timestamp without time zone) AND (timestamp_ <= '2018-05-01 05:59:59.999'::timestamp without time zone) AND ((location_host)::text ~~ '%hrblock.ca'::text) AND ((location_path)::text ~~ '/%tax-calculator%'::text))
8. 5,070.920 5,070.920 ↓ 2.7 1,656 5

Parallel Seq Scan on event_page_ace8b421_634f_4060_9118_ffc36db704dc_2018_1 o_1 (cost=0.00..89,420.94 rows=606 width=24) (actual time=8.490..1,014.184 rows=1,656 loops=5)

  • Filter: ((timestamp_ >= '2018-01-01 07:00:00'::timestamp without time zone) AND (timestamp_ <= '2018-05-01 05:59:59.999'::timestamp without time zone) AND ((location_host)::text ~~ '%hrblock.ca'::text) AND ((location_path)::text ~~ '/%tax-calculator%'::text))
  • Rows Removed by Filter: 241670
  • Buffers: shared hit=36 read=59493
9. 7,424.890 7,424.890 ↓ 4.9 17,448 5

Parallel Index Only Scan using ep_ace8b421_634f_4060_9118_ffc36db704dc_2018_2_location on event_page_ace8b421_634f_4060_9118_ffc36db704dc_2018_2 o_2 (cost=0.68..686,048.09 rows=3,572 width=24) (actual time=1,100.420..1,484.978 rows=17,448 loops=5)

  • Index Cond: ((location_path ~>=~ '/'::text) AND (location_path ~<~ '0'::text) AND (timestamp_ >= '2018-01-01 07:00:00'::timestamp without time zone) AND (timestamp_ <= '2018-05-01 05:59:59.999'::timestamp without time zone))
  • Filter: (((location_host)::text ~~ '%hrblock.ca'::text) AND ((location_path)::text ~~ '/%tax-calculator%'::text))
  • Rows Removed by Filter: 1605911
  • Heap Fetches: 14
  • Buffers: shared hit=1238856 read=327
10. 14,640.680 14,640.680 ↓ 7.4 15,894 5

Parallel Index Only Scan using ep_ace8b421_634f_4060_9118_ffc36db704dc_2018_3_location on event_page_ace8b421_634f_4060_9118_ffc36db704dc_2018_3 o_3 (cost=0.81..1,548,345.67 rows=2,158 width=24) (actual time=2,424.849..2,928.136 rows=15,894 loops=5)

  • Index Cond: ((location_path ~>=~ '/'::text) AND (location_path ~<~ '0'::text) AND (timestamp_ >= '2018-01-01 07:00:00'::timestamp without time zone) AND (timestamp_ <= '2018-05-01 05:59:59.999'::timestamp without time zone))
  • Filter: (((location_host)::text ~~ '%hrblock.ca'::text) AND ((location_path)::text ~~ '/%tax-calculator%'::text))
  • Rows Removed by Filter: 3544890
  • Heap Fetches: 118
  • Buffers: shared hit=2738233 read=490
11. 5,321,948.250 5,321,948.250 ↓ 8.2 18,075 5

Parallel Index Only Scan using ep_ace8b421_634f_4060_9118_ffc36db704dc_2018_4_location on event_page_ace8b421_634f_4060_9118_ffc36db704dc_2018_4 o_4 (cost=0.81..1,631,247.66 rows=2,192 width=24) (actual time=935,130.520..1,064,389.650 rows=18,075 loops=5)

  • Index Cond: ((location_path ~>=~ '/'::text) AND (location_path ~<~ '0'::text) AND (timestamp_ >= '2018-01-01 07:00:00'::timestamp without time zone) AND (timestamp_ <= '2018-05-01 05:59:59.999'::timestamp without time zone))
  • Filter: (((location_host)::text ~~ '%hrblock.ca'::text) AND ((location_path)::text ~~ '/%tax-calculator%'::text))
  • Rows Removed by Filter: 3700026
  • Heap Fetches: 147
  • Buffers: shared hit=2335128 read=769110
12. 172,799.985 172,799.985 ↑ 1.1 221 5

Parallel Index Scan using ep_ace8b421_634f_4060_9118_ffc36db704dc_2018_5_ts_pid_ses on event_page_ace8b421_634f_4060_9118_ffc36db704dc_2018_5 o_5 (cost=0.56..177,097.08 rows=244 width=24) (actual time=274.663..34,559.997 rows=221 loops=5)

  • Index Cond: ((timestamp_ >= '2018-01-01 07:00:00'::timestamp without time zone) AND (timestamp_ <= '2018-05-01 05:59:59.999'::timestamp without time zone))
  • Filter: (((location_host)::text ~~ '%hrblock.ca'::text) AND ((location_path)::text ~~ '/%tax-calculator%'::text))
  • Rows Removed by Filter: 166316
  • Buffers: shared hit=637324 read=64491
Planning time : 13.432 ms
Execution time : 1,104,549.408 ms