Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 286FC1337B53 for ; Wed, 17 Nov 2010 16:47:39 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 98221-04 for ; Wed, 17 Nov 2010 20:47:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gransy.com (mail.gransy.com [82.208.29.194]) by mail.postgresql.org (Postfix) with ESMTP id BA9561337B5B for ; Wed, 17 Nov 2010 16:47:31 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by mail.gransy.com (Postfix) with ESMTP id D7F302E5A0D5 for ; Wed, 17 Nov 2010 21:47:30 +0100 (CET) Received: from mail.gransy.com ([127.0.0.1]) by localhost (nathalia.gransy.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id kMC9yNiJhZzN for ; Wed, 17 Nov 2010 21:47:30 +0100 (CET) Received: from [192.168.1.1] (ip-89-103-152-5.net.upcbroadband.cz [89.103.152.5]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.gransy.com (Postfix) with ESMTPSA id C05522E5A060 for ; Wed, 17 Nov 2010 21:47:30 +0100 (CET) Message-ID: <4CE43F63.2030806@fuzzy.cz> Date: Wed, 17 Nov 2010 21:47:31 +0100 From: Tomas Vondra User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.9) Gecko/20100927 Lightning/1.0b3pre Thunderbird/3.1.3 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Query Performance SQL Server vs. Postgresql References: In-Reply-To: X-Enigmail-Version: 1.1.1 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201011/294 X-Sequence-Number: 41318 Dne 17.11.2010 05:47, Pavel Stehule napsal(a): > 2010/11/17 Humair Mohammed : >> >> There are no indexes on the tables either in SQL Server or Postgresql - I am >> comparing apples to apples here. I ran ANALYZE on the postgresql tables, Actually no, you're not comparing apples to apples. You've provided so little information that you may be comparing apples to cucumbers or maybe some strange animals. 1) info about the install What OS is this running on? I guess it's Windows in both cases, right? How nuch memory is there? What is the size of shared_buffers? The default PostgreSQL settings is very very very limited, you have to bump it to a much larger value. What are the other inportant settings (e.g. the work_mem)? 2) info about the dataset How large are the tables? I don't mean number of rows, I mean number of blocks / occupied disk space. Run this query SELECT relname, relpages, reltuples, pg_size_pretty(pg_table_size(oid)) FROM pg_class WHERE relname IN ('table1', 'table2'); 3) info about the plan Please, provide EXPLAIN ANALYZE output, maybe with info about buffers, e.g. something like EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT ... 4) no indexes ? Why have you decided not to use any indexes? If you want a decent performance, you will have to use indexes. Obviously there is some overhead associated with them, but it's premature optimization unless you prove the opposite. BTW I'm not a MSSQL expert, but it seems like it's building a bitmap index on the fly, to synchronize parallelized query - PostgreSQL does not support that. regards Tomas