On Sat, 9 Mar 2019 at 01:25, Artur Zając <azajac@xxxxxxxxxx> wrote: > I made some workaround. I made function: > > CREATE OR REPLACE FUNCTION smarteq(v1 int,v2 INT) RETURNS BOOL AS > $BODY$ > SELECT (CASE WHEN v2 IS NULL THEN (v1 IS NULL) ELSE v1=v2 END); > $BODY$ LANGUAGE 'sql' IMMUTABLE PARALLEL SAFE; > explain analyze select id from sometable where smarteq(id1,NULL); > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------------------------- > Bitmap Heap Scan on sometable (cost=19338.59..57045.02 rows=882343 width=4) (actual time=116.236..306.304 rows=881657 loops=1) > Recheck Cond: (id1 IS NULL) > Heap Blocks: exact=9581 > -> Bitmap Index Scan on sometable_index1 (cost=0.00..19118.00 rows=882343 width=0) (actual time=114.209..114.209 rows=892552 loops=1) > Index Cond: (id1 IS NULL) > Planning time: 0.135 ms > Execution time: 339.229 ms > > It looks like it works, but I must check if it will still works in plpgsql (I expect some problems if query is prepared). I think with either that you'll just be at the mercy of whether a generic or custom plan is chosen. If you get a custom plan then likely your case statement will be inlined and constant folded away, but for a generic plan, that can't happen since those constants are not consts, they're parameters. Most likely, if you've got an index on the column you'll perhaps always get a custom plan as the generic plan would result in a seqscan and it would have to evaluate your case statement for each row. By default, generic plans are only considered on the 6th query execution and are only chosen if the generic cost is cheaper than the average custom plan cost + fuzz cost for planning. PG12 gives you a bit more control over that with the plan_cache_mode GUC, but... that's the not out yet. However, possibly the cost of planning each execution is cheaper than doing the seq scan, so you might be better off with this. There is a risk that the planner does for some reason choose a generic plan and ends up doing the seq scan, but for that to happen likely the table would have to be small, in which case it wouldn't matter or the costs would have to be off, which might cause you some pain. The transformation mentioned earlier could only work if the arguments of the IS NOT DISTINCT FROM were Vars or Consts. It couldn't work with Params since the values are unknown to the planner. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
