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@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 Mon Nov 07 2005 - 18:13:14 CST