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.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
