čt 28. 11. 2019 v 15:51 odesílatel Laurenz Albe <laurenz.a...@cybertec.at> napsal:
> On Wed, 2019-11-27 at 08:47 +0100, Pavel Stehule wrote: > > The most significant issue was missing correct estimation for coalesce > function. > > He had to rewrite coalesce(var, X) = X to "var IS NULL or var = X". > > Then the result was very satisfactory. > > > > postgres=# explain analyze select * from xxx where coalesce(a, 0) = 0; > > QUERY PLAN > > > > ---------------------------------------------------------------------------------------------------- > > Seq Scan on xxx (cost=0.00..194.00 rows=60 width=4) (actual > time=0.041..4.276 rows=11000 loops=1) > > I think that this is asking for a planner support function: > https://www.postgresql.org/docs/current/xfunc-optimization.html Probably it needs more work - currently this support is for SRF function or for boolean functions. On second hand coalesce is not function - it's expr node. Originally I though so selectivity function can be enough. Now I think so it is not enough. It is similar to DISTINCT FROM operator. So some plan can look like 1. introduction isnull_or_eq operator 2. this operator can be used for indexscan too 3. implement selectivity function for this operator (and maybe for coalesce) 4. translate COALESCE(var, const) = const --> var isnull_or_eq const I am not sure if @4 is possible or if some more complex transformations are possible COALESCE(var1, var2) = var2 But what I read about it - MSSQL and Oracle has does this optimization Regards Pavel > > Yours, > Laurenz Albe > >
