Re: Factoring where clauses through UNIONS take 2

On Thu, 24 Apr 2003, Jonathan Bartlett wrote:

> THe actual view is:
>
> create view all_actions_v2 as select sent_mail, 'REPLY' as type,
> cached_campaign as campaign, cached_list_member as list_member, reply_date
> as occurence_date, reply_subject as other_data from action_reply UNION
> select sent_mail, 'FORWARD' as type, cached_campaign as campaign,
> cached_list_member as list_member, forward_date as occurence_date,
> destination_email as other_data from action_forward UNION select
> ac.sent_mail, 'CLICK' as type, ac.cached_campaign as campaign,
> cached_list_member as list_member, ac.click_date as occurence_date, cl.url
> as other_data from action_click ac, campaign_links cl where ac.link =
> cl.object_id UNION select sent_mail, 'UNSUBSCRIBE' as type,
> cached_campaign as campaign, cached_list_member as list_member,
> unsubscribe_date as occurence_date, NULL::varchar as other_data from
> action_unsubscribe UNION select object_id as sent_mail, 'BOUNCE' as type,
> campaign, list_member, date_sent as occurence_date, NULL::varchar as
> other_data from campaign_sent_mails where bounced = true UNION select
> object_id as sent_mail, 'SENT' as type, campaign, list_member, date_sent
> as occurrence_date, NULL::varchar as other_data from campaign_sent_mails
> UNION select object_id as sent_mail, 'OPEN' as type, campaign,
> list_member, date_opened as occurrence_date, NULL::varchar as other_data
> from campaign_sent_mails where date_opened is not NULL;

On a probably unrelated side note, ISTM you can use UNION ALL rather than
UNION in the above and lose the uniqueness test at the end (since the
constant strings seem different for each one, it shouldn't do anything).

The thing that I think is killing it is the constants. A view like:

create view qv1 as select a as b from q1 union select b from q2;
explain select * from qv1 where b=3;
pushes down into the selects.

create view qv1 as select a as b, 'b' from q1 union select b,'c' from q2;
explain select * from qv1 where b=3;
doesn't.