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]

 



>> It might raise another problem, that the nulls are generated through LEFT

>> JOINS where no rows are defined. Then the 0 or -1 value need to be
>> a computed value. Won't this throw off index lookups? (I might be
>> more confused in this area).
>
>Not following this.  

The nulls are generated by something like this
    SELECT c.circuit_id,
                   cc.customer_id
       FROM circuit AS c
LEFT JOIN circuit_customer AS cc
             ON c.circuit_id = cc.circuit_id

To make a magic '0' customer we would be required to use 
  COALESCE(cc.customer_id, '0')
I dont think the optimizer will do anything clever with the '0' we have 
computed from null.

I could ofc. by default assign all unassigned circuits to '0' in 
circuit_customer. I'm not a fan though.

>BTW, if you want a fast plan over the current
>data without consideration of aesthetics, try this:
>
>CREATE VIEW view_circuit_with_status AS (
>    SELECT r.*,
>          s.circuit_status,
>          s.customer_id AS s_customer_id,
>          p.line_speed,
>          p.customer_id AS p_customer_id
>     FROM view_circuit r
>     JOIN view_circuit_product_main s
>      ON r.circuit_id = s.circuit_id
>      AND r.customer_id, s.customer_id
>     JOIN view_circuit_product p
>       ON r.circuit_id = p.circuit_id
>      AND r.customer_id, s.customer_id
>  UNION ALL SELECT r.*,
>          s.circuit_status,
>          s.customer_id AS s_customer_id,
>          p.line_speed,
>          p.customer_id AS p_customer_id
>     FROM view_circuit r
>     JOIN view_circuit_product_main s
>       ON r.circuit_id = s.circuit_id
>      AND r.customer_id IS NULL
>      AND  s.customer_id IS NULL
>     JOIN view_circuit_product p
>       ON r.circuit_id = p.circuit_id>

I will have to figure something out, but this specific case is still problematic
since we would like to filter this view using different criteria's, like circuit_no, 
products or customers.

But with all these detours, I assume that a change to IS NOT DISTINCT FROM, 
is difficult or not wanted?

[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