Re: Four table join with million records - performance improvement?

Vijay Moses <vijay(dot)moses(at)gmail(dot)com> writes:
> Hi i have four sample tables ename, esal, edoj and esum
> All of them have 1000000 records. Im running the following
> query : select ename.eid, name, sal, doj, summary from
> ename,esal,edoj,esum where ename.eid=esal.eid and ename.eid=edoj.eid
> and ename.eid=esum.eid. Its a join of all four tables which returns
> all 1 million records. The eid field in ename is a Primary Key and the
> eid in all other tables are Foreign Keys. I have created an index for
> all Foreign Keys. This query takes around 16 MINUTES to complete. Can
> this time be reduced?

The indexes will be completely useless for that sort of query; the
reasonable choices are sort/merge or hashjoin. For either one, your
best way to speed it up is to increase sort_mem.

regards, tom lane