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


--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=0.84..997850.53 rows=33292 width=48) (actual time=0.310..972.546 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

  ->  Nested Loop  (cost=0.84..773749.76 rows=22260264 width=24) (actual time=0.063..613.871 rows=285663 loops=1)

        ->  Index Only Scan using the_index on sessions s_start  (cost=0.42..5848.32 rows=100007 width=16) (actual time=0.050..62.815 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.005 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.504 ms

Execution Time: 973.456 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=0.84..941950.18 rows=99376 width=48) (actual time=0.257..864.268 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.76 rows=22260264 width=24) (actual time=0.023..587.016 rows=285663 loops=1)

        ->  Index Only Scan using the_index on sessions s_start  (cost=0.42..5848.32 rows=100007 width=16) (actual time=0.014..56.657 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.240 ms

Execution Time: 865.098 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.54 rows=33336 width=48) (actual time=0.407..1461.144 rows=6460 loops=1)

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

  Rows Removed by Filter: 93547

  ->  WindowAgg  (cost=0.42..7598.44 rows=100007 width=48) (actual time=0.100..1431.515 rows=100007 loops=1)

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

              Heap Fetches: 100007

Planning Time: 0.107 ms

Execution Time: 1461.689 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.58 rows=99507 width=48) (actual time=0.136..316.096 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.44 rows=100007 width=48) (actual time=0.027..261.979 rows=100007 loops=1)

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

              Heap Fetches: 100007

Planning Time: 0.122 ms

Execution Time: 316.617 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.56 rows=99507 width=48) (actual time=0.093..293.097 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.44 rows=100007 width=48) (actual time=0.024..263.002 rows=100007 loops=1)

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

              Heap Fetches: 100007

Planning Time: 0.125 ms

Execution Time: 293.603 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.53..8848.53 rows=1 width=49) (actual time=333.852..335.817 rows=6460 loops=1)

  ->  Sort  (cost=8848.53..9098.54 rows=100007 width=49) (actual time=333.850..334.941 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.49 rows=100007 width=49) (actual time=0.023..293.388 rows=100007 loops=1)

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

                    Heap Fetches: 100007

Planning Time: 0.151 ms

Execution Time: 337.053 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.181..309.978 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.082..299.116 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.060..93.868 rows=100007 loops=1)

              Heap Fetches: 100007

Planning Time: 0.231 ms

Execution Time: 311.050 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.141..325.098 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.056..300.463 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.034..95.810 rows=100007 loops=1)

              Heap Fetches: 100007

Planning Time: 0.279 ms

Execution Time: 325.734 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...
                ^