Re: LEFT JOIN optimization

2005/9/12, Stephen Frost <sfrost@snowman.net>:
> * Ksenia Marasanova (ksenia.marasanova@gmail.com) wrote:
> > Any tips are greatly appreciated.
>
> EXPLAIN ANALYZE of the same queries would be much more useful.

Thanks, here it is:

test=# explain analyze select * from user_ left join church on
user_.church_id = church.id;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=6.44..7626.69 rows=12763 width=325) (actual
time=388.573..2016.929 rows=12763 loops=1)
   Hash Cond: ("outer".church_id = "inner".id)
   ->  Seq Scan on user_  (cost=0.00..7430.63 rows=12763 width=245)
(actual time=360.431..1120.012 rows=12763 loops=1)
   ->  Hash  (cost=5.75..5.75 rows=275 width=80) (actual
time=27.985..27.985 rows=0 loops=1)
         ->  Seq Scan on church  (cost=0.00..5.75 rows=275 width=80)
(actual time=0.124..26.953 rows=275 loops=1)
 Total runtime: 2025.946 ms
(6 rows)

test=# set enable_seqscan='false';
SET
test=# explain analyze select * from user_ left join church on
user_.church_id = church.id;

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
 Merge Right Join  (cost=0.00..44675.77 rows=12763 width=325) (actual
time=0.808..2119.099 rows=12763 loops=1)
   Merge Cond: ("outer".id = "inner".church_id)
   ->  Index Scan using chirch_pkey on church  (cost=0.00..17.02
rows=275 width=80) (actual time=0.365..5.471 rows=275 loops=1)
   ->  Index Scan using user__church_id on user_  (cost=0.00..44500.34
rows=12763 width=245) (actual time=0.324..1243.348 rows=12763 loops=1)
 Total runtime: 2131.364 ms
(5 rows)


I followed some tips on the web and vacuum-ed database, I think the
query is  faster now, almost acceptable, but still interesting to know
if it possible to optimize it...

Thanks again,
--
Ksenia