Re: LEFT JOIN optimization

2005/9/12, Stephen Frost <sfrost(at)snowman(dot)net>:
> * Ksenia Marasanova (ksenia(dot)marasanova(at)gmail(dot)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