Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t(date,attribute)as values
 ('2026-01-03','A')
,('2026-01-03','A')
,('2026-01-03','B')
,('2026-01-04','A')
,('2026-01-04','A')
,('2026-01-05','C')
,('2011-11-11','A')
,('2011-11-11',null);
table t;

SELECT 8

date
attribute

2026-01-03
A

2026-01-03
A

2026-01-03
B

2026-01-04
A

2026-01-04
A

2026-01-05
C

2011-11-11
A

2011-11-11
null

SELECT 8

select date
from t
group by date
having every(attribute is not distinct from 'A');

date

2026-01-04

SELECT 1

select distinct date 
from t as t1
where attribute='A'
and not exists(
  select from t as t2
  where t1.date=t2.date
  and t2.attribute is distinct from 'A');

date

2026-01-04

SELECT 1

select setseed(.42);
insert into t
select current_date+(random()*1e5)::int
     , ('{A,B,C}'::char[])[floor(1+random()*3)::int]
from generate_series(1,4e5);

create index idx_a on t(date)with(fillfactor=100)where(attribute='A');
create index idx_idfa on t(date)with(fillfactor=100)where(attribute is distinct from 'A');

select ctid,* from t tablesample bernoulli(.042)repeatable(.042)limit 10;

setseed

SELECT 1

INSERT 0 400000

CREATE INDEX

CREATE INDEX

ctid
date
attribute

(8,41)
2072-12-07
A

(14,55)
2114-09-10
C

(28,89)
2289-05-04
C

(29,37)
2060-06-14
A

(39,43)
2127-01-23
A

(39,75)
2177-07-06
C

(66,50)
2222-05-09
B

(67,44)
2250-05-03
A

(75,87)
2103-10-05
A

(110,148)
2130-06-26
B

SELECT 10

vacuum analyze t;

VACUUM

prepare every_indf as
select date
from t
group by date
having every(attribute is not distinct from 'A');

explain analyze verbose execute every_indf;

PREPARE

QUERY PLAN

HashAggregate  (cost=9163.14..9992.63 rows=41474 width=11) (actual time=204.468..269.441 rows=5087 loops=1)

  Output: date

  Group Key: t.date

  Filter: every((NOT (t.attribute IS DISTINCT FROM 'A'::text)))

  Batches: 5  Memory Usage: 8241kB  Disk Usage: 3808kB

  Rows Removed by Filter: 93073

  ->  Seq Scan on public.t  (cost=0.00..6163.08 rows=400008 width=13) (actual time=0.011..32.221 rows=400008 loops=1)

        Output: date, attribute

Planning Time: 0.306 ms

Execution Time: 270.546 ms

EXPLAIN

prepare anti_join as
select distinct t1.date
from t as t1
left join t as t2 
  on t1.date=t2.date
  and t2.attribute is distinct from 'A'
where t1.attribute='A'
  and t2.date is null;

explain analyze verbose execute anti_join;

PREPARE

QUERY PLAN

Unique  (cost=9861.91..9861.91 rows=1 width=11) (actual time=207.220..212.098 rows=5087 loops=1)

  Output: t1.date

  ->  Sort  (cost=9861.91..9861.91 rows=1 width=11) (actual time=207.217..210.697 rows=9247 loops=1)

        Output: t1.date

        Sort Key: t1.date

        Sort Method: quicksort  Memory: 890kB

        ->  Gather  (cost=7037.80..9861.90 rows=1 width=11) (actual time=146.349..193.681 rows=9247 loops=1)

              Output: t1.date

              Workers Planned: 2

              Workers Launched: 2

              ->  Parallel Hash Anti Join  (cost=6037.80..8861.80 rows=1 width=11) (actual time=114.545..157.238 rows=3082 loops=3)

                    Output: t1.date

                    Hash Cond: (t1.date = t2.date)

                    Worker 0:  actual time=109.274..139.491 rows=3125 loops=1

                    Worker 1:  actual time=88.256..146.101 rows=2884 loops=1

                    ->  Parallel Index Only Scan using idx_a on public.t t1  (cost=0.42..2618.20 rows=54990 width=11) (actual time=0.068..8.865 rows=44354 loops=3)

                          Output: t1.date

                          Heap Fetches: 0

                          Worker 0:  actual time=0.085..6.517 rows=44983 loops=1

                          Worker 1:  actual time=0.085..5.976 rows=40712 loops=1

                    ->  Parallel Hash  (cost=4641.38..4641.38 rows=111680 width=11) (actual time=102.837..102.838 rows=88982 loops=3)

                          Output: t2.date

                          Buckets: 524288  Batches: 1  Memory Usage: 16672kB

                          Worker 0:  actual time=98.785..98.785 rows=74571 loops=1

                          Worker 1:  actual time=87.952..87.953 rows=90534 loops=1

                          ->  Parallel Index Only Scan using idx_idfa on public.t t2  (cost=0.42..4641.38 rows=111680 width=11) (actual time=0.075..31.926 rows=88982 loops=3)

                                Output: t2.date

                                Heap Fetches: 0

                                Worker 0:  actual time=0.070..57.957 rows=74571 loops=1

                                Worker 1:  actual time=0.090..12.866 rows=90534 loops=1

Planning Time: 0.288 ms

Execution Time: 212.345 ms

EXPLAIN

--Pg16+ 
/*
prepare any_filter as
select date
from t
group by date
having any_value(0)filter(where attribute is distinct from 'A') is null;

explain analyze verbose execute any_filter;*/
prepare min0_filter as
select date
from t
group by date
having min(0)filter(where attribute is distinct from 'A') is null;

explain analyze verbose execute min0_filter;

PREPARE

QUERY PLAN

HashAggregate  (cost=9163.14..9992.63 rows=415 width=11) (actual time=215.686..272.615 rows=5087 loops=1)

  Output: date

  Group Key: t.date

  Filter: (min(0) FILTER (WHERE (t.attribute IS DISTINCT FROM 'A'::text)) IS NULL)

  Batches: 5  Memory Usage: 8241kB  Disk Usage: 3808kB

  Rows Removed by Filter: 93073

  ->  Seq Scan on public.t  (cost=0.00..6163.08 rows=400008 width=13) (actual time=0.016..35.091 rows=400008 loops=1)

        Output: date, attribute

Planning Time: 0.185 ms

Execution Time: 273.754 ms

EXPLAIN

prepare not_exists as
select distinct date 
from t as t1
where attribute='A'
and not exists(
  select from t as t2
  where t1.date=t2.date
  and t2.attribute is distinct from 'A');

explain analyze verbose execute not_exists;

PREPARE

QUERY PLAN

Unique  (cost=9861.91..9861.91 rows=1 width=11) (actual time=199.072..203.900 rows=5087 loops=1)

  Output: t1.date

  ->  Sort  (cost=9861.91..9861.91 rows=1 width=11) (actual time=199.069..202.503 rows=9247 loops=1)

        Output: t1.date

        Sort Key: t1.date

        Sort Method: quicksort  Memory: 890kB

        ->  Gather  (cost=7037.80..9861.90 rows=1 width=11) (actual time=119.681..188.436 rows=9247 loops=1)

              Output: t1.date

              Workers Planned: 2

              Workers Launched: 2

              ->  Parallel Hash Anti Join  (cost=6037.80..8861.80 rows=1 width=11) (actual time=105.746..137.547 rows=3082 loops=3)

                    Output: t1.date

                    Hash Cond: (t1.date = t2.date)

                    Worker 0:  actual time=109.468..128.621 rows=3291 loops=1

                    Worker 1:  actual time=88.323..103.176 rows=2567 loops=1

                    ->  Parallel Index Only Scan using idx_a on public.t t1  (cost=0.42..2618.20 rows=54990 width=11) (actual time=0.034..5.368 rows=44354 loops=3)

                          Output: t1.date

                          Heap Fetches: 0

                          Worker 0:  actual time=0.042..5.887 rows=48949 loops=1

                          Worker 1:  actual time=0.042..4.524 rows=37340 loops=1

                    ->  Parallel Hash  (cost=4641.38..4641.38 rows=111680 width=11) (actual time=94.342..94.343 rows=88982 loops=3)

                          Output: t2.date

                          Buckets: 524288  Batches: 1  Memory Usage: 16704kB

                          Worker 0:  actual time=88.758..88.759 rows=77087 loops=1

                          Worker 1:  actual time=77.868..77.869 rows=75087 loops=1

                          ->  Parallel Index Only Scan using idx_idfa on public.t t2  (cost=0.42..4641.38 rows=111680 width=11) (actual time=0.044..45.574 rows=88982 loops=3)

                                Output: t2.date

                                Heap Fetches: 0

                                Worker 0:  actual time=0.051..46.912 rows=77087 loops=1

                                Worker 1:  actual time=0.051..31.084 rows=75087 loops=1

Planning Time: 0.263 ms

Execution Time: 204.161 ms

EXPLAIN

prepare zero_count_filter as
-- Source - https://stackoverflow.com/a/79906384
-- Posted by Laurenz Albe
-- Retrieved 2026-03-12, License - CC BY-SA 4.0

SELECT date
FROM t
GROUP BY date
HAVING count(*) FILTER (WHERE attribute IS DISTINCT FROM 'A') = 0;

explain analyze verbose execute zero_count_filter;

PREPARE

QUERY PLAN

HashAggregate  (cost=9163.14..10200.00 rows=415 width=11) (actual time=211.181..273.153 rows=5087 loops=1)

  Output: date

  Group Key: t.date

  Filter: (count(*) FILTER (WHERE (t.attribute IS DISTINCT FROM 'A'::text)) = 0)

  Batches: 5  Memory Usage: 8241kB  Disk Usage: 3808kB

  Rows Removed by Filter: 93073

  ->  Seq Scan on public.t  (cost=0.00..6163.08 rows=400008 width=13) (actual time=0.013..34.492 rows=400008 loops=1)

        Output: date, attribute

Planning Time: 0.161 ms

Execution Time: 274.332 ms

EXPLAIN

create table timings(variant text,duration interval);

do $d$
declare 
  i int; 
  prewarm_loops int:=1; 
  loops int:=10; 
  started timestamp; 
  finished timestamp;
  variant text; variants text[]:=
    '{not_exists,zero_count_filter,every_indf,min0_filter,anti_join}';
begin
   for i in 1..prewarm_loops loop
     foreach variant in array variants loop
       execute format('execute %s;',variant);
     end loop;
   end loop;
   for i in 1..loops loop
     foreach variant in array variants loop
       started:=clock_timestamp();
       execute format('execute %s;',variant);
       finished:=clock_timestamp();
       insert into timings values(variant,finished-started);
     end loop;
   end loop;
end $d$;

select variant
      ,avg(duration) 
      ,min(duration) 
      ,max(duration) 
      ,sum(duration) 
      ,make_interval(secs=>stddev(extract(epoch from duration))) as stddev
      ,mode()within group(order by duration) as mode
from timings group by 1 order by 2;

CREATE TABLE

DO

variant
avg
min
max
sum
stddev
mode

not_exists
00:00:00.184743
00:00:00.174636
00:00:00.199075
00:00:01.847426
00:00:00.007394
00:00:00.174636

anti_join
00:00:00.191217
00:00:00.174656
00:00:00.265685
00:00:01.912169
00:00:00.026876
00:00:00.174656

every_indf
00:00:00.254697
00:00:00.235539
00:00:00.29734
00:00:02.546972
00:00:00.020578
00:00:00.235539

min0_filter
00:00:00.2656
00:00:00.245748
00:00:00.337827
00:00:02.655998
00:00:00.034727
00:00:00.245748

zero_count_filter
00:00:00.267132
00:00:00.241334
00:00:00.418356
00:00:02.671325
00:00:00.054409
00:00:00.241334

SELECT 5
date	attribute
2026-01-03	A
2026-01-03	A
2026-01-03	B
2026-01-04	A
2026-01-04	A
2026-01-05	C
2011-11-11	A
2011-11-11	null
ctid	date	attribute
(8,41)	2072-12-07	A
(14,55)	2114-09-10	C
(28,89)	2289-05-04	C
(29,37)	2060-06-14	A
(39,43)	2127-01-23	A
(39,75)	2177-07-06	C
(66,50)	2222-05-09	B
(67,44)	2250-05-03	A
(75,87)	2103-10-05	A
(110,148)	2130-06-26	B
variant	avg	min	max	sum	stddev	mode
not_exists	00:00:00.184743	00:00:00.174636	00:00:00.199075	00:00:01.847426	00:00:00.007394	00:00:00.174636
anti_join	00:00:00.191217	00:00:00.174656	00:00:00.265685	00:00:01.912169	00:00:00.026876	00:00:00.174656
every_indf	00:00:00.254697	00:00:00.235539	00:00:00.29734	00:00:02.546972	00:00:00.020578	00:00:00.235539
min0_filter	00:00:00.2656	00:00:00.245748	00:00:00.337827	00:00:02.655998	00:00:00.034727	00:00:00.245748
zero_count_filter	00:00:00.267132	00:00:00.241334	00:00:00.418356	00:00:02.671325	00:00:00.054409	00:00:00.241334