Thread: BUG #14411: Issue with using OFFSET
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDQxMQpMb2dnZWQgYnk6ICAg ICAgICAgIEphbWllIEtvY2VuaWFrCkVtYWlsIGFkZHJlc3M6ICAgICAgamtv Y2VuaWFrQG1lZGlhbWF0aC5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjQu NgpPcGVyYXRpbmcgc3lzdGVtOiAgIExpbnV4CkRlc2NyaXB0aW9uOiAgICAg ICAgCgpRdWVyeSBwZXJmb3JtYW5jZSBkZWNyZWFzZXMgYXMgdGhlIE9GRlNF VCBpbmNyZWFzZXMuDQoNCkJhZCBRdWVyeToNCg0KU0VMRUNUIHQxLmlkLCAo IFNFTEVDVCBjb3VudCAoICogKSBGUk9NIHNpdGVfbGlzdF9zaXRlcyB0NCBX SEVSRSB0MS5pZCA9CnQ0LnNpdGVfbGlzdF9pZCApIEFTIHNpdGVzX2NvdW50 IA0KRlJPTSBzaXRlX2xpc3RzIHQxICANCldIRVJFICB0MS5vcmdhbml6YXRp b25faWQgSU4gKCBTRUxFQ1QgZGlzdGluY3Qgb3JnYW5pemF0aW9uX2lkIEZS T00KdXNlcl9wZXJtaXNzaW9ucyAoIDIzODUgKSkgDQpPUkRFUiBCWSB0MS5j cmVhdGVkX29uIERFU0MgTElNSVQgMTAwIE9GRlNFVCAyMDA7DQoNCldpdGgg b2Zmc2V0IHNldCB0byAxMDAsIHF1ZXJ5IHJldHVybnMgaW4gOTJtcw0KDQpR dWVyeSBQbGFuIChvZmZzZXQgPSAxMDApOg0KaHR0cHM6Ly9leHBsYWluLmRl cGVzei5jb20vcy9ublBkDQoNCm9mZnNldCA9IDIwMCwgcXVlcnkgdGFrZSA5 Ljggc2Vjb25kcw0KUXVlcnkgcGxhbiAob2Zmc2V0PTIwMCkNCmh0dHBzOi8v ZXhwbGFpbi5kZXBlc3ouY29tL3MvTVFBUw0KDQoNCgoK
On Thu, Nov 3, 2016 at 3:29 PM, <jkoceniak@mediamath.com> wrote: > Query performance decreases as the OFFSET increases. Not a bug, but a fact of life. It must generate the OFFSET number of rows and continue to generated the next LIMIT rows (or continue processing until there are no rows). If matching rows are scarce after OFFSET rows are found, it can take a while to get enough or to find out that LIMIT rows don't exist. Personally, I never use OFFSET and LIMIT for pagination; there are better ways for most situations. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
SGkgS2V2aW4sDQoNCkkgd291bGQgYmUgaW50ZXJlc3RlZCBpbiBoZWFyaW5nIGFib3V0IGFsdGVy bmF0ZSBzb2x1dGlvbnMgdG8gdXNpbmcgT0ZGU0VULg0KV2UgaGF2ZSBleHBsb3JlZCB1c2luZyBi dHJlZSBpbmRleCBhcHByb2FjaCAob3JkZXJpbmcgYnkgaWQgZGVzYyksIHN0b3JpbmcgbGFzdCBp ZCBhbmQgdGhlbiBncmFiYmluZyBuZXh0IHNldCBvZiByb3dzIDwgbGFzdCBpZC4gVGhhdCB3b3Jr cyBncmVhdCBmb3IgZmV0Y2hpbmcgbmV4dCBncm91cCBvZiByb3dzLg0KDQpIb3dldmVyLCBob3cg d291bGQgeW91IGltcGxlbWVudCBqdW1waW5nIGFoZWFkIHRvIGEgc3BlY2lmaWMgcmFuZ2U/DQpJ LmUuIFBpY3R1cmUgYSB3ZWIgc2l0ZSB3aXRoIHBhZ2VzIDEgMiAzIDQgNSDigKYgMTAgMTEgYW5k IHRoZSB1c2VyIGp1bXBpbmcgdG8gcGFnZSAxMS4NCg0KVGhhbmtzLA0KSmFtaWUNCg0KDQoNCg0K T24gMTEvMy8xNiwgMzo1MiBQTSwgIktldmluIEdyaXR0bmVyIiA8a2dyaXR0bkBnbWFpbC5jb20+ IHdyb3RlOg0KDQo+T24gVGh1LCBOb3YgMywgMjAxNiBhdCAzOjI5IFBNLCAgPGprb2Nlbmlha0Bt ZWRpYW1hdGguY29tPiB3cm90ZToNCj4NCj4+IFF1ZXJ5IHBlcmZvcm1hbmNlIGRlY3JlYXNlcyBh cyB0aGUgT0ZGU0VUIGluY3JlYXNlcy4NCj4NCj5Ob3QgYSBidWcsIGJ1dCBhIGZhY3Qgb2YgbGlm ZS4gIEl0IG11c3QgZ2VuZXJhdGUgdGhlIE9GRlNFVCBudW1iZXINCj5vZiByb3dzIGFuZCBjb250 aW51ZSB0byBnZW5lcmF0ZWQgdGhlIG5leHQgTElNSVQgcm93cyAob3IgY29udGludWUNCj5wcm9j ZXNzaW5nIHVudGlsIHRoZXJlIGFyZSBubyByb3dzKS4gIElmIG1hdGNoaW5nIHJvd3MgYXJlIHNj YXJjZQ0KPmFmdGVyIE9GRlNFVCByb3dzIGFyZSBmb3VuZCwgaXQgY2FuIHRha2UgYSB3aGlsZSB0 byBnZXQgZW5vdWdoIG9yDQo+dG8gZmluZCBvdXQgdGhhdCBMSU1JVCByb3dzIGRvbid0IGV4aXN0 Lg0KPg0KPlBlcnNvbmFsbHksIEkgbmV2ZXIgdXNlIE9GRlNFVCBhbmQgTElNSVQgZm9yIHBhZ2lu YXRpb247IHRoZXJlIGFyZQ0KPmJldHRlciB3YXlzIGZvciBtb3N0IHNpdHVhdGlvbnMuDQo+DQo+ LS0NCj5LZXZpbiBHcml0dG5lcg0KPkVEQjogaHR0cDovL3d3dy5lbnRlcnByaXNlZGIuY29tDQo+ VGhlIEVudGVycHJpc2UgUG9zdGdyZVNRTCBDb21wYW55DQo=
On Thu, Nov 3, 2016 at 7:05 PM, Jamie Koceniak <jkoceniak@mediamath.com> wr= ote: > On 11/3/16, 3:52 PM, "Kevin Grittner" <kgrittn@gmail.com> wrote: >> Personally, I never use OFFSET and LIMIT for pagination; there >> are better ways for most situations. > I would be interested in hearing about alternate solutions to > using OFFSET. > We have explored using btree index approach (ordering by id > desc), storing last id and then grabbing next set of rows < last > id. That works great for fetching next group of rows. That works. If you also save the starting key value, you can use it to page backward by reversing your ORDER BY. > However, how would you implement jumping ahead to a specific > range? Range (as in database values) or page (as in count of matching rows)? > I.e. Picture a web site with pages 1 2 3 4 5 =E2=80=A6 10 11 and the user > jumping to page 11. You can't know what's on page 11 without reading pages 1 to 10. You can either track that as you move forward, or just return all the rows on the initial query and write everything to working storage somewhere, navigating through this result when the user chooses a new page. Normally when using this technique you set some overall limit of rows for the query. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
2016-11-04 2:05 GMT+02:00 Jamie Koceniak <jkoceniak@mediamath.com>: > I would be interested in hearing about alternate solutions to using OFFSET. I find this presentation useful in describing how to do pagination: http://www.slideshare.net/MarkusWinand/p2d2-pagination-done-the-postgresql-way -- Victor Yegorov