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_at_srv.protecting.net>
>> Stefan Rybacki wrote:
>>
>> 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.
>>
>> 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,
Date: 08 Nov 2005 11:37:16 +0100
Message-ID: <87acgf1l43.fsf_at_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 - 11:37:16 CET