From: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com> |
---|---|
To: | "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com> |
Cc: | Greg Stark <gsstark(at)mit(dot)edu>, Alexander Scholz <alexander(dot)scholz1(at)freenet(dot)de>, pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Indices for select count(*)? |
Date: | 2005-12-22 16:08:41 |
Message-ID: | 1135267720.18741.39.camel@state.g2switchworks.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, 2005-12-22 at 09:33, Jim C. Nasby wrote:
> On Thu, Dec 22, 2005 at 04:10:50PM +0100, Martijn van Oosterhout wrote:
> > Actually, ISTM the trend is going the other way. MySQL has instant
> > select count(*), as long as you're only using ISAM. Recent versions of
>
> No comment.
>
> > MSSQL use an MVCC type system and it also scans the whole table. Oracle
> > is the only one I've found that has any optimisation on this front.
>
> I think this is more an indication of the power of MVCC over traditional
> locking rather than the importance of indexes covering (reading just an
> index to satisfy a query). Index covering can be a huge benefit, and I'd
> be surprised if MS didn't come out with some way to do it in a future
> version. I'm actually a bit surprised they don't do it in SQL2005.
I wouldn't mind a "with visibility" switch for indexes that you could
throw when creating them for this purpose. But burdening all indexes
with this overhead when most wouldn't need it is not, IMHO, a good idea.
I seem to remember Tom saying that there was a race condition issue
though with updating the table AND the index at the same time, that they
could be out of sync for a fraction of a second or something like that.
So, if we had this kind of thing, the indexes and / or tables would have
to be locked for updates.
Again, for a reporting database, no big deal. For a transactional
database, very big deal.
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2005-12-22 16:10:17 | Re: About Maximum number of columns |
Previous Message | Greg Stark | 2005-12-22 15:58:45 | Re: Indices for select count(*)? |