X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 17971D1EB6B for ; Thu, 29 Apr 2004 17:12:54 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 60864-02 for ; Thu, 29 Apr 2004 17:12:33 -0300 (ADT) Received: from www.totalcardinc.com (unknown [64.33.232.186]) by svr1.postgresql.org (Postfix) with ESMTP id 0F569D1EB30 for ; Thu, 29 Apr 2004 17:12:31 -0300 (ADT) Received: from RSCHWARZW2K ([10.250.0.37]) (authenticated bits=0) by www.totalcardinc.com (8.12.8/8.12.8) with ESMTP id i3TKCTiK000820; Thu, 29 Apr 2004 15:12:29 -0500 From: "Rosser Schwarz" To: "'Gary Doades'" , Subject: Re: planner/optimizer question Date: Thu, 29 Apr 2004 15:12:29 -0500 Message-ID: <003101c42e26$4c384e90$2500fa0a@CardServices.TCI.com> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4510 In-Reply-To: <409153EF.24647.144E9BF7@localhost> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200404/501 X-Sequence-Number: 6801 while you weren't looking, Gary Doades wrote: > Recently I have been looking at raw performance (CPU, IO) > rather than the plans. I have some test queries that (as far > as I can determine) use the same access plans on PostgreSQL > and SQLServer. Getting to the detail, an index scan of an > index on a integer column (222512 rows) takes 60ms on > SQLServer and 540ms on PostgreSQL. After a recent power outage, I had the opportunity to watch both PostgreSQL and MS SQL come back from forced shutdowns (clean, though there were active connections, in one case a bulk insert). PostgreSQL was available and responsive as soon as the postmaster had started. MS SQL, on the other hand, took the better part of an hour to become remotely usable again -- on a radically faster machine (Dell 6650, versus the 6450 we run PostgreSQL on). Digging a bit, I noted that once MS SQL was up again, it was using nearly 2GB main memory even when more or less idle. From this, and having observed the performance differences between the two, I'm left with little alternative but to surmise that part of MS SQL's noted performance advantage [1] is due to its forcibly storing its indices in main memory. Its startup lag (during which it was utterly unusable; even SELECTs blocked) could be accounted for by reindexing the tables. [2] Granted, this is only a hypothesis, is rather unverifyable, and probably belongs more on ADVOCACY than it does PERFORM, but it seemed relevant. It's also entirely possible your indices are using inaccurate statistical information. Have you ANALYZEd recently? /rls [1] Again, at least in our case, the comparison is entirely invalid, as MS SQL gets a hell of a lot more machine than PostgreSQL. Even so, for day-to-day work and queries, even our DBA, an until-recently fervent MS SQL advocate can't fault PostgreSQL's SELECT, INSERT or DELETE performance. We still can't get UPDATEs (at least bulk such) to pass muster. [2] This is further supported by having observed MS SQL run a "recovery process" on databases that were entirely unused, even for SELECT queries, at the time of the outage. The only thing it might conceivably need to recover on them is in-memory indices that were lost when power was lost. -- Rosser Schwarz Total Card, Inc.