Re: union all vs. left outer join

From: Harald Fuchs <>
Date: 08 Nov 2005 11:37:16 +0100
Message-ID: <>

In article <>, Stefan Rybacki <> writes:

> wrote:

>> Stefan Rybacki wrote:
>>> 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 - 11:37:16 CET

Original text of this message