Re: query not using index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 23/12/13 21:58, Johann Spies wrote:



On 19 December 2013 16:48, Tom Lane <tgl@xxxxxxxxxxxxx
<mailto:tgl@xxxxxxxxxxxxx>> wrote:

    Johann Spies <johann.spies@xxxxxxxxx
    <mailto:johann.spies@xxxxxxxxx>> 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



--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux