Skip site navigation (1) Skip section navigation (2)

Re: Query Performance SQL Server vs. Postgresql

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Humair Mohammed <humairm(at)hotmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query Performance SQL Server vs. Postgresql
Date: 2010-11-21 15:14:10
Message-ID: AANLkTi=QTaO47YO8WQrszYPtcfW-wMAv8f1iatA1bE9H@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hello

2010/11/21 Humair Mohammed <humairm(at)hotmail(dot)com>:
> That was a typo:
> work_mem = 2GB
> shared_buffers = 2GB

ok, then try to decrease a shared_buffers. Maybe a win7 has a some
problem - large a shared buffers are well just for UNIX like systems.
I am thinking so 500 MB is enough

Regards

Pavel Stehule

>> From: pavel(dot)stehule(at)gmail(dot)com
>> Date: Sun, 21 Nov 2010 12:38:43 +0100
>> Subject: Re: [PERFORM] Query Performance SQL Server vs. Postgresql
>> To: humairm(at)hotmail(dot)com
>> CC: pgsql-performance(at)postgresql(dot)org
>>
>> 2010/11/21 Humair Mohammed <humairm(at)hotmail(dot)com>:
>> >
>> > 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
>>
>>
>> > 2) Dataset
>> > name,pages,tuples,pg_size_pretty
>> > "pivotbad";1870;93496;"15 MB"
>> > "pivotgood";5025;251212;"39 MB"
>> > 3) EXPLAIN (ANALYZE ON, BUFFERS ON)
>> > "Hash Join  (cost=16212.30..52586.43 rows=92869 width=17) (actual
>> > time=25814.222..32296.765 rows=3163 loops=1)"
>> > "  Hash Cond: (((pb.id)::text = (pg.id)::text) AND ((pb.question)::text
>> > =
>> > (pg.question)::text))"
>> > "  Join Filter: ((COALESCE(pb.response, 'MISSING'::character
>> > varying))::text
>> > <> (COALESCE(pg.response, 'MISSING'::character varying))::text)"
>> > "  Buffers: shared hit=384 read=6511, temp read=6444 written=6318"
>> > "  ->  Seq Scan on pivotbad pb  (cost=0.00..2804.96 rows=93496
>> > width=134)
>> > (actual time=0.069..37.143 rows=93496 loops=1)"
>> > "        Buffers: shared hit=192 read=1678"
>> > "  ->  Hash  (cost=7537.12..7537.12 rows=251212 width=134) (actual
>> > time=24621.752..24621.752 rows=251212 loops=1)"
>> > "        Buckets: 1024  Batches: 64  Memory Usage: 650kB"
>> > "        Buffers: shared hit=192 read=4833, temp written=4524"
>> > "        ->  Seq Scan on pivotgood pg  (cost=0.00..7537.12 rows=251212
>> > width=134) (actual time=0.038..117.780 rows=251212 loops=1)"
>> > "              Buffers: shared hit=192 read=4833"
>> > "Total runtime: 32297.305 ms"
>> > 4) INDEXES
>> > I 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 COALESCE, I pointed the
>> > data
>> > load to SQL Server and ran the same query except with the TSQL specific
>> > ISNULL function.
>> >
>

In response to

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2010-11-21 15:23:07
Subject: Re: Query Performance SQL Server vs. Postgresql
Previous:From: kuopoDate: 2010-11-21 14:55:44
Subject: Re: autovacuum blocks the operations of other manual vacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group