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: | Raw Message | Whole Thread | 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 |