Re: Factoring where clauses through UNIONS take 2

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;

Manually factoring the where clause through would produce this:

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 where cached_campaign = 3
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 where cached_campaign
= 3 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 and cached_campaign = 3 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 where cached_campaign
= 3 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 and campaign = 3 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
where campaign = 3 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 and
campaign = 3;

Using the view, the request takes 10 seconds. Manually factoring takes 2
seconds. I'd like to keep the view if possible for a little better data
discipline.

Jon

On Thu, 24 Apr 2003, Stephan Szabo wrote:

>
> On Thu, 24 Apr 2003, Jonathan Bartlett wrote:
>
> > Okay, after upgrading Postgres to 7.3.2, I got some improvements to my
> > query through factoring, but it was obvious my tables needed a little
> > help. Therefore, I've modified my base tables and views, and now, even
> > with enable_seqscan=off, it's not factoring through. Here is my query:
> >
> > select * from all_actions where campaign = 3;
> >
> > The full query plan is at the end of the email. Basically, all_actions is
> > a view of several tables, each of which either have a field called
> > "campaign" or "cached_campaign". It is indexed for all affected tables.
> > However, for every table it does a sequential scan rather than an indexed
> > scan. Any ideas on how to get it to at least attempt an indexed scan?
>
> What does the actual view look like? It seems to be pushing campaign=3
> into a scan of campaign_sent_mails (although it's using a different index
> on that).
>