Right, buffers are not rows, but still 8 times less...
The table I'm reading from is already aggregated on daily basis (so
there is no way to aggregate it more).
Will extending page to say 128K improve performance?
On 07/19/16 07:41, Jim Nasby wrote:
> On 7/19/16 9:28 AM, trafdev wrote:
>>>>> The difference is - you're fetching\grouping 8 times less rows than I:
>>>
>>> Huh? The explain output certainly doesn't show that.
>>
>> Why not?
>>
>> My output:
>> Buffers: shared hit=1486949
>>
>> Torsten's output:
>> Buffers: shared hit=155711
>>
>> This is amount of rows fetched for further processing (when all data is
>> in memory), isn't it?
>
> That's buffers, not rows.
>
> BTW, if my math is correct, reading 1486949 8K buffers is 11GB, which
> your query did in ~1.8s at 6GB/s. Admittedly that's pretty hand-wavy
> (pulling a datum from a shared buffer doesn't require reading the whole
> buffer; on the other hand, you also visited each buffer
> 3359694/1486949=2.6 times), but last time I measured, 6GB/s was a pretty
> reasonable amount of memory bandwidth for something hitting main memory.
>
> You've got ~30 bigints in that table (240 bytes) plus a bunch of other
> stuff. That means you'll only be able to fit maybe 20 rows per 8K page.
> At some point you'll simply hit the limits of hardware.
>
> If you really need that kind of performance you'll probably need to have
> some form of aggregate tables that you pull from. In your case, an
> aggregate of each day would presumably work well; that would mean you'd
> be reading 30 rows instead of 3.5M.