* Tyrrill, Ed (tyrrill_ed@emc.com) wrote:
> Based on all this we will be going with 8.2.4.1, but it seems like
> currently the query planner isn't choosing the best plan for this case.

Was the 'work_mem' set to the same thing on all these runs? Also, you
might try increasing the 'work_mem' under 8.2.4, at least for this query
(you can set it by just doing: set work_mem = '2GB'; or similar in psql,
or you can change the default in postgresql.conf).

The big thing of note, it seems, is that you've got enough memory and
it's coming out faster when doing a hash-join vs. a sort + merge-join.
Could likely be because it doesn't think there's enough work memory
available for the hash, which might change based on the values it gets
from the statistics on how frequently something shows up, etc.

Enjoy,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGgDomrzgMPqB3kigRAhyYAKCFZKXqJlt4rRSVUwknk0 IaFAByxACcCj0S
QQtrIiR0BQq9QcjhMsPo8Uo=
=r0Mu
-----END PGP SIGNATURE-----

On Mon, 2007-06-25 at 17:56 -0400, Stephen Frost wrote:
> Was the 'work_mem' set to the same thing on all these runs? Also, you
> might try increasing the 'work_mem' under 8.2.4, at least for this query
> (you can set it by just doing: set work_mem = '2GB'; or similar in psql,
> or you can change the default in postgresql.conf).
>
> The big thing of note, it seems, is that you've got enough memory and
> it's coming out faster when doing a hash-join vs. a sort + merge-join.
> Could likely be because it doesn't think there's enough work memory
> available for the hash, which might change based on the values it gets
> from the statistics on how frequently something shows up, etc.
>
> Enjoy,
>
> Stephen

Yes, work_mem was set to 128MB for all runs. All settings were the same
except for the change to default_statistics_target. I'm certainly
memory constrained, but giving 2GB to one one session doesn't allow
other sessions to do anything. Possibly when we upgrade to 16GB. :-)




---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

* Ed Tyrrill (tyrrill_ed@emc.com) wrote:
> Yes, work_mem was set to 128MB for all runs. All settings were the same
> except for the change to default_statistics_target. I'm certainly
> memory constrained, but giving 2GB to one one session doesn't allow
> other sessions to do anything. Possibly when we upgrade to 16GB. :-)

You might consider a smaller increase, say to 256MB, to see if that'll
switch it to a hash join (and then watch the *actual* memory usage, of
course), if you're looking for performance for this query at least.

Yeah, 2GB is what I typically run on our data warehouse box, which is a
nice dual-proc/dual-core DL385 w/ 16GB of ram. The annoying thing is
that I can still run it out of memory sometimes, even w/ 16GB. :/

Thanks,

Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGgFUprzgMPqB3kigRAvmLAJ98vNbgO8VZc0HYnz6ptx BCiOxf5ACfS1q1
vr3/uF/sudWq23MnhDk7aQM=
=I9Mj
-----END PGP SIGNATURE-----