Re: Hardware/OS recommendations for large databases (

[Mark Kirkwood <markir@xxxxxxxxxxxxxxx>]

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




/*
 * 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);
}