Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--https://www.depesz.com/2025/08/29/who-logged-to-system-from-multiple-countries-in-2-hours/
select setseed(.42);
create table sessions (
    user_id int8,
    login_time timestamptz,
    country_id int8
);
insert into sessions (user_id, login_time, country_id) values
    (1, '2025-05-01 12:34:56', 100),
    (1, '2025-05-01 13:34:56', 100),
    (1, '2025-05-01 19:34:56', 100),
    (2, '2025-05-01 12:34:56', 100),
    (2, '2025-05-01 19:34:56', 200),
    (3, '2025-05-01 12:34:56', 100),
    (3, '2025-05-01 13:34:56', 200);
insert into sessions (user_id, login_time, country_id)
select
    floor( 1 + random() * 50 ),
    now() - '3 months'::interval * random(),
    floor( 1 + random() * random() * random() * random() * random() * 5 )
from generate_series(1,100000);
create index the_index on sessions (user_id, login_time)include(country_id)with(fillfactor=100);

select country_id, count(*) from sessions group by 1 order by 1;


setseed






SELECT 1



CREATE TABLE



INSERT 0 7



INSERT 0 100000



CREATE INDEX




country_id
count


1
97566

2
2158

3
254

4
21

5
1

100
5

200
2



SELECT 7


analyze sessions;

ANALYZE


explain analyze
select
    s_start.user_id,
    s_start.login_time,
    array_agg(distinct s_end.country_id) as countries
from
    sessions as s_start
    join sessions s_end on
        s_start.user_id = s_end.user_id and
        s_end.login_time between s_start.login_time and s_start.login_time + '2 hours'::interval
group by
    s_start.user_id, s_start.login_time
having
    (array_agg(distinct s_end.country_id))[2] is not null;--seems cheaper


QUERY PLAN


GroupAggregate  (cost=0.84..941899.24 rows=99395 width=48) (actual time=0.314..874.543 rows=6460 loops=1)

  Group Key: s_start.user_id, s_start.login_time

  Filter: ((array_agg(DISTINCT s_end.country_id))[2] IS NOT NULL)

  Rows Removed by Filter: 93547

  ->  Nested Loop  (cost=0.84..773749.88 rows=22253424 width=24) (actual time=0.090..593.307 rows=285663 loops=1)

        ->  Index Only Scan using the_index on sessions s_start  (cost=0.42..5848.44 rows=100007 width=16) (actual time=0.078..55.854 rows=100007 loops=1)

              Heap Fetches: 100007

        ->  Index Only Scan using the_index on sessions s_end  (cost=0.42..5.46 rows=222 width=24) (actual time=0.003..0.004 rows=3 loops=100007)

              Index Cond: ((user_id = s_start.user_id) AND (login_time >= s_start.login_time) AND (login_time <= (s_start.login_time + '02:00:00'::interval)))

              Heap Fetches: 285663

Planning Time: 0.474 ms

Execution Time: 875.388 ms



EXPLAIN


--Depesz' improvement
create function array_agg_unique( INOUT p_state int8[], IN p_newval int8 ) returns int8[] as $$
    select case
        when p_newval = any(p_state) then p_state
        else p_state || p_newval
    end;
$$ language sql;
 
create aggregate array_agg_unique( int8 ) (
    sfunc = array_agg_unique,
    stype = int8[],
    initcond = '{}'
);

explain analyze
WITH base as (
    SELECT
        s.user_id,
        s.login_time,
        array_agg_unique(s.country_id)over w1 as countries
    FROM
        sessions s
    WINDOW w1 AS(partition by user_id 
                 order by login_time 
                 range between current row 
                           and '2 hours'::interval following)
)
select *
from base
where array_upper(countries, 1) > 1;

CREATE FUNCTION



CREATE AGGREGATE




QUERY PLAN


Subquery Scan on base  (cost=0.42..9098.66 rows=33336 width=48) (actual time=0.408..1421.285 rows=6460 loops=1)

  Filter: (array_upper(base.countries, 1) > 1)

  Rows Removed by Filter: 93547

  ->  WindowAgg  (cost=0.42..7598.56 rows=100007 width=48) (actual time=0.121..1395.433 rows=100007 loops=1)

        ->  Index Only Scan using the_index on sessions s  (cost=0.42..5848.44 rows=100007 width=24) (actual time=0.034..121.089 rows=100007 loops=1)

              Heap Fetches: 100007

Planning Time: 0.113 ms

Execution Time: 1421.790 ms



EXPLAIN


--another uniqueness check with built-ins
explain analyze
WITH base as (
    SELECT
        s.user_id,
        s.login_time,
        array_agg(s.country_id)over w1 as countries
    FROM
        sessions s
    WINDOW w1 AS(partition by user_id 
                 order by login_time 
                 range between current row 
                           and '2 hours'::interval following)
)
select user_id,login_time,array(select distinct unnest(countries))
from base
where array_length(array_remove(countries, countries[1]),1)<>0;


QUERY PLAN


Subquery Scan on base  (cost=0.42..28443.54 rows=99507 width=48) (actual time=0.117..231.950 rows=6460 loops=1)

  Filter: (array_length(array_remove(base.countries, base.countries[1]), 1) <> 0)

  Rows Removed by Filter: 93547

  ->  WindowAgg  (cost=0.42..5050.64 rows=100007 width=48) (actual time=0.049..177.381 rows=100007 loops=1)

        ->  Index Only Scan using the_index on sessions s  (cost=0.42..3300.52 rows=100007 width=24) (actual time=0.038..16.889 rows=100007 loops=1)

              Heap Fetches: 0

  SubPlan 1

    ->  HashAggregate  (cost=0.09..0.22 rows=10 width=8) (actual time=0.002..0.003 rows=2 loops=6460)

          Group Key: unnest(base.countries)

          Batches: 1  Memory Usage: 24kB

          ->  ProjectSet  (cost=0.00..0.07 rows=10 width=8) (actual time=0.001..0.001 rows=4 loops=6460)

                ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=6460)

Planning Time: 0.225 ms

Execution Time: 232.485 ms



EXPLAIN


--another uniqueness check with built-ins
explain analyze
WITH base as (
    SELECT
        s.user_id,
        s.login_time,
        array_agg(s.country_id)over w1 as countries
    FROM
        sessions s
    WINDOW w1 AS(partition by user_id 
                 order by login_time 
                 range between current row 
                           and '2 hours'::interval following)
),cte2 as(
  select user_id
        ,login_time
        ,array(select distinct unnest(countries)) as countries
  from base)
select *
from base
where array_length(countries,1)<>0;


QUERY PLAN


CTE Scan on base  (cost=5050.64..7550.82 rows=99507 width=48) (actual time=0.027..246.309 rows=100007 loops=1)

  Filter: (array_length(countries, 1) <> 0)

  CTE base

    ->  WindowAgg  (cost=0.42..5050.64 rows=100007 width=48) (actual time=0.025..181.617 rows=100007 loops=1)

          ->  Index Only Scan using the_index on sessions s  (cost=0.42..3300.52 rows=100007 width=24) (actual time=0.014..17.067 rows=100007 loops=1)

                Heap Fetches: 0

Planning Time: 0.106 ms

Execution Time: 252.721 ms



EXPLAIN


--no CTE, as a curiosity
explain analyze
SELECT
    s.user_id,
    s.login_time,
    array_agg(s.country_id)over w1 as countries
FROM
    sessions s
WINDOW w1 AS(partition by user_id 
             order by login_time 
             range between current row 
                       and '2 hours'::interval following)
ORDER BY array_length(array_remove(array_agg(s.country_id)over w1, (array_agg(s.country_id)over w1)[1]),1)<>0
FETCH FIRST 1 ROWS WITH TIES


QUERY PLAN


Limit  (cost=6300.73..6300.73 rows=1 width=49) (actual time=236.010..237.897 rows=6460 loops=1)

  ->  Sort  (cost=6300.73..6550.75 rows=100007 width=49) (actual time=236.008..237.067 rows=6461 loops=1)

        Sort Key: ((array_length(array_remove(array_agg(country_id) OVER (?), (array_agg(country_id) OVER (?))[1]), 1) <> 0))

        Sort Method: external merge  Disk: 6856kB

        ->  WindowAgg  (cost=0.42..5800.70 rows=100007 width=49) (actual time=0.024..197.483 rows=100007 loops=1)

              ->  Index Only Scan using the_index on sessions s  (cost=0.42..3300.52 rows=100007 width=24) (actual time=0.011..17.039 rows=100007 loops=1)

                    Heap Fetches: 0

Planning Time: 0.144 ms

Execution Time: 239.046 ms



EXPLAIN


create extension if not exists intarray;
alter table sessions alter column user_id type int4
                    ,alter column country_id type int4;
explain analyze
WITH base as (
    SELECT
        s.user_id,
        s.login_time,
        uniq(sort(array_agg(s.country_id)over w1)) as countries
    FROM
        sessions s
    WINDOW w1 AS(partition by user_id 
                 order by login_time 
                 range between current row 
                           and '2 hours'::interval following)
)
select *
from base
where #countries <> 1;

CREATE EXTENSION



ALTER TABLE




QUERY PLAN


Subquery Scan on base  (cost=0.42..9598.78 rows=99507 width=44) (actual time=0.193..305.372 rows=6460 loops=1)

  Filter: ((# base.countries) <> 1)

  Rows Removed by Filter: 93547

  ->  WindowAgg  (cost=0.42..8098.68 rows=100007 width=44) (actual time=0.089..295.530 rows=100007 loops=1)

        ->  Index Only Scan using the_index on sessions s  (cost=0.42..5848.52 rows=100007 width=16) (actual time=0.068..97.115 rows=100007 loops=1)

              Heap Fetches: 100007

Planning Time: 0.224 ms

Execution Time: 306.462 ms



EXPLAIN


explain analyze
WITH base as (
    SELECT
        s.user_id,
        s.login_time,
        sort(array_agg(s.country_id)over w1) as countries
    FROM
        sessions s
    WINDOW w1 AS(partition by user_id 
                 order by login_time 
                 range between current row 
                           and '2 hours'::interval following)
)
select *
from base
where countries[array_lower(countries,1)]<>countries[array_upper(countries,1)];


QUERY PLAN


Subquery Scan on base  (cost=0.42..9598.78 rows=99507 width=44) (actual time=0.161..302.295 rows=6460 loops=1)

  Filter: (base.countries[array_lower(base.countries, 1)] <> base.countries[array_upper(base.countries, 1)])

  Rows Removed by Filter: 93547

  ->  WindowAgg  (cost=0.42..7848.66 rows=100007 width=44) (actual time=0.074..280.181 rows=100007 loops=1)

        ->  Index Only Scan using the_index on sessions s  (cost=0.42..5848.52 rows=100007 width=16) (actual time=0.055..96.196 rows=100007 loops=1)

              Heap Fetches: 100007

Planning Time: 0.228 ms

Execution Time: 302.795 ms



EXPLAIN


--non window aggregate calls support internal sorting, which would aid the outer check
explain analyze
WITH base as (
    SELECT
        s.user_id,
        s.login_time,
        array_agg(s.country_id order by s.country_id)over w1 as countries
    FROM
        sessions s
    WINDOW w1 AS(partition by user_id 
                 order by login_time 
                 range between current row 
                           and '2 hours'::interval following)
)
select *
from base
where countries[array_lower(countries,1)]<>countries[array_upper(countries,1)];

ERROR:  aggregate ORDER BY is not implemented for window functions
LINE 7:         array_agg(s.country_id order by s.country_id)over w1...
                ^