On Mon, Nov 22, 2010 at 7:12 PM, Humair Mohammed wrote:
> I did some further analysis and here are the results:
> work_mem;response_time
> 1MB;62 seconds
> 2MB;2 seconds
> 4MB;700 milliseconds
> 8MB;550 milliseconds
> In all cases shared_buffers were set to the default value of 32MB. As you
> can
x27;t need this
to be any higher than 8 or 16 MB. Thanks to all for help!
Humair
> Date: Mon, 22 Nov 2010 12:00:15 +0100
> Subject: Re: [PERFORM] Query Performance SQL Server vs. Postgresql
> From: t...@fuzzy.cz
> To: huma...@hotmail.com
> CC: pgsql-performance@postgresql.org
>
&
> I believe you can set work_mem to a different value just for the duration
> of
> a single query, so you needn't have work_mem set so high if for every
> query
> on the system. A single query may well use a multiple of work_mem, so you
> really probably don't want it that high all the time unless
On Sun, Nov 21, 2010 at 10:21 PM, Humair Mohammed wrote:
>
> Correct, the optimizer did not take the settings with the pg_ctl reload
> command. I did a pg_ctl restart and work_mem now displays the updated value.
> I had to bump up all the way to 2047 MB to get the response below (with
> work_mem a
>
>
> Correct, the optimizer did not take the settings with the pg_ctl reload
> command. I did a pg_ctl restart and work_mem now displays the updated
> value. I had to bump up all the way to 2047 MB to get the response below
> (with work_mem at 1024 MB I see 7 seconds response time) and with 2047 M
->
Seq Scan on pivotgood pg (cost=0.00..7537.12 rows=251212 width=134) (actual
time=0.004..26.242 rows=251212 loops=1)"" Buffers: shared
hit=5025""Total runtime: 331.168 ms"
Humair
> CC: t...@fuzzy.cz; huma...@hotmail.com; pavel.steh...@gmail.com;
> p
That was a typo:
work_mem = 2GBshared_buffers = 2GB
> From: pavel.steh...@gmail.com
> Date: Sun, 21 Nov 2010 12:38:43 +0100
> Subject: Re: [PERFORM] Query Performance SQL Server vs. Postgresql
> To: huma...@hotmail.com
> CC: pgsql-performance@postgresql.org
>
> 2010/
l Stehule
>> From: pavel.steh...@gmail.com
>> Date: Sun, 21 Nov 2010 12:38:43 +0100
>> Subject: Re: [PERFORM] Query Performance SQL Server vs. Postgresql
>> To: huma...@hotmail.com
>> CC: pgsql-performance@postgresql.org
>>
>> 2010/11/21 Humair Mohammed :
On Nov 21, 2010, at 12:16 PM, Tom Lane wrote:
> t...@fuzzy.cz writes:
>>> 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
>
I am running 64-bit Windows 7 Enterprise with 8G RAM and a Dual Core 2.67 Ghz
Intel CPU. Both the SQL 2008 R2 and Postgresql are installed on the same
machine. The DISTINCT FROM instead of the COALESCE does not help much. I ran 2
further tests with work_mem modifications (please note memory usa
1) OS/Configuration64-bit Windows 7 Enterprise with 8G RAM and a Dual Core 2.67
Ghz Intel CPUpostgresql-x64-9.0 (PostgreSQL 9.0.1, compiled by Visual C++ build
1500, 64-bit)work_mem 2GBshared_buffers = 22)
Datasetname,pages,tuples,pg_size_pretty"pivotbad";1870;93496;"15
MB""pivotgood";5025;2
t...@fuzzy.cz writes:
>> 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?
> Pavel Stehule wrote:
>> 2010/11/21 Humair Mohammed :
>
>>> shared_buffers = 2
>
>> shared_buffers = 2 ???
>
> Yeah, if that's not a typo, that's a very serious misconfiguration.
I guess that's a typo, as the explain plain in one of the previous posts
contains
Buffers: shared hit=192 read=48
>> 4) INDEXESI can certainly add an index but given the table sizes I am
>> not
>> sure if that is a factor. This by no means is a large dataset less than
>> 350,000 rows in total and 3 columns. Also this was just a quick dump of
>> data for comparison purpose. When I saw the poor performance on th
> 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 f
Pavel Stehule wrote:
> 2010/11/21 Humair Mohammed :
>> shared_buffers = 2
> shared_buffers = 2 ???
Yeah, if that's not a typo, that's a very serious misconfiguration.
With 8 GB of RAM in the machine, this should probably be set to
somewhere between 200 MB and 2 GB, depending on your workloa
> 4) INDEXESI can certainly add an index but given the table sizes I am not
> sure if that is a factor. This by no means is a large dataset less than
> 350,000 rows in total and 3 columns. Also this was just a quick dump of
> data for comparison purpose. When I saw the poor performance on the
> COA
2010/11/21 Humair Mohammed :
>
> 1) OS/Configuration
> 64-bit Windows 7 Enterprise with 8G RAM and a Dual Core 2.67 Ghz Intel CPU
> postgresql-x64-9.0 (PostgreSQL 9.0.1, compiled by Visual C++ build 1500,
> 64-bit)
> work_mem 2GB
> shared_buffers = 2
shared_buffers = 2 ???
Regards
Pavel Stehule
Hello
I don't know. I checked similar query on similar dataset (size), and I
have a times about 3 sec on much more worse notebook. So problem can
be in disk IO operation speed - maybe in access to TOASTed value.
2010/11/21 Humair Mohammed :
> I am running 64-bit Windows 7 Enterprise with 8G RAM a
Tom Lane wrote:
> I have no idea how much memory SQL Server thinks it can use
Hmmm... That triggered an old memory -- when we were running SQL
Server on Windows there was some registry setting which we tweaked
to prevent the OS from trying to cache disk I/O. (Sorry I don't
remember the name
Hello,
there should be a problem in a statistic, they are out of reality.
Please, try to use a DISTINCT OF operator now - maybe a statistic will
be better. Next - try to increase a work_mem. Hash join is
untypically slow in your comp.
Regards
Pavel Stehule
2010/11/17 Humair Mohammed :
> Yes st
Humair Mohammed writes:
> Yes strange indeed, I did rerun ANALYZE and VACCUM. Took 70 seconds to rerun
> the query. Results from EXPLAIN ANALYZE below:
> "Hash Join (cost=16212.30..52586.43 rows=92869 width=17) (actual
> time=43200.223..49502.874 rows=3163 loops=1)"" Hash Cond: (((pb.id)::text
There are no indexes on the tables either in SQL Server or Postgresql - I am
comparing apples to apples here. I ran ANALYZE on the postgresql tables, after
that query performance times are still high 42 seconds with COALESCE and 35
seconds with IS DISTINCT FROM.
Here is the execution plan from
Yes strange indeed, I did rerun ANALYZE and VACCUM. Took 70 seconds to rerun
the query. Results from EXPLAIN ANALYZE below:
"Hash Join (cost=16212.30..52586.43 rows=92869 width=17) (actual
time=43200.223..49502.874 rows=3163 loops=1)"" Hash Cond: (((pb.id)::text =
(pg.id)::text) AND ((pb.ques
pgsql-performance@postgresql.org
Sent: Wed Nov 17 15:51:55 2010
Subject: Re: [PERFORM] Query Performance SQL Server vs. Postgresql
I have to concur. Sql is written specifially and only for Windows. It is
optimized for windows. Postgreal is writeen for just about everything trying
to use common co
I have to concur. Sql is written specifially and only for Windows. It is
optimized for windows. Postgreal is writeen for just about everything
trying to use common code so there isn't much optimization because it has to
be optimized based on the OS that is running it. Check out your config and
s
Dne 17.11.2010 05:47, Pavel Stehule napsal(a):
> 2010/11/17 Humair Mohammed :
>>
>> There are no indexes on the tables either in SQL Server or Postgresql - I am
>> comparing apples to apples here. I ran ANALYZE on the postgresql tables,
Actually no, you're not comparing apples to apples. You've pr
2010/11/17 Humair Mohammed :
>
> There are no indexes on the tables either in SQL Server or Postgresql - I am
> comparing apples to apples here. I ran ANALYZE on the postgresql tables,
> after that query performance times are still high 42 seconds with COALESCE
> and 35 seconds with IS DISTINCT FRO
28 matches
Mail list logo
