Re: union all vs. left outer join

From: <henrik.sorensen_at_balcab.ch>
Date: Tue, 08 Nov 2005 07:14:40 +0000
Message-ID: <dkpbfc$fu1$1@news.hispeed.ch>

Stefan Rybacki wrote:

> henrik.sorensen_at_balcab.ch wrote:
>
> what postgreSQL does is joining B with A then joins this result again with
> C, but it doesn't use indices for this (see the output: Seq Scan on c, Seq
> Scan on b, Seq Scan on a (this could be because of there are no statistics
> and the query is still fast enough))
>
> Afterwards it does a loop over the joined tables and the before
> materialized join of D and E.

so after all, postgreSQL did not get it right either.

>

>> 
>> 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 ?

>
> Yes I can. ;)
> A-B-D-E and A-C-D-E are for the second query that uses union all.
> For the first query it does:
> E-A-B-C-D
> and it is using index XEM1,XBA,XCA but isn't using any index of table D
which means mySql also failed ...

I am beginning to think what I am trying to do with the query is not possible.
Is there any reason why the A-B-C has to be materialized ?

>
> Regards
> Stefan

Henrik Received on Tue Nov 08 2005 - 01:14:40 CST