| From: | The Hermit Hacker <scrappy(at)hub(dot)org> |
|---|---|
| To: | <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | OUTER JOIN vs UNION ... faster? |
| Date: | 2001-04-24 12:06:29 |
| Message-ID: | Pine.BSF.4.33.0104240854000.4451-100000@mobile.hub.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Got a query that looks like:
========================================================================
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send0,card_info,category_details
WHERE send0.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send1,card_info,category_details where send1.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send2,card_info,category_details where send2.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send3,card_info,category_details where send3.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send4,card_info,category_details where send4.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send5,card_info,category_details where send5.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
FROM send6,card_info,category_details where send6.card_id=card_info.card_id
AND category_details.mcategory='e-cards'
AND card_info.main_cat=category_details.category
AND send_date >= '2001/04/08'
AND send_date <= '2001/05/14' group by 1,2
UNION ALL
SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
========================================================================
*Really* dreading the thought of changing it to an OUTER JOIN, and am
wondering if there would be a noticeable speed difference between going
from the UNION above to an OUTER JOIN, or should they be about the same?
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Max Khon | 2001-04-24 12:34:08 | Re: RI oddness |
| Previous Message | Hannu Krosing | 2001-04-24 09:33:43 | Re: Look what book I found |