Re: Much Ado About COUNT(*)

From: Greg Stark <gsstark(at)mit(dot)edu>
To: "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Much Ado About COUNT(*)
Date: 2005-01-12 20:08:37
Message-ID: 87k6qiz9yy.fsf@stark.xeocode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-announce pgsql-hackers pgsql-patches


"Jonah H. Harris" <jharris(at)tvi(dot)edu> writes:

> You are correct, I am proposing to add visibility to the indexes.

Then I think the only way you'll get any support is if it's an option. Since
it would incur a performance penalty on updates and deletes.

> As for unqualified counts, I believe that they could take advantage of an
> index-only scan as it requires much less I/O to perform an index scan than a
> sequential scan on large tables.

No, sequential scans require slightly more i/o than index scans. More
importantly they require random access i/o instead of sequential i/o which is
much slower.

Though this depends. If the tuple is very wide then the index might be faster
to scan since it would only contain the data from the fields being indexed.

This brings to mind another approach. It might be handy to split the heap for
a table into multiple heaps. The visibility information would only be in one
of the heaps. This would be a big win if many of the fields were rarely used,
especially if they're rarely used by sequential scans.

> Relation SOME_USERS
> user_id BIGINT PK
> user_nm varchar(32) UNIQUE INDEX
> some_other_attributes...

What's with the fetish with unique indexes? None of this is any different for
unique indexes versus non-unique indexes.

--
greg

In response to

Responses

Browse pgsql-announce by date

  From Date Subject
Next Message Rod Taylor 2005-01-12 20:09:04 Re: Much Ado About COUNT(*)
Previous Message Tom Lane 2005-01-12 19:59:42 Re: [HACKERS] segfault caused by heimdal (was: SUSE port)

Browse pgsql-hackers by date

  From Date Subject
Next Message Rod Taylor 2005-01-12 20:09:04 Re: Much Ado About COUNT(*)
Previous Message Tom Lane 2005-01-12 19:59:42 Re: [HACKERS] segfault caused by heimdal (was: SUSE port)

Browse pgsql-patches by date

  From Date Subject
Next Message Rod Taylor 2005-01-12 20:09:04 Re: Much Ado About COUNT(*)
Previous Message Tom Lane 2005-01-12 19:59:42 Re: [HACKERS] segfault caused by heimdal (was: SUSE port)