On Thu, Jun 12, 2014 at 12:08 AM, Matt Silverlock <matt@xxxxxxxxxxxxxxxxxx> wrote: > > Pages that SELECT multiple rows with OFFSET and LIMIT conditions struggle to top 1.3k req/s Is that tested at the OFFSET and LIMIT of 0 and 15, as shown in the explain plan? > The query in question is: http://explain.depesz.com/s/7g8 and the table schema is as below: The reported runtime of 0.078 ms should be able to sustain nearly 10 times the reported rate of 1.3k/s, so the bottleneck would seem to be elsewhere. Perhaps the bottleneck is formatting the result set in postgres to be sent over the wire, then sending it over the wire, then parsing it in the Go connection library to hand back to the Go user code, and then the Go user code doing something meaningful with it. What happens if you get rid of the offset and the order by, and just use limit? I bet it doesn't change the speed much (because that is not where the bottleneck is). You seem to be selecting an awful lot of wide columns. Do you really need to see all of them? > > Some pgbench results from this machine as well: > > $ pgbench -c 128 -C -j 4 -T 15 -M extended -S This is just benchmarking how fast you can make and break connections to the database. Because your app is using an embedded connection pooler, this benchmark isn't very relevant to your situation. > > Ultimately I'm not expecting a miracle—database ops are nearly always the slowest part > of a web server outside the latency to the client itself—but I'd expect something a little > closer (even 10% of 33k would be a lot better). And of course, this is somewhat "academic" > because I don't expect to see four million hits an hour—but I'd also like to catch problems > for future reference. I think you have succeeded in doing that. If you want to get substantially faster than the current speed in the future, you will need a web-app-side results cache for this type of query. I can't imagine the results of such a query change more than 1300 times/s, nor that anyone would notice or care if the observed results which were stale by two or three seconds. That type of cache is a PITA, and I've never needed one because I also don't expect to get 4 millions hits an hour. But if this is what your future looks like, you'd be best off to embrace it sooner rather than later. Cheers, Jeff
