RE: [EXTERNAL]Re: BUG #19094: select statement on postgres 17 vs postgres 18 is returning different/duplicate results - Mailing list pgsql-bugs

Tom,

Sorry, I haven't had to enter a PG ticket before.  I realize that there are many things I didn't provide.  My
apologies. I was looking for a link back to my bug report so I could attach more information but didn't see one in the
emailthat came back. 

I am working on getting the file together with the appropriate tables & data this morning.

In the meaintime, I did run the EXPLAIN ANALYZE after I sent out the initial email.  See below.


Postgres 17

EXPLAIN ANALYZE

 Merge Semi Join  (cost=1.46..65223.16 rows=89672 width=4) (actual time=1.315..367.474 rows=115457 loops=1)
   Merge Cond: (s._strain_key = va._object_key)
   ->  Index Scan using prb_strain_pkey on prb_strain s  (cost=0.42..6486.88 rows=115741 width=4) (actual
time=0.013..99.843rows=117065 loops=1) 
         Filter: ((strain !* '%involves%'::text) AND (strain !* '%either%'::text) AND (strain !* '% and %'::text) AND
(strain!* '% or %'::text) AND (private = 0)) 
         Rows Removed by Filter: 16340
   ->  Nested Loop  (cost=0.86..118573.26 rows=322352 width=4) (actual time=1.284..232.161 rows=321574 loops=1)
         ->  Index Only Scan using voc_annot_idx_clustered on voc_annot va  (cost=0.43..95392.15 rows=322480 width=8)
(actualtime=0.054..139.127 rows=322488 loops=1) 
               Index Cond: (_annottype_key = 1009)
               Heap Fetches: 1668
         ->  Memoize  (cost=0.43..0.51 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=322488)
               Cache Key: va._term_key
               Cache Mode: logical
               Hits: 322441  Misses: 47  Evictions: 0  Overflows: 0  Memory Usage: 5kB
               ->  Index Scan using voc_term_pkey on voc_term t  (cost=0.42..0.50 rows=1 width=4) (actual
time=0.010..0.010rows=1 loops=47) 
                     Index Cond: (_term_key = va._term_key)
                     Filter: ((term <> 'Not Applicable'::text) AND (term <> 'Not Specified'::text))
                     Rows Removed by Filter: 0
 Planning Time: 4.688 ms
 Execution Time: 371.618 ms
(19 rows)

Postgres 18

EXPLAIN ANALYZE

 Gather  (cost=17394.54..60888.14 rows=88631 width=4) (actual time=112.329..193.108 rows=115447.0
0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=13901
   ->  Parallel Hash Right Semi Join  (cost=16394.54..51025.04 rows=36930 width=4) (actual time=1
06.616..171.437 rows=38482.33 loops=3)
         Hash Cond: (va._object_key = s._strain_key)
         Buffers: shared hit=13901
         ->  Parallel Hash Join  (cost=12089.56..45217.05 rows=134260 width=4) (actual time=67.78
1..106.574 rows=107171.33 loops=3)
               Hash Cond: (va._term_key = t._term_key)
               Buffers: shared hit=12109
               ->  Parallel Bitmap Heap Scan on voc_annot va  (cost=3046.66..35821.57 rows=134313
 width=8) (actual time=12.580..32.848 rows=107476.00 loops=3)
                     Recheck Cond: (_annottype_key = 1009)
                     Heap Blocks: exact=1802
                     Buffers: shared hit=6277
                     Worker 0:  Heap Blocks: exact=2068
                     Worker 1:  Heap Blocks: exact=2132
                     ->  Bitmap Index Scan on voc_annot_idx_annottype_key  (cost=0.00..2966.07 ro
ws=322352 width=0) (actual time=11.476..11.477 rows=322428.00 loops=1)
                           Index Cond: (_annottype_key = 1009)
                           Index Searches: 1
                           Buffers: shared hit=275
               ->  Parallel Hash  (cost=7583.72..7583.72 rows=116735 width=4) (actual time=53.855
..53.856 rows=93389.33 loops=3)
                     Buckets: 524288  Batches: 1  Memory Usage: 15072kB
                     Buffers: shared hit=5832
                     ->  Parallel Seq Scan on voc_term t  (cost=0.00..7583.72 rows=116735 width=4
) (actual time=0.022..24.986 rows=93389.33 loops=3)
                           Filter: ((term <> 'Not Applicable'::text) AND (term <> 'Not Specified'
::text))
                           Rows Removed by Filter: 36
                           Buffers: shared hit=5832
         ->  Parallel Hash  (cost=3458.96..3458.96 rows=67681 width=4) (actual time=38.276..38.27
7 rows=39019.33 loops=3)
               Buckets: 131072  Batches: 1  Memory Usage: 5632kB
               Buffers: shared hit=1792
               ->  Parallel Seq Scan on prb_strain s  (cost=0.00..3458.96 rows=67681 width=4) (actual
time=0.033..27.676rows=39019.33 loops=3) 
                     Filter: ((strain !* '%involves%'::text) AND (strain !* '%either%'::text) AND (strain !* '% and
%'::text)AND (strain !* '% or %'::text) AND (private = 0)) 
                     Rows Removed by Filter: 2963
                     Buffers: shared hit=1792
 Planning:
   Buffers: shared hit=724
 Planning Time: 5.633 ms
 Execution Time: 198.235 ms
(38 rows)

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Saturday, October 25, 2025 10:31 AM
To: Lori Corbani <Lori.Corbani@jax.org>
Cc: pgsql-bugs@lists.postgresql.org
Subject: [EXTERNAL]Re: BUG #19094: select statement on postgres 17 vs postgres 18 is returning different/duplicate
results

PG Bug reporting form <noreply@postgresql.org> writes:
> If I remove the "exists" statement, then the counts are fine.
> So, it seems that it is the "exists" statement that is causing the issue.

> "select s._Strain_key"     VS  "select distinct s._Strain_key"

>         from prb_strain s
>         where s.private = 0
>             and s.strain not ilike '%involves%'
>             and s.strain not ilike '%either%'
>             and s.strain not ilike '% and %'
>             and s.strain not ilike '% or %'
>             and exists (select 1 from voc_annot va, voc_term t
>                 where va._AnnotType_key = 1009
>                 and va._Term_key = t._Term_key
>                 and t.term != 'Not Applicable'
>                 and t.term != 'Not Specified'
>                 and va._Object_key = s._Strain_key)

This report is inadequate to help us identify the issue.
You've not provided the relevant table declarations, nor any sample data that would reproduce the problem.

Given the squishiness of the described behavior, I realize that building a self-contained reproducer might be hard.  In
themeantime, could you at least provide EXPLAIN ANALYZE results from correct and incorrect executions? 

            regards, tom lane
---

The information in this email, including attachments, may be confidential and is intended solely for the addressee(s).
Ifyou believe you received this email by mistake, please notify the sender by return email as soon as possible.