On Mon, 2005-03-14 at 12:03, Marcin Giedz wrote:
> Hello...
>
>
> Our company is going to change SQL engine from MySQL to PSQL. Of course some
> performance problems occured. Our server is Dual Xeon 3.0GHz + 8GB RAM +
> RAID1(software - two 146GB SCSI 15k) for sql data + RAID1(software - two
> 146GB SCSI 15k) for pg_xlog. Postgres.conf parameters are as follows:
>
> max_connections = 150
> shared_buffers = 50000 # min 16, at least max_connections*2, 8KB each
> work_mem = 2048 # min 64, size in KB
50,000 shared buffers may or may not be too much. Try it at different
sizes from 5,000 or so up to 50,000 and find the "knee". It's usually
closer to 10,000 than 50,000, but ymmv...
On the other hand, for a machine with 8 gigs of ram, 2 meg of work_mem
is pretty small. Try bumping it up to 8 or 16 megs. You can change
this one "on the fly" for testing, so just do:
set work_mem=16384;
and then run the query again and see if that helps. The hash aggregate
method uses sort/work mem to do it's work, and if it doesn't think it
can hold the result set in that space the planner will pick another
method, like the merge left join.
In your explain analyze output, look for gross mismatches between
estimated and actual rows. Most of yours here look pretty good in the
areas where the data is being collected, but during the merges, the
numbers are WAY off, but i'm not sure what to do to change that.