incorrect results and different plan with 2 very similar queries - Mailing list pgsql-hackers
| From | Dave Cramer |
|---|---|
| Subject | incorrect results and different plan with 2 very similar queries |
| Date | |
| Msg-id | CADK3HHJX0XeJQO9gqhLbywcJgXskezPE9p-jaVLUFB5N2wR_KQ@mail.gmail.com Whole thread Raw |
| Responses |
Re: incorrect results and different plan with 2 very similar queries
|
| List | pgsql-hackers |
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 = (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
Dave Cramer
pgsql-hackers by date: