Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id BDCD01337B56
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  4 Nov 2010 11:35:01 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 71873-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  4 Nov 2010 14:34:54 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from rome.boulder.noaa.gov (rome.boulder.noaa.gov [140.172.10.146])
	by mail.postgresql.org (Postfix) with ESMTP id 9AFEB133616B
	for <pgsql-performance@postgresql.org>;
	Thu,  4 Nov 2010 11:34:54 -0300 (ADT)
Received: from [137.75.201.47] (tripledrop.fsl.noaa.gov [137.75.201.47])
	by email.boulder.noaa.gov
	(iPlanet Messaging Server 5.2 HotFix 2.01 (built Aug 26 2004))
	with ESMTPA id <0LBD004F9767JZ@email.boulder.noaa.gov> for
	pgsql-performance@postgresql.org; Thu, 04 Nov 2010 14:34:55 +0000 (GMT)
Date: Thu, 04 Nov 2010 14:34:55 +0000
From: Nick Matheson <Nick.D.Matheson@noaa.gov>
Subject: Re: Simple (hopefully) throughput question?
In-reply-to: <AANLkTik9sP6cNubT1VVW+qEJ2cRxz7TEEP96sYZMbRSX@mail.gmail.com>
To: Marti Raudsepp <marti@juffo.org>
Cc: pgsql-performance@postgresql.org
Message-id: <4CD2C48F.1040208@noaa.gov>
MIME-version: 1.0
Content-type: multipart/alternative;
	boundary="Boundary_(ID_mBauAIhyfqkA9NgNFouiRQ)"
User-Agent: Thunderbird 2.0.0.24 (X11/20101005)
References: <4CD1853F.2010800@noaa.gov>
	<AANLkTik9sP6cNubT1VVW+qEJ2cRxz7TEEP96sYZMbRSX@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.209 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_MED=-2.3,
	T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201011/35
X-Sequence-Number: 41059

This is a multi-part message in MIME format.

--Boundary_(ID_mBauAIhyfqkA9NgNFouiRQ)
Content-type: text/plain; charset=UTF-8; format=flowed
Content-transfer-encoding: 7BIT

Marti-
> Just some ideas that went through my mind when reading your post
> PostgreSQL 8.3 and later have 22 bytes of overhead per row, plus
> page-level overhead and internal fragmentation. You can't do anything
> about row overheads, but you can recompile the server with larger
> pages to reduce page overhead.
>
>   
>> Is there any way using stored procedures (maybe C code that calls
>> SPI directly) or some other approach to get close to the expected 35
>> MB/s doing these bulk reads?
>>     
>
> Perhaps a simpler alternative would be writing your own aggregate
> function with four arguments.
>
> If you write this aggregate function in C, it should have similar
> performance as the sum() query.
>   
You comments seem to confirm some of our foggy understanding of the 
storage 'overhead' and nudge us in the direction of C stored procedures.

Do you have any results or personal experiences from moving calculations 
in this way? I think we are trying to get an understanding of how much 
we might stand to gain by the added investment.

Thanks,

Nick

--Boundary_(ID_mBauAIhyfqkA9NgNFouiRQ)
Content-type: text/html; charset=UTF-8
Content-transfer-encoding: 7BIT

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Marti-
<blockquote
 cite="mid:AANLkTik9sP6cNubT1VVW+qEJ2cRxz7TEEP96sYZMbRSX@mail.gmail.com"
 type="cite">
  <pre wrap="">Just some ideas that went through my mind when reading your post</pre>
</blockquote>
<blockquote
 cite="mid:AANLkTik9sP6cNubT1VVW+qEJ2cRxz7TEEP96sYZMbRSX@mail.gmail.com"
 type="cite">
  <pre wrap="">PostgreSQL 8.3 and later have 22 bytes of overhead per row, plus
page-level overhead and internal fragmentation. You can't do anything
about row overheads, but you can recompile the server with larger
pages to reduce page overhead.

  </pre>
  <blockquote type="cite">
    <pre wrap="">Is there any way using stored procedures (maybe C code that calls
SPI directly) or some other approach to get close to the expected 35
MB/s doing these bulk reads?
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Perhaps a simpler alternative would be writing your own aggregate
function with four arguments.

If you write this aggregate function in C, it should have similar
performance as the sum() query.
  </pre>
</blockquote>
You comments seem to confirm some of our foggy understanding of the
storage 'overhead' and nudge us in the direction of C stored
procedures. <br>
<br>
Do you have any results or personal experiences from moving
calculations in this way? I think we are trying to get an understanding
of how much we might stand to gain by the added investment.<br>
<br>
Thanks,<br>
<br>
Nick<br>
</body>
</html>

--Boundary_(ID_mBauAIhyfqkA9NgNFouiRQ)--