Hello 2010/11/21 Humair Mohammed <humairm@xxxxxxxxxxx>: > 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.stehule@xxxxxxxxx >> Date: Sun, 21 Nov 2010 12:38:43 +0100 >> Subject: Re: Query Performance SQL Server vs. Postgresql >> To: humairm@xxxxxxxxxxx >> CC: pgsql-performance@xxxxxxxxxxxxxx >> >> 2010/11/21 Humair Mohammed <humairm@xxxxxxxxxxx>: >> > >> > 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. >> > > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
