I did some further analysis and here are the results:
work_mem;response_time1MB;62 seconds2MB;2 seconds4MB;700 milliseconds8MB;550
milliseconds
In all cases shared_buffers were set to the default value of 32MB. As you can
see the 1 to 2 MB jump on the work_mem does wonders. I probably don't need
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 MB (whic
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/
. Postgresql
> To: huma...@hotmail.com
> CC: pgsql-performance@postgresql.org
>
> 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_
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
votgood].[ID] as
[pg].[ID],[master].[dbo].[pivotgood].[Question] as [pg].[Question])))
> From: pavel.steh...@gmail.com
> Date: Tue, 16 Nov 2010 08:12:03 +0100
> Subject: Re: [PERFORM]
> To: huma...@hotmail.com
> CC: pgsql-performance@postgresql.org
>
> 2010/11/15 Humair Moh
n
pivotgood pg (cost=0.00..7537.12 rows=251212 width=134) (actual
time=0.119..173.019 rows=251212 loops=1)""Total runtime: 49503.450 ms"
> From: pavel.steh...@gmail.com
> Date: Wed, 17 Nov 2010 05:47:51 +0100
> Subject: Re: Query Performance SQL Server vs. Postgresql
>
I have 2 tables with a 200,000 rows of data 3 character/string columns ID,
Question and Response. The query below compares the data between the 2 tables
based on ID and Question and if the Response does not match between the left
table and the right table it identifies the ID's where there is a
