incorrect results and different plan with 2 very similar queries

Greetings,

There is a report on the pgjdbc github JDBC Driver shows erratic behavior
when filtering on CURRENT_DATE · pgjdbc/pgjdbc · Discussion #3184
(github.com) <https://github.com/pgjdbc/pgjdbc/discussions/3184>

Here are the plans.

JDBC - Nested Loop (incorrect result)

Sort (cost=1071.31..1071.60 rows=114 width=83) (actual time=2.894..2.912
rows=330 loops=1)
Sort Key: p.partno
Sort Method: quicksort Memory: 70kB
-> Nested Loop Left Join (cost=9.46..1067.42 rows=114 width=83) (actual
time=0.082..2.446 rows=330 loops=1)
-> Bitmap Heap Scan on part p (cost=9.18..295.79 rows=114
width=29) (actual time=0.064..0.502 rows=330 loops=1)
Recheck Cond: (mutation >= ((CURRENT_DATE -
'1971-12-31'::date) - 28))
Heap Blocks: exact=181
-> Bitmap Index Scan on i_42609 (cost=0.00..9.15 rows=114
width=0) (actual time=0.041..0.041 rows=344 loops=1)
Index Cond: (mutation >= ((CURRENT_DATE -
'1971-12-31'::date) - 28))
-> Index Scan using i_39773 on part_fa_entity pfe
(cost=0.29..6.76 rows=1 width=65) (actual time=0.005..0.005 rows=1
loops=330)
Index Cond: ((partno)::text = (p.partno)::text)
Planning Time: 0.418 ms
Execution Time: 2.971 ms

JDBC - Hash Right (correct result)

Sort (cost=1352.35..1352.94 rows=238 width=83) (actual time=5.214..5.236
rows=345 loops=1)
Sort Key: p.partno
Sort Method: quicksort Memory: 73kB
-> Hash Right Join (cost=472.00..1342.95 rows=238 width=83) (actual
time=0.654..4.714 rows=345 loops=1)
Hash Cond: ((pfe.partno)::text = (p.partno)::text)
-> Seq Scan on part_fa_entity pfe (cost=0.00..837.27 rows=12827
width=65) (actual time=0.009..2.191 rows=12827 loops=1)
-> Hash (cost=469.03..469.03 rows=238 width=29) (actual
time=0.623..0.624 rows=345 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 30kB
-> Bitmap Heap Scan on part p (cost=18.14..469.03 rows=238
width=29) (actual time=0.073..0.532 rows=345 loops=1)
Recheck Cond: (mutation >= ((CURRENT_DATE -
'1971-12-31'::date) - 29))
Heap Blocks: exact=186
-> Bitmap Index Scan on i_42609 (cost=0.00..18.08
rows=238 width=0) (actual time=0.049..0.049 rows=359 loops=1)
Index Cond: (mutation >= ((CURRENT_DATE -
'1971-12-31'::date) - 29))
Planning Time: 0.304 ms
Execution Time: 5.292 ms

AppX - Nested Loop (correct result)

Sort (cost=1071.31..1071.60 rows=114 width=83) (actual time=3.083..3.102
rows=330 loops=1)
Output: p.partseqno_i, p.partno, p.partmatch, pfe.average_price,
pfe.sales_price, pfe.purch_price, pfe.average_price_2, pfe.avg_repair_cost,
pfe.average_price_func, pfe.fsv, pfe.fsv_func, p.status
Sort Key: p.partno
Sort Method: quicksort Memory: 71kB
-> Nested Loop Left Join (cost=9.46..1067.42 rows=114 width=83) (actual
time=0.069..2.471 rows=330 loops=1)
Output: p.partseqno_i, p.partno, p.partmatch, pfe.average_price,
pfe.sales_price, pfe.purch_price, pfe.average_price_2, pfe.avg_repair_cost,
pfe.average_price_func, pfe.fsv, pfe.fsv_func, p.status
-> Bitmap Heap Scan on .part p (cost=9.18..295.79 rows=114
width=29) (actual time=0.054..0.308 rows=330 loops=1)
Output: p.min_safety_stock, p.manual_safety_stock,
p.extended_stateno_i, p.partno, p.partmatch, p.partseqno_i, p.description,
p.remarks, p.specification, p.ata_chapter, p.vendor, p.weight, p.storetime,
p.alert_qty, p.measure_unit, p.waste_code, p.reord_level, p.safety_stock,
p.max_purch, p.ac_typ, p.mat_class, p.mat_type, p.country_origin,
p.reorder, p.tool, p.repairable, p.avg_ta_time, p.default_supplier,
p.default_repair, p.special_contract, p.fixed_asset,
p.reorder_last_mutator, p.reorder_last_mutation, p.max_shop_visit,
p.shop_visit_reset_condition, p.special_measure_unit, p.manufacturer,
p.pma, p.resource_type_id, p.counter_template_groupno_i, p.mutation,
p.mutator, p.status, p.mutation_time, p.created_by, p.created_date
Recheck Cond: (p.mutation >= ((CURRENT_DATE -
`1971-12-31`::date) - 28))
Heap Blocks: exact=181
-> Bitmap Index Scan on i_42609 (cost=0.00..9.15 rows=114
width=0) (actual time=0.033..0.034 rows=341 loops=1)
Index Cond: (p.mutation >= ((CURRENT_DATE -
`1971-12-31`::date) - 28))
-> Index Scan using i_39773 on .part_fa_entity pfe
(cost=0.29..6.76 rows=1 width=65) (actual time=0.005..0.006 rows=1
loops=330)
Output: pfe.part_fa_entityno_i, pfe.partno, pfe.entityno_i,
pfe.average_price, pfe.sales_price, pfe.purch_price, pfe.average_price_2,
pfe.avg_repair_cost, pfe.avg_repair_cost_func, pfe.fa_qty,
pfe.fa_open_iv_qty, pfe.fa_start_qty, pfe.fa_start_price,
pfe.fa_start_price_2, pfe.mutation, pfe.mutator, pfe.status,
pfe.mutation_time, pfe.created_by, pfe.created_date,
pfe.average_price_func, pfe.fa_start_price_func, pfe.fsv, pfe.fsv_func
Index Cond: ((pfe.partno)::text = (p.partno)::text)
Planning Time: 0.361 ms
Execution Time: 3.157 ms

AppX - Hash Join (correct result)

Sort (cost=1352.35..1352.94 rows=238 width=83) (actual time=5.361..5.384
rows=345 loops=1)
Output: p.partseqno_i, p.partno, p.partmatch, pfe.average_price,
pfe.sales_price, pfe.purch_price, pfe.average_price_2, pfe.avg_repair_cost,
pfe.average_price_func, pfe.fsv, pfe.fsv_func, p.status
Sort Key: p.partno
Sort Method: quicksort Memory: 73kB
-> Hash Right Join (cost=472.00..1342.95 rows=238 width=83) (actual
time=0.594..4.669 rows=345 loops=1)
Output: p.partseqno_i, p.partno, p.partmatch, pfe.average_price,
pfe.sales_price, pfe.purch_price, pfe.average_price_2, pfe.avg_repair_cost,
pfe.average_price_func, pfe.fsv, pfe.fsv_func, p.status
Inner Unique: true
Hash Cond: ((pfe.partno)::text = (p.partno)::text)
-> Seq Scan on amos.part_fa_entity pfe (cost=0.00..837.27
rows=12827 width=65) (actual time=0.006..1.581 rows=12827 loops=1)
Output: pfe.part_fa_entityno_i, pfe.partno, pfe.entityno_i,
pfe.average_price, pfe.sales_price, pfe.purch_price, pfe.average_price_2,
pfe.avg_repair_cost, pfe.avg_repair_cost_func, pfe.fa_qty,
pfe.fa_open_iv_qty, pfe.fa_start_qty, pfe.fa_start_price,
pfe.fa_start_price_2, pfe.mutation, pfe.mutator, pfe.status,
pfe.mutation_time, pfe.created_by, pfe.created_date,
pfe.average_price_func, pfe.fa_start_price_func, pfe.fsv, pfe.fsv_func
-> Hash (cost=469.03..469.03 rows=238 width=29) (actual
time=0.564..0.566 rows=345 loops=1)
Output: p.partseqno_i, p.partno, p.partmatch, p.status
Buckets: 1024 Batches: 1 Memory Usage: 30kB
-> Bitmap Heap Scan on amos.part p (cost=18.14..469.03
rows=238 width=29) (actual time=0.075..0.488 rows=345 loops=1)
Output: p.partseqno_i, p.partno, p.partmatch, p.status
Recheck Cond: (p.mutation >= ((CURRENT_DATE -
`1971-12-31`::date) - 29))
Heap Blocks: exact=186
-> Bitmap Index Scan on i_42609 (cost=0.00..18.08
rows=238 width=0) (actual time=0.035..0.035 rows=356 loops=1)
Index Cond: (p.mutation >= ((CURRENT_DATE -
`1971-12-31`::date) - 29))
Planning Time: 0.379 ms
Execution Time: 5.443 ms
<https://github.com/pgjdbc/pgjdbc/discussions/3184>

Dave Cramer