Postgres 16

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.26..2145802.22 rows=33323 width=48) (actual time=428.183..1078.468 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.26..1921746.02 rows=22255666 width=24) (actual time=428.048..982.265 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.42 rows=22255666 width=24) (actual time=427.947..891.216 rows=285663 loops=1)

              ->  Index Only Scan using the_index on sessions s_start  (cost=0.42..5847.97 rows=100007 width=16) (actual time=0.109..51.134 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 0.964 ms, Inlining 154.129 ms, Optimization 204.112 ms, Emission 69.561 ms, Total 428.766 ms

Execution Time: 1238.271 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.26..2089913.13 rows=99469 width=48) (actual time=142.050..779.105 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.26..1921746.02 rows=22255666 width=24) (actual time=141.895..686.837 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.42 rows=22255666 width=24) (actual time=141.799..596.245 rows=285663 loops=1)

              ->  Index Only Scan using the_index on sessions s_start  (cost=0.42..5847.97 rows=100007 width=16) (actual time=0.015..49.179 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.244 ms

JIT:

  Functions: 11

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

  Timing: Generation 0.723 ms, Inlining 21.249 ms, Optimization 73.864 ms, Emission 46.641 ms, Total 142.477 ms

Execution Time: 780.426 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.20 rows=33336 width=48) (actual time=0.794..1279.267 rows=6460 loops=1)

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

  Rows Removed by Filter: 93547

  ->  WindowAgg  (cost=0.42..7598.09 rows=100007 width=48) (actual time=0.502..1260.720 rows=100007 loops=1)

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

              Heap Fetches: 100007

Planning Time: 0.092 ms

Execution Time: 1279.743 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=0.42..9598.23 rows=99507 width=48) (actual time=0.100..288.760 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=0.42..7598.09 rows=100007 width=48) (actual time=0.024..235.496 rows=100007 loops=1)

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

              Heap Fetches: 100007

Planning Time: 0.104 ms

Execution Time: 289.116 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=0.42..9348.22 rows=99507 width=48) (actual time=0.103..267.433 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..7598.09 rows=100007 width=48) (actual time=0.022..236.283 rows=100007 loops=1)

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

              Heap Fetches: 100007

Planning Time: 0.102 ms

Execution Time: 267.809 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=8848.18..8848.18 rows=1 width=49) (actual time=334.260..336.203 rows=6460 loops=1)

  ->  Sort  (cost=8848.18..9098.20 rows=100007 width=49) (actual time=334.257..335.323 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..8348.15 rows=100007 width=49) (actual time=0.022..277.976 rows=100007 loops=1)

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

                    Heap Fetches: 100007

Planning Time: 0.128 ms

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



ERROR:  could not extend file "base/5/16516": No space left on device
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...
                ^