On 19 December 2013 16:48, Tom Lane <t...@sss.pgh.pa.us> wrote: > Johann Spies <johann.sp...@gmail.com> writes: > > I would appreciate some help optimising the following query: > > It's a mistake to imagine that indexes are going to help much with > a join of this size. Hash or merge join is going to be a lot better > than nestloop. What you need to do is make sure those will perform > as well as possible, and to that end, it'd likely help to raise > work_mem. I'm not sure if you can sanely put it high enough to > make the query operate totally in memory --- it looks like you'd > need work_mem of 500MB or more to prevent any of the sorts or > hashes from spilling to disk, and keep in mind that this query > is going to use several times work_mem because there are multiple > sorts/hashes going on. But if you can transiently dedicate a lot > of RAM to this query, that should help some. I'd suggest increasing > work_mem via a SET command in the particular session running this > query --- you don't want such a high value to be the global default. >
Thanks Tom. Raising work_mem from 384MB to 512MB made a significant difference. You said "hash or merge join id going to be a lot better than nestloop". Is that purely in the hands of the query planner or what can I do to get the planner to use that options apart from raising the work_mem? Regards Johann -- Because experiencing your loyal love is better than life itself, my lips will praise you. (Psalm 63:3)
