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 1E45AD1EF10 for ; Thu, 29 Apr 2004 18:15:02 -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 84879-02 for ; Thu, 29 Apr 2004 18:14:42 -0300 (ADT) Received: from gpdnet.co.uk (gpdnet.plus.com [212.56.100.243]) by svr1.postgresql.org (Postfix) with ESMTP id D9B4AD1EEC5 for ; Thu, 29 Apr 2004 18:14:39 -0300 (ADT) Received: from gary (unverified [192.168.1.2]) by gpdnet.co.uk (SurgeMail 1.8g3) with ESMTP id 247 for ; Thu, 29 Apr 2004 22:14:10 +0100 From: "Gary Doades" To: pgsql-performance@postgresql.org Date: Thu, 29 Apr 2004 22:15:12 +0100 MIME-Version: 1.0 Subject: Re: planner/optimizer question Message-ID: <40917E70.21362.14F4A330@localhost> In-reply-to: <200404291354.33304.josh@agliodbs.com> References: <409172FA.1509.14C7E00E@localhost> X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Server: High Performance Mail Server - http://surgemail.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/506 X-Sequence-Number: 6806 On 29 Apr 2004 at 13:54, Josh Berkus wrote: > Gary, > > > It's also quite possble the MSSQL simply has more efficient index scanning > implementation that we do. They've certainly had incentive; their storage > system sucks big time for random lookups and they need those fast indexes. > (just try to build a 1GB adjacency list tree on SQL Server. I dare ya). > > Certainly the fact that MSSQL is essentially a single-user database makes > things easier for them. They don't have to maintain multiple copies of the > index tuples in memory. I think that may be our main performance loss. > Possibly, but MSSQL certainly uses data from indexes and cuts out the subsequent (possibly random seek) data fetch. This is also why the "Index Tuning Wizard" often recommends multi column compound indexes in some cases. I've tried these recommendations on occasions and they certainly speed up the selects significantly. If anyhing the index scan on the new compound index must be slower then the original single column index and yet it still gets the data faster. This indicates to me that it is not the scan (or IO) performance that is making the difference, but not having to go get the data row. Cheers, Gary.