explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bjPE

Settings
# exclusive inclusive rows x rows loops node
1. 1,833.698 14,412.275 ↓ 0.0 294 1

Custom Scan (Citus Router) (cost=0.00..0.00 rows=0 width=0) (actual time=14,412.261..14,412.275 rows=294 loops=1)

  • Task Count: 1
  • Tasks Shown: All
  • -> Task
  • Node: host=ec2-52-45-94-192.compute-1.amazonaws.com port=5432 dbname=citus
  • Planning time: 0.800 ms
  • Execution time: 12595.895 ms
2. 0.105 12,578.577 ↓ 1.5 294 1

Sort (cost=2,382,840.52..2,382,841.00 rows=192 width=58) (actual time=12,578.563..12,578.577 rows=294 loops=1)

  • Sort Key: (count(uba_session.id)) DESC
  • Sort Method: quicksort Memory: 49kB
3. 957.188 12,578.472 ↓ 1.5 294 1

HashAggregate (cost=2,382,743.84..2,382,833.24 rows=192 width=58) (actual time=12,578.413..12,578.472 rows=294 loops=1)

  • Hash Key: uba_session.os
  • Hash Key: uba_session.country
  • Hash Key: uba_session.devicetype
  • Hash Key: uba_session.usertype
  • Hash Key: uba_session.visitdurationrange
  • Hash Key: uba_session.browser
  • Hash Key: uba_session.us
  • Hash Key: uba_session.um
  • Hash Key: uba_session.uc
4. 471.035 11,621.284 ↓ 338.7 740,699 1

Hash Join (cost=698,757.30..2,382,732.90 rows=2,187 width=83) (actual time=9,406.622..11,621.284 rows=740,699 loops=1)

  • Hash Cond: ((uba_session.userid)::text = (uba_user.id)::text)
5. 3,414.537 3,414.537 ↓ 3.2 740,707 1

Index Scan using uba_session_siteid_initiallast_time_idx_104346 on uba_session_104346 uba_session (cost=0.56..1,683,375.22 rows=228,929 width=116) (actual time=1,670.611..3,414.537 rows=740,707 loops=1)

  • Index Cond: (((siteid)::text = '514-partycitycom'::text) AND (initialvisittime <= '1547149500000'::bigint) AND (lastvisittime >= '1546923600000'::bigint))
  • Filter: (initialvisittime <= lastvisittime)
6. 146.884 7,735.712 ↓ 3.3 528,759 1

Hash (cost=696,729.74..696,729.74 rows=162,160 width=33) (actual time=7,735.712..7,735.712 rows=528,759 loops=1)

  • Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 41756kB
7. 1,152.182 7,588.828 ↓ 3.3 528,759 1

Bitmap Heap Scan on uba_user_104343 uba_user (cost=6,829.53..696,729.74 rows=162,160 width=33) (actual time=6,594.459..7,588.828 rows=528,759 loops=1)

  • Recheck Cond: (((siteid)::text = '514-partycitycom'::text) AND (initialvisittime <= '1547149500000'::bigint) AND (lastvisittime >= '1546923600000'::bigint))
  • Filter: (initialvisittime <= lastvisittime)
  • Heap Blocks: exact=571673
8. 6,436.646 6,436.646 ↓ 5.7 2,793,189 1

Bitmap Index Scan on uba_user_id_sid_adobe_tt_exp_v2_gin_idx_104343 (cost=0.00..6,788.99 rows=486,479 width=0) (actual time=6,436.646..6,436.646 rows=2,793,189 loops=1)

  • Index Cond: (((siteid)::text = '514-partycitycom'::text) AND (initialvisittime <= '1547149500000'::bigint) AND (lastvisittime >= '1546923600000'::bigint))