Re: Hardware/OS recommendations for large databases (

Mark,

This is an excellent idea ­ unfortunately I¹m in Maui right now (Mahalo!)
and I¹m not getting to testing with this. My first try was with 8.0.3 and
it¹s an 8.1 function I presume.

Not to be lazy ­ but any hint as to how to do the same thing for 8.0?

- Luke

On 11/21/05 9:10 PM, "Mark Kirkwood" <markir(at)paradise(dot)net(dot)nz> wrote:

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