Thanks for the replies Jeff, Tom and Merlin. >> 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?
Yes — 0 (OFFSET) and 16 (LIMIT), or 15 and 31 (i.e. “second page” of results). There’s no difference on that front. For context, OFFSET is a multiple of 15 (i.e. 15 results per page) and LIMIT is always 15 + 1 in an attempt to fetch one more result, get the len of the returned slice and then return paginate true + slice the last result off if there’s more than 15. > > >> 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? - Testing SELECT * FROM … with just LIMIT 15 and no offset yields 1299 request/s at the front end of the application. - Testing SELECT id, title, company, location, commute, term, expiry_date (a subset of fields) with LIMIT 15 and no OFFSET yields 1800 request/s at the front end. There’s definitely an increase to be realised there (I’d say by just tossing the rendered HTML field). Based on your comments about the Go side of things, I ran a quick test by cutting the table down to 6 records from the 39 in the test DB in all previous tests. This skips the pagination logic (below) and yields 3068 req/s on the front-end. // Determine if we have more than one page of results. // If so, trim the extra result off and set pagination = true if len(listings) > opts.PerPage { paginate = true listings = listings[:opts.PerPage] } So there certainly appears to be a bottleneck on the Go side as well (outside of even the DB driver), probably from the garbage generated from slicing the slice, although I’d be keen to know if there’s a better way to approach returning a paginated list of results. >>> Well, you can also do client side pagination using the row-wise >>> comparison feature, implemented by you :-). Cursors can be the best >>> approach, but it's nice to know the client side approach if you're >>> really stateless and/or want to be able to pick up external changes >>> during the browse. What would be a better approach here? The cursor approach isn’t ideal in my case (although I could make it work), but what other options are there that are stateless? >> >> 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. Noted — thanks. On 13 Jun 2014, at 4:46 AM, Jeff Janes <jeff.ja...@gmail.com> wrote: > <snip>
