Re: Hardware/OS recommendations for large databases (

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