From: Gary Doades <gpd@gpdnet.co.uk>
To: pgsql-performance@postgresql.org
Subject: Re: planner/optimizer question
Date: Thu, 29 Apr 2004 22:15:12 +0100
Message-ID: <40917E70.21362.14F4A330@localhost> (raw)
In-Reply-To: <200404291354.33304.josh@agliodbs.com>
References: <409172FA.1509.14C7E00E@localhost>
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.
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: gpd@gpdnet.co.uk
Subject: Re: planner/optimizer question
In-Reply-To: <40917E70.21362.14F4A330@localhost>
* 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