Re: Suboptimal query plan fixed by replacing OR with UNION

On Jul 5, 2012, at 6:35 PM, Jasen Betts wrote:

> I note you've decided to rewrite this query as a union
>
>> SELECT * FROM account
>>  WHERE user_id in
>>    (SELECT user_id FROM account
>>      WHERE id =
ANY('{00000000-02f6-379d-c000-000000026810,00000000-0320-b467-c000-000000026810,00000000-000d-cefb-c000-000000026810}'))
>>  OR
>>    id =
ANY('{00000000-02f6-379d-c000-000000026810,00000000-0320-b467-c000-000000026810,00000000-000d-cefb-c000-000000026810}');
>
> I notice both arrays (used with = ANY) have the exact same content,
>
> if this is always true you can use a CTE here for the ID=ANY(...)
> query and reference the CTE on both sides of the union.
>

Thanks for the idea!  I'll be sure to incorporate that.  Doesn't fix the unfortunate behavior with OR, though.

> WITH i as (
> SELECT * FROM account WHERE id =
ANY('{00000000-02f6-379d-c000-000000026810,00000000-0320-b467-c000-000000026810,00000000-000d-cefb-c000-000000026810}')
> )
> SELECT
> * from i
> UNION DISTINCT
> SELECT
> account.* from account join i on i.user_id = account.userid ;
>
> --
> ⚂⚃ 100% natural
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general