Обсуждение: BUG #14302: SQL with LIMIT degrades performance seriously
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDMwMgpMb2dnZWQgYnk6ICAg ICAgICAgIEthaWppYW5nIENoZW4KRW1haWwgYWRkcmVzczogICAgICBjaGVu a2FpamlhbmdAZ21haWwuY29tClBvc3RncmVTUUwgdmVyc2lvbjogOS41LjMK T3BlcmF0aW5nIHN5c3RlbTogICBDZW50T1MgNy4yCkRlc2NyaXB0aW9uOiAg ICAgICAgCgpJIGhhdmUgYSB0YWJsZSwgcmVucmVuLnVzZXJfcmVsYXRpb25z IHdpdGggMTAgcGFydGl0aW9ucyBhbmQgY29udGFpbnMgYWJvdXQKMTBNIHJl Y29yZHMuDQoNCkkgY3JlYXRlZCAyIGluZGV4ZXMgb24gaXQgKGluY2x1ZGlu ZyBwYXJlbnQgdGFibGUgYW5kIGFsbCBwYXJ0aXRpb25zKTogDQoxKSB1c2Vy X3JlbGF0aW9uc19wa2V5IC0tIGJ0cmVlIGluZGV4IG9uIHVzZXJfaWQgY29s dW1uDQoyKSB1c2VyX3JlbGF0aW9uc18wX3BhcmVudF9pZF9pZHggLS0gYnRy ZWUgaW5kZXggb24gcGFyZW50X2lkDQoNCkkgcmFuIHRoZSBTUUwgInNlbGVj dCAqIGZyb20gcmVucmVuLnVzZXJfcmVsYXRpb25zIHdoZXJlIHBhcmVudF9p ZD04NDYzNDYKb3JkZXIgYnkgdXNlcl9pZCBsaW1pdCAxMDsiIGFuZCBpdCB0 b29rIDEuNCBzZWMsIHdoaWNoIGlzIHNsb3cuDQpCdXQgInNlbGVjdCAqIGZy b20gcmVucmVuLnVzZXJfcmVsYXRpb25zIHdoZXJlIHBhcmVudF9pZD04NDYz NDYgb3JkZXIgYnkKdXNlcl9pZCIgKG5vIExJTUlUKSBhbmQgaXQgdG9vayAx MDArIG1zLg0KDQp0aGUgZXhwbGFpbiByZXN1bHQ6DQoNCmV4cGxhaW4gc2Vs ZWN0ICogZnJvbSByZW5yZW4udXNlcl9yZWxhdGlvbnMgd2hlcmUgcGFyZW50 X2lkPTg0NjM0NiBvcmRlciBieQp1c2VyX2lkIGxpbWl0IDEwOw0KDQogICBR VUVSWSBQTEFOICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICANCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0N CiBMaW1pdCAgKGNvc3Q9NC41Ny4uNDQyLjM1IHJvd3M9MTAgd2lkdGg9MTAy KQ0KICAgLT4gIE1lcmdlIEFwcGVuZCAgKGNvc3Q9NC41Ny4uNDk2NTM0Ljky IHJvd3M9MTEzNDIgd2lkdGg9MTAyKQ0KICAgICAgICAgU29ydCBLZXk6IHVz ZXJfcmVsYXRpb25zLnVzZXJfaWQNCiAgICAgICAgIC0+ICBJbmRleCBTY2Fu IHVzaW5nIHVzZXJfcmVsYXRpb25zX3BrZXkgb24gdXNlcl9yZWxhdGlvbnMg Cihjb3N0PTAuMTIuLjUuMjQgcm93cz0xIHdpZHRoPTI3KQ0KICAgICAgICAg ICAgICAgRmlsdGVyOiAocGFyZW50X2lkID0gODQ2MzQ2KQ0KICAgICAgICAg LT4gIEluZGV4IFNjYW4gdXNpbmcgdXNlcl9yZWxhdGlvbnNfMF9wa2V5IG9u IHVzZXJfcmVsYXRpb25zXzAgCihjb3N0PTAuNDIuLjQ5NDI2LjI0IHJvd3M9 ODYyIHdpZHRoPTEwMikNCiAgICAgICAgICAgICAgIEZpbHRlcjogKHBhcmVu dF9pZCA9IDg0NjM0NikNCiAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5n IHVzZXJfcmVsYXRpb25zXzFfcGtleSBvbiB1c2VyX3JlbGF0aW9uc18xIAoo Y29zdD0wLjQyLi40OTQzNy4yMSByb3dzPTEwMjIgd2lkdGg9MTAyKQ0KICAg ICAgICAgICAgICAgRmlsdGVyOiAocGFyZW50X2lkID0gODQ2MzQ2KQ0KICAg ICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgdXNlcl9yZWxhdGlvbnNfMl9w a2V5IG9uIHVzZXJfcmVsYXRpb25zXzIgCihjb3N0PTAuNDIuLjQ5NjgxLjIz IHJvd3M9OTczIHdpZHRoPTEwMykNCiAgICAgICAgICAgICAgIEZpbHRlcjog KHBhcmVudF9pZCA9IDg0NjM0NikNCiAgICAgICAgIC0+ICBJbmRleCBTY2Fu IHVzaW5nIHVzZXJfcmVsYXRpb25zXzNfcGtleSBvbiB1c2VyX3JlbGF0aW9u c18zIAooY29zdD0wLjQyLi40OTQyNy4zOCByb3dzPTk5MCB3aWR0aD0xMDIp DQogICAgICAgICAgICAgICBGaWx0ZXI6IChwYXJlbnRfaWQgPSA4NDYzNDYp DQogICAgICAgICAtPiAgSW5kZXggU2NhbiB1c2luZyB1c2VyX3JlbGF0aW9u c180X3BrZXkgb24gdXNlcl9yZWxhdGlvbnNfNCAKKGNvc3Q9MC40Mi4uNDk3 MTMuOTUgcm93cz05NDEgd2lkdGg9MTAyKQ0KICAgICAgICAgICAgICAgRmls dGVyOiAocGFyZW50X2lkID0gODQ2MzQ2KQ0KICAgICAgICAgLT4gIEluZGV4 IFNjYW4gdXNpbmcgdXNlcl9yZWxhdGlvbnNfNV9wa2V5IG9uIHVzZXJfcmVs YXRpb25zXzUgCihjb3N0PTAuNDIuLjQ5NzExLjY1IHJvd3M9MTY4NyB3aWR0 aD0xMDIpDQogICAgICAgICAgICAgICBGaWx0ZXI6IChwYXJlbnRfaWQgPSA4 NDYzNDYpDQogICAgICAgICAtPiAgSW5kZXggU2NhbiB1c2luZyB1c2VyX3Jl bGF0aW9uc182X3BrZXkgb24gdXNlcl9yZWxhdGlvbnNfNiAKKGNvc3Q9MC40 Mi4uNDk2NzYuNjAgcm93cz0xMTA0IHdpZHRoPTEwMikNCiAgICAgICAgICAg ICAgIEZpbHRlcjogKHBhcmVudF9pZCA9IDg0NjM0NikNCiAgICAgICAgIC0+ ICBJbmRleCBTY2FuIHVzaW5nIHVzZXJfcmVsYXRpb25zXzdfcGtleSBvbiB1 c2VyX3JlbGF0aW9uc183IAooY29zdD0wLjQyLi40OTcxNS4yNyByb3dzPTEx Njggd2lkdGg9MTAyKQ0KICAgICAgICAgICAgICAgRmlsdGVyOiAocGFyZW50 X2lkID0gODQ2MzQ2KQ0KICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcg dXNlcl9yZWxhdGlvbnNfOF9wa2V5IG9uIHVzZXJfcmVsYXRpb25zXzggCihj b3N0PTAuNDIuLjQ5Njk4LjU1IHJvd3M9MTE2OCB3aWR0aD0xMDIpDQogICAg ICAgICAgICAgICBGaWx0ZXI6IChwYXJlbnRfaWQgPSA4NDYzNDYpDQogICAg ICAgICAtPiAgSW5kZXggU2NhbiB1c2luZyB1c2VyX3JlbGF0aW9uc185X3Br ZXkgb24gdXNlcl9yZWxhdGlvbnNfOSAKKGNvc3Q9MC40Mi4uNDk2MjAuNjkg cm93cz0xNDI2IHdpZHRoPTEwMykNCiAgICAgICAgICAgICAgIEZpbHRlcjog KHBhcmVudF9pZCA9IDg0NjM0NikNCigyNSByb3dzKQ0KDQpUaW1lOiAxLjM0 NCBtcw0KDQpJdCB1c2VzIHRoZSBJbmRleCBTY2FuIHVzaW5nIGluZGV4IG9u IHVzZXJfaWQsIHdoaWNoIGlzIHZlcnkgc3R1cGlkLg0KDQpJZiBJIGV4cGxh aW4gc2VsZWN0ICogZnJvbSByZW5yZW4udXNlcl9yZWxhdGlvbnMgd2hlcmUg cGFyZW50X2lkPTg0NjM0NgpvcmRlciBieSB1c2VyX2lkLCB0aGVuIGl0IHVz ZXMgdGhlIGluZGV4IG9uIHBhcmVudF9pZCB0byBnZXQgcmVjb3JkcyBhbmQK dGhlbiBzb3J0IGl0LCB3aGljaCBpcyB2ZXJ5IHdpc2Ugc2luY2UgdGhlIG51 bWJlciBvZiBxdWFsaWZpZWQgcmVjb3JkcyBpcwoxNzI1Lg0KDQpGaW5hbGx5 LCBJIGdvdCBhIHdheSB0byB3b3JrIGFyb3VuZDoNCg0KZXhwbGFpbiB3aXRo IHRtcCBhcyAoc2VsZWN0ICogZnJvbSByZW5yZW4udXNlcl9yZWxhdGlvbnMg d2hlcmUKcGFyZW50X2lkPTg0NjM0NiBvcmRlciBieSB1c2VyX2lkKSBzZWxl Y3QgdXNlcl9pZCBmcm9tIHRtcCBsaW1pdCAxMDsNCiAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg IFFVRVJZIFBMQU4gICAgICAKICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgDQotLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KIExpbWl0ICAoY29zdD0yMzA2 NS4yNC4uMjMwNjUuNDQgcm93cz0xMCB3aWR0aD04KQ0KICAgQ1RFIHRtcA0K ICAgICAtPiAgU29ydCAgKGNvc3Q9MjMwMzYuODkuLjIzMDY1LjI0IHJvd3M9 MTEzNDIgd2lkdGg9MTAyKQ0KICAgICAgICAgICBTb3J0IEtleTogdXNlcl9y ZWxhdGlvbnMudXNlcl9pZA0KICAgICAgICAgICAtPiAgQXBwZW5kICAoY29z dD0wLjAwLi4yMjI3My4wNCByb3dzPTExMzQyIHdpZHRoPTEwMikNCiAgICAg ICAgICAgICAgICAgLT4gIFNlcSBTY2FuIG9uIHVzZXJfcmVsYXRpb25zICAo Y29zdD0wLjAwLi4wLjAwIHJvd3M9MQp3aWR0aD0yNykNCiAgICAgICAgICAg ICAgICAgICAgICAgRmlsdGVyOiAocGFyZW50X2lkID0gODQ2MzQ2KQ0KICAg ICAgICAgICAgICAgICAtPiAgSW5kZXggU2NhbiB1c2luZyB1c2VyX3JlbGF0 aW9uc18wX3BhcmVudF9pZF9pZHggb24KdXNlcl9yZWxhdGlvbnNfMCAgKGNv c3Q9MC40Mi4uMTY4Mi40OSByb3dzPTg2MiB3aWR0aD0xMDIpDQogICAgICAg ICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6IChwYXJlbnRfaWQgPSA4NDYz NDYpDQogICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHVz ZXJfcmVsYXRpb25zXzFfcGFyZW50X2lkX2lkeCBvbgp1c2VyX3JlbGF0aW9u c18xICAoY29zdD0wLjQyLi4yMTU2LjAyIHJvd3M9MTAyMiB3aWR0aD0xMDIp DQogICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6IChwYXJlbnRf aWQgPSA4NDYzNDYpDQogICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2Fu IHVzaW5nIHVzZXJfcmVsYXRpb25zXzJfcGFyZW50X2lkX2lkeCBvbgp1c2Vy X3JlbGF0aW9uc18yICAoY29zdD0wLjQyLi4xODg3LjcxIHJvd3M9OTczIHdp ZHRoPTEwMykNCiAgICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29uZDog KHBhcmVudF9pZCA9IDg0NjM0NikNCiAgICAgICAgICAgICAgICAgLT4gIElu ZGV4IFNjYW4gdXNpbmcgdXNlcl9yZWxhdGlvbnNfM19wYXJlbnRfaWRfaWR4 IG9uCnVzZXJfcmVsYXRpb25zXzMgIChjb3N0PTAuNDIuLjIwMzUuOTUgcm93 cz05OTAgd2lkdGg9MTAyKQ0KICAgICAgICAgICAgICAgICAgICAgICBJbmRl eCBDb25kOiAocGFyZW50X2lkID0gODQ2MzQ2KQ0KICAgICAgICAgICAgICAg ICAtPiAgSW5kZXggU2NhbiB1c2luZyB1c2VyX3JlbGF0aW9uc180X3BhcmVu dF9pZF9pZHggb24KdXNlcl9yZWxhdGlvbnNfNCAgKGNvc3Q9MC40Mi4uMTgw NS4yOSByb3dzPTk0MSB3aWR0aD0xMDIpDQogICAgICAgICAgICAgICAgICAg ICAgIEluZGV4IENvbmQ6IChwYXJlbnRfaWQgPSA4NDYzNDYpDQogICAgICAg ICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHVzZXJfcmVsYXRpb25z XzVfcGFyZW50X2lkX2lkeCBvbgp1c2VyX3JlbGF0aW9uc181ICAoY29zdD0w LjQyLi4zMjQwLjI5IHJvd3M9MTY4NyB3aWR0aD0xMDIpDQogICAgICAgICAg ICAgICAgICAgICAgIEluZGV4IENvbmQ6IChwYXJlbnRfaWQgPSA4NDYzNDYp DQogICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHVzZXJf cmVsYXRpb25zXzZfcGFyZW50X2lkX2lkeCBvbgp1c2VyX3JlbGF0aW9uc182 ICAoY29zdD0wLjQyLi4yMTQ5LjEwIHJvd3M9MTEwNCB3aWR0aD0xMDIpDQog ICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6IChwYXJlbnRfaWQg PSA4NDYzNDYpDQogICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVz aW5nIHVzZXJfcmVsYXRpb25zXzdfcGFyZW50X2lkX2lkeCBvbgp1c2VyX3Jl bGF0aW9uc183ICAoY29zdD0wLjQyLi4yMjU2LjUzIHJvd3M9MTE2OCB3aWR0 aD0xMDIpDQogICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6IChw YXJlbnRfaWQgPSA4NDYzNDYpDQogICAgICAgICAgICAgICAgIC0+ICBJbmRl eCBTY2FuIHVzaW5nIHVzZXJfcmVsYXRpb25zXzhfcGFyZW50X2lkX2lkeCBv bgp1c2VyX3JlbGF0aW9uc184ICAoY29zdD0wLjQyLi4yMzA0LjUxIHJvd3M9 MTE2OCB3aWR0aD0xMDIpDQogICAgICAgICAgICAgICAgICAgICAgIEluZGV4 IENvbmQ6IChwYXJlbnRfaWQgPSA4NDYzNDYpDQogICAgICAgICAgICAgICAg IC0+ICBJbmRleCBTY2FuIHVzaW5nIHVzZXJfcmVsYXRpb25zXzlfcGFyZW50 X2lkX2lkeCBvbgp1c2VyX3JlbGF0aW9uc185ICAoY29zdD0wLjQyLi4yNzU1 LjE2IHJvd3M9MTQyNiB3aWR0aD0xMDMpDQogICAgICAgICAgICAgICAgICAg ICAgIEluZGV4IENvbmQ6IChwYXJlbnRfaWQgPSA4NDYzNDYpDQogICAtPiAg Q1RFIFNjYW4gb24gdG1wICAoY29zdD0wLjAwLi4yMjYuODQgcm93cz0xMTM0 MiB3aWR0aD04KQ0KKDI4IHJvd3MpDQoNClRpbWU6IDEuNDA5IG1zDQoNCmFu ZCAid2l0aCB0bXAgYXMgKHNlbGVjdCAqIGZyb20gcmVucmVuLnVzZXJfcmVs YXRpb25zIHdoZXJlIHBhcmVudF9pZD04NDYzNDYKb3JkZXIgYnkgdXNlcl9p ZCkgc2VsZWN0IHVzZXJfaWQgZnJvbSB0bXAgbGltaXQgMTA7IiB0b29rIG9u bHkgMTAwKyBtcy4NCg0KU28sIEkgdGhpbmsgdGhlIG9wdGltaXplci9wbGFu bmVyIGhhcyBhIHBlcmZvcm1hbmNlIGJ1ZyB3aXRoIExJTUlUIGNsYXVzZS4N Cg0KCgo=
On Mon, Aug 29, 2016 at 11:48 PM, <chenkaijiang@gmail.com> wrote: > > the explain result: > > explain select * from renren.user_relations where parent_id=846346 order by > user_id limit 10; > > QUERY PLAN > ------------------------------------------------------------ > ------------------------------------------------------- > Limit (cost=4.57..442.35 rows=10 width=102) > -> Merge Append (cost=4.57..496534.92 rows=11342 width=102) > Sort Key: user_relations.user_id > ... > > It uses the Index Scan using index on user_id, which is very stupid. > This a classic planning problem with ORDER BY...LIMIT. Probably parent_id is correlated with user_id, and if you pick a high value of parent_id then you are implicitly getting high values of user_id. But PostgreSQL doesn't know that, it assumes things with parent_id=846346 are randomly dispersed over the user_id values, and so it will gather 10 of them very quickly by walking the indexes in order. > > If I explain select * from renren.user_relations where parent_id=846346 > order by user_id, then it uses the index on parent_id to get records and > then sort it, which is very wise since the number of qualified records is > 1725. > You know it is 1725, but PostgreSQL thinks it is 11342. Is autoanalyze analyzing often enough? Is default_statistics_target high enough? (Although if I'm right about the correlation between parent_id and user_id, then fixing that estimate might still not be enough to fix things). > So, I think the optimizer/planner has a performance bug with LIMIT clause. > Well, it has to make decisions with the information available to it. That is not really a bug. It is constantly being improved, but will never be perfect. Cheers, Jeff
Thank you very much for your quick response! So I know I have to deal with my own solutions. Fortunately, I got the solution with the "WITH" clause: with t as (select * from renren.user_relations where parent_id=846346 order by user_id) select * from t LIMIT 10; which separate the ORDER BY and LIMIT to avoid the classic planning problem. On Tue, Aug 30, 2016 at 11:45 PM, Jeff Janes <jeff.janes@gmail.com> wrote: > On Mon, Aug 29, 2016 at 11:48 PM, <chenkaijiang@gmail.com> wrote: > > >> >> the explain result: >> >> explain select * from renren.user_relations where parent_id=846346 order >> by >> user_id limit 10; >> >> QUERY PLAN >> ------------------------------------------------------------ >> ------------------------------------------------------- >> Limit (cost=4.57..442.35 rows=10 width=102) >> -> Merge Append (cost=4.57..496534.92 rows=11342 width=102) >> Sort Key: user_relations.user_id >> > ... > >> >> It uses the Index Scan using index on user_id, which is very stupid. >> > > This a classic planning problem with ORDER BY...LIMIT. Probably parent_id > is correlated with user_id, and if you pick a high value of parent_id then > you are implicitly getting high values of user_id. But PostgreSQL doesn't > know that, it assumes things with parent_id=846346 are randomly dispersed > over the user_id values, and so it will gather 10 of them very quickly by > walking the indexes in order. > > >> >> If I explain select * from renren.user_relations where parent_id=846346 >> order by user_id, then it uses the index on parent_id to get records and >> then sort it, which is very wise since the number of qualified records is >> 1725. >> > > You know it is 1725, but PostgreSQL thinks it is 11342. Is autoanalyze > analyzing often enough? Is default_statistics_target high enough? > (Although if I'm right about the correlation between parent_id and > user_id, then fixing that estimate might still not be enough to fix things). > > >> So, I think the optimizer/planner has a performance bug with LIMIT clause. >> > > > Well, it has to make decisions with the information available to it. That > is not really a bug. It is constantly being improved, but will never be > perfect. > > Cheers, > > Jeff >
A suggestion: In the sql "select * from renren.user_relations where parent_id=846346 order by user_id LIMIT 10;", planner can query the number of rows (with parent_id=846346) from the btree index, right? If so, planner will know that it's better to sort the rows (actually less than 2000) than to scan 10M rows. On Wed, Aug 31, 2016 at 12:02 PM, Kaijiang Chen <chenkaijiang@gmail.com> wrote: > Thank you very much for your quick response! > > So I know I have to deal with my own solutions. Fortunately, I got the > solution with the "WITH" clause: > with t as (select * from renren.user_relations where parent_id=846346 order > by user_id) > select * from t LIMIT 10; > > which separate the ORDER BY and LIMIT to avoid the classic planning > problem. > > On Tue, Aug 30, 2016 at 11:45 PM, Jeff Janes <jeff.janes@gmail.com> wrote: > >> On Mon, Aug 29, 2016 at 11:48 PM, <chenkaijiang@gmail.com> wrote: >> >> >>> >>> the explain result: >>> >>> explain select * from renren.user_relations where parent_id=846346 order >>> by >>> user_id limit 10; >>> >>> QUERY PLAN >>> ------------------------------------------------------------ >>> ------------------------------------------------------- >>> Limit (cost=4.57..442.35 rows=10 width=102) >>> -> Merge Append (cost=4.57..496534.92 rows=11342 width=102) >>> Sort Key: user_relations.user_id >>> >> ... >> >>> >>> It uses the Index Scan using index on user_id, which is very stupid. >>> >> >> This a classic planning problem with ORDER BY...LIMIT. Probably >> parent_id is correlated with user_id, and if you pick a high value of >> parent_id then you are implicitly getting high values of user_id. But >> PostgreSQL doesn't know that, it assumes things with parent_id=846346 are >> randomly dispersed over the user_id values, and so it will gather 10 of >> them very quickly by walking the indexes in order. >> >> >>> >>> If I explain select * from renren.user_relations where parent_id=846346 >>> order by user_id, then it uses the index on parent_id to get records and >>> then sort it, which is very wise since the number of qualified records is >>> 1725. >>> >> >> You know it is 1725, but PostgreSQL thinks it is 11342. Is autoanalyze >> analyzing often enough? Is default_statistics_target high enough? >> (Although if I'm right about the correlation between parent_id and >> user_id, then fixing that estimate might still not be enough to fix things). >> >> >>> So, I think the optimizer/planner has a performance bug with LIMIT >>> clause. >>> >> >> >> Well, it has to make decisions with the information available to it. >> That is not really a bug. It is constantly being improved, but will never >> be perfect. >> >> Cheers, >> >> Jeff >> > >
>>>>> "Kaijiang" == Kaijiang Chen <chenkaijiang@gmail.com> writes: Kaijiang> Thank you very much for your quick response! Kaijiang> So I know I have to deal with my own solutions. Fortunately, Kaijiang> I got the solution with the "WITH" clause: Why not just create the correct index on each partition? (parent_id,user_id) -- Andrew (irc:RhodiumToad)
It couldn't solve the problem. I've already created 2 btree indexes, one for parent_id, the other for user_id. Do you mean to create an multi-column index on (parent_id, user_id)? -- still couldn't solve the problem, since we still need index for user_id (for other sql) and planner will turn to user_id index. On Wed, Aug 31, 2016 at 1:01 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote: > >>>>> "Kaijiang" == Kaijiang Chen <chenkaijiang@gmail.com> writes: > > Kaijiang> Thank you very much for your quick response! > > Kaijiang> So I know I have to deal with my own solutions. Fortunately, > Kaijiang> I got the solution with the "WITH" clause: > > Why not just create the correct index on each partition? > (parent_id,user_id) > > -- > Andrew (irc:RhodiumToad) >
On Wed, Aug 31, 2016 at 6:09 AM, Kaijiang Chen <chenkaijiang@gmail.com> wrote: > In the sql "select * from renren.user_relations where parent_id=846346 order > by user_id LIMIT 10;", planner can query the number of rows (with > parent_id=846346) from the btree index, right? > If so, planner will know that it's better to sort the rows (actually less > than 2000) than to scan 10M rows. mmmm, AFAIK planner does NOT query, it plans using stored statistics. This is why some data distribution, which are a bad match for the postgres ways of storing stats, tend to plan ( oacasionally ) badly. Francisco Olarte.
Top posting makes the flow difficult to follow, editing a little bit. >> Why not just create the correct index on each partition? >> (parent_id,user_id) On Wed, Aug 31, 2016 at 2:42 PM, Kaijiang Chen <chenkaijiang@gmail.com> wrote: > It couldn't solve the problem. > I've already created 2 btree indexes, one for parent_id, the other for > user_id. A (parent_id, user_id) index can be used instead of the one for parent_id, so it will nbot need to much extra, and can solve some queries. The planner can normally detected where some keys can be solved directly with a composite index. > Do you mean to create an multi-column index on (parent_id, user_id)? -- > still couldn't solve the problem, since we still need index for user_id (for > other sql) and planner will turn to user_id index. How do you know it will turn to the bad index? Francisco Olarte.
>>>>> "Kaijiang" == Kaijiang Chen <chenkaijiang@gmail.com> writes:
Kaijiang> It couldn't solve the problem.
Kaijiang> I've already created 2 btree indexes, one for parent_id, the
Kaijiang> other for user_id. Do you mean to create an multi-column
Kaijiang> index on (parent_id, user_id)?
Yes. The 2 separate indexes are not sufficient, but you can omit the
index on parent_id alone if you create the multi-column index.
Kaijiang> still couldn't solve the problem, since we still need index
Kaijiang> for user_id (for other sql) and planner will turn to user_id
Kaijiang> index.
The planner should not do that (if it does, it's a bug).
The plan you're looking for is:
Limit
-> MergeAppend
-> Index scan on parent_id_user_id_idx
Index Cond: (parent_id = ?)
-> Index scan on parent_id_user_id_idx
Index Cond: (parent_id = ?)
...
Note the use of Index Cond rather than Filter, this is important.
--
Andrew (irc:RhodiumToad)
Yes, I try it and it works now! Thank you very much, Francisco Olarte, and Andrew Gierth! On Thu, Sep 1, 2016 at 1:41 AM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote: > >>>>> "Kaijiang" == Kaijiang Chen <chenkaijiang@gmail.com> writes: > > Kaijiang> It couldn't solve the problem. > Kaijiang> I've already created 2 btree indexes, one for parent_id, the > Kaijiang> other for user_id. Do you mean to create an multi-column > Kaijiang> index on (parent_id, user_id)? > > Yes. The 2 separate indexes are not sufficient, but you can omit the > index on parent_id alone if you create the multi-column index. > > Kaijiang> still couldn't solve the problem, since we still need index > Kaijiang> for user_id (for other sql) and planner will turn to user_id > Kaijiang> index. > > The planner should not do that (if it does, it's a bug). > > The plan you're looking for is: > > Limit > -> MergeAppend > -> Index scan on parent_id_user_id_idx > Index Cond: (parent_id = ?) > -> Index scan on parent_id_user_id_idx > Index Cond: (parent_id = ?) > ... > > Note the use of Index Cond rather than Filter, this is important. > > -- > Andrew (irc:RhodiumToad) >