X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 34A355288B for ; Tue, 24 May 2005 02:47:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34224-03 for ; Tue, 24 May 2005 05:47:21 +0000 (GMT) Received: from web53106.mail.yahoo.com (web53106.mail.yahoo.com [206.190.39.209]) by svr1.postgresql.org (Postfix) with SMTP id 8298152849 for ; Tue, 24 May 2005 02:47:19 -0300 (ADT) Received: (qmail 3990 invoked by uid 60001); 24 May 2005 05:47:15 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=1lpNa5tHopoAPuYspEXPbKmsomJu7TjZMTb6YQ7/qkIqx3pJCnmOUTncBoNMF+CA80Hi2dwVwhitzCRnVTQm+VapVWGVWYiYDYPk5YwRwFAtqiv7K4UOHaeuhIkLptaK2kyypTwXGp9I3bDQvsN/ZnEhb1e75XdkF7EZMwXElUU= ; Message-ID: <20050524054715.3988.qmail@web53106.mail.yahoo.com> Received: from [68.70.32.45] by web53106.mail.yahoo.com via HTTP; Mon, 23 May 2005 22:47:15 PDT Date: Mon, 23 May 2005 22:47:15 -0700 (PDT) From: mark durrant Subject: Select performance vs. mssql To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.266 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, FROM_ENDS_IN_NUMS X-Spam-Level: ** X-Archive-Number: 200505/353 X-Sequence-Number: 12517 Hi, I have some experience with MSSQL and am examining PostgreSQL. I'm running under Windows. I like what I see so far, but I'm hoping for some performance advice: 1. My test database has 7 million records. 2. There are two columns - an integer and a char column called Day which has a random value of Mon or Tues, etc. in it. 3. I made an index on Day. My query is: select count(*) from mtable where day='Mon' Results: 1. P3 600 512MB RAM MSSQL. It takes about 4-5 secs to run. If I run a few queries and everything is cached, it is sometimes just 1 second. 2. Athlon 1.3 Ghz 1GB RAM. PostgreSQL takes 7 seconds. I have played with the buffers setting and currently have it at 7500. At 20000 it took over 20 seconds to run. 5 seconds vs 7 isn't that big of a deal, but 1 second vs 7 seconds is. Also, the slower performance is with much lesser hardware. Any ideas to try? Thanks much, Mark __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com