From: | Kim Rose Carlsen <krc(at)hiper(dot)dk> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to hint 2 coulms IS NOT DISTINCT FROM each other |
Date: | 2016-11-04 13:08:33 |
Message-ID: | AM4PR0501MB2610ADFE47C78D29B3825E12C7A20@AM4PR0501MB2610.eurprd05.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>> 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2016-11-04 13:41:07 | Re: How to hint 2 coulms IS NOT DISTINCT FROM each other |
Previous Message | Gionatan Danti | 2016-11-04 10:20:21 | Re: Recover from corrupted database due to failing disk |