Date: Tue, 22 Nov 2005 18:10:24 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Hardware/OS recommendations for large databases (
In-reply-to: <BFA7E00E.143DB%llonergan@greenplum.com>
To: Luke Lonergan <llonergan@greenplum.com>
Cc: stange@rentec.com, Greg Stark <gsstark@mit.edu>,
	Dave Cramer <pg@fastcrypt.com>, Joshua Marsh <icub3d@gmail.com>,
	pgsql-performance@postgresql.org
Message-id: <4382A840.3030401@paradise.net.nz>
MIME-version: 1.0
Content-type: multipart/mixed; boundary=------------030902030501050304090108
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106)
References: <BFA7E00E.143DB%llonergan@greenplum.com>

This is a multi-part message in MIME format.
--------------030902030501050304090108
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Luke Lonergan wrote:

> So that leaves the question - why not more than 64% of the I/O scan rate?
> And why is it a flat 64% as the I/O subsystem increases in speed from
> 333-400MB/s?
> 

It might be interesting to see what effect reducing the cpu consumption 
  entailed by the count aggregation has - by (say) writing a little bit 
of code to heap scan the desired relation (sample attached).

Cheers

Mark


--------------030902030501050304090108
Content-Type: text/plain;
 name="fastcount.c"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="fastcount.c"

/*
 * fastcount.c
 *
 * Do a count that uses considerably less CPU time than an aggregate.
 */

#include "postgres.h"

#include "funcapi.h"
#include "access/heapam.h"
#include "catalog/namespace.h"
#include "utils/builtins.h"


extern Datum fastcount(PG_FUNCTION_ARGS);


PG_FUNCTION_INFO_V1(fastcount);
Datum
fastcount(PG_FUNCTION_ARGS)
{
	text	   *relname = PG_GETARG_TEXT_P(0);
	RangeVar   *relrv;
	Relation	rel;
	HeapScanDesc scan;
	HeapTuple	tuple;
	int64		result = 0;

	/* Use the name to get a suitable range variable and open the relation. */
	relrv = makeRangeVarFromNameList(textToQualifiedNameList(relname));
	rel = heap_openrv(relrv, AccessShareLock);

	/* Start a heap scan on the relation. */
	scan = heap_beginscan(rel, SnapshotNow, 0, NULL);
	while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL)
	{
		result++;
	}

	/* End the scan and close up the relation. */
	heap_endscan(scan);
	heap_close(rel, AccessShareLock);


	PG_RETURN_INT64(result);
}

--------------030902030501050304090108--