Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id B34039FB614 for ; Wed, 10 Oct 2007 20:27:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 38403-03 for ; Wed, 10 Oct 2007 20:26:46 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from rwcrmhc13.comcast.net (rwcrmhc13.comcast.net [204.127.192.83]) by postgresql.org (Postfix) with ESMTP id 971799FB3A0 for ; Wed, 10 Oct 2007 20:26:55 -0300 (ADT) Received: from [127.0.0.1] (c-71-63-209-98.hsd1.mn.comcast.net[71.63.209.98]) by comcast.net (rwcrmhc13) with ESMTP id <20071010232651m1300ohpb3e>; Wed, 10 Oct 2007 23:26:51 +0000 Message-ID: <470D5F81.5090403@comcast.net> Date: Wed, 10 Oct 2007 18:25:53 -0500 From: Mike Adams User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.6) Gecko/20070728 Thunderbird/2.0.0.6 Mnenhy/0.7.5.666 MIME-Version: 1.0 To: pgsql-sql Subject: Refactored queries needing validation of syntactic equivalence Content-Type: multipart/mixed; boundary="------------010903090608020001040801" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200710/54 X-Sequence-Number: 29426 This is a multi-part message in MIME format. --------------010903090608020001040801 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hello! I'm a long time lurker who has become responsible for maintaining / updating utility queries at work. I've reworked two queries (as text attachment as they are wide lines) to enhance the planner's chance of speeding up the queries (Oracle8i's). I'm looking for someone to eyeball them and let me know if I've folded the sub-selects up correctly (I'm the ONLY sql speaking person at work so having a coworker do so is unfortunately not possible). Also unfortunately, there currently aren't any issues in the database that these queries are designed to find. All I can say for sure is (as you can see below each query) my refactored queries *at the least* return *no* data faster than the legacy queries... Thank you in advance and I wish the application at work used postgresql as it's backend! Michael Adams --------------010903090608020001040801 Content-Type: text/plain; name="legacy_vs_refactored.txt" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="legacy_vs_refactored.txt" ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIGxlZ2FjeSAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgfCAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHJlZmFjdG9y DQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCiBzZWxlY3QgbS5jb19jZCwgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgc2Vs ZWN0IG0uY29fY2QsDQogICAgICAgIG0udmVfY2QsICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8ICAgICAgICBtLnZlX2NkLA0KICAgICAg ICBtLml2Y19jZCwgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgfCAgICAgICAgbS5pdmNfY2QsDQogICAgICAgIG0ucG9fY2QsICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8ICAgICAgICBt LnBvX2NkLA0KICAgICAgICBtLml0bV9jZCwgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgfCAgICAgICAgbS5pdG1fY2QsDQogICAgICAgIG0u cXR5LCAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICB8ICAgICAgICBtLnF0eSwNCiAgICAgICAgbS51bml0X2NzdCwgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgICAgICAgIG0udW5pdF9j c3QsDQogICAgICAgIChtLnF0eSptLnVuaXRfY3N0KSBhcyBleHRfY3N0LCAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICB8ICAgICAgICAobS5xdHkqbS51bml0X2NzdCkgYXMgZXh0 X2NzdCwNCiAgICAgICAgdG9fY2hhcihtLnJjdl9kdCwnWVlZWS1NTS1ERCcpIGFzIHJlY2Vp dmVkLCAgICAgICAgICAgICAgICAgIHwgICAgICAgIHRvX2NoYXIobS5yY3ZfZHQsJ1lZWVkt TU0tREQnKSBhcyByZWNlaXZlZCwNCiAgICAgICAgb3JpZ2luX2NkLCAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgICAgICAgIG0ub3JpZ2lu X2NkLA0KICAgICAgICB0b19jaGFyKG0uYXNzaWduZWRfZHQsJ1lZWVktTU0tREQnKSBhcyBh c3NpZ25lZCAgICAgICAgICAgICAgfCAgICAgICAgdG9fY2hhcihtLmFzc2lnbmVkX2R0LCdZ WVlZLU1NLUREJykgYXMgYXNzaWduZWQNCiBmcm9tICAgcmN2X21vIG0gICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgZnJvbSAgIHJjdl9t byBtLCByY3ZfbW8gcg0KIHdoZXJlICAoIG0ub3JpZ2luX2NkICAgID0gJ01PTScgKSAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgfCB3aGVyZSAgKCBtLm9yaWdpbl9jZCAg ICA9ICdNT00nICkNCiAgICBhbmQgKCBtLkFTU0lHTkVEX0RUIDw9ICczMS1PY3QtMjAwNycg ICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgICAgYW5kICggbS5BU1NJR05FRF9EVCA8 PSAnMzEtT2N0LTIwMDcnDQogICAgICAgICAgb3IgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8ICAgICAgICAgIG9yDQogICAgICAg ICAgbS5BU1NJR05FRF9EVCBpcyBudWxsICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICB8ICAgICAgICAgIG0uQVNTSUdORURfRFQgaXMgbnVsbA0KICAgICkgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgfCAgICApDQogICAgYW5kICggZXhpc3RzICggc2VsZWN0IDEgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICB8ICAgIGFuZCAoICAgbS5wb19jZCAgICAgPSAg ci5wb19jZCAgKQ0KICAgICAgICAgICAgICAgICAgIGZyb20gICByY3ZfbW8gIG8gICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgfCAgICBhbmQgKCAgIG0uaXRtX2NkICAgID0g IHIuaXRtX2NkICkNCiAgICAgICAgICAgICAgICAgICB3aGVyZSAgKCBtLnBvX2NkICAgICAg ICA9IG8ucG9fY2QgICkgICAgICAgICAgICAgIHwgICAgYW5kICggci5hc3NpZ25lZF9kdCBp cyBudWxsICAgICAgICkNCiAgICAgICAgICAgICAgICAgICAgICBhbmQgKCBtLml0bV9jZCAg ICAgICA9IG8uaXRtX2NkICkgICAgICAgICAgICAgIHwgICAgYW5kICggci5yY3ZfZHQgICAg ICA8PSAnMzEtT2N0LTIwMDcnKQ0KICAgICAgICAgICAgICAgICAgICAgIGFuZCAoIG8uYXNz aWduZWRfZHQgaXMgbnVsbCApICAgICAgICAgICAgICAgICAgfCBvcmRlciBieSBtLlZFX0NE LCBtLnBvX2NkLCBtLml0bV9jZA0KICAgICAgICAgICAgICAgICAgICAgIGFuZCAoIG8ucmN2 X2R0ICAgICAgPD0gJzMxLU9jdC0yMDA3JykgICAgICAgICAgfA0KICAgICAgICAgICAgICAg ICApICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg fCAtLSAwIHJlY29yZChzKSBzZWxlY3RlZCBbRmV0Y2ggTWV0YURhdGE6IDAvbXNdIFtGZXRj aCBEYXRhOiAwL21zXQ0KICAgICkgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgfCAtLSBbRXhlY3V0ZWQ6IDEwLzEwLzA3 IDk6MjQ6MDkgQU0gQ0RUIF0gW0V4ZWN1dGlvbjogOTM3L21zXQ0KIG9yZGVyIGJ5IG0uVkVf Q0QsIG0ucG9fY2QsIG0uaXRtX2NkICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg fA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgfA0KIC0tIDAgcmVjb3JkKHMpIHNlbGVjdGVkIFtGZXRjaCBN ZXRhRGF0YTogMTYvbXNdIFtGZXRjaCBEYXRhOiAwL21zXSAgfA0KIC0tIFtFeGVjdXRlZDog MTAvMTAvMDcgODo0NzozOSBBTSBDRFQgXSBbRXhlY3V0aW9uOiAyMDU0MzMzL21zXSAgICAg fA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgfA0KLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tDQot LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCiBzZWxlY3Qgby5jb19jZCwgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgc2VsZWN0 IG8uY29fY2QsDQogICAgICAgIG8udmVfY2QsICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICB8ICAgICAgICBvLnZlX2NkLA0KICAgICAgICBv Lml2Y19jZCwgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgfCAgICAgICAgby5pdmNfY2QsDQogICAgICAgIG8ucG9fY2QsICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8ICAgICAgICBvLnBv X2NkLA0KICAgICAgICBvLml0bV9jZCwgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgfCAgICAgICAgby5pdG1fY2QsDQogICAgICAgIG8ucXR5 LCAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICB8ICAgICAgICBvLnF0eSwNCiAgICAgICAgby51bml0X2NzdCwgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgICAgICAgIG8udW5pdF9jc3Qs DQogICAgICAgIChvLnF0eSpvLnVuaXRfY3N0KSBhcyBleHRfY3N0LCAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICB8ICAgICAgICAoby5xdHkqby51bml0X2NzdCkgYXMgZXh0X2Nz dCwNCiAgICAgICAgdG9fY2hhcihvLnJjdl9kdCwnWVlZWS1NTS1ERCcpIGFzIHJlY2VpdmVk LCAgICAgICAgICAgICAgICAgIHwgICAgICAgIHRvX2NoYXIoby5yY3ZfZHQsJ1lZWVktTU0t REQnKSBhcyByZWNlaXZlZCwNCiAgICAgICAgby5vcmlnaW5fY2QsICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgICAgICAgIG8ub3JpZ2luX2Nk LA0KICAgICAgICB0b19jaGFyKG8uYXNzaWduZWRfZHQsJ1lZWVktTU0tREQnKSBhcyBhc3Np Z25lZCAgICAgICAgICAgICAgfCAgICAgICAgdG9fY2hhcihvLmFzc2lnbmVkX2R0LCdZWVlZ LU1NLUREJykgYXMgYXNzaWduZWQNCiBmcm9tICAgcmN2X21vIG8gICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgZnJvbSAgIHJjdl9tbyBv LCByY3ZfbW8gbQ0KIHdoZXJlICAoIG8uYXNzaWduZWRfZHQgaXMgbnVsbCAgICAgICAgICAg ICAgICkgICAgICAgICAgICAgICAgICAgICAgfCB3aGVyZSAgKCBvLmFzc2lnbmVkX2R0IGlz IG51bGwgICAgICAgICAgICkNCiAgICBhbmQgKCBvLnJjdl9kdCAgICAgIDw9ICczMS1PY3Qt MjAwNycgKSAgICAgICAgICAgICAgICAgICAgICAgICAgIHwgICAgYW5kICggby5yY3ZfZHQg ICAgICA8PSAnMzEtT2N0LTIwMDcnICApDQogICAgYW5kICggZXhpc3RzICggc2VsZWN0IDEg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8ICAgIGFuZCAoIG0u b3JpZ2luX2NkICAgID0gJ01PTScgICAgICAgICAgKQ0KICAgICAgICAgICAgICAgICAgIGZy b20gICByY3ZfbW8gIG0gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgfCAgICBh bmQgKCBvLnBvX2NkICAgICAgICA9IG0ucG9fY2QgICAgICAgICkNCiAgICAgICAgICAgICAg ICAgICB3aGVyZSAgKCBtLm9yaWdpbl9jZCAgICA9ICdNT00nICAgICAgICAgKSAgICAgICAg IHwgICAgYW5kICggby5pdG1fY2QgICAgICAgPSBtLml0bV9jZCAgICAgICApDQogICAgICAg ICAgICAgICAgICAgICAgYW5kICggby5wb19jZCAgICAgICAgPSBtLnBvX2NkICAgICAgICkg ICAgICAgICB8ICAgIGFuZCAoIG0uYXNzaWduZWRfZHQgPD0gJzMxLU9jdC0yMDA3Jw0KICAg ICAgICAgICAgICAgICAgICAgIGFuZCAoIG8uaXRtX2NkICAgICAgID0gbS5pdG1fY2QgICAg ICApICAgICAgICAgfCAgICAgICAgb3INCiAgICAgICAgICAgICAgICAgICAgICBhbmQgKCBt LmFzc2lnbmVkX2R0IDw9ICczMS1PY3QtMjAwNycgICAgICAgICAgIHwgICAgICAgICAgbS5h c3NpZ25lZF9kdCBpcyBudWxsDQogICAgICAgICAgICAgICAgICAgICAgICAgICAgb3IgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8ICAgICkNCiAgICAgICAgICAg ICAgICAgICAgICAgICAgICBtLmFzc2lnbmVkX2R0IGlzIG51bGwgICAgICAgICAgICAgICAg ICAgIHwgb3JkZXIgYnkgby52ZV9jZCwgby5wb19jZCwgby5pdG1fY2QNCiAgICAgICAgICAg ICAgICAgICAgICApICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgIHwNCiAgICAgICAgICAgICAgICAgKSAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgIHwgLS0gMCByZWNvcmQocykgc2VsZWN0ZWQgW0ZldGNo IE1ldGFEYXRhOiAwL21zXSBbRmV0Y2ggRGF0YTogMC9tc10NCiAgICApICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwg LS0gW0V4ZWN1dGVkOiAxMC8xMC8wNyA5OjMyOjAzIEFNIENEVCBdIFtFeGVjdXRpb246IDM0 NC9tc10NCiBvcmRlciBieSBvLnZlX2NkLCBvLnBvX2NkLCBvLml0bV9jZCAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgIHwNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwNCiAtLSAwIHJlY29y ZChzKSBzZWxlY3RlZCBbRmV0Y2ggTWV0YURhdGE6IDAvbXNdIFtGZXRjaCBEYXRhOiAwL21z XSAgIHwNCiAtLSBbRXhlY3V0ZWQ6IDEwLzEwLzA3IDk6MzE6MjggQU0gQ0RUIF0gW0V4ZWN1 dGlvbjogMTQ4NTYyL21zXSAgICAgIHwNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHwNCi0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0NCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0= --------------010903090608020001040801--