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.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461