From: Tom Lane <tgl@sss.pgh.pa.us>
To: Humair Mohammed <humairm@hotmail.com>
Cc: pavel.stehule@gmail.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Performance SQL Server vs. Postgresql
Date: Wed, 17 Nov 2010 18:21:18 -0500
Message-ID: <26558.1290036078@sss.pgh.pa.us> (raw)
In-Reply-To: <COL115-W186A7B93051E6907958ED6A8380@phx.gbl>
References: <COL115-W5250BF8BDC50E0A64DABE4A8360@phx.gbl>
<AANLkTin+MPhrYUzkCZjdAMLhF3KKZCCuvDm0=Npf9jz7@mail.gmail.com>
<COL115-W3987D9D7DE908C5C47792DA8380@phx.gbl>
<AANLkTi=yFV69yNZpHzKiZRtZ0X0PtKcpOrMNNkAo+Si6@mail.gmail.com>
<COL115-W186A7B93051E6907958ED6A8380@phx.gbl>
Humair Mohammed <humairm@hotmail.com> 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 = (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)"" -> Seq Scan on pivotbad pb (cost=0.00..2804.96 rows=93496 width=134) (actual time=0.009..48.200 rows=93496 loops=1)"" -> Hash (cost=7537.12..7537.12 rows=251212 width=134) (actual time=42919.453..42919.453 rows=251212 loops=1)"" Buckets: 1024 Batches: 64 Memory Usage: 650kB"" -> Seq Scan on 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"
I have no idea how much memory SQL Server thinks it can use, but
Postgres is limiting itself to work_mem which you've apparently left at
the default 1MB. You might get a fairer comparison by bumping that up
some --- try 32MB or so. You want it high enough so that the Hash
output doesn't say there are multiple batches.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, humairm@hotmail.com, pavel.stehule@gmail.com
Subject: Re: Query Performance SQL Server vs. Postgresql
In-Reply-To: <26558.1290036078@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox