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);
}
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

Reply via email to