Re: union all vs. left outer join

From: <henrik.sorensen_at_balcab.ch>
Date: Tue, 08 Nov 2005 21:53:29 +0000
Message-ID: <dkquv7$pmk$1_at_news.hispeed.ch>

Harald Fuchs wrote:

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

>> henrik.sorensen_at_balcab.ch wrote:
>>> 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.
I don't really believe this in this case. Because why would postgreSQL choose to use an index in the much smaller table E ?
(can be seen in the explain from Stefan ealier:

   -> Hash (cost=15.39..15.39 rows=4 width=75)

       ->  Index Scan using xem1 on e  (cost=0.00..15.39 rows=4 width=75)
              Index Cond: (m1 = 'A'::bpchar)

IMO, what should have happen is a list of consisting of B.aid and C.aid should be built and used for an indexed lookup on A.

After all it doesn't make sense to scan the whole table, when not necessary.

Henrik Received on Tue Nov 08 2005 - 22:53:29 CET