Thread: BUG #14399: Order by id DESC causing bad query plan
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDM5OQpMb2dnZWQgYnk6ICAg ICAgICAgIEphbWllIEtvY2VuaWFrCkVtYWlsIGFkZHJlc3M6ICAgICAgamtv Y2VuaWFrQG1lZGlhbWF0aC5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjQu NgpPcGVyYXRpbmcgc3lzdGVtOiAgIExpbnV4CkRlc2NyaXB0aW9uOiAgICAg ICAgCgpPbmUgdGFibGUgaGFzIDJNIHJlY29yZHMgKG9yZGVycykgam9pbmlu ZyB0byBhbm90aGVyIHRhYmxlIHdpdGggNzVLIHJlY29yZHMKKGN1c3RvbWVy cykuDQoNClF1ZXJ5Og0Kc2VsZWN0ICogIEZST00NCiAgICAgICAgICBvcmRl cnMgdDENCiAgICAgICAgICBKT0lOIGN1c3RvbWVyIHQyIE9OICh0MS5jdXN0 b21lcl9pZCA9IHQyLmlkKQ0KICAgICAgICBXSEVSRQ0KICAgICAgICAgIHQy LmlkIElOIChzZWxlY3QgZGlzdGluY3QgY3VzdG9tZXJfaWQgZnJvbSB2YWxp ZF9jdXN0b21lcnMpDQogICAgICAgIE9SREVSIEJZIHQxLmlkICAgICAgICAg ICAgICANCiAgICAgICAgTElNSVQgMTAgOw0KDQotLSB2YWxpZCBjdXN0b21l cnMgc3VicXVlcnkgY29udGFpbnMgMjAwIHJlY29yZHMuDQoNCkZvciBzb21l IHJlYXNvbiB0aGUgbmVzdGVkIGpvaW4gaXMgZG9pbmcgZmlsdGVyOg0KICAg Um93cyBSZW1vdmVkIGJ5IEpvaW4gRmlsdGVyOiA0MTA5NzY0MTUNCg0KU2Vl IGFub255bWl6ZWQgcXVlcnkgcGxhbiBoZXJlOg0KaHR0cHM6Ly9leHBsYWlu LmRlcGVzei5jb20vcy9rOXM1DQoNCklmIEkgcmVtb3ZlZCBvcmRlciBieSwg cXVlcnkgcmV0dXJucyBpbiAxLjVtcwoK
On Thu, Oct 27, 2016 at 5:16 PM, <jkoceniak@mediamath.com> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14399
> Logged by: Jamie Koceniak
> Email address: jkoceniak@mediamath.com
> PostgreSQL version: 9.4.6
> Operating system: Linux
> Description:
>
> One table has 2M records (orders) joining to another table with 75K recor=
ds
> (customers).
>
> Query:
> select * FROM
> orders t1
> JOIN customer t2 ON (t1.customer_id =3D t2.id)
WHERE
> t2.id IN (select distinct customer_id from valid_customers)
ORDER BY t1.id
> LIMIT 10 ;
>
=E2=80=8BBug potential aside the better way to write =E2=80=8Bthat is to us=
e a proper
semi-join (i.e., EXISTS)
SELECT *
FROM order t1
JOIN customer t2 ON (t1.customer_id =3D t2.id)
WHERE EXISTS (SELECT 1 FROM valid_customers t3 WHERE t3.customer_id =3D t2.=
id)
ORDER BY t1.id
LIMIT 10;
Note too that your query plan has a "function scan" node unlike what your
query implies...
Sorry I can't be of more help with the information you've provided.
David J.
SGkgRGF2aWQsDQoNClRoYW5rcyBmb3IgdGhlIHN1Z2dlc3Rpb24gb24gcmV3cml0aW5nIHRoZSBx dWVyeS4NClVuZm9ydHVuYXRlbHksIGl0IHlpZWxkcyB0aGUgc2FtZSBwZXJmb3JtYW5jZSBwcm9i bGVtLg0KDQo8aHR0cHM6Ly9leHBsYWluLmRlcGVzei5jb20vcy9mYWI+aHR0cHM6Ly9leHBsYWlu LmRlcGVzei5jb20vcy9mYWINCg0KUXVlcnkgcmV3cml0dGVuIChzb3JyeSBkaWQgbGVhdmUgb3V0 IGZ1bmN0aW9uIGNhbGwgb24gb3JpZ2luYWwgZW1haWwpOg0Kc2VsZWN0ICogIEZST00NCiAgICAg ICAgICBvcmRlcnMgdDENCiAgICAgICAgICBKT0lOIGN1c3RvbWVyIHQyIE9OICh0MS5jdXN0b21l cl9pZCA9IHQyLmlkPGh0dHA6Ly90Mi5pZD4pDQogICAgICAgIFdIRVJFDQogICAgICAgICAgdDIu aWQ8aHR0cDovL3QyLmlkPiBFWElTVFMgKHNlbGVjdCAxIGZyb20gdmFsaWRfY3VzdG9tZXJzKDE1 MzQ4KSB0MyB3aGVyZSB0My5jdXN0b21lcl9pZCA9IHQyLmlkKQ0KICAgICAgICBPUkRFUiBCWSB0 MS5pZDxodHRwOi8vdDEuaWQ+IERFU0MNCiAgICAgICAgTElNSVQgMTAgOw0KDQpXZSBhbHNvIGRv IHBhZ2luYXRpb24gYW5kIGlmIHlvdSBhZGQgYSBsaW1pdCAxMCBvZmZzZXQgOTAgZm9yIGV4YW1w bGUsIHRoZSBwZXJmb3JtYW5jZSBpcyAxMCB0aW1lcyBhcyB3b3JzZS4NCg0KSWYgeW91IGFjdHVh bGx5IHNvcnQgYnkgYSBub24taW5kZXhlZCBmaWVsZCwgdGhlbiB0aGUgcXVlcnkgcnVucyBpbiAz N21zLg0KSGVyZSBpcyB0aGUgcXVlcnkgcGxhbiB1c2luZyBub24taW5kZXhlZCBmaWVsZDoNCmh0 dHBzOi8vZXhwbGFpbi5kZXBlc3ouY29tL3MvQnRGMg0KDQpTbyBxdWVyeSBzb3J0ZWQgYnkgbm9u LWluZGV4ZWQgZmllbGQgbG9va3MgbGlrZToNCg0Kc2VsZWN0ICogIEZST00NCiAgICAgICAgICBv cmRlcnMgdDENCiAgICAgICAgICBKT0lOIGN1c3RvbWVyIHQyIE9OICh0MS5jdXN0b21lcl9pZCA9 IHQyLmlkPGh0dHA6Ly90Mi5pZD4pDQogICAgICAgIFdIRVJFDQogICAgICAgICAgdDIuaWQ8aHR0 cDovL3QyLmlkPiBFWElTVFMgKHNlbGVjdCAxIGZyb20gdmFsaWRfY3VzdG9tZXJzKDE1MzQ4KSB0 MyB3aGVyZSB0My5jdXN0b21lcl9pZCA9IHQyLmlkKQ0KICAgICAgICBPUkRFUiBCWSB0MS48aHR0 cDovL3QxLmlkPmNyZWF0ZWRfb24gZGVzYw0KICAgICAgICBMSU1JVCAxMCA7DQoNCg0KVGhhbmtz LA0KSmFtaWUNCg0KRnJvbTogRGF2aWQgSm9obnN0b24gPGRhdmlkLmcuam9obnN0b25AZ21haWwu Y29tPG1haWx0bzpkYXZpZC5nLmpvaG5zdG9uQGdtYWlsLmNvbT4+DQpEYXRlOiBUdWVzZGF5LCBO b3ZlbWJlciAxLCAyMDE2IGF0IDQ6NDEgUE0NClRvOiBKYW1pZSBLb2NlbmlhayA8amtvY2VuaWFr QG1lZGlhbWF0aC5jb208bWFpbHRvOmprb2Nlbmlha0BtZWRpYW1hdGguY29tPj4NCkNjOiAicGdz cWwtYnVnc0Bwb3N0Z3Jlc3FsLm9yZzxtYWlsdG86cGdzcWwtYnVnc0Bwb3N0Z3Jlc3FsLm9yZz4i IDxwZ3NxbC1idWdzQHBvc3RncmVzcWwub3JnPG1haWx0bzpwZ3NxbC1idWdzQHBvc3RncmVzcWwu b3JnPj4NClN1YmplY3Q6IFJlOiBbQlVHU10gQlVHICMxNDM5OTogT3JkZXIgYnkgaWQgREVTQyBj YXVzaW5nIGJhZCBxdWVyeSBwbGFuDQoNCk9uIFRodSwgT2N0IDI3LCAyMDE2IGF0IDU6MTYgUE0s IDxqa29jZW5pYWtAbWVkaWFtYXRoLmNvbTxtYWlsdG86amtvY2VuaWFrQG1lZGlhbWF0aC5jb20+ PiB3cm90ZToNClRoZSBmb2xsb3dpbmcgYnVnIGhhcyBiZWVuIGxvZ2dlZCBvbiB0aGUgd2Vic2l0 ZToNCg0KQnVnIHJlZmVyZW5jZTogICAgICAxNDM5OQ0KTG9nZ2VkIGJ5OiAgICAgICAgICBKYW1p ZSBLb2Nlbmlhaw0KRW1haWwgYWRkcmVzczogICAgICBqa29jZW5pYWtAbWVkaWFtYXRoLmNvbTxt YWlsdG86amtvY2VuaWFrQG1lZGlhbWF0aC5jb20+DQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNC42 DQpPcGVyYXRpbmcgc3lzdGVtOiAgIExpbnV4DQpEZXNjcmlwdGlvbjoNCg0KT25lIHRhYmxlIGhh cyAyTSByZWNvcmRzIChvcmRlcnMpIGpvaW5pbmcgdG8gYW5vdGhlciB0YWJsZSB3aXRoIDc1SyBy ZWNvcmRzDQooY3VzdG9tZXJzKS4NCg0KUXVlcnk6DQpzZWxlY3QgKiAgRlJPTQ0KICAgICAgICAg IG9yZGVycyB0MQ0KICAgICAgICAgIEpPSU4gY3VzdG9tZXIgdDIgT04gKHQxLmN1c3RvbWVyX2lk ID0gdDIuaWQ8aHR0cDovL3QyLmlkPikNCiAgICAgICAgV0hFUkUNCiAgICAgICAgICB0Mi5pZDxo dHRwOi8vdDIuaWQ+IElOIChzZWxlY3QgZGlzdGluY3QgY3VzdG9tZXJfaWQgZnJvbSB2YWxpZF9j dXN0b21lcnMpDQogICAgICAgIE9SREVSIEJZIHQxLmlkPGh0dHA6Ly90MS5pZD4NCiAgICAgICAg TElNSVQgMTAgOw0KDQrigItCdWcgcG90ZW50aWFsIGFzaWRlIHRoZSBiZXR0ZXIgd2F5IHRvIHdy aXRlIOKAi3RoYXQgaXMgdG8gdXNlIGEgcHJvcGVyIHNlbWktam9pbiAoaS5lLiwgRVhJU1RTKQ0K DQpTRUxFQ1QgKg0KRlJPTSBvcmRlciB0MQ0KSk9JTiBjdXN0b21lciB0MiBPTiAodDEuY3VzdG9t ZXJfaWQgPSB0Mi5pZDxodHRwOi8vdDIuaWQ+KQ0KV0hFUkUgRVhJU1RTIChTRUxFQ1QgMSBGUk9N IHZhbGlkX2N1c3RvbWVycyB0MyBXSEVSRSB0My5jdXN0b21lcl9pZCA9IHQyLmlkPGh0dHA6Ly90 Mi5pZD4pDQpPUkRFUiBCWSB0MS5pZDxodHRwOi8vdDEuaWQ+DQpMSU1JVCAxMDsNCg0KTm90ZSB0 b28gdGhhdCB5b3VyIHF1ZXJ5IHBsYW4gaGFzIGEgImZ1bmN0aW9uIHNjYW4iIG5vZGUgdW5saWtl IHdoYXQgeW91ciBxdWVyeSBpbXBsaWVzLi4uDQoNClNvcnJ5IEkgY2FuJ3QgYmUgb2YgbW9yZSBo ZWxwIHdpdGggdGhlIGluZm9ybWF0aW9uIHlvdSd2ZSBwcm92aWRlZC4NCg0KRGF2aWQgSi4NCg0K