Re: union all vs. left outer join

From: Harald Fuchs <hf0923x_at_protecting.net>
Date: 08 Nov 2005 11:37:16 +0100
Message-ID: <87acgf1l43.fsf@srv.protecting.net>

In article <3ta67mFrii33U1_at_individual.net>, Stefan Rybacki <stefan.rybacki_at_gmx.net> writes:

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

No, PostgreSQL does not (and should not) use indices because the tables are so small. I guess with large tables and proper statistics it'll switch to index scans. Received on Tue Nov 08 2005 - 04:37:16 CST