Re: Much Ado About COUNT(*)

From: Rod Taylor <pg(at)rbt(dot)ca>
To: "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Much Ado About COUNT(*)
Date: 2005-01-12 22:03:58
Message-ID: 1105567438.690.76.camel@home
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-announce pgsql-hackers pgsql-patches

> >The index could then make sensible the reasonably common practice of
> >using a covered index - i.e. putting additional columns into the index
> >to satisfy the whole query just from the index.

> I am willing to take it on and I understand that the workload is mine.
> As long as everyone gives me some suggestions, I'm good it being optional.

If nobody is working on it, you may find that the below TODO item might
accomplish most of what you're looking for as well as generally
improving performance. The count(*) on a where clause would result in
one index scan and one partial sequential heap scan. Not as fast for the
specific examples you've shown, but far better than today and covers
many other cases as well.

Fetch heap pages matching index entries in sequential order

Rather than randomly accessing heap pages based on index
entries, mark heap pages needing access in a bitmap and do the
lookups in sequential order. Another method would be to sort
heap ctids matching the index before accessing the heap rows.

In response to

Browse pgsql-announce by date

  From Date Subject
Next Message Jeff Davis 2005-01-13 02:45:09 Re: Much Ado About COUNT(*)
Previous Message Jonah H. Harris 2005-01-12 21:58:14 Re: Much Ado About COUNT(*)

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Wong 2005-01-12 22:18:36 sparse (static analyzer) report
Previous Message Jim Buttafuoco 2005-01-12 21:59:43 Re: PANIC: right sibling's left-link doesn't match

Browse pgsql-patches by date

  From Date Subject
Next Message Jeff Davis 2005-01-13 02:45:09 Re: Much Ado About COUNT(*)
Previous Message Jonah H. Harris 2005-01-12 21:58:14 Re: Much Ado About COUNT(*)