Factoring where clauses through UNIONS take 2

From: Jonathan Bartlett <johnnyb(at)eskimo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Factoring where clauses through UNIONS take 2
Date: 2003-04-24 17:15:50
Message-ID: Pine.GSU.4.44.0304240940140.12528-100000@eskimo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

More details can be provided if needed. Thank you in advance for your
help.

Here is the full query plan:

Nested Loop (cost=300092946.09..300095861.21 rows=163 width=137)
-> Hash Join (cost=300092946.09..300095335.40 rows=163 width=104)
Hash Cond: ("outer".sent_mail = "inner".object_id)
-> Subquery Scan aa (cost=300088861.93..300091114.08 rows=18017
width=134)
-> Unique (cost=300088861.93..300091114.08 rows=18017
width=134)
-> Sort (cost=300088861.93..300089312.36 rows=180172
width=134)
Sort Key: sent_mail, occurrence_date, "type",
data
-> Append (cost=0.00..300057538.85 rows=180172
width=134)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..764.30 rows=11118 width=80)
-> Merge Join (cost=0.00..764.30
rows=11118 width=80)
Merge Cond: ("outer".link =
"inner".object_id)
-> Index Scan using
action_click_link_index on action_click ac (cost=0.00..568.78 rows=11118
width=24)
-> Index Scan using
campaign_links_pkey on campaign_links cl (cost=0.00..27.98 rows=722
width=56)
-> Subquery Scan "*SELECT* 2"
(cost=100000000.00..100000069.00 rows=1400 width=60)
-> Seq Scan on action_reply ar
(cost=100000000.00..100000069.00 rows=1400 width=60)
-> Subquery Scan "*SELECT* 3"
(cost=100000000.00..100000092.27 rows=2327 width=134)
-> Seq Scan on action_forward af
(cost=100000000.00..100000092.27 rows=2327 width=134)
-> Subquery Scan "*SELECT* 4"
(cost=100000000.00..100000018.86 rows=586 width=16)
-> Seq Scan on action_unsubscribe
au (cost=100000000.00..100000018.86 rows=586 width=16)
-> Subquery Scan "*SELECT* 5"
(cost=0.00..18497.71 rows=39073 width=33)
-> Merge Join (cost=0.00..18497.71
rows=39073 width=33)
Merge Cond:
("outer".list_member = "inner".object_id)
Filter: (("inner".is_test =
false) OR ("inner".is_test IS NULL))
-> Index Scan using
campaign_sent_mails_list_member on campaign_sent_mails
(cost=0.00..10409.94 rows=39073 width=24)
Filter: (date_opened IS
NOT NULL)
-> Index Scan using
list_members_pkey on list_members (cost=0.00..7206.64 rows=117749 width=9)
-> Subquery Scan "*SELECT* 6"
(cost=0.00..18395.81 rows=17829 width=33)
-> Merge Join (cost=0.00..18395.81
rows=17829 width=33)
Merge Cond:
("outer".list_member = "inner".object_id)
Filter: (("inner".is_test =
false) OR ("inner".is_test IS NULL))
-> Index Scan using
campaign_sent_mails_list_member on campaign_sent_mails csm
(cost=0.00..10679.53 rows=17829 width=24)
Filter: (bounced =
true)
-> Index Scan using
list_members_pkey on list_members lm (cost=0.00..7206.64 rows=117749
width=9)
-> Subquery Scan "*SELECT* 7"
(cost=0.00..19700.90 rows=107838 width=33)
-> Merge Join (cost=0.00..19700.90
rows=107838 width=33)
Merge Cond:
("outer".list_member = "inner".object_id)
Filter: (("inner".is_test =
false) OR ("inner".is_test IS NULL))
-> Index Scan using
campaign_sent_mails_list_member on campaign_sent_mails csm
(cost=0.00..10409.94 rows=107838 width=24)
Filter: (date_sent IS
NOT NULL)
-> Index Scan using
list_members_pkey on list_members lm (cost=0.00..7206.64 rows=117749
width=9)
-> Hash (cost=4081.72..4081.72 rows=973 width=24)
-> Index Scan using campaign_sent_mails_pkey on
campaign_sent_mails csm (cost=0.00..4081.72 rows=973 width=24)
Filter: (campaign = 3)
-> Index Scan using list_members_pkey on list_members lm
(cost=0.00..3.22 rows=1 width=33)
Index Cond: ("outer".list_member = lm.object_id) (45 rows)

-----------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Olbersen 2003-04-24 17:21:31 ODBC & Access [Try 2]
Previous Message Dev 2003-04-24 17:14:14 C++ and v7.3.2