Skip site navigation (1) Skip section navigation (2)

Re: Much Ado About COUNT(*)

From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Much Ado About COUNT(*)
Date: 2005-01-12 19:23:44
Message-ID: 87pt0azc1r.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-announcepgsql-hackerspgsql-patches
"Jonah H. Harris" <jharris(at)tvi(dot)edu> writes:

> Looking at the message boards, there is significant interest in the COUNT(*)
> aspect. However, rather than solely address the COUNT(*) TODO item, why not fix
> it and add additional functionality found in commercial databases as well? I
> believe Oracle has had this feature since 7.3 and I know people take advantage
> of it.

I think part of the problem is that there's a bunch of features related to
these types of queries and the lines between them blur. 

You seem to be talking about putting visibility information inside indexes for
so index-only plans can be performed. But you're also talking about queries
like "select count(*) from foo" with no where clauses. Such a query wouldn't
be helped by index-only scans.

Perhaps you're thinking about caching the total number of records in a global
piece of state like a materialized view? That would be a nice feature but I
think it should done as a general materialized view implementation, not a
special case solution for just this one query.

Perhaps you're thinking of the min/max problem of being able to use indexes to
pick out just the tuples satisfying the min/max constraint. That seems to me
to be one of the more tractable problems in this area but it would still
require lots of work.

I suggest you post a specific query you find is slow. Then discuss how you
think it ought to be executed and why.

-- 
greg


In response to

Responses

pgsql-announce by date

Next:From: Kurt RoeckxDate: 2005-01-12 19:28:39
Subject: Re: segfault caused by heimdal (was: SUSE port)
Previous:From: Reinhard MaxDate: 2005-01-12 19:11:32
Subject: Re: [HACKERS] segfault caused by heimdal (was: SUSE port)

pgsql-hackers by date

Next:From: Kurt RoeckxDate: 2005-01-12 19:28:39
Subject: Re: segfault caused by heimdal (was: SUSE port)
Previous:From: Reinhard MaxDate: 2005-01-12 19:11:32
Subject: Re: [HACKERS] segfault caused by heimdal (was: SUSE port)

pgsql-patches by date

Next:From: Kurt RoeckxDate: 2005-01-12 19:28:39
Subject: Re: segfault caused by heimdal (was: SUSE port)
Previous:From: Reinhard MaxDate: 2005-01-12 19:11:32
Subject: Re: [HACKERS] segfault caused by heimdal (was: SUSE port)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group