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_at_news.hispeed.ch>
>
> 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 ...
Date: Tue, 08 Nov 2005 07:14:40 +0000
Message-ID: <dkpbfc$fu1$1_at_news.hispeed.ch>
> 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 - 08:14:40 CET