2005/9/12, Stephen Frost <[EMAIL PROTECTED]>: > * Ksenia Marasanova ([EMAIL PROTECTED]) 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org