OUTER JOIN vs UNION ... faster?

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

Responses

Browse pgsql-hackers by date

  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