writes: > Is there a propensity for 9.2.4 to prefer a sort-merge-join, in place of= a > hash join? Not particularly; I don't think there's any actual difference in the cost estimation equations between 9.2 and 9.3. The two plans you show are close enough in estimated cost that the ordering of their costs might be coming out differently just as a matter of random variation in statistics. It'd be worth double-checking the work_mem setting on both systems, though, as (IIRC) an undersized work_mem hurts the estimate for hashes more than for sorts. regards, tom lane --B_3482745780_64553334 Content-type: text/html; charset="UTF-8" Content-transfer-encoding: quoted-printable

Hmm. Interesti= ng.

Thanks Tom, it does indeed look like the p= lanner is evaluating and excluding the hashed-join plan as having a higher c= ost. I can see this by setting enable_mergejoin=3D0.

I think this ma= y play against other aspects of the query (though only marginally), so I can= ’t really compare the resulting cost metrics – but they’re= certainly close.

I’ve = just now played a little more with work_mem. I had already tried incre= asing work_mem to all kinds of obscene levels.

Oddl= y, it seems the solution here is in fact to *reduce* work_mem in order to el= icit the preferred hash-join based plan.

In fact, I needed to redu= ce it to as low as 64MB for this behaviour – which seems counter-intui= tive. These are not small queries, so I previously had it pushed up to 6GB f= or these tasks.

For smaller datasets,I need t= o reduce work_mem further still in order to obtain a hash-join plan – = though the difference in execution time becomes less of a problem at this si= ze.

Tim

From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Monday, 12 May 2014 12:23
To: = Tim Kane <tim.kane@gmail.com><= br>Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>= ;
Subject: Re: [PERFORM] 9.2.4 vs = 9.3.0 query planning (sort merge join vs hash join)

Tim Kane <tim.kane@gma= il.com> writes:

<= div> Is there a propensity for 9.2.4 to prefer a sort-merge-join, in place o= f a

hash join?

Not particul= arly; I don't think there's any actual difference in the cost

esti= mation equations between 9.2 and 9.3. The two plans you show are<= /div>

close enough in estimated cost that the ordering of their costs mi= ght be

coming out differently just as a matter of random variation= in statistics.

It'd be worth double-checking the w= ork_mem setting on both systems,

though, as (IIRC) an undersized w= ork_mem hurts the estimate for hashes

more than for sorts.

regards, tom lane

--B_3482745780_64553334--