Home -> Usenet -> comp.databases.theory -> Re: union all vs. left outer join

Re: union all vs. left outer join

From: <henrik.sorensen_at_balcab.ch>
Date: Tue, 08 Nov 2005 00:13:14 +0000
Message-ID: <dkoip6$upp$1_at_news.hispeed.ch> 






Stefan Rybacki wrote:



> henrik.sorensen_at_balcab.ch wrote:



>> ...
>> 
>> Any ideas, or hints would be much appreciated.
>> 
>> Henrik




> Its not slow over here.


I should have mentioned that the test case of course is not the real
problem. And both performs well here as well, but the real tables in our
production system have 200'000 records each, and it is crucial to get the
access path right.


> 

> Tested on postgreSQL 8





thanks for testing it.


It seems postgreSQL does what I am looking for, but I am not familiar with
the output you have shown.


Maybe you can confirm this is happening:
The accesspath should ideally be: use index EM1 to find matching records,
join with table D using index XDE, use the found set of D.bids to join
using matching indexes on C and B respectively, and finally join to the
matching records in A, also using indexes.



It might be that the actual testcase have too few records to demonstrate
this accesspath.



Looking at the explain from Mysql, it indeed seem that the two access paths
A-B-D-E and A-C-D-E is recognized. Can you see if the matching indexes are
used ?



Henrik
Received on Tue Nov 08 2005 - 01:13:14 CET












Original text of this message