Re: union all vs. left outer join

henrik.sorensen_at_balcab.ch wrote:
> 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,

yes

> join with table D using index XDE,

no its joining with table D but doesn't use an index (Seq Scan on d)

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

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.

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

Regards
Stefan

>
> Henrik
>
>
Received on Tue Nov 08 2005 - 00:27:51 CET