Postgres 18

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 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 date
from t
group by date
having every(attribute is not distinct from 'A');

date

2026-01-04

SELECT 1

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

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

setseed

SELECT 1

INSERT 0 120000

ctid
date
attribute

(8,41)
2040-03-21
A

(14,55)
2052-09-29
C

(28,89)
2105-02-21
C

(29,37)
2036-06-22
A

(39,43)
2056-06-15
A

(39,75)
2071-08-05
C

(66,50)
2085-01-16
B

(67,44)
2093-06-09
A

(75,87)
2049-06-19
A

(110,148)
2057-06-25
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=2749.14..3010.61 rows=13074 width=11) (actual time=80.421..83.411 rows=1478.00 loops=1)

  Output: date

  Group Key: t.date

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

  Batches: 1  Memory Usage: 2585kB

  Rows Removed by Filter: 27948

  Buffers: shared hit=649

  ->  Seq Scan on public.t  (cost=0.00..1849.08 rows=120008 width=13) (actual time=0.008..12.935 rows=120008.00 loops=1)

        Output: date, attribute

        Buffers: shared hit=649

Planning:

  Buffers: shared hit=10

Planning Time: 0.132 ms

Execution Time: 83.571 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=6099.80..6099.81 rows=1 width=11) (actual time=57.452..58.107 rows=1478.00 loops=1)

  Output: t1.date

  Buffers: shared hit=1298

  ->  Sort  (cost=6099.80..6099.81 rows=1 width=11) (actual time=57.449..57.623 rows=2713.00 loops=1)

        Output: t1.date

        Sort Key: t1.date

        Sort Method: quicksort  Memory: 97kB

        Buffers: shared hit=1298

        ->  Hash Anti Join  (cost=3146.71..6099.79 rows=1 width=11) (actual time=31.818..55.314 rows=2713.00 loops=1)

              Output: t1.date

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

              Buffers: shared hit=1298

              ->  Seq Scan on public.t t1  (cost=0.00..2149.10 rows=40199 width=11) (actual time=0.010..13.783 rows=39868.00 loops=1)

                    Output: t1.date, t1.attribute

                    Filter: (t1.attribute = 'A'::text)

                    Rows Removed by Filter: 80140

                    Buffers: shared hit=649

              ->  Hash  (cost=2149.10..2149.10 rows=79809 width=11) (actual time=31.732..31.733 rows=80140.00 loops=1)

                    Output: t2.date

                    Buckets: 131072  Batches: 1  Memory Usage: 4390kB

                    Buffers: shared hit=649

                    ->  Seq Scan on public.t t2  (cost=0.00..2149.10 rows=79809 width=11) (actual time=0.007..15.705 rows=80140.00 loops=1)

                          Output: t2.date

                          Filter: (t2.attribute IS DISTINCT FROM 'A'::text)

                          Rows Removed by Filter: 39868

                          Buffers: shared hit=649

Planning:

  Buffers: shared hit=9

Planning Time: 0.231 ms

Execution Time: 58.425 ms

EXPLAIN

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

QUERY PLAN

HashAggregate  (cost=2749.14..3010.61 rows=131 width=11) (actual time=45.792..48.757 rows=1478.00 loops=1)

  Output: date

  Group Key: t.date

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

  Batches: 1  Memory Usage: 2585kB

  Rows Removed by Filter: 27948

  Buffers: shared hit=649

  ->  Seq Scan on public.t  (cost=0.00..1849.08 rows=120008 width=13) (actual time=0.009..7.623 rows=120008.00 loops=1)

        Output: date, attribute

        Buffers: shared hit=649

Planning:

  Buffers: shared hit=3

Planning Time: 0.088 ms

Execution Time: 49.040 ms

EXPLAIN

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=2749.14..3010.61 rows=131 width=11) (actual time=43.557..46.502 rows=1478.00 loops=1)

  Output: date

  Group Key: t.date

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

  Batches: 1  Memory Usage: 2585kB

  Rows Removed by Filter: 27948

  Buffers: shared hit=649

  ->  Seq Scan on public.t  (cost=0.00..1849.08 rows=120008 width=13) (actual time=0.009..7.426 rows=120008.00 loops=1)

        Output: date, attribute

        Buffers: shared hit=649

Planning:

  Buffers: shared hit=2 read=1

Planning Time: 0.404 ms

Execution Time: 46.647 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=6099.80..6099.81 rows=1 width=11) (actual time=56.163..56.789 rows=1478.00 loops=1)

  Output: t1.date

  Buffers: shared hit=1298

  ->  Sort  (cost=6099.80..6099.81 rows=1 width=11) (actual time=56.160..56.327 rows=2713.00 loops=1)

        Output: t1.date

        Sort Key: t1.date

        Sort Method: quicksort  Memory: 97kB

        Buffers: shared hit=1298

        ->  Hash Anti Join  (cost=3146.71..6099.79 rows=1 width=11) (actual time=30.964..54.105 rows=2713.00 loops=1)

              Output: t1.date

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

              Buffers: shared hit=1298

              ->  Seq Scan on public.t t1  (cost=0.00..2149.10 rows=40199 width=11) (actual time=0.010..13.108 rows=39868.00 loops=1)

                    Output: t1.date, t1.attribute

                    Filter: (t1.attribute = 'A'::text)

                    Rows Removed by Filter: 80140

                    Buffers: shared hit=649

              ->  Hash  (cost=2149.10..2149.10 rows=79809 width=11) (actual time=30.884..30.884 rows=80140.00 loops=1)

                    Output: t2.date

                    Buckets: 131072  Batches: 1  Memory Usage: 4390kB

                    Buffers: shared hit=649

                    ->  Seq Scan on public.t t2  (cost=0.00..2149.10 rows=79809 width=11) (actual time=0.005..16.017 rows=80140.00 loops=1)

                          Output: t2.date

                          Filter: (t2.attribute IS DISTINCT FROM 'A'::text)

                          Rows Removed by Filter: 39868

                          Buffers: shared hit=649

Planning Time: 0.190 ms

Execution Time: 57.097 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=2749.14..3075.98 rows=131 width=11) (actual time=47.700..50.814 rows=1478.00 loops=1)

  Output: date

  Group Key: t.date

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

  Batches: 1  Memory Usage: 2585kB

  Rows Removed by Filter: 27948

  Buffers: shared hit=649

  ->  Seq Scan on public.t  (cost=0.00..1849.08 rows=120008 width=13) (actual time=0.010..7.700 rows=120008.00 loops=1)

        Output: date, attribute

        Buffers: shared hit=649

Planning:

  Buffers: shared hit=2 read=1

Planning Time: 0.284 ms

Execution Time: 51.058 ms

EXPLAIN

create table timings(variant text,duration interval);

do $d$
declare 
  i int; 
  prewarm_loops int:=1; 
  loops int:=80; 
  started timestamp; 
  finished timestamp;
  variant text; variants text[]:=
    '{not_exists,zero_count_filter,any_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

every_indf
00:00:00.04012
00:00:00.034299
00:00:00.046298
00:00:03.209578
00:00:00.002619
00:00:00.034299

any_filter
00:00:00.040359
00:00:00.035581
00:00:00.062869
00:00:03.228738
00:00:00.003601
00:00:00.036665

min0_filter
00:00:00.041193
00:00:00.035438
00:00:00.07555
00:00:03.295453
00:00:00.005236
00:00:00.035438

zero_count_filter
00:00:00.042854
00:00:00.037508
00:00:00.079345
00:00:03.428287
00:00:00.005217
00:00:00.037508

anti_join
00:00:00.049514
00:00:00.043567
00:00:00.066522
00:00:03.961119
00:00:00.004647
00:00:00.043567

not_exists
00:00:00.050998
00:00:00.044804
00:00:00.123039
00:00:04.07988
00:00:00.00943
00:00:00.050541

SELECT 6
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)	2040-03-21	A
(14,55)	2052-09-29	C
(28,89)	2105-02-21	C
(29,37)	2036-06-22	A
(39,43)	2056-06-15	A
(39,75)	2071-08-05	C
(66,50)	2085-01-16	B
(67,44)	2093-06-09	A
(75,87)	2049-06-19	A
(110,148)	2057-06-25	B
variant	avg	min	max	sum	stddev	mode
every_indf	00:00:00.04012	00:00:00.034299	00:00:00.046298	00:00:03.209578	00:00:00.002619	00:00:00.034299
any_filter	00:00:00.040359	00:00:00.035581	00:00:00.062869	00:00:03.228738	00:00:00.003601	00:00:00.036665
min0_filter	00:00:00.041193	00:00:00.035438	00:00:00.07555	00:00:03.295453	00:00:00.005236	00:00:00.035438
zero_count_filter	00:00:00.042854	00:00:00.037508	00:00:00.079345	00:00:03.428287	00:00:00.005217	00:00:00.037508
anti_join	00:00:00.049514	00:00:00.043567	00:00:00.066522	00:00:03.961119	00:00:00.004647	00:00:00.043567
not_exists	00:00:00.050998	00:00:00.044804	00:00:00.123039	00:00:04.07988	00:00:00.00943	00:00:00.050541