Postgres 17

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


--basic
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
    count(distinct s_end.country_id) > 1;


QUERY PLAN


GroupAggregate  (cost=17.27..2146647.51 rows=33304 width=48) (actual time=288.092..919.396 rows=6460 loops=1)

  Group Key: s_start.user_id, s_start.login_time

  Filter: (count(DISTINCT s_end.country_id) > 1)

  Rows Removed by Filter: 93547

  ->  Incremental Sort  (cost=17.27..1922481.97 rows=22266685 width=24) (actual time=287.963..830.710 rows=285663 loops=1)

        Sort Key: s_start.user_id, s_start.login_time, s_end.country_id

        Presorted Key: s_start.user_id, s_start.login_time

        Full-sort Groups: 8590  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB

        ->  Nested Loop  (cost=0.84..773749.58 rows=22266685 width=24) (actual time=287.862..744.751 rows=285663 loops=1)

              ->  Index Only Scan using the_index on sessions s_start  (cost=0.42..5848.13 rows=100007 width=16) (actual time=0.109..47.813 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.002..0.003 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.739 ms

JIT:

  Functions: 11

  Options: Inlining true, Optimization true, Expressions true, Deforming true

  Timing: Generation 1.093 ms (Deform 0.148 ms), Inlining 126.435 ms, Optimization 93.630 ms, Emission 67.650 ms, Total 288.807 ms

Execution Time: 1107.247 ms



EXPLAIN


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=17.27..2090731.02 rows=99413 width=48) (actual time=146.734..775.391 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

  ->  Incremental Sort  (cost=17.27..1922481.97 rows=22266685 width=24) (actual time=146.610..683.145 rows=285663 loops=1)

        Sort Key: s_start.user_id, s_start.login_time, s_end.country_id

        Presorted Key: s_start.user_id, s_start.login_time

        Full-sort Groups: 8590  Sort Method: quicksort  Average Memory: 26kB  Peak Memory: 26kB

        ->  Nested Loop  (cost=0.84..773749.58 rows=22266685 width=24) (actual time=146.515..594.743 rows=285663 loops=1)

              ->  Index Only Scan using the_index on sessions s_start  (cost=0.42..5848.13 rows=100007 width=16) (actual time=0.016..45.227 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.002..0.003 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.246 ms

JIT:

  Functions: 11

  Options: Inlining true, Optimization true, Expressions true, Deforming true

  Timing: Generation 0.740 ms (Deform 0.123 ms), Inlining 23.332 ms, Optimization 76.295 ms, Emission 46.836 ms, Total 147.204 ms

Execution Time: 776.746 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=51.17..9098.36 rows=33336 width=48) (actual time=0.794..1221.964 rows=6460 loops=1)

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

  Rows Removed by Filter: 93547

  ->  WindowAgg  (cost=51.17..7598.26 rows=100007 width=48) (actual time=0.513..1201.419 rows=100007 loops=1)

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

              Heap Fetches: 100007

Planning Time: 0.103 ms

Execution Time: 1222.449 ms



EXPLAIN


--uniqueness check without deduplication and/or custom aggregates
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 *
from base
where array_length(countries, 1)<>array_length(array_positions(countries,countries[1]),1);


QUERY PLAN


Subquery Scan on base  (cost=51.17..9598.40 rows=99507 width=48) (actual time=0.097..274.169 rows=6460 loops=1)

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

  Rows Removed by Filter: 93547

  ->  WindowAgg  (cost=51.17..7598.26 rows=100007 width=48) (actual time=0.027..221.698 rows=100007 loops=1)

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

              Heap Fetches: 100007

Planning Time: 0.109 ms

Execution Time: 274.562 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 *
from base
where array_length(array_remove(countries, countries[1]),1)<>0;


QUERY PLAN


Subquery Scan on base  (cost=51.17..9348.38 rows=99507 width=48) (actual time=0.103..253.098 rows=6460 loops=1)

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

  Rows Removed by Filter: 93547

  ->  WindowAgg  (cost=51.17..7598.26 rows=100007 width=48) (actual time=0.025..220.751 rows=100007 loops=1)

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

              Heap Fetches: 100007

Planning Time: 0.109 ms

Execution Time: 253.480 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

ERROR:  could not write to file "base/pgsql_tmp/pgsql_tmp786.1": No space left on device


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



ERROR:  could not extend file "base/5/16516": wrote only 4096 of 8192 bytes at block 73
HINT:  Check free disk space.


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)];

ERROR:  function sort(bigint[]) does not exist
LINE 6:         sort(array_agg(s.country_id)over w1) as countries
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


--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...
                ^