Re: query not using index

On 23/12/13 21:58, Johann Spies wrote:
>
>
>
> On 19 December 2013 16:48, Tom Lane <tgl@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     Johann Spies <johann.spies@gmail.com
>     <mailto:johann.spies@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?
>
>

You can disable the hash and merge join options by doing:

SET enable_hashjoin=off;
SET enable_mergejoin=off;

before running the query again. Timing it (or EXPLAIN ANALYZE) should
demonstrate if that planner made the right call by choosing hash or
merge in the first place.

regards

Mark