Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 9298D1337B39 for ; Mon, 28 Feb 2011 05:38:14 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 64394-10 for ; Mon, 28 Feb 2011 09:38:07 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mimolette.dalibo.net (unknown [212.85.154.222]) by mail.postgresql.org (Postfix) with ESMTP id 56FF41337B49 for ; Mon, 28 Feb 2011 05:38:06 -0400 (AST) Received: from marco-dalibo.localnet (gev44-1-78-228-108-65.fbx.proxad.net [78.228.108.65]) by mimolette.dalibo.net (Postfix) with ESMTPSA id F2780D83D8 for ; Mon, 28 Feb 2011 10:38:05 +0100 (CET) From: Marc Cousin To: pgsql-performance@postgresql.org Subject: inheritance: planning time vs children number vs column number Date: Mon, 28 Feb 2011 10:38:04 +0100 User-Agent: KMail/1.13.6 (Linux/2.6.37-ARCH; KDE/4.6.0; x86_64; ; ) MIME-Version: 1.0 X-KMail-Markup: true Content-Type: Multipart/Mixed; boundary="Boundary-00=_8z2aNV2lKzIcySr" Message-Id: <201102281038.04945.cousinmarc@gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.095 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RDNS_NONE=0.793, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201102/522 X-Sequence-Number: 42660 --Boundary-00=_8z2aNV2lKzIcySr Content-Type: multipart/alternative; boundary="Boundary-01=_8z2aNmXwKAyhRwc" Content-Transfer-Encoding: 7bit --Boundary-01=_8z2aNmXwKAyhRwc Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi, I've been facing a very large (more than 15 seconds) planning time in a partitioned configuration. The amount of partitions wasn't completely crazy, around 500, not in the thousands. The problem was that there were nearly 1000 columns in the parent table (very special use case, there is a reason for this application for having these many columns). The check constraint was extremely simple (for each child, 1 column = 1 constant, always the same column). As I was surprised by this very large planning time, I have been trying to study the variation of planning time against several parameters: - number of columns - number of children tables - constraint exclusion's value (partition or off) What (I think) I measured is that the planning time seems to be O(n^2) for the number of columns, and O(n^2) for the number of children tables. Constraint exclusion had a limited impact on planning time (it added between 20% and 100% planning time when there were many columns). I'd like to know if this is a known behavior ? And if I could mitigate it somehow ? Attached is a zipped csv file containing the result of the tests for constraint_exclusion=partition, for children from 100 to 1000 in steps of 100, and for columns from 10 to 1590 in steps of 20. A few values are a bit off-chart as this was done on my personal computer, and it was sometimes used for other things at the same time. The tests were done with a parent table made of only integer columns, and every children having a check (col0=id_of_child) constraint (I can also provide the perl script, of course). The test query was "SELECT * FROM parent_table WHERE col0=id_of_child_0". Replacing it with "SELECT col0 FROM parent_table WHERE col0=id_of_child_0" didn't change the planning time significantly: it was around 5% lower, but still O(n^2). This query returned nothing (every partition is empty). I've also done an openoffice spreadsheet graphing all this, but as it's 130kB I won't send it before being told to do so :) The computer running the tests was an Intel core i7 870. Postgresql was 9.0.3. Anything else I could add ? Cheers --Boundary-01=_8z2aNmXwKAyhRwc Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: 7bit

Hi,


I've been facing a very large (more than 15 seconds) planning time in a partitioned configuration. The amount of partitions wasn't completely crazy, around 500, not in the thousands. The problem was that there were nearly 1000 columns in the parent table (very special use case, there is a reason for this application for having these many columns). The check constraint was extremely simple (for each child, 1 column = 1 constant, always the same column).


As I was surprised by this very large planning time, I have been trying to study the variation of planning time against several parameters:

- number of columns

- number of children tables

- constraint exclusion's value (partition or off)


What (I think) I measured is that the planning time seems to be O(n^2) for the number of columns, and O(n^2) for the number of children tables.


Constraint exclusion had a limited impact on planning time (it added between 20% and 100% planning time when there were many columns).


I'd like to know if this is a known behavior ? And if I could mitigate it somehow ?




Attached is a zipped csv file containing the result of the tests for constraint_exclusion=partition, for children from 100 to 1000 in steps of 100, and for columns from 10 to 1590 in steps of 20.


A few values are a bit off-chart as this was done on my personal computer, and it was sometimes used for other things at the same time.


The tests were done with a parent table made of only integer columns, and every children having a check (col0=id_of_child) constraint (I can also provide the perl script, of course).


The test query was "SELECT * FROM parent_table WHERE col0=id_of_child_0". Replacing it with "SELECT col0 FROM parent_table WHERE col0=id_of_child_0" didn't change the planning time significantly: it was around 5% lower, but still O(n^2). This query returned nothing (every partition is empty).


I've also done an openoffice spreadsheet graphing all this, but as it's 130kB I won't send it before being told to do so :)


The computer running the tests was an Intel core i7 870. Postgresql was 9.0.3.


Anything else I could add ?


Cheers

--Boundary-01=_8z2aNmXwKAyhRwc-- --Boundary-00=_8z2aNV2lKzIcySr Content-Type: application/zip; name="result.csv.zip" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="result.csv.zip" UEsDBBQAAAAIAOd0Wz5FMTDXJBMAAAIrAAAKABwAcmVzdWx0LmNzdlVUCQADAVRqTddfa011eAsA AQToAwAABGQAAABNmuuuLTkNhP+fh9nqXJ1oHgahYQQjDQfE5f35yuVeG6SBYXl3x3HsqrLTv/7t 9z/+8q/ffv7y6z/++O/ff/77l3/+8eefP3//+dc//ef3v//2oz3PL+355fl6ntbi2WP1PsY5O9I0 bNJP0WO01fe+I03LpjHuvL3t/uyzz0pT2DTvPfuZc+11xt5pujbt1jvGGWvzRr+wlR+xG0+N5z6n P6PZZkfa09Z97mr9jIh6zI60/hw20Nfin14mO9LmHo0Xjuc8fdYL7UiLaM/lx3nWunax2w/Wvuu2 GW2s2RyObjd654XPc2e7wZ/YZDf6mkfGwZ/GnTbZjX5iz4txEdBZJrsx+n5unGf3S0iuQ283xoqx R+/z6XeV88Nu4N+M23EnnvUcm+zGnOdyXMFBcqI+lWE3tDpPjtbubaOO2W6ss0mC9pyNIxXDaTfw /CwCEYSqlfPTbvBArGcS/3P69lrTbkTE5Ql23jmBMtmNO6LNu/rcB6v3Ne3GuYQjsHOo5eCyF3jM yfdFqProZUovcLrH3O3EmeuUJZ1gnz33uvshx21JHxohJkfXc5vy0JZ0gT2ufcn0w2rOip0etEWS Ee245EdzyLcd0BmQ0w+rNodue/2jM9x37r7OcuS2179ka4zBWV3eZ0uu38mtywsniXe7l4l0oCvB xz4qj4pa5Pp9nEvtTGXlW2yRDhAukocalAfdDkQ6QMTOVgqfiLdmIh0YYygjx72Dg7JrJx0YWX53 6H97VcwZfqaRQVQAj5IStqQHYxL9iDlHo+btwUkPlATEnuBR7dv7OfbgshFCt9skqk6dmx7MBs6o moGqvf22mx7M2UGoS7DJ/lmAkx5MQCbIXzlNrduSHpBzk8hgGotytiU9WO3cOeP/C4+MTAshfs7E hbbeEqLk07Sp+r2jT2osVpnSh/0cheD0s8jVXaZ0IlPj7D4jANJZpvRikyIA4rgdqCqLkXIf6gcc PJNzqlxsBsrY+IGLVAUpf8uUXhAXwF12qnO/L0wv4rQt9MKTiUNlSi9weFHLlDhM0J0PzUAJcIoC KDAO8NZTBkpOsmnXOHrJgTKlG6Q+WYf/vBM/y5RuXF4DbBCZQ+EWkgsoG+SgXCZTwIWCSRWxLCAe NcuTZ7+uCyaxnAS8DiTd/VqWLISRfImpLUWdokASC5UPHay9SYKXgdKDRFs2OlnwDexMD/qeA86I QZq1Ot2ZHoDDvA6TfK91ZnqgUAO2hHD3d6czPaDEN8S5qSeCUZb0YE6dDk/ORY4VBaYHk/Ps1D54 9ubrSgeESJzfmjtmoUlb6cBWdPZDXNt5Dbn+HqcN6huiOG+Gr1xf6QOSBMhw9o/+rR+Acugr9QC8 M9NU+oHNC9Go3yn8TFPph3NEH0T7oWq7nyraZmVFYOGL3O4f+QD0PpN/jgRG8+tKPXT+niRoV9A5 uk1Fl/zSBm+aokavVOJhsn+CBwFTW1BY/xYP8B7SQkW2hB02vXQ5SHjyDdIPL1XaIboU1ENhWFb0 b+3AD6RBSAGR/F7KJQFMjDnvgOKSwvpHOuDAlgyYCw1WkbVy4BgJAI6NqVRxzM1Ueykdr1Ji1GGY KDgopMlVzOPUIyYKMWu7FjXNrlk09B06BaoY/qvQWTMIbftYUj2LwKfFkgFag/klGY5eaouJAhx8 lERLqeyNWjBQQRzCVA0QA3tgvaCfl7g1mujHFsM0h8BJkzsAR+3UcoEggkkhOQuAeqdWCyQqmQX7 3QH/2Terhc17oH6EJDQx621Gx0lUkDFEmoQqS4EjoA24cEq7ksdq4WoraGrwhkjbaasFkIjceLRR GMEJslcBmUIDXHJsjs02JiGy2Rf5sSX2eokFgdWC1kEDUrfOM4xIiDiIEb7BAa8eLyDhoA4BZqkn cnFkj7T/Fvm/r8rFqYJBxYmJe20kjAYkGRhOMpEZ3vvJxQn9JGcPDmO1IRcnwDy0hKzP9M5PLn50 JFL4YvF6VS7OaUgikUdonnoVi/cvsfBij+w/KislETr5KrlD8g+S078P/U76SjfAqCSal5A+6Gyc qoAF4GxCYEPIoHYINXZQfFSNDbl2cAz0DWodSPzCwVwcLdXICAIoDC9LLg+rkFYgq+C7UEbaYIBb F50Ho6uQC7UkDcYXbjUCBwQ8asr6qwzG15QEZg2Id1S8UhkMSoJwkVowGF1ZWYYshKVPnOOFrb/A mR5A/mQqTIDiauVBSw+OCot05A8kefurCsYXgL5yCejrOGopCmYKWKQllaIeryxDliHJBJwIHF8Q Fv7Nr6VMoTu54MYtD4R/WKgUjhOAE36X5cpyQz0r9Y+yqjpOQTBVe31fkSfJXl4LANeXsp6Oiwit c16GWLJw/qTlkrroBbMpCBb6kSPrJDKJ8p6pAHB9gWD8P/aPxH19EwAuIooPQ91U75WFKQgWeUAd Tik7WcuCB5taFm5fmmzO4ZYlZEE6L71qpfAty5UFnIcZlJ7n45sAcCMDj7QoSSHpXZYhC2RMR8JZ rPsWQiqCQN/T3hFV3tc+lpCFiAkzQDTi9L7tyjIl8R7Vg/Lrx/jWBCPE6xvIhHCA0/GtCejjCW7A gvwYJ03VygNAyBikh7A9DdVB7yk4gYLWRoKnpRpoFiGxr+Bbinx8KwJaU/iMcpgSm36oFIG44SFP 4HUaO69UiuDQLWAWtenfbIrqJ8lHJSlePM2W6hpDxQQyELnjlSwIoKElfZyd/OMI9Wrb0Ey4x+Fe YmGLm6am6QPYgmx5Rr3NTdOUEiBG0hflm+XAlJSi8cBFYupnLAdg4TmCvr4JfusgqmPREASOgnI4 RFvMhUMEhiYBLOFKW4oLkVEwEZ37Y59HMaHAPnQ4yCbHxmoAVUHmqEyb2GuUGGiamDwX1cpuEKg2 WJ1Df8IWQfOoJ5IOUFubxHoApF17tDIGUB71f2AQ3aMTKrmI/+45n6JKtv21MFZi0qlJtyKkbMjF kfgSDbCd5gA2mIt2yzHTljr1Bpe5qAvuSUJO5trdbS5aoiEyF40UfmIPcw6Vu+gicddO7eQiQIbV AWxq5jiVd3IRrAUk08rARq8h1+bAOWUoUHToekkeEEUJ6x4pYsc2kgZEHBrAgUK9fk8SkL7ij6H/ sytNIzmAQhlCc3ofGjMbTAEa1A2lEGDmQJ3EX5EWahYiUuNjgwngZCtZwGhD4j9MMRT3Gm2M4v8J AUFokIRazNeQ2PuIlPhX9SlVBzehF7nT4iCVKSA/cBN4CQY5KEBEAHvtm8h/sp7xlvp4HJGbsNs0 J+PoeVOv8ryJujRbbDERAhwpzEvU5QwgJIn7uL2QQ/wf5DpOdU1DcoY2Xv4HW3lV01BU0Xzflqir yQSZKLh82mtJ1FVaLd6lf6LeJsw7CD/Nc9RWarpWliHLyiHY0tCrV8In/x+81lwLuU5KrVUWPLhw +dohIh1S+WW5smzapkdzJ3Ze1Z783xJbYRY0I6Lux/wmBMqJMqXmlZfw2PwQglhTXWIId1cavkeq oPpWaWsQMz98AFzRflBCHkukpVozHSUkgjBkKb+t+ODEAGCkwo7ozyaP8LrmgBpyg4Yw9vzQAYFA /LC+2j3yf37YAPzuSaVUuHTg/LDBUEuAOuMB4CINJgMVADihkfpBNNsyqskZmiIgX9U42bLcymiq xxtpAgiSLYZidSoIswG6A8e2GIxZYRBkUC/8hKngar4DtiGnjpqfWVQA5MIzdD+gEsTr3xMNxfc0 oOiCg+awwW3BUFOsoQL9dhkSipdyYilxdne4PCShDWIJSW3+8QOmAbXsXe+nMId3MRMNm3pp6uKK BRySmWg4yMfk590lvGbRQKe5o5ChSLgwup9YicQasCEkDyUQlUcr4RBpes7RMYo5bEg8HIodXL40 V6ycTDzcRJvCAxPCR7tSjkq6TGEqTteLtvUw0P6oIclubxYHTDWvUkIaCt73gQRDDo+3h1pBNdyz SGCB6WgFulealeOfEwqXVNujVre3t0aaAewijTR6AVu9QqQEzOEA1UlksdqQGpRO7vYcG6D3ypBY yMK0i0o0EtnRiEQiOiR+QbB1TT7TIBKIr6CiQtSgOY/z4CQOpYATbocmxjYkDKFH81dILgoeTqKQ JkenCwR7ocNJDJrSnPASb0PXGwKaDEcyH1e7cs4bFwm0HC1nu74AuB/rG5vo2zjG0MrAyk5TYZPm +s9QnVHSIy2rrmeEQTRgXfO6k5a6Z9FMC0E6c+610mJwYm9dchWOP0+9zeAEyRAY8lC9Hoi6Ptik 2y3Qh8Qmg699MzZplDFJFwWUbtWWqBE6OcoJSxYM+2ZsWmoCl/QKQp/grA84bU0rQo6BNNPhqWFu 18XaI6ksqLHFs1zlC3rohOi7nvHM4qwcXIcQf9pgqYbWa9mHSg040s3zBLWhg2KHPe2YsekMyIf+ nAghnGxIgHg0eoYL4WgNk1aBUzb7ujajZZnqSlaBEwABhoIaV3cctfmZAHGFM5rbR47ZVqETTTjB UasZoaGWDRZMQ53pVtTQPza4aWZJ0g8oV6NrQ0KExkua/YhO60xWQgTBQHtACjhQx7jcr6Iymrqb 83kgNYvanZVD067h7Spwkphp0OjW8NibWKlY4JWjAdZRN+2/3xYs0lFX/diD9LRhWJUcFpkaCRJg G1KuQFyaTqHDBf02RCoPabRHBg39bbgyhAZEcEEIAe1VZJlmgyHW3bVCuEiptH47CK4Z/Y/9XaXQ AwpBg0OEyUxL3cpOBDZqYLBv0DEtNd4NMT1gvrKhSYuLVGjZdfOzd+q7/RapLn31psBdwmwH6gZO 5xdiXF0b2eK5JsUbUiMknHTn/tToFC2ocZ9SBXa61c2PbuugGd1StG5LehBABBm3NXoGYtLSfdeh eg5N9uA3P9KHOyNNcdAQY7byrC9fAOT8akpbD+/fBUr/LR4DP4e06q4Cpao4eBiZs3qU8bsKlK5F IhY5SjzBDhuynZE8wI2neuldBTq+BP24qyK59nZkiZD2kJMmKxpU25A9hXoPgBO2oSO2YZpFOfYN 4JPITx3wTBpVPzrhmamL/GODJzokKTjiO17vb2aN4Dvq4YrFnzoqT1PgNzBp62IBSewkcpXkTShh FzV7HyurhATp+jZCMn066h6l0GJoPnfUaYUjsrJKKPUpXbbFdfVEkhmIuKirUFvhLNlZJDQUZFZL 1hpee7tMdKNBocCLElrxKRMhOCd6NQMHn9NS1yCQOvujQCmuZYvLRGML2iMKdeqrgrTUyEPDdRit oUbqEVfJXfkqwO9IZManSoauyVlLslHQH58qgd5APrT7o5nVscXzhq0CJq0hRf5jSzoADmnufKU3 dZsRVSVNN/Wa2pKn1IIdcJHwp0tTUMgW8LIhiyQlwcrZdPceu4WmEmtpLE4L0mxIHtGVlZqVmf17 fEoEXzstmPAf6eoIN3fXKYVgGRpa/54sAiw9uhzW1WFtfKTYG4I5DhDdfSq8w53v0L28huiaFdiQ cg/fqVm071brlQYPHTUNf9T7hTSRDSn3tnAUqU5AvMJ046mebpNfU82FDdl3cja6N93tqvW3IZFc km3qS5GpgZHTJ5NUQnORJXRvUbmwnKRNjY6+YVGG/TjfSYoYnls1GJr/pKWSVB+HULpAomouLe8n JegD3e95eJkWJ6kaPXRr5Kx4pMFXVMgZKkane/WZx/kkqXJka+igy4XHjzhJt6Bdt9a8S0l6PknK 36Kehi6npZJt8VxKSoQgSKW+BjdDeUOhKafmvWlwjuqbENJBggoVaUPdU+iCkOrZulqzIZNUnVDk XYHYx4YEUyG+pDya7qlIdoOpSk03ISGEdIgTTIEztijd2J/63XJDbCkFIe/slKfjYOLUpbCI79ip kX2BLi2E+Vf9iqM4si94NHbl4KXZyqmZfQFKQHMblZQuAU5lKTKB/pNDarq/WX6V8vR+CUd4vyox 3icikwvlDcSrK9EnS/c7uSSodLetdo9jv5/kQm88z+dbkJkWJ9cl2bQPIiOpn5b6VIfj1q0oZ8VW 0+AuH/HN/1lZwVTi/STX0sXo0UD26m7/fnKLRoejULJKAIctRdPRDseIMtZXG/eTWmultifx1ru6 U+sCyWpB6E5q8W6WDn2DQ8mRSP77nhD0RHLV1qDh1gNJ0prg6WJY3ZR/Dt/W3ESASM6wwRcveVeG FpN6cAhHAhAanUaNHHLh3sorsqTpRnDoyvEpn4YhaEvRDWXoqdMYJSYRn/rcKwfBNiRN5qdH6qYp wmXDLJ5sCA1dui51+LfyqmmaPHJsDP7lRzlvlqT/+BRLnzxtm5wmS32IRrCg1OMvRypPRIch9cAp 1acWlSe60x+aTbe837HlFu2RPVMN3qqvdd5MYXfiu3NS3cwy1ccrap3hal77jLJUrmQa68uVc1+L R9RHXyYABU3f0JUlGWvlF3f5HcTy5zjOF02KDd0gxfFHUG/GnLyuvHppPLUh39eh8TUP0kc0QFJZ UlyRLhJ9Gvad/T6T6krfCGl0Md4HRiuguDqAoUuXis3woEAzXA6VOn1el0ciAit0zZYFRp+3GRI4 1KZhdX6v+ON/UEsBAh4DFAAAAAgA53RbPkUxMNckEwAAAisAAAoAGAAAAAAAAQAAAKSBAAAAAHJl c3VsdC5jc3ZVVAUAAwFUak11eAsAAQToAwAABGQAAABQSwUGAAAAAAEAAQBQAAAAaBMAAAAA --Boundary-00=_8z2aNV2lKzIcySr--