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

Re: Query Performance SQL Server vs. Postgresql

From: Humair Mohammed <humairm(at)hotmail(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Query Performance SQL Server vs. Postgresql
Date: 2010-11-21 06:25:03
Message-ID: COL115-W5263E5369E2BD0B28E78B9A83C0@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-performance

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;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) 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 COALESCE, I pointed the data load to SQL Server and ran the same query except with the TSQL specific ISNULL function.
 		 	   		  

In response to

Responses

pgsql-performance by date

Next:From: Pavel StehuleDate: 2010-11-21 11:38:43
Subject: Re: Query Performance SQL Server vs. Postgresql
Previous:From: Pavel StehuleDate: 2010-11-21 06:20:12
Subject: Re: Query Performance SQL Server vs. Postgresql

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