Search Postgresql Archives

Re: How to hint 2 coulms IS NOT DISTINCT FROM each other

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sat, Oct 29, 2016 at 8:27 AM, Kim Rose Carlsen <krc@xxxxxxxx> wrote:

> I have tried creating a function called
> zero_if_null(int) : int that just select COALESCE($1, 0)
> and adding a index on (zero_if_null(customer_id)) on table that contains
> customer_id. The only thing I get from is the planner now only knows how to
> compare customer_id, but it still doesn't know that they are of same value,
> > only I know that and I want to declare it for the planner.


> Well, the *behavior* is mandated by the sql standard.  Our
implementation is slow however.  

Sorry I'm not following, what behavior is mandated by the sql standard?

I'm surprised the attached function
didn't help, it can be inlined and I was able to get bitmap or which
is pretty good.  As I said upthread I think INDF could theoretically
run as fast as equality -- it just doesn't today.

It might be harsh to say that it doesn't help at all. I does half the running time, 
but I need it to run an order of magnitude faster. Here is the plan with the 
empty_if_null (customer_id is actually varchar)

https://explain.depesz.com/s/M1LV with empty_if_null + functional index

As your joins are written you could probably convert this by reserving
a customer_id to the work that you're trying to do with null, say, 0,
or -1.  This is a somewhat dubious practice but seems a better fit for
your use case.  I don't think INDF is good in this usage.

merlin

This will work well, I think. 

But I'm not sure I can mentally accept an unfilled value should not be 
null (eg. 0, '', '0000-01-01'). But I can see how the equals operator will
work well with this.

It might raise another problem, that the nulls are generated through LEFT
JOINS where now rows are defined. Then the 0 or -1 value need to be 
a computed value. Won't this throw of index lookups? (I might be 
more confused in this area).


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux