On Fri, Nov 4, 2016 at 9:38 AM, Alban Hertroys <haramrae@xxxxxxxxx> wrote: > On 4 November 2016 at 14:41, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: >> On Fri, Nov 4, 2016 at 8:08 AM, Kim Rose Carlsen <krc@xxxxxxxx> wrote: >>> 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. >> >> It would if you explicitly indexed it as such; >> CREATE INDEX ON circuit_customer((COALESCE(customer_id, '0')); > > Merlin, it's a LEFT JOIN. There probably are no NULLs in the > circuit_customer.customer_id column, so that COALESCE isn't going to > achieve anything at all. Hang on -- upthread the context was inner join, and the gripe was join fast with '=', slow with INDF. When he said the nulls were 'generated', I didn't follow that they were part of the original query. If the nulls are generated along with the query, sure, an index won't help. I maintain my earlier point; with respect to the original query, to get from performance of INDF to =, you have three options: a) expr index the nulls (assuming they are physically stored) b) convert to ((a = b) or a is null and b is null) which can help with a bitmap or plan c) covert to union all equivalent of "b" merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
