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
