Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--https://stackoverflow.com/q/78046665/5298879
select setseed(.42);--stabilise randomised samples between tests

create table your_table (id int not null, eventdate date);
insert into your_table 
select 1+random()*1e4,
       now()-random()*interval '64 years'
from generate_series(1,5e5)_(n);
/*
ID | eventdate
1 | 28/12/2023
2 | 27/08/2024
3 | 12/05/2022
*/
select * from your_table tablesample bernoulli(.42)repeatable(.42)limit 10;
--create index ix_eventdate_include_id on your_table(eventdate)include(id);
create index ix_id_eventdate on your_table(id,eventdate);
create index ix_eventdate_id on your_table(eventdate,id);

setseed

SELECT 1

CREATE TABLE

INSERT 0 500000

id
eventdate

8020
1991-11-18

2847
1979-03-21

3720
2014-09-30

7
2021-01-31

9522
2007-11-22

2687
1992-02-14

4058
2010-11-30

8577
1966-01-28

9408
1977-12-11

1209
1991-08-02

SELECT 10

CREATE INDEX

CREATE INDEX

vacuum analyze your_table;

VACUUM

prepare not_in as 
select count(distinct id)  --"I need to know how many IDs"
from your_table
where id not in (          --"do not have a date record"
  select id                
  from your_table
  where eventdate>'01/01/2022');--"recorded after 01/01/2022"

explain analyze verbose execute not_in;

execute not_in;

PREPARE

QUERY PLAN

Aggregate  (cost=15383.70..15383.72 rows=1 width=8) (actual time=123.789..123.797 rows=1 loops=1)

  Output: count(DISTINCT your_table.id)

  ->  Index Only Scan using ix_id_eventdate on public.your_table  (cost=512.71..14758.70 rows=250000 width=4) (actual time=4.815..118.484 rows=93329 loops=1)

        Output: your_table.id, your_table.eventdate

        Filter: (NOT (hashed SubPlan 1))

        Rows Removed by Filter: 406671

        Heap Fetches: 0

        SubPlan 1

          ->  Index Only Scan using ix_eventdate_id on public.your_table your_table_1  (cost=0.42..471.30 rows=16393 width=4) (actual time=0.036..2.348 rows=16607 loops=1)

                Output: your_table_1.id

                Index Cond: (your_table_1.eventdate > '2022-01-01'::date)

                Heap Fetches: 0

Planning Time: 0.421 ms

Execution Time: 123.902 ms

EXPLAIN

count

1936

SELECT 1

prepare subtraction as
select (select count(distinct id) from your_table)
      -(select count(distinct id) from your_table where eventdate>'01/01/2022') "count";

explain analyze verbose execute subtraction;

execute subtraction;

PREPARE

QUERY PLAN

Result  (cost=15947.28..15947.29 rows=1 width=8) (actual time=82.462..82.464 rows=1 loops=1)

  Output: ($0 - $1)

  InitPlan 1 (returns $0)

    ->  Aggregate  (cost=14246.42..14246.43 rows=1 width=8) (actual time=76.679..76.680 rows=1 loops=1)

          Output: count(DISTINCT your_table.id)

          ->  Index Only Scan using ix_id_eventdate on public.your_table  (cost=0.42..12996.42 rows=500000 width=4) (actual time=0.014..50.352 rows=500000 loops=1)

                Output: your_table.id, your_table.eventdate

                Heap Fetches: 0

  InitPlan 2 (returns $1)

    ->  Aggregate  (cost=1700.84..1700.85 rows=1 width=8) (actual time=5.774..5.775 rows=1 loops=1)

          Output: count(DISTINCT your_table_1.id)

          ->  Sort  (cost=1618.87..1659.86 rows=16393 width=4) (actual time=4.005..4.739 rows=16607 loops=1)

                Output: your_table_1.id

                Sort Key: your_table_1.id

                Sort Method: quicksort  Memory: 769kB

                ->  Index Only Scan using ix_eventdate_id on public.your_table your_table_1  (cost=0.42..471.30 rows=16393 width=4) (actual time=0.016..1.960 rows=16607 loops=1)

                      Output: your_table_1.id

                      Index Cond: (your_table_1.eventdate > '2022-01-01'::date)

                      Heap Fetches: 0

Planning Time: 0.371 ms

Execution Time: 82.509 ms

EXPLAIN

count

1936

SELECT 1

prepare anti_join as 
select count(distinct a.id)  --"I need to know how many IDs"
from your_table a left join your_table b
  on a.id=b.id and b.eventdate>'01/01/2022'
where b.id is null;--anti-join

explain analyze verbose execute anti_join;

execute anti_join;

PREPARE

QUERY PLAN

Aggregate  (cost=15381.81..15381.82 rows=1 width=8) (actual time=159.562..166.036 rows=1 loops=1)

  Output: count(DISTINCT a.id)

  ->  Gather Merge  (cost=2619.34..15381.81 rows=1 width=4) (actual time=36.370..161.086 rows=93329 loops=1)

        Output: a.id

        Workers Planned: 3

        Workers Launched: 3

        ->  Merge Anti Join  (cost=1619.30..14381.65 rows=1 width=4) (actual time=13.655..40.369 rows=23332 loops=4)

              Output: a.id

              Merge Cond: (a.id = b.id)

              Worker 0:  actual time=10.165..39.711 rows=28425 loops=1

              Worker 1:  actual time=20.438..52.067 rows=31418 loops=1

              Worker 2:  actual time=4.379..43.891 rows=29193 loops=1

              ->  Parallel Index Only Scan using ix_id_eventdate on public.your_table a  (cost=0.42..9609.33 rows=161290 width=4) (actual time=0.032..15.283 rows=125000 loops=4)

                    Output: a.id, a.eventdate

                    Heap Fetches: 0

                    Worker 0:  actual time=0.030..16.433 rows=154129 loops=1

                    Worker 1:  actual time=0.051..17.180 rows=161406 loops=1

                    Worker 2:  actual time=0.032..24.928 rows=161041 loops=1

              ->  Sort  (cost=1618.87..1659.86 rows=16393 width=4) (actual time=13.590..14.474 rows=16548 loops=4)

                    Output: b.id

                    Sort Key: b.id

                    Sort Method: quicksort  Memory: 769kB

                    Worker 0:  actual time=10.079..10.963 rows=16607 loops=1

                      Sort Method: quicksort  Memory: 769kB

                    Worker 1:  actual time=20.381..21.265 rows=16556 loops=1

                      Sort Method: quicksort  Memory: 769kB

                    Worker 2:  actual time=4.297..5.185 rows=16467 loops=1

                      Sort Method: quicksort  Memory: 769kB

                    ->  Index Only Scan using ix_eventdate_id on public.your_table b  (cost=0.42..471.30 rows=16393 width=4) (actual time=0.013..2.011 rows=16607 loops=4)

                          Output: b.id

                          Index Cond: (b.eventdate > '2022-01-01'::date)

                          Heap Fetches: 0

                          Worker 0:  actual time=0.015..1.985 rows=16607 loops=1

                          Worker 1:  actual time=0.014..2.047 rows=16607 loops=1

                          Worker 2:  actual time=0.015..2.020 rows=16607 loops=1

Planning Time: 0.379 ms

Execution Time: 166.089 ms

EXPLAIN

count

1936

SELECT 1

prepare not_exists as 
select count(distinct id) from your_table a
where not exists (
  select from your_table b 
  where eventdate>'01/01/2022' 
  and a.id=b.id);

explain analyze verbose execute not_exists;

execute not_exists;

PREPARE

QUERY PLAN

Aggregate  (cost=15381.81..15381.82 rows=1 width=8) (actual time=159.456..162.567 rows=1 loops=1)

  Output: count(DISTINCT a.id)

  ->  Gather Merge  (cost=2619.34..15381.81 rows=1 width=4) (actual time=27.917..157.426 rows=93329 loops=1)

        Output: a.id

        Workers Planned: 3

        Workers Launched: 3

        ->  Merge Anti Join  (cost=1619.30..14381.65 rows=1 width=4) (actual time=15.789..40.449 rows=23332 loops=4)

              Output: a.id

              Merge Cond: (a.id = b.id)

              Worker 0:  actual time=23.056..53.229 rows=28687 loops=1

              Worker 1:  actual time=15.637..47.487 rows=31249 loops=1

              Worker 2:  actual time=8.384..38.798 rows=29434 loops=1

              ->  Parallel Index Only Scan using ix_id_eventdate on public.your_table a  (cost=0.42..9609.33 rows=161290 width=4) (actual time=0.028..13.660 rows=125000 loops=4)

                    Output: a.id, a.eventdate

                    Heap Fetches: 0

                    Worker 0:  actual time=0.034..16.774 rows=158112 loops=1

                    Worker 1:  actual time=0.028..18.314 rows=158844 loops=1

                    Worker 2:  actual time=0.038..16.924 rows=159254 loops=1

              ->  Sort  (cost=1618.87..1659.86 rows=16393 width=4) (actual time=15.731..16.601 rows=16507 loops=4)

                    Output: b.id

                    Sort Key: b.id

                    Sort Method: quicksort  Memory: 769kB

                    Worker 0:  actual time=23.008..23.879 rows=16473 loops=1

                      Sort Method: quicksort  Memory: 769kB

                    Worker 1:  actual time=15.560..16.448 rows=16564 loops=1

                      Sort Method: quicksort  Memory: 769kB

                    Worker 2:  actual time=8.293..9.180 rows=16607 loops=1

                      Sort Method: quicksort  Memory: 769kB

                    ->  Index Only Scan using ix_eventdate_id on public.your_table b  (cost=0.42..471.30 rows=16393 width=4) (actual time=0.019..8.587 rows=16607 loops=4)

                          Output: b.id

                          Index Cond: (b.eventdate > '2022-01-01'::date)

                          Heap Fetches: 0

                          Worker 0:  actual time=0.016..13.042 rows=16607 loops=1

                          Worker 1:  actual time=0.014..13.307 rows=16607 loops=1

                          Worker 2:  actual time=0.036..6.039 rows=16607 loops=1

Planning Time: 0.239 ms

Execution Time: 162.603 ms

EXPLAIN

count

1936

SELECT 1

prepare except_ as 
select count(*) from
(  select id from your_table
   except
   select id from your_table where eventdate>'01/01/2022');

explain analyze verbose execute except_;

execute except_;

PREPARE

QUERY PLAN

Aggregate  (cost=16845.58..16845.59 rows=1 width=8) (actual time=206.867..206.870 rows=1 loops=1)

  Output: count(*)

  ->  Subquery Scan on unnamed_subquery  (cost=0.00..16820.70 rows=9952 width=0) (actual time=206.295..206.772 rows=1936 loops=1)

        Output: unnamed_subquery.id

        ->  HashSetOp Except  (cost=0.00..16721.18 rows=9952 width=8) (actual time=206.293..206.593 rows=1936 loops=1)

              Output: "*SELECT* 1".id, (0)

              ->  Append  (cost=0.00..15430.19 rows=516393 width=8) (actual time=0.014..136.456 rows=516607 loops=1)

                    ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..12213.00 rows=500000 width=8) (actual time=0.014..98.061 rows=500000 loops=1)

                          Output: "*SELECT* 1".id, 0

                          ->  Seq Scan on public.your_table  (cost=0.00..7213.00 rows=500000 width=4) (actual time=0.013..53.775 rows=500000 loops=1)

                                Output: your_table.id

                    ->  Subquery Scan on "*SELECT* 2"  (cost=0.42..635.23 rows=16393 width=8) (actual time=0.030..3.610 rows=16607 loops=1)

                          Output: "*SELECT* 2".id, 1

                          ->  Index Only Scan using ix_eventdate_id on public.your_table your_table_1  (cost=0.42..471.30 rows=16393 width=4) (actual time=0.029..2.173 rows=16607 loops=1)

                                Output: your_table_1.id

                                Index Cond: (your_table_1.eventdate > '2022-01-01'::date)

                                Heap Fetches: 0

Planning Time: 0.182 ms

Execution Time: 206.961 ms

EXPLAIN

count

1936

SELECT 1
id	eventdate
8020	1991-11-18
2847	1979-03-21
3720	2014-09-30
7	2021-01-31
9522	2007-11-22
2687	1992-02-14
4058	2010-11-30
8577	1966-01-28
9408	1977-12-11
1209	1991-08-02