> First, I modified the work_mem setting to 1GB (reloaded config) from the
> default 1MB and I see a response time of 33 seconds. Results below from
> EXPLAIN ANALYZE:
...
> Second, I modified the work_mem setting to 2GB (reloaded config) and I see
> a response time of 38 seconds. Results below from EXPLAIN ANALYZE:
...
How did you reload the config? Using 'kill -HUP pid'? That should work
fine. Have you cheched 'work_mem' after the reload?
Because the explain plans are exactly the same (structure, estimated
costs). The really interesting bit is this and it did not change at all
Buckets: 1024 Batches: 64 Memory Usage: 650kB
As Tom Lane already mentioned, splitting hash join into batches (due to
small memory) adds overhead, the optimal number of batches is 1. But I
guess 1GB of work_mem is an overkill - something like 64MB should be fine.
The suspicious thing is the query plans have not changed at all
(especially the number of batches). I think you're not telling us
something important (unintentionally of course).
> By no means I am trying to compare the 2 products. When I noticed the slow
> behavior of COALESCE I tried it on SQL Server. And since they are running
> on the same machine my comment regarding apples to apples. It is possible
> that this is not an apples to apples comparison other than the fact that
> it is running on the same machine.
OK. The point of my post was that you've provided very little info about
the settings etc. so it was difficult to identify why PostgreSQL is so
slow.
Tomas
