X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 13C349DCA13 for ; Sat, 21 Jan 2006 16:55:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 49129-04 for ; Sat, 21 Jan 2006 16:55:25 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.194]) by postgresql.org (Postfix) with ESMTP id 9FE029DC821 for ; Sat, 21 Jan 2006 16:55:21 -0400 (AST) Received: by xproxy.gmail.com with SMTP id i30so466867wxd for ; Sat, 21 Jan 2006 12:55:23 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:mime-version:content-type; b=V2k9yUnofzp4mcvxxrN+u7hwWYqx0I203t/9qTp1GY/9gI0zj69Vr6DSCFivqgOlpLmNM//uPNAwD/NdvvIaSRuUEqDWApQBF+0xksrGF+wGcWsf47cduFVJYUviV61hpho82gjGElFCEMRGy4N4vIXGGXLAeXo9kIudDf9waOA= Received: by 10.70.60.17 with SMTP id i17mr3928468wxa; Sat, 21 Jan 2006 12:55:23 -0800 (PST) Received: by 10.70.102.3 with HTTP; Sat, 21 Jan 2006 12:55:23 -0800 (PST) Message-ID: Date: Sat, 21 Jan 2006 22:55:23 +0200 From: =?ISO-8859-1?Q?=DCmit_=D6ztosun?= To: pgsql-performance@postgresql.org Subject: Slow queries consisting inner selects and order bys & hack to speed up MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_7951_5594348.1137876923424" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200601/399 X-Sequence-Number: 16877 ------=_Part_7951_5594348.1137876923424 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hello, Our application uses typical queries similar to following (very simplified)= : SELECT part_id, part_name, (SELECT SUM(amount) FROM part_movements M WHERE P.part_id =3D M.part_id ) as part_amount FROM parts P LIMIT 50 The parts table holds thousands of items. Movement table stores yearly movement information of those items. We are presenting results to users pag= e by page, hence the limit case. User can sort and filter results. When sorting is introduced, query performance drops significantly: SELECT part_id, part_name, (SELECT SUM(amount) FROM part_movements M WHERE P.part_id =3D M.part_id ) as part_amount FROM parts P ORDER BY part_name LIMIT 50 Postgres seems to compute all possible rows and then sorts the results, which nearly renders the paging meaningless. A dummy WHERE case dramatically improves performance: SELECT part_id, part_name, (SELECT SUM(amount) FROM part_movements M WHERE P.part_id =3D M.part_id ) as part_amount FROM parts P ORDER BY part_name WHERE part_amount > -10000000 LIMIT 50 Is there a way to improve performance of these queries? Is it possible to instruct Postgres to first sort the rows then compute the inner queries? (We have simulated this by using temporary tables and two stage queries, but this is not practical because most queries are automatically generated). Attached is the output of real queries and their corresponding EXPLAIN ANALYZE outputs. Regards, Umit Oztosun ------=_Part_7951_5594348.1137876923424 Content-Type: application/zip; name=pgperf.zip Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="pgperf.zip" UEsDBBQAAgAIADS2NTQ6KGSkkhgAAOJbAgAKAAAAcGdwZXJmLnR4dO1d627bSLL+HyDv0BsEsJSR BXY3r8Y4QCbjweZMZpMTZ89gfxmMxTiEbl5KysR+q32HfbDTZJMSL02yaVESL6UBJiab7GtVseqr 6urr2fIv9L8bx3u4eP7scvd7/uz5s+ur91dvP6NX6LdPH/5AAxReP3+GCn6vRiUPDGxv7d7cOd6t M71xvRX6CaXu3Extb+V+db25XVhIotK5PfvqrpJXNxPnfnmOBs50lWotdSfZWn4hiUq3rcWugtaG Q2SvUPjyar2cjmRm4qvrztz4RGxvCOZBVCY/DfGmkjcEk5DXlMwc8Hf9KSiegeJSn+xKZlCKJOXq mG5mM3vh3rr2xOUUv2Jj2t5Ff/796tMVupk6D+gS+UMb32xWjjdEb64T744km1tOdq0sH52Zf0PQ Bvv7JvYIRkGD0dVhGyOJxkjlxm6/MtLwpnZuU9EDQTPBX6M6lvKL67nzRDf8NhkZr2+CopnjuaNg Lvz7/N7NzF2tnZVb3EI4EnGVu/nLVuuPW1gQvFQyLvTmH7/ups2vqqj9WJlMteKq7IUdXLDGzvBZ cT1DtnjBw+XUL7d+BcODJT3akkbPsx7Uw5WMv4Enj7yAwZzXsnxf3Qd7jWH5jrZ8fMLrEai+drb8 7j6Od8pNpSUc7aqA1ay8mkX1bKckmFwcTgNfLL+snE74i7XQydsPb95fXb+9QoPVZj5guv/Unvkj nbvTte0NR0gZ7qiHFdsz98FB7I9R4g6zGGbO3EW8AimCCdsKZiN4e73xNnFlUWKG43UkOnjpF1Wo ZRyzhtjyvpVZXn+2xn6nLxk5oA+f0O5aTV0bqWszdY3x2VCmxeSAQwPsZrXcMAPU77eFTUPRsaGr pm6YmJoma1mq5sEgVjV6/Rqp5pDVSDTD76m4UMNqfiHWqFVQalh6filRNFpQSrEiKtX0oGZiDIeS i+7er+2ZP2/nZ5JvMJ5wv6Gf2StEUfRzBZ9js0yw++Zx0pQHzj0Y5/5ekXP1FCdaqWuSuqapa20f xp04qzWwbWvYNgaLAQc3hoPh2wtMnPs7P0fX9sRhK2Q/LP77HxRQ2cz2GGfbK3YdES1TzCcO+uas 7PuZzR58WHqyVJicguhOQCFBa4GU30fWkAMIG47fF8kb9sSjM3dYj5ilxh8fZQoi0bOtTkr67BrP WGcVZZCgpkS/L8MnZOsL5yXx7U/c0rK3dEnxEevJVnzsKzeS48+KjrzySHrklW8FSO4DkQzJ7UEk RnIfiCRJ9oFqwiRajH0+8SF9A5+dhM/wHhxUiw4NbFQnGwXrAgpyY8Cpk5q4oB833shNxn4A44Jl C+BU+/gWwCmAl+HbC9jUQbCp/PBQsJlPYzPTLBClZm8ZgE212aiOhVoDn7UPmwIOahIHASzV7Jgp sTU7SJi/Ucvbzn+1WZmN/nbpz3nycWb9Fj8PmnXnQzd227qA8RtrE4tt4EHCaK7C+Nrh+B4wsXZx PYBibcC14csPMqCn8V7ifecgq0BWgbbSEw8eaCtgroCyAspK8x2AoKy0LNIPRBWIqoNpK6O9cjBF DD+IJIC9umP94vy+Y/WANu6ZPJw7a8/PGTC5L6k4zEYQywwwuRf4GP3J2BZERPjVnbFW/MlUzrjM 8e20WMdGtQ75bvPdWcyaOOZEz+odtP3IhHoTl3nXr1G1IRaXBl18JZH3LJEuApVnUgt6vVoXPBXk 2Lq5/vNzOFvonx8/Xn0aTJgsn/tihl+evTnz5fyvV5/QL/8K70XdmC4nmyFiOsItev/uj3efkab4 aQLfLOzZw6ODPmzW95t1IotgkEjwPZvINfu43y5X60usEp3QMSbjcfSnibzlX6tLoqG/3Mn626Wm 0CEa2LfrDRNla3fuXGLL0M2xZajsJfanNaa6zl/SFDRbLu9XlziQkOevEbpeerW0pmlRa1gjwtb4 L2jvd+fhAm3u7x1vEEzXODFnFxdr58c69k7QT+ff6PrWXqDlIrvY0QCUsaKEfSdjTS3tuzo2iO73 mxJ1rFPKX1At1RD0PKQ2Rv+Od4EGYfc5QYRd9tnizVmm/+HIN18+MhU0S3L+8N7c3XnOnb12oqFo bE7ZWIJ/eLdwOAysp0bBRq2ytfb/0aNHeff9keR+C/1m/8E0WGeC3rOnk3PI2jWt8nYpb5eGU63I tMt//7N0F7vJfOEuFo73IjcK5cVys47KSyr2h/VuMXF+cHLZrNzFXTbuxtfJbtzJj4icBNE6qQkZ M00oMR9mdjqIFUwHsSpPB//xfr9dLiZsTkJdGl9crFiHZu5iLVXHdk4H6XgagXJ4cfHFvfNr5kqg ULu5uJgwuhzWMu3cxMh8avJWIhMelVoTOqY4uShEZauycL47HnJ+OLeMaibVZ14UVfVSrTj7g4wi zS2CQUaHFt731WdxAdOchQW+0iwuYPpyqqBQVc6VR5jLIywhFwwuF6wa5ZGMHCQmZ0CzMgM2iX5T g8JKMChMKkwm0Ha5GSi35Pd+tRW+EmbpVwJjvp44SaREZ7NSeT3jX8aS+M2qixx+gGjsAxTMdHhf zblvxO6H35WDf4mEIos1MtZ9XTb4t1x46HqwLrpZl9CSbZjyhjWtPqmVAvWSCF1cXInRv/Q4cIau iwSVUZugioOKlaVUDkhWTnytkW97U0dcuMWR4eT664xEy6UaNwVwioh1lao1SDUBKlyRGtKOLLbm 97ffbC9a85DK9BypZuXcJzn386SmlpWOMRQy1akiKRh2O0kVAgfFwcRl7Tpe0LCEcsls+7qVvNOI Sz4OrNZhLdYtLsP4BxCWTxCWpFxYasIvJVZM3CZhifdQDeP3zZz7GNclLQ+mM3KVRyfHFoKUW7oa brsQDI0hE3TGHorB8AtIW6Ezvm29zjg8hPOBKgEL052NcBznAzEDEUiiLvbd+RBKUiP6eKgN8j7I q5MdcUGk1kbhtpKSspV0i2gmYLjH90+onFdUemz/RGgzY/BPAG033T9B2+GfIBJaU/y+3lb/hMaj C3bW4bH8EypHK1SrI/4JsDX76J9ola3ZAcjt9A4K7lal5tEdFLxhYoCDAhwU7XZQpLy5bXNQdA6Z q+yf4Lq8TsE/Af4JkIL99E90z03bkt0RlCugFBwUsDsCdkeA9yGpG6nKsb0PT2ZA8D6A9wF2R3Rv dwQNDToqYUlyJUrXj747gkMRmgq7I8CSbK33gXZ6d4Q0biYE4GJScJcU5+fXflKcxEua5Euwr+JJ gpbQbu2r0MBt0Tsx2+19FdLAnFALlRWzCTjvYHK2XR4PGbnNreso+w14PEBPhSiZhu3I6JCeeqC9 HITv5dCOvJfDUvheDgKukhgbGgT2csBeDvCmFHt3udmqaqfJNfUE724LvCkUvCnd8KaIQzhhL8eJ vSmn3suhKR2xUsGb0mNvSkf3cnQJ5uvoLhACu0DAndIbd0pHd4H0EA2s7E05WdRP6E2hHZGeFuip 4E3p2P6RHrij08eOiA7bSdCF//9y6aZwA0apIt12y/ZS3erQMXBNhH/sjutRtvwMI+3NSNubciQM gms/TAVBv/D5JxBM0SXzqcqmCW5A68apjpSATRMgP+FICYCfOo/y172lFjZNAMoPmybAm9riTRNV wlG4+NSPLj6j1CwW6Kmgp0I4CghQCEeBcBQIRwE5C+EoEI7SpM29RkfSmYKeCv4oCEfpjKEPiVDb mAjVfCpmDIlQIRFqlxKh1p3qqtrW3TqdNg3Yl25BIlRIhAqJUA9ug546pkcDGxRsUIjpgYOFehyZ g/sXmQMOj96ewmZ2MzKnvecPVdYZOQ/r6tGPYbPgGDbQGeEYtibvo2ntYZRPSy2qHjm1qBmIQGJh cD7EU4uqkFoUUouCf6LYX2omdiEfzT8R+Us75Z+IkB8d/BPd8E8kt41AatGG+CdgLwf4J8DWhL0c LYHcOnremoyDwoCtw+CgaDcyZyZg2a7tyGgtMldVBpocXjfpsWWgxj+3RuvzfJmJo9VBZ+yjFLS6 ua+iNW7aXGlkaWOcEkYqEQijIMAOVwpVSfkIEovH+M+Hp26+uJ47v5m5rHfcUxDzJDxNvFp0bKYG pCkC1ZZb5Fp1S7ZkWJPld/cRyw6lhCW38GMwTzPHK0Yms4+n5oaMDa38yxN5lNU6JHZO9s5KbGov 7GBEPjfgHTeUT2oin2m0OKnP2Nb0pQXkomgJ25fKagHpDgiIPtWb7flSNF+qKpx4qb59tLA7xfyi jnd+xwJTMGgSK7gpAuBE/FIUgVGdX/bnlKfyyOGJUzsMcYq+TlTjxEl6TpwiyZWCIIA4Dyk5TaDN SooG0GY2S/qhaJMImtSCBcBE6TlxinYiq4mkQH2iTarnKsBbV4AEcaYPE5jb3tRON25q5Z/3EESo lGp/O4UBQHKJXtKcXmYpjk3a8tGZTZeTHRKyu5cOB0iTkq7nx3dUCdHNwF7+IMgpByFO2V59EFiG Xlhfp5vZzF64txmCJYYEzYSuK7IPzSjs0c9LH4bxNgu/5guELcNkLGMpaL56/sz/7zebcZWH/s/x Vu5ywd5eLmYPyJ5MmGB+8dV1Z27Ag+g1OsdK8HuBbu2Vw+q+TP6eP7u+en/19jN6hX779OEPNEDh dbG0eDUqeWDAJI57c+d4t87Ux8zQTyh15yaGgxUVkqg0DI1JXAWI6jkaONNVqrXUnWRr+YUkKt22 FrsKWhsOkb1C4cv+LI9kZoIvSmwitjcE8yAqk5+GeFPJG4JJyGtKZg52hFY8AyUfHkZ2JTMoRZJy dWwZ3J64nOKTbP/n368+XaGQGf2hjW82K8dHQa8T744km2Nyb9tKJAgFbUSaTPgIRkGD0dVhGyOJ xkjlxraf2bymogeCZoK/RnUsZaBgJLqR1cJGOfpICRTIRyKuskTtFBcEL5WMKwDat9PmV1XUfqxM plpxVUldr7ieIVu84OFy6pdbv4LhwZIebUmj51kP6uFKxt/Ak0dewGDOa1m+r+6DvcawfEdbPj7h 9QjUyNs03ik3lZZwtKsCVrPyahbVk3XV7hbLLyunE/5iLXTy9sOb91fXb6/QYLWZD3icjT/SuTtd 295whJThjnriwRijgkAdKYIJ28pE1GwXVWKG43XkBGTI1TJOB2JIvObP1tjv9CUjBz+aYnetpq6N 1LWZusb4bCjTYnLAmSCiM0EU0RmSqnkwiFWdCDIK4kSEhRpW8wv9cKOCUsPS80v9wKOCUooVUan0 tpztosdCXCTfEIa+lDCsbx4nTXng3INx7u8VOVdPcaKVuiapa5q61vZh3DACGti2HWwbg8WAgxvD wfDtBSbOD8I7R9f2xGErZD8s/vsfFFDZzPYYZ9srdh0RLVPMJw765qzs+5nNHnxYerJUmJyC6E5q i9Q+soYcQNhs94vnyhvBTuRR+fZzKemza1y0V7yKDBLUlL87WWZBw8fj3/7ELS17S5cUH7GexHcq 7yU3kuPPio688kh65JVvBUjuA5EMye1BJEZyH4gkSfaBasIkWox9PvEhfQOfnYTP8B4cVIsODWxU JxsF6wIKcmPAqZOauKAfN97ITcZ+AOOCZQvgVPv4FsApgJfh2wvY1EGwqfzwULCZT2Mz0ywQpWZv GYBNtdmojoVaA5+1D5sCDmoSBwEs1eyYKbE1O0iYv1HL6WN9/3aJwoRECeu3+HnQrDsfurHb1gWM 31ibWGwDDxJGcxXG1w7H94CJtYvrARRrA64NX36QAT2N9xLvOwdZBbIKtJWeePBAWwFzBZQVUFaa 7wAEZaVlkX4gqkBUHUxbGe2Vgyli+EEkAezVHesX5/cdqwe0cc/k4dxZe37OgMl9ScVhNoJYZoDJ vcDH6E/GtiAiwq/ujLXiT6ZyxmWOb6fFOjaqdch3m+/OYtbEMSd6Vu+g7Ucm1Ju4zLt+jaoNsbg0 6OIribxniXQRqDyTWtDr1brgqSDH1s31n5/D2UL//Pjx6tNgwmT53Bcz/PLszRk/10GYK5AJqV+v PqFf/hU+HXVwupxshohpD7fo/bs/3n1GmsLG+PzZm4U9e3h00IfN+n6zvhDkGHzPZnmdOsrDpJqm U/Y3z5ZobfNJ01RmRTq2/DMAiWKMtejsBG17tlEgM1P5KH+xb6d/2d5ElM81PhhRBtdgFfboqp/t 2e8q0S1hV8PF354Gsrm/d7xBuEAXF2vnx9pfJ7ZC0UX4nfN/q80XXx0cop/Q9m/pi3O0dx3DGJUo FxeLzdzx3Nv4d+t68+UjezROoPsfVq6oPE2xmkrarClVcxTLHVKuUJLI5KkUtcd/Bzic/JTHkhNT eBhewQTw336nkeecmHS4I8ibdD7zwvnueMj54dwy2phUmecnnb7c7nOXazhNnPITNCitQabIyDDC ZQqRlykNPj1csXhiZmUf+dBDuq2+vAc4KVycVBuTKmtX3wnh2bPB6R4nnx3+bPB6Trnl/KPifYWP dIP8FG2q7C99TnOioyo81fYJAqfSSY5whiM69onf4oXG2l7Sqfqpjd09tfZpR3zXI/Zo8pyu/cSe zKHe4YkqasvFntoMsdej47uPemQtwULbQG26zGvpGbW1yDKVa1QqOZoss1quwlFQ4XohzcJzkLDR UBXubetVuGG9+DoxAjWDGPpx8HWiB6KA/dNXfD08vteIxL56UoBdXq9rPcqexjG5qFJSfg5DASDz 4AB8bQqULACvcyVb7wIAH53/TQCAbx0ATzjhE6WpADyR0F/i93UA4AGAB+utIwC81lIAvj1g1CkR eB7YoypHQ6202rQukHuAwB8Ks4oi/tTU2d3gdWwsAK9xztT0Y4myyGBtqzMxsjsIqHAdF2Y0Icza A8B3yZ8IEe7NjnDnyC8xIMIdItwhwr0WgJ3LMEI7AbBzZQljANghwh0i3E8MNBHp8ChiQXgUWGcA sEOE+2nwdQ5KUR0i3CHCHSApiHCHCPfqEe4YfIWgwjU6wp2HEmIdItxbEuGuWzzCHR8pwl3j7WlW 3yPcFYhwb0SEuwER7scH4OsyBmUB+DDWSusSAA8pZloIwIdYJoUI9x5FuOOWW28AwAMAD2AU5JgB BB4Q+O4i8BDh3poId5UvmUqPJspMAOBBhWsDAK+1NMUMRLhDhDvkcIcIdwAqIYc7AOwAsEOEe7Mi 3GloLFGIcAeAHawziHCvhikJwamYNNsdnvnza//wzMRLmuRLEBsvKzDV2uIhAM4CZP5Q4rJrsfHS mJVQKZSVlgmk62Di8gCgPklkkTleXAYBKQhqY6PVRr1jeeM7pDbWHo9v8nh85Vjx+AaPxzcg4zzE 40M8fk/dBeIkj4dzF9QXGQYJccBdsIe7QBziB/H44C4AdwHYfeAuAAAM/AXgLwB/Qav8BbQjkfy9 gslOuwdAXm1svbtAB7UR3AWt2gPQab/ptfNvThTh4gfMcW979txZe87KRZOUvPL/Xy6tFA7pKTLS ardQL9WtEhvDokRAAtNwZ6x/7HFly61HGxs3gBUKYzvM2NqXYyHa09F2TEeFj3M/MB0dfPltNVIq YNv1ZgqFXDMgBzuD1VChCxhC4XseCs9tV2oCtA3QNojLzAZbCqHwbQ2Fh5AIwLZBDkJIBIREQEgE JDcEvfGoIREqhET0ICTi2GgjpEUEtRFCIiAkomEJFdUjJ1SkiWRrPUyoWBk9gISKkFARTizqS0JF xYKEipBQERIqPhncOro7AKLpwK4DdwAc3tEhTB8OLAJMv1sHFnUlLWIbj/g4LTQvY7AqEAgMKlwb TizSO7YhooXnrzU8RSFvTzP7nqKQQIpCSFHYVwCeW/rUOBYAT4UB1y1NUYghRWFrAfgciAtSFB4R gOeCn1rHBuBNsN7AegMAvtdgVB8ReIiqBwS+yQh8SKZ6R6LqWwhZVQbg6zpluyoAT9sqysQh06DC dQ6A58YNtroSG98Cf2KudLG0MU4JF5Vk14xD8Fgq+WkKDE8sFOMuH825+eJ67vxm5rJecUg8BplX FZAWHZupIbB+5WX60GQyfUgNZLL87j7i8s6XsNgWkQvmZOZ4xWBd9vHUbJCxoZV/LaIwS016NgRM mJMFUJLt7IUdjMGnbryj7qIJTORAjJYg9bnZWpG0gBiU0IzcPlrydU43LCDiVC+iqjHNl4RKytzJ 6UYx9atMyShfby3QDrCinY6BT0T9Rfi0Kk39+9B9dYo/PMkZ9ZKc6JuhcoFrWb0jOZHE6THJ0eRh XzWRnKAl1eBCTu8dxdXzie+YkNvuX6iJ4oiZqxgTtXckJ4KpQreV0m2Ko6I02nrSY1dAculE3nPb m9rpRk2t/AMbIj66TNLr7WQFsMAlekkz/crSEZue5aMzmy4nO/t/dy/tW04TiK7nu29kjLAMsON3 m5ym2xXiGYTdxsVUwHo33cxm9sK9zZAfMcopIXR/UvIUSvAf+rz0YQZvs/BrvEAEW2Os6Wi+ev6M //f/UEsBAhQAFAACAAgANLY1NDooZKSSGAAA4lsCAAoAAAAAAAAAAQAgAAAAAAAAAHBncGVyZi50 eHRQSwUGAAAAAAEAAQA4AAAAuhgAAAAA ------=_Part_7951_5594348.1137876923424--