Обсуждение: BUG #14411: Issue with using OFFSET

Поиск
Список
Период
Сортировка

BUG #14411: Issue with using OFFSET

От
jkoceniak@mediamath.com
Дата:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDQxMQpMb2dnZWQgYnk6ICAg
ICAgICAgIEphbWllIEtvY2VuaWFrCkVtYWlsIGFkZHJlc3M6ICAgICAgamtv
Y2VuaWFrQG1lZGlhbWF0aC5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjQu
NgpPcGVyYXRpbmcgc3lzdGVtOiAgIExpbnV4CkRlc2NyaXB0aW9uOiAgICAg
ICAgCgpRdWVyeSBwZXJmb3JtYW5jZSBkZWNyZWFzZXMgYXMgdGhlIE9GRlNF
VCBpbmNyZWFzZXMuDQoNCkJhZCBRdWVyeToNCg0KU0VMRUNUIHQxLmlkLCAo
IFNFTEVDVCBjb3VudCAoICogKSBGUk9NIHNpdGVfbGlzdF9zaXRlcyB0NCBX
SEVSRSB0MS5pZCA9CnQ0LnNpdGVfbGlzdF9pZCApIEFTIHNpdGVzX2NvdW50
IA0KRlJPTSBzaXRlX2xpc3RzIHQxICANCldIRVJFICB0MS5vcmdhbml6YXRp
b25faWQgSU4gKCBTRUxFQ1QgZGlzdGluY3Qgb3JnYW5pemF0aW9uX2lkIEZS
T00KdXNlcl9wZXJtaXNzaW9ucyAoIDIzODUgKSkgDQpPUkRFUiBCWSB0MS5j
cmVhdGVkX29uIERFU0MgTElNSVQgMTAwIE9GRlNFVCAyMDA7DQoNCldpdGgg
b2Zmc2V0IHNldCB0byAxMDAsIHF1ZXJ5IHJldHVybnMgaW4gOTJtcw0KDQpR
dWVyeSBQbGFuIChvZmZzZXQgPSAxMDApOg0KaHR0cHM6Ly9leHBsYWluLmRl
cGVzei5jb20vcy9ublBkDQoNCm9mZnNldCA9IDIwMCwgcXVlcnkgdGFrZSA5
Ljggc2Vjb25kcw0KUXVlcnkgcGxhbiAob2Zmc2V0PTIwMCkNCmh0dHBzOi8v
ZXhwbGFpbi5kZXBlc3ouY29tL3MvTVFBUw0KDQoNCgoK

Re: BUG #14411: Issue with using OFFSET

От
Kevin Grittner
Дата:
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

Re: BUG #14411: Issue with using OFFSET

От
Jamie Koceniak
Дата:
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=

Re: BUG #14411: Issue with using OFFSET

От
Kevin Grittner
Дата:
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

Re: BUG #14411: Issue with using OFFSET

От
Victor Yegorov
Дата:
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