From: tv@fuzzy.cz
To: Humair Mohammed <humairm@hotmail.com>
Cc: pavel.stehule@gmail.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Performance SQL Server vs. Postgresql
Date: Sun, 21 Nov 2010 16:36:25 +0100
Message-ID: <3ba1d0462bc7df1c3eb5a6d4777ce08a.squirrel@sq.gransy.com> (raw)
In-Reply-To: <COL115-W6193714B75A26E58EA773AA83C0@phx.gbl>
References: <COL115-W5250BF8BDC50E0A64DABE4A8360@phx.gbl>
<AANLkTin+MPhrYUzkCZjdAMLhF3KKZCCuvDm0=Npf9jz7@mail.gmail.com>
<COL115-W3987D9D7DE908C5C47792DA8380@phx.gbl>
<AANLkTi=yFV69yNZpHzKiZRtZ0X0PtKcpOrMNNkAo+Si6@mail.gmail.com>
<COL115-W186A7B93051E6907958ED6A8380@phx.gbl>
<AANLkTinQmaQ5Ui-vQwfqWFtOTCj76NWFBr6VDKMdVUCs@mail.gmail.com>
<COL115-W6193714B75A26E58EA773AA83C0@phx.gbl>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, humairm@hotmail.com, pavel.stehule@gmail.com
Subject: Re: Query Performance SQL Server vs. Postgresql
In-Reply-To: <3ba1d0462bc7df1c3eb5a6d4777ce08a.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox