Hi list,
I don't have much experience with Postgres optimization, somehow I was
happily avoiding anything more difficult than simple select statement,
and it was working all right.
Now LEFT JOIN must be used, and I am not happy with the performance:
It takes about 5 seconds to run very simple LEFT JOIN query on a table
"user_" with ~ 13.000 records left joined to table "church" with ~ 300
records on Powerbook PPC 1.67 GHz with 1.5 GB ram.
Is it normal?
Some details:
test=# explain 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)
Hash Cond: ("outer".church_id = "inner".id)
-> Seq Scan on user_ (cost=0.00..7430.63 rows=12763 width=245)
-> Hash (cost=5.75..5.75 rows=275 width=80)
-> Seq Scan on church (cost=0.00..5.75 rows=275 width=80)
(5 rows)
From what I understand, it doesn't use foreign key index on user_
table. So I tried:
mydb=# set enable_seqscan='false';
SET
mydb=# explain 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)
Merge Cond: ("outer".id = "inner".church_id)
-> Index Scan using chirch_pkey on church (cost=0.00..17.02
rows=275 width=80)
-> Index Scan using user__church_id on user_ (cost=0.00..44500.34
rows=12763 width=245)
(4 rows)
It's my first time reading Query plans, but from wat I understand, it
doesn't make the query faster..
Any tips are greatly appreciated.
--
Ksenia
