Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
--https://stackoverflow.com/q/74624610/5298879
create table userinteractions(
    userid       bigint, 
    dobyr        int, 
    other_column text);

insert into userinteractions values
  (1,1995,'a'),--saved for deduplication: repeated unique combination
  (1,1995,'a'),--saved for deduplication: repeated unique combination
  (2,1999,'a'),--saved for deduplication: true unique combination
  (3,1990,'a'),--discarded: different dobyr for the same userid
  (3,1999,'a'),--discarded: different dobyr for the same userid
  (4,1989,'a'),--saved for deduplication: repeated unique (userid,dobyr) combination
  (4,1989,'b');--saved for deduplication: repeated unique (userid,dobyr) combination

CREATE TABLE



INSERT 0 7


select distinct on (userid,dobyr) * 
from userinteractions u1
where not exists (
    select true
    from userinteractions u2
    where u1.userid=u2.userid 
    and u1.dobyr<>u2.dobyr)
order by userid,dobyr;


userid
dobyr
other_column


1
1995
a

2
1999
a

4
1989
a



SELECT 3


select distinct on (userid,dobyr) * 
from userinteractions
where userid in ( 
    select userid
    from userinteractions
    group by userid
    having count(distinct dobyr)=1 )
order by userid,dobyr;


userid
dobyr
other_column


1
1995
a

2
1999
a

4
1989
a



SELECT 3


--testing on more rows

--10000 rows:100 userids and 100 different dobyrs per userid --all will be discarded
insert into userinteractions 
select 
    a.id,
    b.id,--100 different dobyrs per userid
    'a'
from generate_series(1,100,1) a(id),
     generate_series(1,100,1) b(id);

--10000 rows:10000 new userids with same dobyr --all should be kept
insert into userinteractions 
select 
    a.id,--1000
    a.id,
    'a'
from generate_series(101,10100,1) a(id);

--10000 rows:10000 new userids with same dobyr --all should be kept
insert into userinteractions 
select 
    a.id,--1000
    a.id+1e4,
    'a'
from generate_series(10101,20100,1) a(id);
     
insert into userinteractions 
  select * from userinteractions
  where userid>=10101;--duplicating the uniques a few times
insert into userinteractions 
  select * from userinteractions
  where userid>=10101;--duplicating the uniques a few times
insert into userinteractions 
  select * from userinteractions
  where userid>=10101;--duplicating the uniques a few times

--adding another 1000 random rows that will alter the uniqueness a bit
insert into userinteractions 
select  (random()*2000)::int,
        (random()*400)::int,
        'a'
from generate_series(1,1e3,1);

INSERT 0 10000



INSERT 0 10000



INSERT 0 10000



INSERT 0 10000



INSERT 0 20000



INSERT 0 40000



INSERT 0 1000


--deduplicated table uniqueness
with counts as (
select count(distinct (userid,dobyr)) distincts, 
       count(distinct (userid,dobyr)) filter (where userid in (select userid
    from userinteractions
    group by userid
    having count(distinct dobyr)=1 )) true_distincts,
       count(*) total from userinteractions)
select 
  distincts,
  true_distincts,
  total, 
  round((distincts::numeric/total*100),2) percent_unique,
  round((true_distincts::numeric/total*100),2) percent_true_unique
from counts;


distincts
true_distincts
total
percent_unique
percent_true_unique


30987
19260
101007
30.68
19.07



SELECT 1


--Disabling parallelism to simplify plans. Enable and maximize it for performance.
prepare show_worker_settings as select 
  name,
  setting,
  short_desc 
from pg_settings 
  where name in 
  ('max_worker_processes',
  'max_parallel_workers',
  'max_parallel_workers_per_gather');

execute show_worker_settings;
set max_parallel_workers=0;
set max_parallel_workers_per_gather=0;
execute show_worker_settings;

PREPARE




name
setting
short_desc


max_parallel_workers
8
Sets the maximum number of parallel workers that can be active at one time.

max_parallel_workers_per_gather
4
Sets the maximum number of parallel processes per executor node.

max_worker_processes
8
Maximum number of concurrent worker processes.



SELECT 3



SET



SET




name
setting
short_desc


max_parallel_workers
0
Sets the maximum number of parallel workers that can be active at one time.

max_parallel_workers_per_gather
0
Sets the maximum number of parallel processes per executor node.

max_worker_processes
8
Maximum number of concurrent worker processes.



SELECT 3


--just the whitelist - notice the outermost `rows=` that remains the same later
--`dobyr` is redundant for unique identification of whitelisted rows
explain analyze verbose
select   userid
from     userinteractions
group by userid
having   count(distinct dobyr)=1


QUERY PLAN


GroupAggregate  (cost=6071.96..6537.20 rows=1 width=8) (actual time=220.916..416.078 rows=19260 loops=1)

  Output: userid

  Group Key: userinteractions.userid

  Filter: (count(DISTINCT userinteractions.dobyr) = 1)

  Rows Removed by Filter: 840

  ->  Sort  (cost=6071.96..6226.21 rows=61698 width=12) (actual time=170.040..288.122 rows=101007 loops=1)

        Output: userid, dobyr

        Sort Key: userinteractions.userid

        Sort Method: external merge  Disk: 2184kB

        ->  Seq Scan on public.userinteractions  (cost=0.00..1162.98 rows=61698 width=12) (actual time=0.016..13.859 rows=101007 loops=1)

              Output: userid, dobyr

Planning Time: 0.074 ms

Execution Time: 417.359 ms



EXPLAIN


--all the fields 
explain analyze verbose
create table userinteractions_clean as 
select distinct on (userid,dobyr) * 
from userinteractions
where userid in ( 
    select userid
    from userinteractions
    group by userid
    having count(distinct dobyr)=1 )
order by userid,dobyr;


QUERY PLAN


Unique  (cost=7878.31..7880.62 rows=308 width=44) (actual time=765.446..926.071 rows=19260 loops=1)

  Output: userinteractions.userid, userinteractions.dobyr, userinteractions.other_column

  ->  Sort  (cost=7878.31..7879.08 rows=308 width=44) (actual time=765.443..869.597 rows=89260 loops=1)

        Output: userinteractions.userid, userinteractions.dobyr, userinteractions.other_column

        Sort Key: userinteractions.userid, userinteractions.dobyr

        Sort Method: external merge  Disk: 2104kB

        ->  Hash Join  (cost=6537.22..7865.58 rows=308 width=44) (actual time=471.463..644.451 rows=89260 loops=1)

              Output: userinteractions.userid, userinteractions.dobyr, userinteractions.other_column

              Inner Unique: true

              Hash Cond: (userinteractions.userid = userinteractions_1.userid)

              ->  Seq Scan on public.userinteractions  (cost=0.00..1162.98 rows=61698 width=44) (actual time=0.014..11.068 rows=101007 loops=1)

                    Output: userinteractions.userid, userinteractions.dobyr, userinteractions.other_column

              ->  Hash  (cost=6537.21..6537.21 rows=1 width=8) (actual time=469.524..469.527 rows=19260 loops=1)

                    Output: userinteractions_1.userid

                    Buckets: 32768 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 1009kB

                    ->  GroupAggregate  (cost=6071.96..6537.20 rows=1 width=8) (actual time=183.203..406.851 rows=19260 loops=1)

                          Output: userinteractions_1.userid

                          Group Key: userinteractions_1.userid

                          Filter: (count(DISTINCT userinteractions_1.dobyr) = 1)

                          Rows Removed by Filter: 840

                          ->  Sort  (cost=6071.96..6226.21 rows=61698 width=12) (actual time=180.362..238.019 rows=101007 loops=1)

                                Output: userinteractions_1.userid, userinteractions_1.dobyr

                                Sort Key: userinteractions_1.userid

                                Sort Method: external merge  Disk: 2184kB

                                ->  Seq Scan on public.userinteractions userinteractions_1  (cost=0.00..1162.98 rows=61698 width=12) (actual time=0.010..13.692 rows=101007 loops=1)

                                      Output: userinteractions_1.userid, userinteractions_1.dobyr

Planning Time: 0.151 ms

Execution Time: 1011.016 ms



EXPLAIN


explain analyze verbose
create table userinteractions_clean2 as 
select distinct on (userid,dobyr) * 
from userinteractions u1
where not exists ( 
    select true
    from userinteractions u2
    where u1.userid=u2.userid 
    and u1.dobyr<>u2.dobyr)
order by userid,dobyr;


QUERY PLAN


Unique  (cost=6712.37..6943.74 rows=6170 width=44) (actual time=908.281..1030.476 rows=19260 loops=1)

  Output: u1.userid, u1.dobyr, u1.other_column

  ->  Sort  (cost=6712.37..6789.49 rows=30849 width=44) (actual time=908.279..974.752 rows=89260 loops=1)

        Output: u1.userid, u1.dobyr, u1.other_column

        Sort Key: u1.userid, u1.dobyr

        Sort Method: external merge  Disk: 2104kB

        ->  Hash Anti Join  (cost=1934.20..4412.12 rows=30849 width=44) (actual time=160.301..832.780 rows=89260 loops=1)

              Output: u1.userid, u1.dobyr, u1.other_column

              Hash Cond: (u1.userid = u2.userid)

              Join Filter: (u1.dobyr <> u2.dobyr)

              Rows Removed by Join Filter: 650100

              ->  Seq Scan on public.userinteractions u1  (cost=0.00..1162.98 rows=61698 width=44) (actual time=0.016..56.614 rows=101007 loops=1)

                    Output: u1.userid, u1.dobyr, u1.other_column

              ->  Hash  (cost=1162.98..1162.98 rows=61698 width=12) (actual time=112.364..112.365 rows=101007 loops=1)

                    Output: u2.userid, u2.dobyr

                    Buckets: 131072 (originally 65536)  Batches: 1 (originally 1)  Memory Usage: 5365kB

                    ->  Seq Scan on public.userinteractions u2  (cost=0.00..1162.98 rows=61698 width=12) (actual time=0.014..13.647 rows=101007 loops=1)

                          Output: u2.userid, u2.dobyr

Planning Time: 0.113 ms

Execution Time: 1114.743 ms



EXPLAIN


/*
explain analyze verbose
create table userinteractions_clean3 as 
select distinct on (userid,dobyr) * 
from userinteractions u1
where exists ( 
    select true
    from userinteractions u2
    where u1.userid=u2.userid
    group by userid
    having count(distinct dobyr)=1 )
order by userid,dobyr;
*/
/*
explain analyze verbose
create table userinteractions_clean3 as 
select distinct on (userid,dobyr) * 
from userinteractions u1
where (select count(distinct dobyr)=1
       from userinteractions u2
       where u1.userid=u2.userid)
order by userid,dobyr;
*/
name	setting	short_desc
max_parallel_workers	8	Sets the maximum number of parallel workers that can be active at one time.
max_parallel_workers_per_gather	4	Sets the maximum number of parallel processes per executor node.
max_worker_processes	8	Maximum number of concurrent worker processes.