Re: less than 2 sec for response - possible? - Mailing list pgsql-performance

From trafdev
Subject Re: less than 2 sec for response - possible?
Date
Msg-id eaf764fb-a840-6bfa-f2b3-de2b74d7feb3@mail.ru
Whole thread Raw
In response to Re: less than 2 sec for response - possible?  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
Responses Re: less than 2 sec for response - possible?  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
List pgsql-performance
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.


pgsql-performance by date:

Previous
From: Jim Nasby
Date:
Subject: Re: Random slow queries
Next
From: Tom Lane
Date:
Subject: Re: Seeing execution plan of foreign key constraint check?