Re: incorrect results and different plan with 2 very similar queries

[Tomas Vondra]

On 3/27/24 23:10, Dave Cramer wrote:
> Dave Cramer
> 
> 
> On Wed, 27 Mar 2024 at 17:57, David Rowley  wrote:
> 
>> On Thu, 28 Mar 2024 at 10:33, Dave Cramer  wrote:
>>> There is a report on the pgjdbc github JDBC Driver shows erratic
>> behavior when filtering on CURRENT_DATE · pgjdbc/pgjdbc · Discussion #3184 (
>> github.com)
>>>
>>> Here are the plans.
>>>
>>> JDBC - Nested Loop (incorrect result)
>>>
>>> Index Cond: (mutation >= ((CURRENT_DATE -
>> '1971-12-31'::date) - 28))
>>
>>> JDBC - Hash Right (correct result)
>>>
>>> Recheck Cond: (mutation >= ((CURRENT_DATE -
>> '1971-12-31'::date) - 29))
>>
>> I don't see any version details or queries, but going by the
>> conditions above, the queries don't appear to be the same, so
>> different results aren't too surprising and not a demonstration that
>> there's any sort of bug.
>>
> 
> Sorry, you are correct. Version is 12.14. Here is the query
> 
> SELECT
>   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
> 
> FROM part p
> LEFT JOIN part_fa_entity pfe ON (p.partno = pfe.partno)
> WHERE 1=1
> AND (p.mutation >= (CURRENT_DATE - '1971-12-31'::date)-27) ORDER BY p.partno
> 

I guess the confusing bit is that the report does not claim that those
queries are expected to produce the same result, but that the parameter
value affects which plan gets selected, and one of those plans produces
incorrect result.

I think the simplest explanation might be that one of the indexes on
part_fa_entity is corrupted and fails to lookup some rows by partno.
That would explain why the plan with seqscan works fine, but nestloop
with index scan is missing these rows.

They might try a couple things:

1) set enable_nestloop=off, see if the results get correct

2) try bt_index_check on i_39773, might notice some corruption

3) rebuild the index

If it's not this, they'll need to build a reproducer. It's really
difficult to deduce what's going on just from query plans for different
parameter values.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: incorrect results and different plan with 2 very similar queries

[Dave Cramer]

Dave Cramer


On Wed, 27 Mar 2024 at 17:57, David Rowley  wrote:

> On Thu, 28 Mar 2024 at 10:33, Dave Cramer  wrote:
> > There is a report on the pgjdbc github JDBC Driver shows erratic
> behavior when filtering on CURRENT_DATE · pgjdbc/pgjdbc · Discussion #3184 (
> github.com)
> >
> > Here are the plans.
> >
> > JDBC - Nested Loop (incorrect result)
> >
> > Index Cond: (mutation >= ((CURRENT_DATE -
> '1971-12-31'::date) - 28))
>
> > JDBC - Hash Right (correct result)
> >
> > Recheck Cond: (mutation >= ((CURRENT_DATE -
> '1971-12-31'::date) - 29))
>
> I don't see any version details or queries, but going by the
> conditions above, the queries don't appear to be the same, so
> different results aren't too surprising and not a demonstration that
> there's any sort of bug.
>

Sorry, you are correct. Version is 12.14. Here is the query

SELECT
  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

FROM part p
LEFT JOIN part_fa_entity pfe ON (p.partno = pfe.partno)
WHERE 1=1
AND (p.mutation >= (CURRENT_DATE - '1971-12-31'::date)-27) ORDER BY p.partno

Dave


> David
>

Re: incorrect results and different plan with 2 very similar queries

[David Rowley]

On Thu, 28 Mar 2024 at 10:33, Dave Cramer  wrote:
> There is a report on the pgjdbc github JDBC Driver shows erratic behavior 
> when filtering on CURRENT_DATE · pgjdbc/pgjdbc · Discussion #3184 (github.com)
>
> Here are the plans.
>
> JDBC - Nested Loop (incorrect result)
>
> Index Cond: (mutation >= ((CURRENT_DATE - 
> '1971-12-31'::date) - 28))

> JDBC - Hash Right (correct result)
>
> Recheck Cond: (mutation >= ((CURRENT_DATE - 
> '1971-12-31'::date) - 29))

I don't see any version details or queries, but going by the
conditions above, the queries don't appear to be the same, so
different results aren't too surprising and not a demonstration that
there's any sort of bug.

David

incorrect results and different plan with 2 very similar queries

[Dave Cramer]

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) 

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 =