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

Re: Much Ado About COUNT(*)

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
Cc: <pgsql-hackers(at)postgresql(dot)org>, "Greg Stark" <gsstark(at)mit(dot)edu>
Subject: Re: Much Ado About COUNT(*)
Date: 2005-01-12 19:47:07
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB3412A75AC@Herge.rcsinc.local (view raw or flat)
Thread:
Lists: pgsql-hackers
> Greg Stark wrote:
> 
> >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.
> >
> >
> >
> You are correct, I am proposing to add visibility to the indexes.
> 
> 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.

I agree with Greg...I think the way to approach this is a general
materialized view solution.  This would solve a broad class of tricky
problems including count() and count(*)...you get to choice between the
pay now/pay later tradeoff, etc.

Merlin

pgsql-hackers by date

Next:From: Jonah H. HarrisDate: 2005-01-12 19:52:53
Subject: Re: Much Ado About COUNT(*)
Previous:From: Tom LaneDate: 2005-01-12 19:41:56
Subject: Re: Much Ado About COUNT(*)

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